discovered during #8989, this is simply not implemented at all and the bind expression is ignored when it's an element of a tuple. Also, the impl of a TupleType() is not getting the internal types propagated along, which is part of what would be needed for this to work
from sqlalchemy import Column
from sqlalchemy import func
from sqlalchemy import MetaData
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import tuple_
from sqlalchemy.engine import default
class MyString(String):
def bind_expression(self, bindvalue):
return func.lower(bindvalue)
def column_expression(self, col):
return func.lower(col)
test_table = Table(
"test_table", MetaData(), Column("x", String), Column("y", MyString())
)
stmt1 = select(test_table).where(test_table.c.y.in_(["y1", "y2", "y3"]))
print(stmt1.compile(compile_kwargs={"render_postcompile": True}))
stmt2 = select(test_table).where(
tuple_(test_table.c.x, test_table.c.y).in_(
[
("x1", "y1"),
("x2", "y2"),
("x3", "y3"),
]
)
)
print(stmt2.compile(compile_kwargs={"render_postcompile": True}))
expr = test_table.c.y.in_(["y1", "y2", "y3"])
impl = expr.right.type.dialect_impl(default.DefaultDialect())
print(f"right type of expr: {expr.right.type}")
print(f"right impl type of expr: {impl}")
expr = tuple_(test_table.c.x, test_table.c.y).in_(
[
("x1", "y1"),
("x2", "y2"),
("x3", "y3"),
]
)
impl = expr.right.type.dialect_impl(default.DefaultDialect())
print(f"right type of expr: {expr.right.type}")
print(f"right impl type of expr: {impl}")# non-tuple - correct
SELECT test_table.x, lower(test_table.y) AS y
FROM test_table
WHERE test_table.y IN (lower(:y_1_1), lower(:y_1_2), lower(:y_1_3))
# tuple - incorrect
SELECT test_table.x, lower(test_table.y) AS y
FROM test_table
WHERE (test_table.x, test_table.y) IN ((:param_1_1_1, :param_1_1_2), (:param_1_2_1, :param_1_2_2), (:param_1_3_1, :param_1_3_2))
# non-tuple - VARCHAR for outer, inner, correct
right type of expr: VARCHAR
right impl type of expr: VARCHAR
# tuple - outer is correct, inner is incorrect
right type of expr: TupleType(String(), MyString())
# incorrect, should be something like TupleType(VARCHAR, MyString)
right impl type of expr: TupleType()
discovered during #8989, this is simply not implemented at all and the bind expression is ignored when it's an element of a tuple. Also, the impl of a TupleType() is not getting the internal types propagated along, which is part of what would be needed for this to work