numericWithBooleanComparison is ignored in the ConditionIn Optimizer

[iyzana]

When running H2 in a Mode where numericWithBooleanComparison is set to true (e.g. LEGACY), certain queries where boolean and integers are compared still do not work.

For example, in the following select query

create table data (a boolean, b boolean);
select * from data d where d.a = 1 or d.b = 1;

the following exception will be thrown:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "INTEGER" and "BOOLEAN" are not comparable:
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:644) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.value.TypeInfo.checkComparable(TypeInfo.java:766) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.expression.condition.ConditionIn.optimize(ConditionIn.java:110) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.expression.condition.ConditionAndOrN.optimize(ConditionAndOrN.java:229) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.expression.SearchedCase.optimize(SearchedCase.java:46) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.expression.ConcatenationOperation.determineType(ConcatenationOperation.java:201) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.expression.ConcatenationOperation.optimize(ConcatenationOperation.java:141) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.command.query.Select.prepareExpressions(Select.java:1170) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.command.query.Query.prepare(Query.java:218) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.command.Parser.prepareCommand(Parser.java:489) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:639) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:559) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1166) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:93) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:316) ~[h2-2.2.224.jar:2.2.224]

Version: 2.2.224

Independent from this ticket: I believe numericWithBooleanComparison should also be set to true for Mode=ORACLE

[katzyn]

You can add ;OPTIMIZE_OR=FALSE to JDBC URL as a workaround. This workaround may reduce performance of some queries.

A better solution is to use standard where d.a or d.b or where d.a = true or d.b = true.

[iyzana]

Good to know. My current workaround was to use d.a != 0 or d.b != 0 because then the optimizaton did not run, but ;OPTIMIZE_OR=FALSE seems to be a better workaround

I can't use standard d.a or d.b because the same query also runs against an actual Oracle database, which does not know booleans

[katzyn]

Oracle 23ai supports standard BOOLEAN data type, TRUE and FALSE literals and null-safe boolean test predicate. UNKNOWN isn't supported, but NULL can be used instead.

Older versions of Oracle don't have BOOLEAN data type in SQL, so if you run the same SQL in old version of Oracle and H2, you probably shouldn't use BOOLEAN data type in H2 too.