PREPARE statement causes an error in ConvertPostgresParameterToDuckValue for NUMERIC DuckDB type
- Dominant language
- C++
- Stars
- 3.2k
- Forks
- 204
- PR merge metrics
- No merged PRs in 30d
Description
### What happens?
pg_duckdb reports an error
`ERROR: Could not convert Postgres parameter of type: 1700 to DuckDB type`
This comes from
```
duckdb::Value
ConvertPostgresParameterToDuckValue(Datum value, Oid postgres_type) {
```
because the type `postgres_type` equal to
#define NUMERICOID 1700
is not considered in switch operator and reaches the default case (with the error).
### To Reproduce
Add extra parameters in postgresql.conf:
```
shared_preload_libraries = 'pg_duckdb'
duckdb.force_execution = true
duckdb.postgres_role = 'duckdb_group'
log_temp_files = -1
duckdb.threads = 1
```
Execute following queries from `test/regression/sql/query_filter.sql`:
```
CREATE TABLE query_filter_float(a FLOAT8);
INSERT INTO query_filter_float VALUES (0.9), (1.0), (1.1);
```
then do
```
PREPARE dbg_numeric_select(numeric) AS SELECT COUNT(*) FROM query_filter_float WHERE a < $1;
EXECUTE dbg_numeric_select(1.1);
```
And it causes the ERROR
`ERROR: Could not convert Postgres parameter of type: 1700 to DuckDB type`
while
```
PREPARE dbg_float8_select(double precision) AS SELECT COUNT(*) FROM query_filter_float WHERE a < $1;
EXECUTE dbg_float8_select(1.1);
```
does not and works correctly.
### OS:
Ubuntu 22.04.5 LTS, x86_64, gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0
### pg_duckdb Version (if built from source use commit hash):
55874e3e0104d4bf11a6ec4d4d3923dd6cf8650
### Postgres Version (if built from source use commit hash):
2b79e12095351470370d422e291bbb3c929db2c8
### Hardware:
_No response_
### Full Name:
Mikhail Litsarev
### Affiliation:
Postgres Professional
### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a source build
### Did you include all relevant data sets for reproducing the issue?
Yes
### 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
Contributor guide
Research direction
Search for ConvertPostgresParameterToDuckValue and inspect its handling of PostgreSQL type OIDs, especially NUMERICOID 1700. Run the PREPARE and EXECUTE queries from test/regression/sql/query_filter.sql, comparing numeric with double precision parameters. Done means the numeric statement executes without the reported conversion error and the regression coverage passes.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 55/100