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.
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.
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