Prepared statement error: Could not convert DuckDB type: UNKNOWN to Postgres type
- 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
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