[hibernate-issues] [JIRA] (HHH-15513) Hibernate 5.6.11.Final - H2-Database 2.1.214 - HSQL with clause "IN" generates wrong SQL

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=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100207- sha1:4ec4822 )