When you create a subquery on entity which is joined then additional inner join is created in SQM query.
Entities:
{code:java}@Entity
public class EntityA {
@Id
private Integer id;
@OneToMany
private List<EntityB> bCollection;
}{code}
{code:java}@Entity
public class EntityB {
@Id
private Integer id;
@ManyToOne
private EntityA a;
@ElementCollection
private final Set<EntityC> cCollection = new HashSet<>();
}{code}
{code:java}@Embeddable
public class EntityC {
private LocalDateTime date;
private Long value;
}{code}
Query creation:
{code:java}entityManager.createQuery("""
select distinct a.id, b.id, c.value
from EntityA a
left join EntityB b on b.a.id = a.id
left join b.cCollection as c
where (c.date = (select max(c2.date)
from b.cCollection as c2) or c.date is null)
""");{code}
{code:java}entityManager.createQuery("""
select distinct b.id, c.value
from EntityB b
left join b.cCollection as c
where (c.date = (select max(c2.date)
from b.cCollection as c2) or c.date is null)
""");{code}
In the first query we receive following result:
{noformat}select
distinct e1_0.id,
e2_0.id,
c1_0.value
from
entitya e1_0
left join
entityb e2_0
on e2_0.a_id=e1_0.id
left join
entityb_c_collection c1_0
on e2_0.id=c1_0.entityb_id
join
entityb_c_collection c2_0
on e2_0.id=c2_0.entityb_id
where
(
c1_0.date=(
select
max(c3_0.date)
from
entityb_c_collection c3_0
where
e2_0.id=c3_0.entityb_id
)
or c1_0.date is null
){noformat}
There’s the additional inner join to {{c2_0}}, which is never used.
In the second query this is the result:
{noformat}select
distinct e1_0.id,
c1_0.value
from
entityb e1_0
left join
entityb_c_collection c1_0
on e1_0.id=c1_0.entityb_id
where
(
c1_0.date=(
select
max(c2_0.date)
from
entityb_c_collection c2_0
where
e1_0.id=c2_0.entityb_id
)
or c1_0.date is null
){noformat}
There are not additional joins.
First query with Hibernate 5.6.15:
{noformat}select
distinct entitya0_.id as col_0_0_,
entityb1_.id as col_1_0_,
ccollectio2_.value as col_2_0_
from
entitya entitya0_
left outer join
entityb entityb1_
on (
entityb1_.a_id=entitya0_.id
)
left outer join
entityb_c_collection ccollectio2_
on entityb1_.id=ccollectio2_.entityb_id
where
ccollectio2_.date=(
select
max(ccollectio3_.date)
from
entityb_c_collection ccollectio3_
where
entityb1_.id=ccollectio3_.entityb_id
)
or ccollectio2_.date is null{noformat}
It doesn’t have any additional inner join.
Test case - [https://github.com/damianmajer/hibernate-test-case-templates|https://github.com/damianmajer/hibernate-test-case-templates|smart-link]
I have created same test case in orm-5 and orm-6. In orm-5 it works and it fails in orm-6. |
|
