Criteria and Entity graph generates same join clause twice

Description

During migration from spring boot version 2 to version 3 we have found different behavior between Hibernate 5 and 6. We are using Entity graph and Specification (Criteria). Similar scenario can be seen in provided test case:

SQL generated by Hibernate 5:

select
        distinct jpaunittes0_.id as id1_1_0_,
        jpaunittes1_.id as id1_0_1_,
        jpaunittes0_.address_id as address_3_1_0_,
        jpaunittes0_.name as name2_1_0_,
        jpaunittes1_.description as descript2_0_1_ 
    from
        JPAUnitTestCase$Person jpaunittes0_ 
    left outer join
        JPAUnitTestCase$Address jpaunittes1_ 
            on jpaunittes0_.address_id=jpaunittes1_.id 
    where
        jpaunittes1_.description=? 
    order by
        jpaunittes1_.id asc

SQL generated by Hibernate 6:

select
        distinct p1_0.id,
        a2_0.id,
        a2_0.description,
        p1_0.name 
    from
        "JPAUnitTestCase$Person" p1_0 
    left join
        "JPAUnitTestCase$Address" a1_0 
            on a1_0.id=p1_0.address_id 
    left join
        "JPAUnitTestCase$Address" a2_0 
            on a2_0.id=p1_0.address_id 
    where
        a1_0.description=? 
    order by
        a1_0.id

Hibernate 6 generates same join twice - first time from Criteria and second time from Entity Graph and when you want to sort by column from table in join then it causes crash.

Order by expression "A1_0.ID" must be in the result list in this case.

Discussed on hibernate forum (https://discourse.hibernate.org/t/criteria-and-entity-graph-generates-same-join-clause/8810 )

Attachments

09 Jan 2024, 01:23 PM

Linked issues

is duplicated by

HHH-17690

Joins with Predicates using Criteria API in combination with EntityGraph poducing additional joins.

relates to

HHH-19095

Re-use existing compatible joins for fetch if not used in where clause

Activity

Show:

Robert Mazgut January 11, 2024 at 8:14 AM

You are right on this particular select, but this was just first example. I focused on problem which is described. First case I tested was inner join, but then I decided to test also left join case, just to know whether the result is the same. Unfortunately I used left join as example in description of this bug, but behavior is the same for both inner and left join.

Gavin King January 9, 2024 at 1:56 PM

Note that the query here is pretty weird/wrong. It’s not normal to use left join to join a table which is then used as a restriction in the where clause. That’s not really an outer join, it’s implicitly an inner join.