Message Title

Stanislav Alekminskiy created an issue

Issue Type:	Bug
Affects Versions:	5.1.4, 5.2.8
Assignee:	Unassigned
Components:	hibernate-core, query-hql
Created:	02/Mar/2017 00:03 AM
Labels:	joins query sqlquery
Priority:	Major
Reporter:	Stanislav Alekminskiy

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

Add Comment

This message was sent by Atlassian JIRA