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

Redundant left outer joins in generated SQL

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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:

1 2 3 4 5 6 7 8 9 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

Environment

None

Status

Assignee

Christian Beikov

Reporter

Станислав Алёкминский

Fix versions

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

backportDecision

None

Components

Affects versions

5.2.8
5.1.4

Priority

Major