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:

1 2 3 4 5 6 7 SELECT d.id FROM Document d LEFT JOIN d.contacts c WITH KEY(c) = 1

Generated SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 select document0_.id as col_0_0_ from Document document0_ left outer join Document_Person contacts1_ on document0_.id=contacts1_.Document_id left outer join Person person2_ on contacts1_.contacts_id=person2_.id and ( contacts1_.position=1 )

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select document0_.id as col_0_0_ from Document document0_ left outer join Document_Person contacts1_ on document0_.id=contacts1_.Document_id left outer join Person person2_ on contacts1_.contacts_id=person2_.id and ( contacts1_.position=1 ) where person2_.id IS NOT NULL

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

Status

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

5.0.2
4.2.8

Priority

Major