We're updating the issue view to help you get more done. 

HQL FromElement is not reused in some cases resulting in an additional join

Description

HQL FromElement is not reused in some cases resulting in an additional join

There are some cases where Hibernate does not reuse an HQL FromElement when it should, resulting in the same join being duplicated in the generated SQL:

Here are some examples:

1) a to-one association is referenced in the SELECT clause and explicitly joined in the FROM clausee;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 HQL: select e.department from Employee e inner join e.department Generated SQL: select department2_.id_dep as id_dep1_42_, department2_.deptName as deptName2_42_ from employee employee0_ inner join department department1_ on employee0_.id_depto=department1_.id_dep inner join department department2_ on employee0_.id_depto=department2_.id_dep Expected generated SQL: select department1_.id_dep as id_dep1_42_, department1_.deptName as deptName2_42_ from employee employee0_ inner join department department1_ on employee0_.id_depto=department1_.id_dep

2) SELECT DISTINCT, WHERE, and ORDER BY clauses contain an implicit join;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 HQL: select distinct a.zoo from Animal a where a.zoo is not null order by a.zoo.name Generated SQL: select distinct zoo1_.id as id1_34_, zoo1_.name as name3_34_, zoo1_.classification as classifi4_34_, zoo1_.street as street5_34_, zoo1_.city as city6_34_, zoo1_.postalCode as postalCo7_34_, zoo1_.country as country8_34_, zoo1_.state_prov_id as state_pr9_34_, zoo1_.zooType as zooType2_34_ from Animal animal0_ inner join Zoo zoo1_ on animal0_.zoo_id=zoo1_.id cross join Zoo zoo2_ where animal0_.zoo_id=zoo2_.id and ( animal0_.zoo_id is not null ) order by zoo2_.name Expected generated SQL: select distinct zoo1_.id as id1_34_, zoo1_.name as name3_34_, zoo1_.classification as classifi4_34_, zoo1_.street as street5_34_, zoo1_.city as city6_34_, zoo1_.postalCode as postalCo7_34_, zoo1_.country as country8_34_, zoo1_.state_prov_id as state_pr9_34_, zoo1_.zooType as zooType2_34_ from Animal animal0_ inner join Zoo zoo1_ on animal0_.zoo_id=zoo1_.id where animal0_.zoo_id is not null order by zoo1_.name

3) When there are 2 or more to-one associations and at least 1 is implicitly joined, a non-ANSI join can be in the middle of ANSI joins, causing an exception on those DBs that don't support this (HHH-6326).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 HQL: select e.firstName, e.lastName, e.title.description, e.department from org.hibernate.test.hql.Employee e inner join e.department Generated SQL: select employee0_.firstName as col_0_0_, employee0_.lastName as col_1_0_, title2_.description as col_2_0_, employee0_.id_depto as col_3_0_, department3_.id_dep as id_dep1_42_, department3_.deptName as deptName2_42_ from employee employee0_ inner join department department1_ on employee0_.id_depto=department1_.id_dep, title title2_ inner join department department3_ on employee0_.id_depto=department3_.id_dep where employee0_.id_title=title2_.id_title Expected generated SQL: select employee0_.firstName as col_0_0_, employee0_.lastName as col_1_0_, title2_.description as col_2_0_, employee0_.id_depto as col_3_0_, department1_.id_dep as id_dep1_42_, department1_.deptName as deptName2_42_ from employee employee0_ inner join department department1_ on employee0_.id_depto=department1_.id_dep, title title2_ where employee0_.id_title=title2_.id_title

The reason for incorrect SQL generation is because the fix for was applied to 3.2 and 3.3 branches properly, but was only partially fixed in the trunk (which ultimately was branched for 3.5).

There were 2 commits for 3.3:

15867 (https://github.com/hibernate/hibernate-orm/commit/89cf22bab6bbd5aa317ca7a1df63624bb7b677c4)
15873 (https://github.com/hibernate/hibernate-orm/commit/7b943f0089f6f90cbf836b499e2da7c1ac9e9dea)

There was only 1 commit for trunk, which corresponds to the first commit on 3.3 (15867):
15866 (https://github.com/hibernate/hibernate-orm/commit/4ed21cbce5057cfdaf637bd7e98b3788f87e035d)

Applying the 2nd commit to 4.3 did not cause any unit test failures, and it fixes some regressions, including:

  • : Query translator generates an incorrect query with SELECT distinct + Order by + Implicit join

Environment

None

Status

Assignee

Gail Badner

Reporter

Gail Badner

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Priority

Major