IN operator with list of tuples fails in Python but works from SQL

[jacopofar]

What happens?

I have a query in the form:

 select (2026, 1) IN [(2026,1),(2026,2),(2026,3)];

which works fine (the real query is more complex, but this is a minimal example to reproduce the issue.

If I however pass the list of values for the IN clause as a parameter in Python:

import duckdb

duckdb.execute(
  "select (2026, 1) in $ym",
  dict(ym=[(2026,1),(2026,2),(2026,3)]),
)

I obtain this error:

_duckdb.BinderException: Binder Error: Cannot deduce template type 'T' in function: 'contains(T[], T) -> BOOLEAN'
Type 'T' was inferred to be:
 - 'INTEGER[]', from first occurrence
 - 'STRUCT(INTEGER, INTEGER)', which is incompatible with previously inferred type!

LINE 1: select (2026, 1) in $ym

it looks like DuckDB is inferring the SQL type of the Python data in a different way than when it's in the query, and I could not find any way to do the casting in the query itself.

To Reproduce

import duckdb

duckdb.execute(
  "select (2026, 1) in $ym",
  dict(ym=[(2026,1),(2026,2),(2026,3)]),
)

OS:

Linux

DuckDB Version:

1.4.3 and 1.5.0

DuckDB Client:

Python

Hardware:

No response

Full Name:

Jacopo Farina

Affiliation:

Flixbus

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

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

[evertlammerts]

duckdb-python tries to infer the logic type of a parameter, and a Python tuple is inferred to be an integer array. The error is about trying to run CONTAINS with two different types, resulting in this:

memory D select (2026, 1) in [[2026,1],[2026,2],[2026,3]];
Binder Error:
Cannot deduce template type 'T' in function: 'contains(T[], T) -> BOOLEAN'
Type 'T' was inferred to be:
 - 'INTEGER[]', from first occurrence
 - 'STRUCT(INTEGER, INTEGER)', which is incompatible with previously inferred type!

LINE 1: select (2026, 1) in [[2026,1],[2026,2],[2026,3]];

This works, because now the argument is a list instead of a struct:

duckdb.execute(
  "select [2026, 1] in $ym",
  dict(ym=[(2026,1),(2026,2),(2026,3)]),
).fetchall()