[JIRA] (HHH-15513) Hibernate 5.6.11.Final - H2-Database 2.1.214 - HSQL with clause "IN" generates wrong SQL
by Romain Pillot (JIRA)
Romain Pillot ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMmVlYWRmZTE4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15513?atlOrigin=eyJpIjoiMmVlYW... ) HHH-15513 ( https://hibernate.atlassian.net/browse/HHH-15513?atlOrigin=eyJpIjoiMmVlYW... ) Hibernate 5.6.11.Final - H2-Database 2.1.214 - HSQL with clause "IN" generates wrong SQL ( https://hibernate.atlassian.net/browse/HHH-15513?atlOrigin=eyJpIjoiMmVlYW... )
Change By: Romain Pillot ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
Recent H2-Dtabase CVE’s forced our team to migrate from H2 1.X.X to latest.
Here a snippet
{noformat}Double dataset = new Double[] { 2.0, 3.0, 4.0 }
List<Double> list = Arrays.asList(dataset);
String query = "SELECT a " +
"FROM TestEntity a " +
"WHERE a.testDouble IN :param ";
Query query = getEntityManager().createQuery(query , Double.class );
query.setParameter("param", list);
List<TestEntity> results = query.getResultList();{noformat}
Causing
{noformat}18:58:54.051 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Values of types "NUMERIC(500)" and "ROW(C1 INTEGER, C2 INTEGER, C3 INTEGER)" are not comparable; SQL statement:{noformat}
After some debugs, we noticed that the hsql is translated to sql with a syntax not supported anymore by the newest H2 version.
The “in” statement is surrounded by double parentheses
{noformat}in ((2, 3, 4)){noformat}
When executing manually the generated SQL in our H2 database, it does work when replacing the double parentheses with single parenthesis, like the following
{noformat}in (2, 3, 4){noformat}
( https://hibernate.atlassian.net/browse/HHH-15513#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15513#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100207- sha1:4ec4822 )
2 years, 3 months
[JIRA] (HHH-15513) Hibernate 5.6.11.Final - H2-Database 2.1.214 - HSQL with clause "IN" generates wrong SQL
by Romain Pillot (JIRA)
Romain Pillot ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNGY0Yjc1NTRj... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15513?atlOrigin=eyJpIjoiNGY0Yj... ) HHH-15513 ( https://hibernate.atlassian.net/browse/HHH-15513?atlOrigin=eyJpIjoiNGY0Yj... ) Hibernate 5.6.11.Final - H2-Database 2.1.214 - HSQL with clause "IN" generates wrong SQL ( https://hibernate.atlassian.net/browse/HHH-15513?atlOrigin=eyJpIjoiNGY0Yj... )
Issue Type: Bug Affects Versions: 5.6.11 Assignee: Unassigned Components: hibernate-core Created: 15/Sep/2022 10:03 AM Environment: JDK 8/11, Hibernate 5.6.11.Final, H2 2.1.214 Labels: H2 Priority: Blocker Reporter: Romain Pillot ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
Recent H2-Dtabase CVE’s forced our team to migrate from H2 1.X.X to latest.
Here a snippet
Double dataset = new Double[] { 2.0, 3.0, 4.0 }
List<Double> list = Arrays.asList(dataset);
String query = "SELECT a " +
"FROM TestEntity a " +
"WHERE a.testDouble IN :param ";
Query query = getEntityManager().createQuery(query, Double.class);
query.setParameter("param", list);
List<TestEntity> results = query.getResultList();
Causing
18:58:54.051 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Values of types "NUMERIC(500)" and "ROW(C1 INTEGER, C2 INTEGER, C3 INTEGER)" are not comparable; SQL statement:
After some debugs, we noticed that the hsql is translated to sql with a syntax not supported anymore by the newest H2 version.
The “in” statement is surrounded by double parentheses
in ((2, 3, 4))
When executing manually the generated SQL in our H2 database, it does work when replacing the double parentheses with single parenthesis, like the following
in (2, 3, 4)
( https://hibernate.atlassian.net/browse/HHH-15513#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15513#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100207- sha1:4ec4822 )
2 years, 3 months