Does Hibernate paginate JOIN FETCH queries in memory instead of SQL?

Short answer

Yes. In Hibernate ORM 6.x, if a JPQL/HQL query contains a JOIN FETCH of a collection-valued attribute and you also call setFirstResult()/setMaxResults(), Hibernate does not apply the limit in SQL. It executes the full join, deduplicates the parent rows, and slices the result list in memory. It logs a warning (message id HHH000104, roughly: firstResult/maxResults specified with collection fetch; applying in memory) at WARN level when this happens.

Why it behaves this way

A collection fetch join multiplies each parent row by the number of joined children. A SQL LIMIT 20 would cap joined rows, not parents — a parent with 20 children could consume the entire page and have its collection silently truncated. Since Hibernate cannot express "20 distinct parents" with a plain row limit on a joined result, it refuses to push pagination down and does it in Java instead.

Confirmed vs. assumed

• Confirmed behavior (well-established, Hibernate 5 and 6): the SQL omits LIMIT/OFFSET; all rows matching the WHERE clause are fetched; the WARN line is emitted; the returned list is sliced to the requested page.
• Collection type: the fallback applies equally to List (bag or ordered), Set, and other collection mappings. The mapping type changes how duplicates are handled in the result, not whether pagination moves to memory.
• Likely but worth verifying in your setup: exact warning text and logger category vary slightly across 6.x point releases, so confirm against your version rather than grepping for a hardcoded string.

How to verify against generated SQL

spring.jpa.show-sql=true
logging.level.org.hibernate.orm.deprecation=WARN
logging.level.org.hibernate=WARN

Run the query with setFirstResult(0) and setMaxResults(10) against a table with more than 10 matching parents. You should see (a) SQL with the join but no limit/offset clause, and (b) the HHH000104 warning. Repeat with List, Set, and bag mappings to confirm identical SQL shape.

The accepted fix: two queries

Yes — splitting is the standard pattern. First paginate on parent IDs only (this query has no collection join, so LIMIT/OFFSET stay in SQL), then fetch the collections for just those IDs:

// Query 1: paginated, SQL-level
List<Long> ids = em.createQuery(
    "select p.id from Parent p where ... order by p.id", Long.class)
  .setFirstResult(offset).setMaxResults(size).getResultList();

// Query 2: fetch collections for the page only
List<Parent> parents = em.createQuery(
    "select distinct p from Parent p left join fetch p.children "
  + "where p.id in :ids order by p.id", Parent.class)
  .setParameter("ids", ids).getResultList();

The second query has no pagination parameters, so no in-memory fallback is triggered, and the database only ever sees a bounded ID list. An entity graph on the second query is an equivalent alternative to the explicit fetch join.

Two operational caveats

• Always add a deterministic ORDER BY (e.g., on the primary key). Without it, row order before in-memory slicing is undefined, so pages can overlap or skip rows even in the fallback path.
• If your log level is set above WARN, the HHH000104 warning disappears and the first symptom of the problem becomes memory pressure in production. A quick regression test asserting the presence of limit in the logged SQL is cheap insurance.

One missing detail only matters if you see different behavior: your exact Hibernate 6.x minor version and whether the query uses distinct explicitly — include the logged SQL and warning line if results diverge from the above.