Prepared statement not working with multiple CTEs

[maclockard]

What happens?

When trying to run a query w/ prepared statement and a CTE, I get an error regardless of how many parameters I pass.

To Reproduce

When trying to run this query (or one like it):

with temp_first as (
    select * from "user" 
    where "name" = ?
), temp_second as (
    select * from "user" 
    where "name" = ?
)

select * from temp_first

If I try to execute it like so:

con.execute(query_string, ["val1", "val2"])

I get this error, which is incorrect since there should be two arguments to insert:

---------------------------------------------------------------------------
RuntimeError                              Traceback (most recent call last)
<ipython-input-7-90de98a041e9> in <module>
      4 
      5 # pass computed args to data service
----> 6 query_result = hex_data_service.query_dataframe(**_hex_args)
      7 query_result.head(10)
      8 

~/workspace/hex-inc/hex/data-service-python/hex_data_service/sql_cell.py in query_dataframe(templatized_sql_string, **kwargs)
    209     try:
    210         print(query_string)
--> 211         con.execute(query_string, ["val1", "val2"])
    212     except RuntimeError as err:
    213         matcher = re.compile(

RuntimeError: Binder Error: Parameter/argument count mismatch for prepared statement. Expected 1, got 2

If I instead only pass 1 like so:

con.execute(query_string, ["val1"])

I get the an error which looks correct:

---------------------------------------------------------------------------
RuntimeError                              Traceback (most recent call last)
<ipython-input-7-90de98a041e9> in <module>
      4 
      5 # pass computed args to data service
----> 6 query_result = hex_data_service.query_dataframe(**_hex_args)
      7 query_result.head(10)
      8 

~/workspace/hex-inc/hex/data-service-python/hex_data_service/sql_cell.py in query_dataframe(templatized_sql_string, **kwargs)
    209     try:
    210         print(query_string)
--> 211         con.execute(query_string, ["val1"])
    212     except RuntimeError as err:
    213         matcher = re.compile(

RuntimeError: Prepared statement needs 2 parameters, 1 given

Environment (please complete the following information):

• OS: macOS
• DuckDB Version: unsure, how do I check?
• DuckDB Client: 0.3.1 (this is the python package version)

Before Submitting

• Have you tried this on the latest master branch?
  I tried using 0.3.2.dev1173

• Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

[hannes]

Indeed that looks like a bug and probably we simply never tested this. Thanks for reporting, will have a go at fixing.

[hannes]

Here is a full example:

import duckdb
con = duckdb.connect()
con.execute('create table "user" (name string)')
print(con.execute('''
with temp_first as (
    select * from "user" 
    where "name" = ?
), temp_second as (
    select * from "user" 
    where "name" = ?
)
select * from temp_first
''', ["val1", "val2"]).fetchall())

Interestingly, when I change the select to include the other CTE, it works, e.g.

select * from temp_first, temp_second

Also, when I use the following query

with temp_first as (
    select * from "user" 
    where "name" = ?
), temp_second as (
    select * from temp_first 
    where "name" = ?
)
select * from temp_second

all is well, too. This means the bug is likely due of DuckDB ignoring the unused CTE in its planning/binding phase. Is there a reason for these unused CTEs in your query?

[maclockard]

I believe the query in this was being built up iteratively, testing out parts as they went, so the second part was planned to be used but this error confused the consumer