Join With SQL generation regression

Description

Problem:
When a WITH condition for a LEFT OUTER JOIN of a map relation, which is mapped with a collection table, filters results by KEY the resulting query is wrong in my opinion.

HQL:

Generated SQL:

Hibernate 4.2.7.SP1 and below moved the condition "contacts1_.position=1" into the collection table join.
Hibernate 4.2.8.Final+ always puts the condition in the second join.

Solution:
If such a WITH condition occurs, an additional where-condition has to be added. The where-condition has to be like ALIAS_FOR_VALUE_TABLE.ID IS NOT NULL where ALIAS_FOR_VALUE_TABLE is the name of the table, which contains the values of the map.

Corrected generated SQL:

To summarize, this where-condition, only has to be added if

  • an on-condition contains a KEY expression for the joined relation

  • in a left outer join

  • of indexed(map- or list-attribute)-attributes

  • where the map-key-column or order-column is located in a collection table

Environment

None

Activity

Show:
Gail Badner
February 17, 2016, 1:17 AM

Possibly caused by HHH-8671.

Christian Beikov
September 20, 2016, 5:39 PM

Implemented the proposed approach in: https://github.com/hibernate/hibernate-orm/pull/1561

Steve Ebersole
September 20, 2016, 9:01 PM

Kudos for finding a way to do this Its ugly (sorry), but as I have mentioned I am not sure what else you could do given how Hibernate currently generates SQL.

Gail Badner
June 8, 2017, 12:19 AM

Fixed in 5.1 as well.

Assignee

Christian Beikov

Reporter

Christian Beikov

Fix versions

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure