Bug: Time interval "CAST((NOW() + INTERVAL '-40 day')" leads to error

[BigBerny]

What
Running this query results in a warning that query can't be pushed down and finally crashes after 20-50 seconds.

SELECT *
FROM parquet_predictions as predictions
WHERE
 "predictions"."date_day" BETWEEN CAST((NOW() + INTERVAL '-40 day') AS date)
  AND CAST((NOW() + INTERVAL '-1 day') AS date)

This is the error I get:

WARNING: This query was not fully pushed down to DuckDB because DuckDB returned an error. Query times may be impacted. If you would like to see this query pushed down, please submit a request to https://github.com/paradedb/paradedb/issues with the following context:
Binder Error: No function matches the given name and argument types '+(TIMESTAMP WITH TIME ZONE, INTERVAL)'. 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
+(UHUGEINT) -> UHUGEINT
+(UHUGEINT, UHUGEINT) -> UHUGEINT
+(DATE, INTEGER) -> DATE
+(INTEGER, DATE) -> DATE
+(INTERVAL, INTERVAL) -> INTERVAL
+(DATE, INTERVAL) -> TIMESTAMP
+(INTERVAL, DATE) -> TIMESTAMP
+(TIME, INTERVAL) -> TIME
+(INTERVAL, TIME) -> TIME
+(TIMESTAMP, INTERVAL) -> TIMESTAMP
+(INTERVAL, TIMESTAMP) -> TIMESTAMP
+(TIME WITH TIME ZONE, INTERVAL) -> TIME WITH TIME ZONE
+(INTERVAL, TIME WITH TIME ZONE) -> TIME WITH TIME ZONE
+(TIME, DATE) -> TIMESTAMP
+(DATE, TIME) -> TIMESTAMP
+(TIME WITH TIME ZONE, DATE) -> TIMESTAMP WITH TIME ZONE
+(DATE, TIME WITH TIME ZONE) -> TIMESTAMP WITH TIME ZONE
+(ANY[], ANY[]) -> ANY[]

LINE 4: ...*
FROM parquet_predictions as predictions
WHERE
"predictions"."date_day" BETWEEN CAST((NOW() + INTERVAL '-40 day') AS date)

Why
Metabase filters data like that if you select "Last x days" and this can't be changed there.
The same query runs fine if I call it directly via DuckDB.

[rebasedming]

There are a few issues I'm working through before I can get to this. In the meantime, casting now() to timestamp, ie now()::timestamp will probably solve since the error message says that (TIMESTAMP, INTERVAL) -> TIMESTAMP is allowed.

[BigBerny]

Ok, thanks for having a look 👍 Unfortunately Metabase (and probably other graphical SQL IDEs) generate the sql query automatically based on what the user selects in the date widget. So there's no easy way to cast now() to timestamp when using their graphical interface.

[rebasedming]

Okay, I have a temporary workaround for you. Just run the following:

SELECT duckdb_execute('INSTALL icu');
SELECT duckdb_execute('LOAD icu');

And the above query should work.

[BigBerny]

Thanks, Devin 😄

[philippemnoel]

I am trying out Devin haha!

[BigBerny]

Okay, I have a temporary workaround for you. Just run the following:

SELECT duckdb_execute('INSTALL icu');
SELECT duckdb_execute('LOAD icu');

And the above query should work.

When do we have to run that? Only once or every time the server starts or for every session? I just got the same error again and had to run the commands.

[philippemnoel]

Okay, I have a temporary workaround for you. Just run the following:

SELECT duckdb_execute('INSTALL icu');
SELECT duckdb_execute('LOAD icu');

And the above query should work.

When do we have to run that? Only once or every time the server starts or for every session? I just got the same error again and had to run the commands.

Until we merge this fix, it needs to be run in every session as a workaround.

[evanxg852000]

@rebasedming what's the consensus for resolving this, auto-installing the icu extension?

[rebasedming]

@rebasedming what's the consensus for resolving this, auto-installing the icu extension?

Ideally we would have it already loaded in the embedded DuckDB provided by duckdb-rs. If that's prohibitive, I think we can use my caching PR to have it installed once when the DuckDB database file is created.