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
Possibly caused by HHH-8671.
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.
Fixed in 5.1 as well.