Describe the bug
In some cases, EclipseLink generates SQL for H2 that compares a boolean attribute to an integer.
Exception [EclipseLink-4002] (Eclipse Persistence Services - 5.0.0.v202603230926-bc4f4eb36eb1dcce223533d87890ba458628a2f9): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BOOLEAN" and "INTEGER" are not comparable; SQL statement:
SELECT NAME, DENOMINATOR, NUMERATOR, REDUCED, CEILING, INVERSE, TRUNCATED, TYPE, VAL, NONREPEATING, REPEATING FROM Fraction WHERE (NAME LIKE '%Four%' ESCAPE '\' AND (REDUCED = 1)) ORDER BY DENOMINATOR, NUMERATOR DESC [90110-240]
Error Code: 90110
Call: SELECT NAME, DENOMINATOR, NUMERATOR, REDUCED, CEILING, INVERSE, TRUNCATED, TYPE, VAL, NONREPEATING, REPEATING FROM Fraction WHERE (NAME LIKE '%Four%' ESCAPE '\' AND (REDUCED = 1)) ORDER BY DENOMINATOR, NUMERATOR DESC
Query: ReadAllQuery(referenceClass=Fraction sql="SELECT NAME, DENOMINATOR, NUMERATOR, REDUCED, CEILING, INVERSE, TRUNCATED, TYPE, VAL, NONREPEATING, REPEATING FROM Fraction WHERE (NAME LIKE ? ESCAPE ? AND (REDUCED = ?)) ORDER BY DENOMINATOR, NUMERATOR DESC")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:344)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:703)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:570)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2071)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:611)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:274)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:281)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:267)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:353)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:793)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2923)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2876)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:584)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1298)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:937)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1257)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:485)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1345)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3018)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1866)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1848)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1813)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:265)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BOOLEAN" and "INTEGER" are not comparable; SQL statement:
SELECT NAME, DENOMINATOR, NUMERATOR, REDUCED, CEILING, INVERSE, TRUNCATED, TYPE, VAL, NONREPEATING, REPEATING FROM Fraction WHERE (NAME LIKE '%Four%' ESCAPE '\' AND (REDUCED = 1)) ORDER BY DENOMINATOR, NUMERATOR DESC [90110-240]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:644)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.value.TypeInfo.checkComparable(TypeInfo.java:766)
at org.h2.expression.condition.Comparison.optimize(Comparison.java:168)
at org.h2.expression.condition.ConditionAndOr.optimize(ConditionAndOr.java:137)
at org.h2.expression.Expression.optimizeCondition(Expression.java:149)
at org.h2.command.query.Select.prepareExpressions(Select.java:1215)
at org.h2.command.query.Query.prepare(Query.java:235)
at org.h2.command.Parser.prepareCommand(Parser.java:492)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:647)
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:563)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1160)
at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:94)
at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeQuery(WSJdbcStatement.java:576)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1025)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:663)
@Entity
public class Fraction {
...
@Column(nullable = false)
boolean reduced;Steps to Reproduce
Put this test case into Data_1_1_Servlet and remove ;MODE=LEGACY from the server.xml.
@Test
public void testBooleanRestriction() {
fractions.where(Restrict.all(_Fraction.name.contains("Four"),
_Fraction.reduced.isTrue()));
}Then run the test bucket:
./gradlew io.openliberty.data.internal_fat_1_1:buildandrun
JPQL is:
FROM Fraction WHERE (this.name LIKE ?1 ESCAPE ?2 AND this.reduced=?3) ORDER BY this.denominator, this.numerator DESC
supplied parameter values observed in trace are:
... set ?1 %Four%
... set ?2 \
... set ?3 true
Also note that if you omit _Fraction.name.contains("Four"), from the test, then it runs successfully.
JPQL for successful scenario is:
FROM Fraction WHERE (this.reduced=?1) ORDER BY this.denominator, this.numerator DESC
Also, the test runs successfully on Hibernate.
Expected behavior
Query should successfully run and retrieve results from the H2 database
Diagnostic information:
- OpenLiberty Version: latest
- Affected feature(s) : persistence-3.2
- Java Version: [i.e. full output of
java -version]
java.version = 21
java.runtime = OpenJDK Runtime Environment (21+35-2513)
os = Mac OS X (15.7.4; aarch64) (en_US)
- server.xml configuration (WITHOUT sensitive information like passwords): see test case
- If it would be useful, upload the messages.log file found in
$WLP_OUTPUT_DIR/messages.log : run test case
Describe the bug
In some cases, EclipseLink generates SQL for H2 that compares a boolean attribute to an integer.
Steps to Reproduce
Put this test case into Data_1_1_Servlet and remove
;MODE=LEGACYfrom the server.xml.Then run the test bucket:
./gradlew io.openliberty.data.internal_fat_1_1:buildandrun
JPQL is:
supplied parameter values observed in trace are:
Also note that if you omit
_Fraction.name.contains("Four"),from the test, then it runs successfully.JPQL for successful scenario is:
Also, the test runs successfully on Hibernate.
Expected behavior
Query should successfully run and retrieve results from the H2 database
Diagnostic information:
java -version]$WLP_OUTPUT_DIR/messages.log: run test case