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

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