Left join with exists subquery adds extra join

Description

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

Activity

Show:
Fixed

Details

Assignee

Reporter

Worked in

Components

Sprint

Fix versions

Affects versions

Priority

Created July 4, 2023 at 12:54 PM
Updated August 31, 2023 at 5:30 PM
Resolved August 1, 2023 at 11:42 AM