In some instances compiled SQL is incompatible with duckdb client

[roveo]

Twin issue of duckdb/duckdb#4314, it was suggested to continue here.

To reproduce

import duckdb
from sqlalchemy import func, select, create_engine, MetaData, Table

# setup
conn = duckdb.connect("test.duckdb")
conn.execute("create table test (dt date); insert into test values ('2022-01-01');");

# query
engine = create_engine("duckdb:///test.duckdb")
metadata = MetaData(engine)
metadata.reflect()
test = metadata.tables["test"]
part = "year"
date_part = func.date_part(part, test.c.dt)

stmt = select(date_part).select_from(test).group_by(date_part)
engine.execute(stmt).fetchall()

Compiled statement:

SELECT date_part(?, test.dt) AS date_part_1 
FROM test GROUP BY date_part(?, test.dt)

So, from our discussion, I see several possibilities:

• I just shouldn't generate queries this way with sqla.
• There is a bug in duckdb library itself, I guess the consensus is that this is not the case.
• The compiled statement is incorrect and it's fixable from within duckdb_engine.
• The compiled statement is incorrect and it's only fixable from within the core sqlalchemy library.

[Mause]

As of #836 this is now fixed for SQLAlchemy 2+