duckdb
pgduckdb_get_querydef() ignores certain type conversion
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?
- [x] Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?
- [x] Yes, I have
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.
