duckdb / duckdb/pg_duckdb

Prepared statement error: Could not convert DuckDB type: UNKNOWN to Postgres type

Open
#480 2 comments 3 reactions 0 assignees View on GitHub
bug
Dominant language
C++
Stars
3.2k
Forks
204
PR merge metrics
No merged PRs in 30d

Description

### What happens?

Prepared statement is still broken even after the fix in https://github.com/duckdb/pg_duckdb/pull/147
Minor tweaks to an existing test results in failure

### To Reproduce

Add a new test case `test_prepared_new()` to [`test/pycheck/prepared_test.py`](https://github.com/duckdb/pg_duckdb/blob/main/test/pycheck/prepared_test.py)
```python
def test_prepared_new(cur: Cursor):
cur.sql("CREATE TEMP TABLE test_table (id int) USING duckdb")
cur.sql("INSERT INTO test_table VALUES (1), (2), (3)")
q2 = "SELECT count(*) FROM test_table where id = %s + 1"
cur.sql("SET plan_cache_mode = 'force_custom_plan'")
assert cur.sql(q2, (1,)) == 1
```
This is just a simplified version of `test_prepared()` with the following modifications:
1. Use DuckDB temp table to force DuckDB execution
2. Change from `WHERE %s` to `WHERE %s + 1`

Running the test with `pytest test/pycheck/prepared_test.py::test_prepared_new` results in the following error:
```shell
2024-12-06 09:16:14.947 UTC [47236] WARNING: (PGDuckDB/GetPostgresDuckDBType) Could not convert DuckDB type: UNKNOWN to Postgres type
2024-12-06 09:16:14.947 UTC [47236] ERROR: (PGDuckDB/CreatePlan) Cache lookup failed for type 0
2024-12-06 09:16:14.947 UTC [47236] STATEMENT: SELECT count(*) FROM test_table where id = $1 + 1
```

### OS:

Linux

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

bb82c93

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

17.0

### 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 source build

### 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

Contributor guide

Open the contributing guide

Research direction

Start by running pytest test/pycheck/prepared_test.py::test_prepared_new using the reproduction in test/pycheck/prepared_test.py. Trace the reported GetPostgresDuckDBType and CreatePlan paths, then confirm the prepared query completes without the UNKNOWN type or cache lookup errors and the test passes.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp, postgres, python
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.