# Difference between Query#getResultStream() / Query#getResultList() and JOIN FETCH

**URL:** <https://discourse.hibernate.org/t/difference-between-query-getresultstream-query-getresultlist-and-join-fetch/7109>\
**Category:** Hibernate ORM\
**Created:** [January 5, 2023, 9:58pm UTC](https://discourse.hibernate.org/t/difference-between-query-getresultstream-query-getresultlist-and-join-fetch/7109 "2023-01-05T21:58:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![tomsontom](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tomsontom/32/2198_2.png) [@tomsontom](https://discourse.hibernate.org/u/tomsontom)\
**Post date:** [January 5, 2023, 9:58pm UTC](https://discourse.hibernate.org/t/difference-between-query-getresultstream-query-getresultlist-and-join-fetch/7109/1 "2023-01-05T21:58:01Z")

</div>

If I use JOIN FETCH and DISTINCT Hibernate deduplicates the duplicates if I use `getResultList()` - my probably wrong expectation was that this would hold true for `getResultStream()` as well.

 ![export](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/b/bacd265fd6204c627120735222dabe180ba186fd.png)

But as my tests show it is not. So my question are:

- is expected that hibernate doesn’t do the deduplication also on `getResultStream()`
- should i better use `getResultStream().distinct()....` oder `getResultList().stream()...`

---

<div class="post-metadata">

**Author:** ![tomsontom](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tomsontom/32/2198_2.png) [@tomsontom](https://discourse.hibernate.org/u/tomsontom)\
**Post date:** [January 8, 2023, 9:38am UTC](https://discourse.hibernate.org/t/difference-between-query-getresultstream-query-getresultlist-and-join-fetch/7109/2 "2023-01-08T09:38:46Z")

</div>

It was pointed out at the twitter thread I started that this problem goes away by ordering the result by kl.id - this also explains to me that our Unit-Tests did not find this problem because they set up data in away (and I assume most tests do) that the naturaly order in the JOINED-Table matches exactly the one in the “klient”-Table.

I think this makes the behavior worse because there is some sort of deduplication going on but only if the entities are returned in order.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [January 9, 2023, 9:31am UTC](https://discourse.hibernate.org/t/difference-between-query-getresultstream-query-getresultlist-and-join-fetch/7109/3 "2023-01-09T09:31:20Z")

</div>

`getResultStream()` will make use of the JDBC scrolling API which is lazy i.e. fetches rows on demand. De-duplication usually requires that a set of objects is materialized to check for duplicates, which defeats the purpose of streaming, as you should only do streaming if chances are high, that you can’t fit all your results in memory.  
I would advise you against using `getResultStream()` unless you are sure that memory consumption is an issue, as the JDBC scrolling API is usually implemented with database cursors, which comes with other possible issues, especially if the cursor is kept open for long.

I guess `vertragsDaten` is a collection? If so, then streaming/scrolling won’t work for you anyway, because that kind of requires that the entity cardinality matches the row cardinality i.e. join fetched collections are disallowed.

---

<div class="post-metadata">

**Author:** ![tomsontom](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tomsontom/32/2198_2.png) [@tomsontom](https://discourse.hibernate.org/u/tomsontom)\
**Post date:** [January 9, 2023, 10:57am UTC](https://discourse.hibernate.org/t/difference-between-query-getresultstream-query-getresultlist-and-join-fetch/7109/4 "2023-01-09T10:57:43Z")

</div>

Yes vertragsDaten is a collection - your conclusion that FETCH JOIN are disallowed sounds reasonable - but I would expect if this is true that Hibernate throws an error or at least logs a warning.

As I stated in my other reply the main problem is that Hibernate does remove duplicates under some circumstances - so there a big likely hood that people get hit by this in production as their JUnit-Test setup most likely creates a system where hibernate deduplicates them.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [January 9, 2023, 12:55pm UTC](https://discourse.hibernate.org/t/difference-between-query-getresultstream-query-getresultlist-and-join-fetch/7109/5 "2023-01-09T12:55:45Z")

</div>

Hibernate 6 by default does deduplication for queries that select a single entity alias. Actually, it seems like there is special code for scrollable results when collection fetches are involved, though it doesn’t seem to handle deduplication. Also see `FetchingScrollableResultsImpl`. In that case, I’d classify this as a bug, so please create a JIRA issue and attach a reproducer [test case](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java).