[hibernate-issues] [JIRA] (HHH-16886) Incorrect SQL alternative for tuple-in-lists on DBMS not supporting tuple-in-lists

Dennis Katz ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5f6b7a7... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMzJhOTU3NTE1... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16886?atlOrigin=eyJpIjoiMzJhOT... ) HHH-16886 ( https://hibernate.atlassian.net/browse/HHH-16886?atlOrigin=eyJpIjoiMzJhOT... ) Incorrect SQL alternative for tuple-in-lists on DBMS not supporting tuple-in-lists ( https://hibernate.atlassian.net/browse/HHH-16886?atlOrigin=eyJpIjoiMzJhOT... ) Issue Type: Bug Assignee: Unassigned Components: hibernate-core Created: 04/Jul/2023 02:13 AM Environment: Hibernate 6.2.5.final - OpenJDK17 - Windows 10 and Linux (Debian 12) - Db2 for z/OS (also with MariaDB with DB2Dialect, DB2zDialect) Priority: Major Reporter: Dennis Katz ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5f6b7a7... ) Hibernate generates in for some DBMS wrong SQL in special cases. In my case, i have an Spring application that querys an table with an collection of an embeddable key with two fields and one additional boolean value (similar as in-List with tuples - which is not supported by DB2). In this case, the generated SQL returns all rows that matches the embeddable expect for the last - for this the row must match the embeddable and the boolean value. Example ------- *Table-Definition* No Name Typ 1 id int(11) 2 key_1 char(3) 3 key_2 char(3) 4 present char(1) *Data* id key_1 key_2 present 1 ZH2 HDS Y 2 ZH3 HDS Y 3 GXZ HDS N 4 KAZ TST Y *Query-Method (Spring Data JPA)* public List<TestEntity> findAllBySubInAndPresent(Collection<Sub> subs, String present); *Model* @AllArgsConstructor @Data @Entity @Table(name = "tester") public class TestEntity { @Id @Column(name = "id") private int id; @Embedded private Sub sub; @Column(name = "present") private String present; @RequiredArgsConstructor @AllArgsConstructor @Data @Embeddable public static class Sub { @Column(name = "key_1") private String key1; @Column(name = "key_2") private String key2; } } *generated wrong SQL:* {{select t1_0.id,t1_0.present,t1_0.key_1,t1_0.key_2 from tester t1_0 where (t1_0.key_1=? and t1_0.key_2=?) or (t1_0.key_1=? and t1_0.key_2=?) or (t1_0.key_1=? and t1_0.key_2=?) and t1_0.present=? }} Which returns the rows with the IDs: 1, 3 and 4. Expected are only the rows with the IDs: 1 and 4 *This is fixed by adding parenthesis around the ORs of the Collection-Data:* select t1_0.id,t1_0.present,t1_0.key_1,t1_0.key_2 from tester t1_0 where ((t1_0.key_1=? and t1_0.key_2=?) or (t1_0.key_1=? and t1_0.key_2=?) or (t1_0.key_1=? and t1_0.key_2=?)) and t1_0.present=? This incorrect SQL generation only occours for DBMS that do not support the supportsRowValueConstructorSyntaxInInList. For example DB2. This problem occurs in versions 5.x and 6.x. I've allready fixed this issue and i'll submit an PR the next hours. ( https://hibernate.atlassian.net/browse/HHH-16886#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16886#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#100228- sha1:4a42edc )