Dan Mercean ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMGNkOWU1YTBj... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16888?atlOrigin=eyJpIjoiMGNkOW... ) HHH-16888 ( https://hibernate.atlassian.net/browse/HHH-16888?atlOrigin=eyJpIjoiMGNkOW... ) Left join with exists subquery adds extra join ( https://hibernate.atlassian.net/browse/HHH-16888?atlOrigin=eyJpIjoiMGNkOW... ) Issue Type: Bug Affects Versions: 6.1.7, 6.2.6 Assignee: Unassigned Components: hibernate-core Created: 04/Jul/2023 05:54 AM Priority: Major Reporter: Dan Mercean ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) Having two unrelated entities: @Entity public class Entity1 { @Id @GeneratedValue private Long id; private String uuid; ... } @Entity public class Entity2 { @Id @GeneratedValue private Long id; private String uuid; @ManyToMany(fetch = FetchType.LAZY) private List<Property> properties; ... } And the Property : @Entity public class Property { @Id @GeneratedValue private Long id; private String name; ... } We are trying to get both Entities, but left join only the Entities that have a specific Property: List<Object[]> results = entityManager.createQuery( "select e1, e2 " + "from Entity1 e1 " + "left join Entity2 e2 on e1.uuid = e2.uuid and exists (" + "select 1 from e2.properties p where p.name = :propertyName)") .setParameter("propertyName", "admin") .getResultList(); So we are interested only in the Entity2 that have a Property named admin. This is just an example, our actual use case is more complicated. This was working fine before hibernate 6, generating the following query: select entity1x0_.id as id1_0_0_, entity2x1_.id as id1_1_1_, entity1x0_.uuid as uuid2_0_0_, entity2x1_.uuid as uuid2_1_1_ from Entity1 entity1x0_ left outer join Entity2 entity2x1_ on ( entity1x0_.uuid=entity2x1_.uuid and ( exists ( select 1 from Entity2_Property properties2_, Property property3_ where entity2x1_.id=properties2_.Entity2_id and properties2_.properties_id=property3_.id and property3_.name=? ) ) ) But with Hibernate 6, another join is added at the end which duplicates the results and breaks the left join: select e1_0.id, e1_0.uuid, e2_0.id, e2_0.uuid from Entity1 e1_0 left join Entity2 e2_0 on e1_0.uuid=e2_0.uuid and exists(select 1 from Entity2_Property p1_0 join Property p1_1 on p1_1.id=p1_0.properties_id where p1_1.name=? and e2_0.id=p1_0.Entity2_id) join Entity2_Property p2_0 on e2_0.id=p2_0.Entity2_id Join that has no purpose: join Entity2_Property p2_0 on e2_0.id=p2_0.Entity2_id Created test cases for both Hibernate 5 and 6: https://github.com/danmercean/hibernate-test-case-left-join-exists ( https://hibernate.atlassian.net/browse/HHH-16888#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16888#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 )