How to handle IN clause expansion properly

[tlegrave]

Hello there,

The following query won't work, because IN clause expansion won't be done properly.

from trino.dbapi import connect
from trino.auth import BasicAuthentication

conn = connect(
    host="xxx",
    port=443,
    user="xxx",
    auth=BasicAuthentication("xxx", "xxx"),
    http_scheme="https",
)

cur = conn.cursor()
cur.execute(
    "SELECT * FROM iceberg.schema.table WHERE column IN ?",
    params=[("value1", "value2")],
)
rows = cur.fetchone()

Depending on the type (list or tuple), ("value1", "value2") will either be expanded to:

EXECUTE IMMEDIATE 'SELECT * FROM iceberg.schema.table WHERE column IN ?' USING ROW('value1','value2')

or

EXECUTE IMMEDIATE 'SELECT * FROM iceberg.schema.table WHERE column IN ?' USING ARRAY('value1','value2')

and this will result in the following error on trino:

io.trino.spi.TrinoException: line 14:29: IN value and list items must be the same type or coercible to a common type. Cannot find common type between varchar and row(varchar(3), varchar(3)), all types (without duplicates): [varchar, row(varchar(3), varchar(3))]

Is there any way to perform a clean IN clause expansion, without fully templating the clause before (which might not be sql-injection safe)?

Thank you for the help,

[hashhar]

No way to do this today. psycopg2 for example "hides" it by doing templating FOR YOU but it's still doing templating.

Needs some research to explore possible solutions.