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

[dpxcc]

What happens?

Prepared statement is still broken even after the fix in #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

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:

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?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

[dpxcc]

DuckdbPrepare() calls duckdb::ClientContext::Prepare() on "SELECT count(*) AS count FROM pg_temp.main.test_table WHERE (id = ($1 + 1))", which throws ParameterNotResolvedException internally.

#0  0x0000ffff914a2dbc in __cxa_throw () from /lib/aarch64-linux-gnu/libstdc++.so.6
#1  0x0000ffff8ca79e20 in duckdb::FunctionBinder::BindFunctionFromArguments<duckdb::ScalarFunction> (this=0xffffc2e7f8b8, name="+", functions=..., arguments=..., error=...) at /workspaces/pg_duckdb/third_party/duckdb/src/function/function_binder.cpp:152
#2  0x0000ffff8ca71fb4 in duckdb::FunctionBinder::BindFunction (this=0xffffc2e7f8b8, name="+", functions=..., arguments=..., error=...) at /workspaces/pg_duckdb/third_party/duckdb/src/function/function_binder.cpp:164
#3  0x0000ffff8ca72490 in duckdb::FunctionBinder::BindFunction (this=0xffffc2e7f8b8, name="+", functions=..., arguments=..., error=...) at /workspaces/pg_duckdb/third_party/duckdb/src/function/function_binder.cpp:209
#4  0x0000ffff8ca72ec8 in duckdb::FunctionBinder::BindScalarFunction (this=0xffffc2e7f8b8, func=..., children=..., error=..., is_operator=true, binder=...) at /workspaces/pg_duckdb/third_party/duckdb/src/function/function_binder.cpp:313
#5  0x0000ffff8bc456d4 in duckdb::ExpressionBinder::BindFunction (this=0xffffc2e807b0, function=..., func=..., depth=0) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder/expression/bind_function_expression.cpp:129
#6  0x0000ffff8bc4531c in duckdb::ExpressionBinder::BindExpression (this=0xffffc2e807b0, function=..., depth=0, expr_ptr=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder/expression/bind_function_expression.cpp:93
#7  0x0000ffff8bd822f0 in duckdb::ExpressionBinder::BindExpression (this=0xffffc2e807b0, expr=..., depth=0, root_expression=false) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/expression_binder.cpp:89
#8  0x0000ffff8bd4c210 in duckdb::WhereBinder::BindExpression (this=0xffffc2e807b0, expr_ptr=..., depth=0, root_expression=false) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/expression_binder/where_binder.cpp:38
#9  0x0000ffff8bd840d8 in duckdb::ExpressionBinder::Bind (this=0xffffc2e807b0, expr=..., depth=0, root_expression=false) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/expression_binder.cpp:365
#10 0x0000ffff8bd8334c in duckdb::ExpressionBinder::BindChild (this=0xffffc2e807b0, expr=..., depth=0, error=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/expression_binder.cpp:223
#11 0x0000ffff8bc443cc in duckdb::ExpressionBinder::BindExpression (this=0xffffc2e807b0, expr=..., depth=0) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder/expression/bind_comparison_expression.cpp:160
#12 0x0000ffff8bd82244 in duckdb::ExpressionBinder::BindExpression (this=0xffffc2e807b0, expr=..., depth=0, root_expression=false) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/expression_binder.cpp:77
#13 0x0000ffff8bd4c210 in duckdb::WhereBinder::BindExpression (this=0xffffc2e807b0, expr_ptr=..., depth=0, root_expression=true) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/expression_binder/where_binder.cpp:38
#14 0x0000ffff8bd840d8 in duckdb::ExpressionBinder::Bind (this=0xffffc2e807b0, expr=..., depth=0, root_expression=true) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/expression_binder.cpp:365
#15 0x0000ffff8bd83c3c in duckdb::ExpressionBinder::Bind (this=0xffffc2e807b0, expr=..., result_type=..., root_expression=true) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/expression_binder.cpp:317
#16 0x0000ffff8bc78250 in duckdb::Binder::BindSelectNode (this=0xaaaafb99da20, statement=..., from_table=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder/query_node/bind_select_node.cpp:456
#17 0x0000ffff8bc77974 in duckdb::Binder::BindNode (this=0xaaaafb99da20, statement=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder/query_node/bind_select_node.cpp:373
#18 0x0000ffff8bd87e68 in duckdb::Binder::BindNode (this=0xaaaafb99da20, node=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder.cpp:327
#19 0x0000ffff8bd881e0 in duckdb::Binder::Bind (this=0xaaaafb99da20, node=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder.cpp:365
#20 0x0000ffff8bcb8648 in duckdb::Binder::Bind (this=0xaaaafb99da20, stmt=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder/statement/bind_select.cpp:11
#21 0x0000ffff8bd8705c in duckdb::Binder::Bind (this=0xaaaafb99da20, statement=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/binder.cpp:149
#22 0x0000ffff8bd8f170 in duckdb::Planner::CreatePlan (this=0xffffc2e811f0, statement=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/planner.cpp:43
#23 0x0000ffff8bd8fa44 in duckdb::Planner::CreatePlan (this=0xffffc2e811f0, statement=...) at /workspaces/pg_duckdb/third_party/duckdb/src/planner/planner.cpp:142
#24 0x0000ffff8d4c538c in duckdb::ClientContext::CreatePreparedStatementInternal (this=0xaaaafb99bc70, lock=..., query="SELECT count(*) AS count FROM pg_temp.main.test_table WHERE (id = ($1 + 1))", statement=..., values=...) at /workspaces/pg_duckdb/third_party/duckdb/src/main/client_context.cpp:338
#25 0x0000ffff8d4c5a7c in duckdb::ClientContext::CreatePreparedStatement (this=0xaaaafb99bc70, lock=..., query="SELECT count(*) AS count FROM pg_temp.main.test_table WHERE (id = ($1 + 1))", statement=..., values=..., mode=duckdb::PreparedStatementMode::PREPARE_ONLY) at /workspaces/pg_duckdb/third_party/duckdb/src/main/client_context.cpp:424
#26 0x0000ffff8d4c75ec in operator() (__closure=0xaaaafb9a6910) at /workspaces/pg_duckdb/third_party/duckdb/src/main/client_context.cpp:659
#27 0x0000ffff8d548850 in std::__invoke_impl<void, duckdb::ClientContext::PrepareInternal(duckdb::ClientContextLock&, duckdb::unique_ptr<duckdb::SQLStatement>)::<lambda()>&>(std::__invoke_other, struct {...} &) (__f=...) at /usr/include/c++/11/bits/invoke.h:61
#28 0x0000ffff8d4f17f0 in std::__invoke_r<void, duckdb::ClientContext::PrepareInternal(duckdb::ClientContextLock&, duckdb::unique_ptr<duckdb::SQLStatement>)::<lambda()>&>(struct {...} &) (__fn=...) at /usr/include/c++/11/bits/invoke.h:154
#29 0x0000ffff8d4effb4 in std::_Function_handler<void(), duckdb::ClientContext::PrepareInternal(duckdb::ClientContextLock&, duckdb::unique_ptr<duckdb::SQLStatement>)::<lambda()> >::_M_invoke(const std::_Any_data &) (__functor=...) at /usr/include/c++/11/bits/std_function.h:290
#30 0x0000ffff8bbd9ac8 in std::function<void ()>::operator()() const (this=0xffffc2e817d8) at /usr/include/c++/11/bits/std_function.h:590
#31 0x0000ffff8d4ca524 in duckdb::ClientContext::RunFunctionInTransactionInternal(duckdb::ClientContextLock&, std::function<void ()> const&, bool) (this=0xaaaafb99bc70, lock=..., fun=..., requires_valid_transaction=false) at /workspaces/pg_duckdb/third_party/duckdb/src/main/client_context.cpp:1082
#32 0x0000ffff8d4c7728 in duckdb::ClientContext::PrepareInternal (this=0xaaaafb99bc70, lock=..., statement=...) at /workspaces/pg_duckdb/third_party/duckdb/src/main/client_context.cpp:658
#33 0x0000ffff8d4c7bb0 in duckdb::ClientContext::Prepare (this=0xaaaafb99bc70, query="SELECT count(*) AS count FROM pg_temp.main.test_table WHERE (id = ($1 + 1))") at /workspaces/pg_duckdb/third_party/duckdb/src/main/client_context.cpp:691
#34 0x0000ffff91fe7684 in DuckdbPrepare (query=0xaaaafb715ea0) at src/pgduckdb_planner.cpp:50
#35 0x0000ffff91fe7744 in CreatePlan (query=0xaaaafb715ea0, throw_error=true) at src/pgduckdb_planner.cpp:61
#36 0x0000ffff91fe7d94 in pgduckdb::__CPPFunctionGuard__<Plan* (*)(Query*, bool), CreatePlan, Query*, bool> (func_name=0xffff9204b178 "DuckdbPlanNode") at src/pgduckdb_planner.cpp:147
#37 0x0000ffff91fe7be0 in DuckdbPlanNode (parse=0xaaaafb715ea0, query_string=0xaaaafb80e710 "SELECT count(*) FROM test_table where id = $1 + 1", cursor_options=2048, bound_params=0xaaaafb80efd0, throw_error=true) at src/pgduckdb_planner.cpp:147
#38 0x0000ffff91fd9664 in DuckdbPlannerHook_Cpp (parse=0xaaaafb715ea0, query_string=0xaaaafb80e710 "SELECT count(*) FROM test_table where id = $1 + 1", cursor_options=2048, bound_params=0xaaaafb80efd0) at src/pgduckdb_hooks.cpp:199
#39 0x0000ffff91fd9c44 in pgduckdb::__CPPFunctionGuard__<PlannedStmt* (*)(Query*, char const*, int, ParamListInfoData*), DuckdbPlannerHook_Cpp, Query*, char const*, int, ParamListInfoData*> (func_name=0xffff92049a78 "DuckdbPlannerHook") at src/pgduckdb_hooks.cpp:232
#40 0x0000ffff91fd97fc in DuckdbPlannerHook (parse=0xaaaafb715ea0, query_string=0xaaaafb80e710 "SELECT count(*) FROM test_table where id = $1 + 1", cursor_options=2048, bound_params=0xaaaafb80efd0) at src/pgduckdb_hooks.cpp:232
#41 0x0000aaaaca04ae14 in planner (parse=0xaaaafb715ea0, query_string=0xaaaafb80e710 "SELECT count(*) FROM test_table where id = $1 + 1", cursorOptions=2048, boundParams=0xaaaafb80efd0) at planner.c:280
#42 0x0000aaaaca1be3b8 in pg_plan_query (querytree=0xaaaafb715ea0, query_string=0xaaaafb80e710 "SELECT count(*) FROM test_table where id = $1 + 1", cursorOptions=2048, boundParams=0xaaaafb80efd0) at postgres.c:908
#43 0x0000aaaaca1be4f8 in pg_plan_queries (querytrees=0xaaaafb715e50, query_string=0xaaaafb80e710 "SELECT count(*) FROM test_table where id = $1 + 1", cursorOptions=2048, boundParams=0xaaaafb80efd0) at postgres.c:1000
#44 0x0000aaaaca3ab988 in BuildCachedPlan (plansource=0xaaaafb760520, qlist=0xaaaafb715e50, boundParams=0xaaaafb80efd0, queryEnv=0x0) at plancache.c:962
#45 0x0000aaaaca3ac0d4 in GetCachedPlan (plansource=0xaaaafb760520, boundParams=0xaaaafb80efd0, owner=0x0, queryEnv=0x0) at plancache.c:1244
#46 0x0000aaaaca1bfc9c in exec_bind_message (input_message=0xffffc2e82128) at postgres.c:2023
#47 0x0000aaaaca1c45a0 in PostgresMain (dbname=0xaaaafb76d068 "postgres", username=0xaaaafb76d048 "postgres") at postgres.c:4815
#48 0x0000aaaaca1ba5d0 in BackendMain (startup_data=0xffffc2e82310 "", startup_data_len=4) at backend_startup.c:105
#49 0x0000aaaaca0b8de4 in postmaster_child_launch (child_type=B_BACKEND, startup_data=0xffffc2e82310 "", startup_data_len=4, client_sock=0xffffc2e82378) at launch_backend.c:277
#50 0x0000aaaaca0bfea8 in BackendStartup (client_sock=0xffffc2e82378) at postmaster.c:3593
#51 0x0000aaaaca0bc580 in ServerLoop () at postmaster.c:1674
#52 0x0000aaaaca0bbd64 in PostmasterMain (argc=5, argv=0xaaaafb70fef0) at postmaster.c:1372
#53 0x0000aaaac9f72dac in main (argc=5, argv=0xaaaafb70fef0) at main.c:197

This behavior is expected in DuckDB - duckdb::ClientContext::Prepare() may not be able to resolve all parameters, and unresolved parameters will be re-bound later in PreparedStatement::PendingQuery() when input parameters are provided. As a result, DuckDB catches that exception, but the result types are unresolved in this case.

On the other hand, pg_duckdb assumes all result types to be fully resolved in duckdb::ClientContext::Prepare(), which is incorrect.

[JelteF]

I think this is the same issue as reported in #396. PRs are definitely welcome to fix this.