We have a query that is fetching a grandchild collection via outer join. There should only be one root object but in certain cases Hibernate can return duplicate root objects. The cause of the inconsistency seems to be the ordering of the rows in the results. If all the rows for the root object are grouped together (via an “order by” clause) for example, only one root is returned. If the rows are disordered, duplicates are returned.
I’ve simulated the disordered results using an “order by random()” in the unit test, but in our product environment we’ve seen rows can be returned ordered or unordered (it seems to depend on the number of rows in the table as to whether the database decides to order the results). I’ve included explain plans for different database behavior for the same query as part of my pull request.
Unit test is available at [https://github.com/hibernate/hibernate-test-case-templates/pull/301|https://github.com/hibernate/hibernate-test-case-templates/pull/301|smart-link] |
|