skip to content

A JPQL query left-join-fetches a one-to-many collection and also calls setMaxResults; Hibernate logs a warning that firstResult/maxResults were specified with a collection fetch and are being applied in memory. What is Hibernate actually doing, and why is that dangerous in production?

level: middleimportance: must knowfreq 55%

answer

  1. fetch join collection = N rows per parent
  2. limit would truncate a collection
  3. HHH000104 / HHH90003004
  4. whole result set materialised, then sliced
  5. fail_on_pagination_over_collection_fetch

basics

~20 s

A collection fetch join returns one SQL row per child, so a SQL LIMIT would cut a parent's collection in half. Hibernate drops the limit, runs the whole query, builds every matching parent in memory, then slices — unbounded reads and heap use.

solid answer

~50 s

With `left join fetch o.items`, one order with ten items is ten SQL rows. Hibernate can no longer map "20 rows" to "20 orders", and a SQL `LIMIT 20` would return a *partial* collection for the last parent — silently wrong data. So Hibernate chooses correctness: it strips `limit/offset` out of the generated SQL, executes the **entire** matching result set, de-duplicates parents while assembling their collections, and only then applies `firstResult`/`maxResults` to that in-memory list. Hibernate 5 logs `HHH000104`, Hibernate 6 logs `HHH90003004`; the text is the same. The danger is that results stay *correct*, so nothing fails in test data. In production the query reads the whole table plus its children on every request: latency scales with total rows, heap holds every entity, and the persistence context balloons. It typically surfaces as an OutOfMemoryError or a timeout months later. Fetch-joining a to-one association does not multiply rows, so paging stays in SQL. The fixes are a two-query id-then-fetch, batch/subselect fetching, or a DTO projection.

code

java · 7 lines
java
List<Order> page = em.createQuery(
        "select o from Order o left join fetch o.items " +
        "where o.status = :st order by o.createdAt desc, o.id desc", Order.class)
    .setParameter("st", Status.OPEN)
    .setFirstResult(0)
    .setMaxResults(20)   // WARN: applied in memory, SQL has no limit
    .getResultList();

go deeper

for a junior

Recognise the warning and know it means the page limit was not sent to the database. Knowing that collection fetch joins and paging do not mix is enough.

for a middle

Explain the row-multiplication mechanic, why truncating in SQL would corrupt a collection, and name at least one fix such as the two-query approach.

for a senior

Talk about the production failure mode — heap and latency scaling with table size while results stay correct — and about enforcing the fail-fast setting in tests plus choosing between batch fetching, two queries and projections.

for a principal

Position it as a class of defect that must be caught by tooling rather than review: fail-fast configuration, query-count assertions in tests, and a fetch-strategy convention that keeps collections lazy by default.

## Why the row count stops matching the entity count A fetch join over a collection is an ordinary SQL join. If `Order 1` has 3 items and `Order 2` has 7, the join produces 10 rows, and Hibernate assembles them into 2 `Order` entities each with its list populated. The result *list* has 2 elements; the result *set* had 10 rows. Pagination in SQL is expressed in rows. So `limit 20` means "20 join rows", which could be one order with 20 items, or five orders where the fifth is missing half its items. That last outcome is the killer: Hibernate would hand you an `Order` whose `items` collection is populated but **incomplete**, and because the collection is initialised, nothing would ever load the rest. You would silently ship wrong data. ## Hibernate's choice Hibernate refuses to be wrong. When it detects `firstResult`/`maxResults` together with a fetched collection it: 1. Removes the limit clause from the generated SQL. 2. Executes the query unbounded — every row matching the `WHERE`. 3. Streams the JDBC rows, de-duplicating root entities by identifier and attaching children to the right parent. 4. Applies the offset and page size to the resulting in-memory `List`. And it warns you: `HHH000104: firstResult/maxResults specified with collection fetch; applying in memory` in Hibernate 5, `HHH90003004` with the same message in Hibernate 6. Hibernate 6 also lets you turn that warning into an exception (`hibernate.query.fail_on_pagination_over_collection_fetch = true`), which is the setting you want in CI so the trap can never reach production unnoticed. ## Why it is dangerous rather than merely slow - **The results are correct.** No exception, no wrong row. It passes review and passes tests with 50 rows of seed data. - **Cost scales with the whole table, not the page.** Page 1 of a 5-million-row table reads 5 million rows plus their children. - **Memory is the first casualty.** Every matched entity, its children, and the persistence context's snapshot copies for dirty checking sit in the heap simultaneously. This is a classic OutOfMemoryError source. - **It gets worse silently.** Latency and heap grow with data volume; the code never changed, so nobody suspects the query. - **Connection hold time** grows with it, so a slow query drains the pool under load. ## Related things people confuse it with - **To-one fetch joins are fine.** `join fetch o.customer` yields one row per order, so the row count equals the entity count and Hibernate keeps `limit` in SQL. The trap is specific to collection-valued (`@OneToMany`/`@ManyToMany`) fetches. - **`distinct` does not save you.** In Hibernate 5, `select distinct o` both de-duplicated in Java and (unless `hibernate.query.passDistinctThrough=false`) pushed a useless `DISTINCT` into SQL, which cost a sort of the whole join. In Hibernate 6 root entities are de-duplicated automatically and that hint was removed. Either way, de-duplication is not pagination. - **Two collection bags cannot even be fetched together.** Fetch-joining two `List` associations throws `MultipleBagFetchException`, a different symptom of the same row-multiplication problem. ## The fixes, in the order to try them 1. **Do not fetch the collection at all.** Page the parents with a plain query, project what the screen needs, and load children only when the screen actually shows them. 2. **Two-query id-then-fetch.** Query a page of ids with SQL-level paging, then a second query with the fetch join restricted to `where o.id in :ids`. Both queries are bounded. 3. **Batch or subselect fetching.** Leave the collection lazy and let `@BatchSize(size = n)` or `@Fetch(FetchMode.SUBSELECT)` initialise all pages' collections in a handful of extra statements — the page query itself stays paginated in SQL. 4. **DTO projection.** Select scalar columns for the list view; the join can still multiply rows, so aggregate or do a second query for children. ## How to catch it Turn on SQL logging in tests and assert that the page query carries a `limit`. Enable the fail-on-pagination setting in the test profile. Grep the codebase for `join fetch` on a collection appearing in the same query builder as a page size.

  • Would the same warning appear if the query fetch-joined a @ManyToOne instead?
    No. A to-one fetch join produces exactly one row per root entity, so a SQL row limit maps cleanly onto an entity limit and Hibernate keeps limit/offset in the generated SQL. The in-memory fallback exists only because collection joins multiply rows and a SQL limit could truncate a parent's collection.
  • Does adding DISTINCT to the JPQL make pagination safe again?
    No. DISTINCT only removes duplicate root references from the returned list; the join still produces one row per child, so a SQL limit would still cut a collection short and Hibernate still paginates in memory. In Hibernate 5 it also pushed a real SQL DISTINCT that sorted the whole join for nothing, which is why the passDistinctThrough hint existed; Hibernate 6 de-duplicates automatically and removed the hint.

saying these in an interview costs you the question

  • Claiming the warning is harmless because the results are correct
  • Adding DISTINCT and believing pagination is now done in SQL
  • Thinking the limit still reaches the database and Hibernate only 'adjusts' it
  • Assuming it only affects @OneToMany and not @ManyToMany
  • Fixing it by raising the JVM heap instead of bounding the query

context

resolving links…