Subquery in 'where' clause causes additional 'join' creation in SQM

Description

When you create a subquery on entity which is joined then additional inner join is created in SQM query.

Entities:

@Entity
public class EntityA {

    @Id
    private Integer id;

    @OneToMany
    private List<EntityB> bCollection;
}

@Entity
public class EntityB {

    @Id
    private Integer id;

    @ManyToOne
    private EntityA a;

    @ElementCollection
    private final Set<EntityC> cCollection = new HashSet<>();
}

@Embeddable
public class EntityC {

    private LocalDateTime date;

    private Long value;
}

Query creation:

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)
                """);

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)
                """);

In the first query we receive following result:

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
        )

There’s the additional inner join to c2_0, which is never used.

In the second query this is the result:

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
        )

There are not additional joins.

First query with Hibernate 5.6.15:

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

It doesn’t have any additional inner join.

Test case - https://github.com/damianmajer/hibernate-test-case-templates

I have created same test case in orm-5 and orm-6. In orm-5 it works and it fails in orm-6.

Linked issues

is fixed by

HHH-16888

Left join with exists subquery adds extra join

Activity

Show:

Marco Belladelli August 11, 2023 at 12:30 PM

Hi @Damian Majer, thank you for reporting the issue and providing a reproducer.

This error has already been fixed by https://hibernate.atlassian.net/browse/HHH-16888. I will still add your case to Hibernate’s test suite to make sure we continue supporting this correctly.