pgduckdb_get_querydef() ignores certain type conversion

[dpxcc]

What happens?

Certain type conversions are ignored by pgduckdb_get_querydef(), resulting in the query not runnable by DuckDB

To Reproduce

SET duckdb.force_execution TO true;
WITH t as (SELECT chr((126983+i)::int) FROM generate_series(0,26) t(i)) SELECT * FROM t; -- warning

throws warning

WARNING:  (PGDuckDB/CreatePlan) Prepared query returned an error: 'Binder Error: No function matches the given name and argument types 'chr(BIGINT)'. You might need to add explicit type casts.
        Candidate functions:
        chr(INTEGER) -> VARCHAR

because pgduckdb_get_querydef() ignores the type conversion in (126983+i)::int

OS:

Linux

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

0.2.0

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

17.2

Hardware:

No response

Full Name:

Cheng Chen

Affiliation:

Mooncake Labs

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

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

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

[Y--]

I looked a bit into this: the problem doesn't come from pgduckdb_get_querydef ignoring conversion, but from PG resolving generate_series using int4 types, thereby rendering the int cast obsolete:

SELECT
  p.oid, proname, proargtypes, typname, prorettype, prosupport --, t.*
FROM pg_proc p
INNER JOIN pg_type t ON t.oid = p.prorettype
WHERE proname='generate_series';

 oid  |     proname     |    proargtypes    |   typname   | prorettype |          prosupport
------+-----------------+-------------------+-------------+------------+------------------------------
 1066 | generate_series | 23 23 23          | int4        |         23 | generate_series_int4_support
 1067 | generate_series | 23 23             | int4        |         23 | generate_series_int4_support
 1068 | generate_series | 20 20 20          | int8        |         20 | generate_series_int8_support
 1069 | generate_series | 20 20             | int8        |         20 | generate_series_int8_support
 3259 | generate_series | 1700 1700 1700    | numeric     |       1700 | -
 3260 | generate_series | 1700 1700         | numeric     |       1700 | -
  938 | generate_series | 1114 1114 1186    | timestamp   |       1114 | -
  939 | generate_series | 1184 1184 1186    | timestamptz |       1184 | -
 6274 | generate_series | 1184 1184 1186 25 | timestamptz |       1184 | -
(9 rows)

Hence, when the query is deparsed with pgduckdb_get_querydef it doesn't contain the cast anymore.

One way to force it is to forcibly cast i:

WITH t as (SELECT chr((126983+i::BIGINT)::int) FROM generate_series(0,26) t(i)) SELECT * FROM t;

We'll try to find a way to preserve the original casting eventually.

[dpxcc]

Thanks for looking into this!