[hibernate-issues] [JIRA] (HHH-16472) Treated left join with restriction on entity results in incorrect query

Emond Papegaaij ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNWZmOWMwMmFk... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16472?atlOrigin=eyJpIjoiNWZmOW... ) HHH-16472 ( https://hibernate.atlassian.net/browse/HHH-16472?atlOrigin=eyJpIjoiNWZmOW... ) Treated left join with restriction on entity results in incorrect query ( https://hibernate.atlassian.net/browse/HHH-16472?atlOrigin=eyJpIjoiNWZmOW... ) Issue Type: Bug Affects Versions: 6.2.1 Assignee: Unassigned Components: hibernate-core Created: 14/Apr/2023 06:50 AM Priority: Major Reporter: Emond Papegaaij ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) When adding a restriction on a path to an entity from a treated left outer join, the restriction on the dtype is rendered twice: once in a sub-select in the outer join (this is correct) and once in where clause of the outer select (this is incorrect). The second restriction effectively transforms the left outer join into an inner join, because DTYPE will be NULL for all other records. The resulting query looks like this: select m1_0.id, m1_0.ref2 from MyEntity1 m1_0 left join ( select * from MyEntity2 t where t.DTYPE= 'MySubEntity2' ) r1_0 on r1_0.id=m1_0.ref2 where ( m1_0.ref2 is null or m1_0.ref2 is not null and r1_0.ref3=? ) and r1_0.DTYPE= 'MySubEntity2' I’ve attached a testcase that shows the problem. This test runs fine against 5.6.15. ( https://hibernate.atlassian.net/browse/HHH-16472#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16472#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:8830b7c )