[Bug]: Regression in sqlalchemy 2.0.45 in Duckdb reflection

[NicolasMuellerQC]

What happened?

Bug description

The following code produces an exception:

import sqlalchemy as sa
from sqlalchemy import create_engine
import pandas as pd

eng = create_engine("duckdb:///:memory:")

df = pd.DataFrame({"a": [1, 2, 3], "b": [4, 5, 6]})

with eng.connect() as conn:
    df.to_sql("test_table", eng, index=False, if_exists="replace")
inspect = sa.inspect(eng)
print(inspect.get_columns("test_table"))

Expected behavior
No crash, instead columns are returned correctly.

When reverting sqlalchemy to 2.0.44 while keeping all other versions, the code works correctly. It prints

[{'name': 'a', 'type': BigInteger(), 'nullable': True, 'default': None, 'autoincrement': False, 'comment': None}, {'name': 'b', 'type': BigInteger(), 'nullable': True, 'default': None, 'autoincrement': False, 'comment': None}]

According to sqlalchemy/sqlalchemy#13031 this is an issue in the duckdb engine.

DuckDB Engine Version

0.15.0 and 0.17.0 (problem exists in both versions)

DuckDB Version

1.4.2

SQLAlchemy Version

2.0.45

Relevant log output

sqlalchemy.exc.ProgrammingError: (_duckdb.CatalogException) Catalog Error: Table with name pg_collation does not exist!
Did you mean "pg_constraint"?

LINE 6: FROM pg_catalog.pg_collation 
             ^
[SQL: SELECT pg_catalog.pg_attribute.attname AS name, pg_catalog.format_type(pg_catalog.pg_attribute.atttypid, pg_catalog.pg_attribute.atttypmod) AS format_type, (SELECT pg_catalog.pg_get_expr(pg_catalog.pg_attrdef.adbin, pg_catalog.pg_attrdef.adrelid) AS pg_get_expr_1 
FROM pg_catalog.pg_attrdef 
WHERE pg_catalog.pg_attrdef.adrelid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_attrdef.adnum = pg_catalog.pg_attribute.attnum AND pg_catalog.pg_attribute.atthasdef) AS "default", pg_catalog.pg_attribute.attnotnull AS not_null, pg_catalog.pg_class.relname AS table_name, pg_catalog.pg_description.description AS comment, NULL AS generated, NULL AS identity_options, CASE WHEN (pg_catalog.pg_attribute.attcollation != $1 AND (SELECT pg_catalog.pg_type.typcollation 
FROM pg_catalog.pg_type 
WHERE pg_catalog.pg_type.oid = pg_catalog.pg_attribute.atttypid) != pg_catalog.pg_attribute.attcollation) THEN (SELECT pg_catalog.pg_collation.collname 
FROM pg_catalog.pg_collation 
WHERE pg_catalog.pg_collation.oid = pg_catalog.pg_attribute.attcollation) ELSE NULL END AS collation 
FROM pg_catalog.pg_class LEFT OUTER JOIN pg_catalog.pg_attribute ON pg_catalog.pg_class.oid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_attribute.attnum > $2 AND NOT pg_catalog.pg_attribute.attisdropped LEFT OUTER JOIN pg_catalog.pg_description ON pg_catalog.pg_description.objoid = pg_catalog.pg_attribute.attrelid AND pg_catalog.pg_description.objsubid = pg_catalog.pg_attribute.attnum JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[$3, $4, $5, $6, $7]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != $8 AND pg_catalog.pg_class.relname IN ($9) ORDER BY pg_catalog.pg_class.relname, pg_catalog.pg_attribute.attnum]
[parameters: (0, 0, 'r', 'p', 'f', 'v', 'm', 'pg_catalog', 'test_table')]
(Background on this error at: https://sqlalche.me/e/20/f405)

Code of Conduct

• I agree to follow this project's Code of Conduct