|
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;
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;
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).
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
HHH-3749
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:
-
HHH-8290 : Query translator generates an incorrect query with SELECT distinct + Order by + Implicit join
|
