Support for DuckDB?

[TheCedarPrince]

Hi @xitology and @clarkevans !

I was wondering, would it be possible to support DuckDB syntax here? I had read the syntax was very similar to PostgreSQL, so I ran OHDSICohortExpressions with the model flavor set to PostgreSQL but I got an error that looked like this

julia> DBInterface.execute(conn, queries[3] |> String)
ERROR: Binder Error: No function matches the given name and argument types '+(DATE, BIGINT)'. You might need to add explicit type casts.
        Candidate functions:
        +(TINYINT) -> TINYINT
        +(TINYINT, TINYINT) -> TINYINT
        +(SMALLINT) -> SMALLINT
        +(SMALLINT, SMALLINT) -> SMALLINT
        +(INTEGER) -> INTEGER
        +(INTEGER, INTEGER) -> INTEGER
        +(BIGINT) -> BIGINT
        +(BIGINT, BIGINT) -> BIGINT
        +(HUGEINT) -> HUGEINT
        +(HUGEINT, HUGEINT) -> HUGEINT
        +(FLOAT) -> FLOAT
        +(FLOAT, FLOAT) -> FLOAT
        +(DOUBLE) -> DOUBLE
        +(DOUBLE, DOUBLE) -> DOUBLE
        +(DECIMAL) -> DECIMAL
        +(DECIMAL, DECIMAL) -> DECIMAL
        +(UTINYINT) -> UTINYINT
        +(UTINYINT, UTINYINT) -> UTINYINT
        +(USMALLINT) -> USMALLINT
        +(USMALLINT, USMALLINT) -> USMALLINT
        +(UINTEGER) -> UINTEGER
        +(UINTEGER, UINTEGER) -> UINTEGER
        +(UBIGINT) -> UBIGINT
        +(UBIGINT, UBIGINT) -> UBIGINT
        +(DATE, INTEGER) -> DATE
        +(INTEGER, DATE) -> DATE
        +(INTERVAL, INTERVAL) -> INTERVAL
        +(DATE, INTERVAL) -> DATE
        +(INTERVAL, DATE) -> DATE
        +(TIME, INTERVAL) -> TIME
        +(INTERVAL, TIME) -> TIME
        +(TIMESTAMP, INTERVAL) -> TIMESTAMP
        +(INTERVAL, TIMESTAMP) -> TIMESTAMP
        +(TIME, DATE) -> TIMESTAMP
        +(DATE, TIME) -> TIMESTAMP
        +(ANY[], ANY[]) -> ANY[]

I could give my whole SQL expression for what I am trying but am curious if there'd ever be interest to support DuckDB. Thanks!

[xitology]

DuckDB should work with FunSQL if you use SQLite dialect, but there's no support for it in OHDSICohortExpressions. If you want DuckDB support in OHDSICohortExpressions, please open an issue there.

[andreypopp]

DuckDB should work with FunSQL if you use SQLite dialect

probably PostgreSQL dialect though? they use PostgreSQL parser (with some extensions now)

[xitology]

DuckDB should work with FunSQL if you use SQLite dialect

probably PostgreSQL dialect though? they use PostgreSQL parser (with some extensions now)

Good point. When I tried DuckDB with FunSQL, I used SQLite dialect, but it wasn't a comprehensive testing, so it may fail in some corner cases of SQL grammar. I suppose, DuckDB deserves its own dialect.

[TheCedarPrince]

Ah gotcha -- I'll open an issue over in OHDSICohortExpressions.jl as well. Thanks @xitology ! :smile: