Having two unrelated entities:
{noformat}@Entity
public class Entity1 {
@Id
@GeneratedValue
private Long id;
private String uuid;
...
}{noformat}
{noformat}@Entity
public class Entity2 {
@Id
@GeneratedValue
private Long id;
private String uuid;
@ManyToMany(fetch = FetchType.LAZY)
private List<Property> properties;
...
}{noformat}
And the {{Property}}:
{noformat}@Entity
public class Property {
@Id
@GeneratedValue
private Long id;
private String name;
...
}{noformat}
We are trying to get both Entities, but left join only the Entities that have a specific Property:
{noformat} 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();{noformat}
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 Hibernate 6, generating the following query:
{noformat}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=?
)
)
){noformat}
But with Hibernate 6, another join is added at the end which duplicates the results and breaks the left join:
{noformat}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{noformat}
Join that has no purpose:
{noformat}join
Entity2_Property p2_0
on e2_0.id=p2_0.Entity2_id{noformat}
Created test cases for both Hibernate 5 and 6: [https://github.com/danmercean/hibernate-test-case-left-join-exists|https://github.com/danmercean/hibernate-test-case-left-join-exists|smart-link] |
|
