[hibernate-issues] [JIRA] (HHH-16413) Subquery with multiple left joins broken in 6.2.0

Andreas Asplund ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNDRjZmVkNTg5... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16413?atlOrigin=eyJpIjoiNDRjZm... ) HHH-16413 ( https://hibernate.atlassian.net/browse/HHH-16413?atlOrigin=eyJpIjoiNDRjZm... ) Subquery with multiple left joins broken in 6.2.0 ( https://hibernate.atlassian.net/browse/HHH-16413?atlOrigin=eyJpIjoiNDRjZm... ) Issue Type: Bug Affects Versions: 6.2.0 Assignee: Unassigned Created: 31/Mar/2023 08:38 AM Priority: Major Reporter: Andreas Asplund ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Subquery with multiple joins broken in 6.2.0. With 6.2.0.CR4 the SQL sub select with joins is correct. select s1_0.id,s1_0.name from some_object_1 s1_0 where s1_0.id in( select a1_0.object_id_identity from acl_object_identity a1_0 left join acl_entry a2_0 on a1_0.id=a2_0.acl_object_identity left join acl_sid s2_0 on s2_0.id=a2_0.sid left join acl_class o1_0 on o1_0.id=a1_0.object_id_class left join acl_sid o2_0 on o2_0.id=a1_0.owner_sid where o1_0.class=? and (a2_0.granting=? and a2_0.mask>=? and s2_0.sid in(?,?) or o2_0.sid in(?,?) and o2_0.principal=?) ) But with 6.2.0.Final the left joins after the first left join becomes joins. select s1_0.id,s1_0.name from some_object_1 s1_0 where s1_0.id in( select a1_0.object_id_identity from acl_object_identity a1_0 left join acl_entry a2_0 on a1_0.id=a2_0.acl_object_identity join acl_sid s3_0 on s3_0.id=a2_0.sid join acl_class o3_0 on o3_0.id=a1_0.object_id_class join acl_sid o4_0 on o4_0.id=a1_0.owner_sid where o3_0.class=? and (a2_0.granting=? and a2_0.mask>=? and s3_0.sid in(?,?) or o4_0.sid in(?,?) and o4_0.principal=?) ) I tracked it down to https://hibernate.atlassian.net/browse/HHH-16396. Removing commit https://github.com/hibernate/hibernate-orm/commit/fb174d19c7fd41e597af066... restores the functionality. ( https://hibernate.atlassian.net/browse/HHH-16413#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16413#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#100221- sha1:57c27f3 )