@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
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_idJoin that has no purpose:
join Entity2_Property p2_0 on e2_0.id=p2_0.Entity2_idCreated test cases for both Hibernate 5 and 6: https://github.com/danmercean/hibernate-test-case-left-join-exists