[hibernate-issues] [JIRA] (HHH-17040) Hibernate can return duplicate records on join depending on row ordering

Alex Wood ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYjVlOWY3YWIz... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-17040?atlOrigin=eyJpIjoiYjVlOW... ) HHH-17040 ( https://hibernate.atlassian.net/browse/HHH-17040?atlOrigin=eyJpIjoiYjVlOW... ) Hibernate can return duplicate records on join depending on row ordering ( https://hibernate.atlassian.net/browse/HHH-17040?atlOrigin=eyJpIjoiYjVlOW... ) Issue Type: Bug Affects Versions: 6.2.7 Assignee: Unassigned Created: 07/Aug/2023 07:43 AM Environment: openjdk 17.0.7 2023-04-18 Priority: Major Reporter: Alex Wood ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) 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. ( https://hibernate.atlassian.net/browse/HHH-17040#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-17040#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100233- sha1:2faa3a6 )