LEFT JOIN FETCH returns double results

I recently got a Bugreport from my manager that she was seeing double rows in one of our screens. After a little research I found the culprit to be the following (paraphrased) HQL query.

SELECT order from Order order LEFT JOIN FETCH order.items

The resultset returned from the database will look like this:

orderid	customerid	date	orderitemid	orderid	articlenr	amount
1	1	2015-01-01	1	1	245	5
1	1	2015-01-01	2	1	574	1

As you can see, the order appears twice, once for each order item.

What we want to get from Hibernate is:

What we get from hibernate is:

Order
Order@25AF4E
Order@25AF4E

However, this is by design. As you can see you get two Orders, but they both refer to the same instance. Things are as it should be.

To fix this, see the great answer by Gavin King¹ or on StackOverflow².

When changing the result into:

SELECT DISTINCT order from Order order LEFT JOIN FETCH order.items

The double rows disappears, however the distinct does go through to the database (where it does nothing, as the rows are already distinct) however, it also adds the ResultTransformer implicitly, causing me to get the one row as designed.

Talked about it with the Architect, he doesn't like the use of 'distinct' in HQL queries, because:

• it is unclear how it will be processed (locally or in the database)
• may be a cause for performance issues if the distinct is forwarded to your database
• In general, if you need a 'distinct' in your query to defeat double data, chances are that the query could use some improvements

With an explicit ResultTransformer, the code looks like the following:

List result = session.createQuery("select o from Order o left join fetch o.items") 
                      .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                      .list();

And the problem is fixed.

References

[1] JBoss - Hibernate FAQ

https://developer.jboss.org/wiki/HibernateFAQ-AdvancedProblems#jive_content_id_Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword

[2] StackOverflow - Hibernate Criteria returns children multiple times with FetchType.EAGER

http://stackoverflow.com/questions/1995080/hibernate-criteria-returns-children-multiple-times-with-fetchtype-eager