Uploaded image for project: 'Hibernate ORM'
  1. HHH-9305

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

    Details

    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true
    • Sprint:

      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;

      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 Open ).

      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 Closed 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 Closed : Query translator generates an incorrect query with SELECT distinct + Order by + Implicit join

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                gbadner Gail Badner
                Reporter:
                gbadner Gail Badner
                Participants:
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: