Map/List key restrictions in ON clause not rendered appropriately to SQL for join table relations

[beikov]

A simple JPQL query like

SELECT c.id
FROM Document d
LEFT JOIN d.contacts c
          ON KEY(c) = 1

does not render through the restriction part KEY(c) = 1 correctly in the SQL. The rendered SQL roughly looks like this

SELECT e.id
FROM Document d
LEFT JOIN d.contacts_join_table c
          ON d.id = c.id
LEFT JOIN c.contacts_entity_table e
          ON c.value_id = e.id AND c.contacts_key = 1

Assuming one document has 2 contacts, the first join for contacts_join_table will duplicate rows for the document which is wrong. The condition is wrongly on the second left join which will not cause filtering of duplicates.

The correct SQL for this query and in general a very easy translation looks like this:

SELECT e.id
FROM Document d
LEFT JOIN (
          d.contacts_join_table c
          JOIN c.contacts_entity_table e
                    ON c.value_id = e.id
)
          ON d.id = c.id AND c.contacts_key = 1

I know this looks odd at first because the parenthesis suggest that aliases within are not available outside, but in case of joins, the parenthesis only give a way of grouping joins. Aliases of the tables are available in the whole query, only the join order is different. This is something that already ANSI SQL-92 specified as joined table in table reference.

So the general idea is to group the join table and target table joins into one join group and apply the ON clause restrictions on that join group.

[andyjefferson]

"Join groups" are not supported at all.