Certain queries with (TIMESTAMPTZ + INTERVAL) operation fail

[naoyak]

What happens?

I have a DuckDB table with a TIMESTAMPTZ column (time) attached with pg_duckdb 0.2.0, and certain queries fail when the WHERE clause filters on a (TIMESTAMPTZ + INTERVAL) (addition) operation. This doesn't happen when querying the same data in DuckDB and only happens if an actual table is referenced.

To Reproduce

Example 1

Query:

select 1
FROM
"ts_public"."measurements"
WHERE  timestamp with time zone '2024-12-10 13:59:59.776896+00' < CAST((NOW() + INTERVAL '1 day') AS date)
LIMIT 1

Output

ERROR:  (PGDuckDB/CreatePlan) Prepared query returned an error: '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 1: ...0'::timestamp with time zone < ((now() + '1 day'::interval))::date) LIMIT 200

Example 2 (success)

Query

select  timestamp with time zone '2024-12-10 13:59:59.776896+00' < CAST((NOW() + INTERVAL '1 day') AS date);

Output

``` ?column? ---------- t (1 row) ```

Example 3 (success)

Query

SELECT "time"
FROM
"ts_public"."measurements"
WHERE  timestamp with time zone '2024-12-10 13:59:59.776896+00' < CAST(NOW() AS DATE)  + INTERVAL '1 day'
LIMIT 1
;

Output

``` time ------------------------------- 2022-08-31 23:51:18.165404+00 (1 row) ```

OS:

macOS & Linux

pg_duckdb Version (if built from source use commit hash):

0.2.0

Postgres Version (if built from source use commit hash):

17

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a nightly build

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

[JelteF]

It seems that somehow this has to do with us not installing the icu extension by default in pg_duckdb. After I run this command these queries start working for me:

select duckdb.install_extension('icu');

[JelteF]

@wuputah @Y-- @bleskes This seems like a normal enough query that I think it probably makes sense to install icu by default, or include it into our duckdb build. Does that sounds reasonable to you?

[wuputah]

Fine with me.

[naoyak]

This works great, thanks for the tip!

[bleskes]

I think our default build should be the same as the duckdb default build. It seems that icu statically linked by default so I see no reason to exclude it. I would double check the binary size before shipping (that's why these things are typically not included)

[JelteF]

Alright let's include icu by default then. I checked the duckdb cli and apart from icu it only has fts (full text search) and autocomplete compiled in statically. I think neither of those makes sense for us to include by default, so let's only change it to include icu for now.

[ritwizsinha]

How do I enable icu by default, do I make changes in the makefile and add the BUILD_ICU compiler flag?