Regression when using CAST expressions in ROW IN predicates #4114
Description
This used to work before, e.g. in 2.2.224, but no longer does in 2.3.232:
try (PreparedStatement s = connection.prepareStatement(
"""
select 1
where (cast(? as int), cast(? as int)) in ((cast(? as int), cast(? as int)), (cast(? as int), cast(? as int)))
"""
)) {
s.setInt(1, 1);
s.setInt(2, 2);
s.setInt(3, 1);
s.setInt(4, 2);
s.setInt(5, 1);
s.setInt(6, 2);
try (ResultSet rs = s.executeQuery()) {
while (rs.next())
System.out.println(rs.getInt(1));
}
}The error thrown is:
50000
General error: "java.lang.ClassCastException: class org.h2.expression.function.CastSpecification cannot be cast to class org.h2.expression.ExpressionColumn (org.h2.expression.function.CastSpecification and org.h2.expression.ExpressionColumn are in unnamed module of loader 'app')" [50000-232]
HY000
org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.ClassCastException: class org.h2.expression.function.CastSpecification cannot be cast to class org.h2.expression.ExpressionColumn (org.h2.expression.function.CastSpecification and org.h2.expression.ExpressionColumn are in unnamed module of loader 'app')" [50000-232]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:566)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
at org.h2.message.DbException.get(DbException.java:212)
at org.h2.message.DbException.convert(DbException.java:407)
at org.h2.message.DbException.toSQLException(DbException.java:379)
at org.h2.message.TraceObject.logAndConvert(TraceObject.java:365)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:318)
at org.jooq.testscripts.JDBC.main(JDBC.java:39)
Caused by: java.lang.ClassCastException: class org.h2.expression.function.CastSpecification cannot be cast to class org.h2.expression.ExpressionColumn (org.h2.expression.function.CastSpecification and org.h2.expression.ExpressionColumn are in unnamed module of loader 'app')
at org.h2.index.IndexCondition.getCompoundInList(IndexCondition.java:146)
at org.h2.expression.condition.ConditionIn.createCompoundIndexCondition(ConditionIn.java:193)
at org.h2.expression.condition.ConditionIn.createIndexConditions(ConditionIn.java:171)
at org.h2.command.query.Select.preparePlan(Select.java:1256)
at org.h2.command.query.Query.prepare(Query.java:233)
at org.h2.command.Parser.prepareCommand(Parser.java:489)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:645)
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:561)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1164)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:93)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:315)
... 1 more
For the problem to reproduce, these things are relevant:
- Using a prepared statement
- Using bind values
- Using
INwith rows and more than one item in theINlist
A workaround is to avoid the IN list with VALUES:
select 1
where (cast(? as int), cast(? as int)) in (
values
(cast(? as int), cast(? as int)),
(cast(? as int), cast(? as int))
)