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

1
  • 09 Jan 2024, 01:23 PM

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.

Fixed

Details

Assignee

Reporter

Labels

Worked in

Components

Sprint

Fix versions

Affects versions

Priority

Created January 9, 2024 at 1:25 PM
Updated February 3, 2025 at 9:12 AM
Resolved January 24, 2024 at 5:33 PM