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

Redundant left outer joins in generated SQL

    Details

      Description

      Redundant left outer joins generated in SQL query when HQL query contains left join on some entity (not by primary key).

      HQL: select c.inventoryNumber from Employee e left join Computer c on c.workplace = e.workplace

      Generated SQL:

      select
              computer1_.Inventory_Number as col_0_0_ 
          from
              Employees employee0_ 
          left outer join
              Workplaces workplace3_ 
                  on employee0_.Workplace_ID=workplace3_.Workplace_ID 
          left outer join
              Computers computer1_ 
                  on (
                      computer1_.Workplace_ID=employee0_.Workplace_ID
                  ) 
          left outer join
              Workplaces workplace2_ 
                  on computer1_.Workplace_ID=workplace2_.Workplace_ID
      

      And if there is join by primary key: select c.inventoryNumber from Employee e left join Computer c on c.workplace*.id* = e.workplace*.id*

      Then the generated SQL looks like:

      select
              computer1_.Inventory_Number as col_0_0_ 
          from
              Employees employee0_ 
          left outer join
              Computers computer1_ 
                  on (
                      computer1_.Workplace_ID=employee0_.Workplace_ID
                  )
      

      No redundant joins here.

      The problem is that such SQL significantly impacts performance when selecting from several large tables.

      To reproduce you can run the following test and see the generated query in output: org.hibernate.bugs.QueryGenerationTestCase#testRedundantSqlJoins
      from https://github.com/stasal/hibernate-tests

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: