Multiple collection joins result in duplicate results

[Mobe91]

When we have multiple collections joins in combination with restrictions (like an array access on a collection), hibernate generates left joins only. This causes duplicate entries in the result list. E.g.:

LEFT OUTER JOIN personlist personlist6_ ON documentfo0_.id=personlist6_.DocumentForCollections_id
LEFT OUTER JOIN PersonForCollections personforc7_ ON personlist6_.personList_id=personforc7_.id
AND (personlist6_.position=1)

It is not clear whether the alteration of the second join type to an inner joins is a general solution although it would fix this concrete case.

To reproduce, comment in the personList[1] mapping in com.blazebit.persistence.view.testsuite.collections.basic.model.BasicDocumentSetMapListView and run com.blazebit.persistence.view.testsuite.collections.basic.BasicCollectionsTest#testCollections.

[Mobe91]

Blocks #191

[beikov]

Also see: https://hibernate.atlassian.net/browse/HHH-9329
I think we should implement a workaround for this as described in my last comment since the hibernate team is probably not going to fix this before Hibernate 6 and for that we will still have to wait some time.