duckdb / duckdb/pg_duckdb

PREPARE statement causes an error in ConvertPostgresParameterToDuckValue for NUMERIC DuckDB type

Open
#891 0 comments 2 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.