Behavior limits of setFirstResult() with collection fetch joins

Direct answer

No. A collection fetch join is the most common trigger, but the in-memory fallback is not determined exclusively by it. The deciding factor is whether the SQL result set duplicates parent rows for each child row. When that happens, LIMIT/OFFSET semantics are ambiguous, so Hibernate applies pagination after loading the full graph. Dialects with native LIMIT/OFFSET support still fall back in this shape because the issue is result-set duplication, not SQL syntax.

Likely explanation versus confirmed behavior

Likely explanation: Hibernate detects collection fetch joins such as JOIN FETCH orders and logs HHH000104, then trims setFirstResult() and setMaxResults() in the persistence context. Confirmed behavior assumes Hibernate ORM 5.x or 6.x with a standard relational dialect: native dialect pagination does not prevent the fallback when collection duplication would occur.

Association mapping style can change the outcome. Fetching a single-valued association usually does not duplicate the root row, so database-level pagination may remain intact. Fetching a collection, especially a bag or list without a dedicated primary key, produces the Cartesian duplication that forces in-memory handling.

ScrollableResults interaction

When HHH000104 is active, ScrollableResults still iterates over the full result set loaded from the database; setMaxResults() does not reduce the rows fetched. It can limit persistence-context growth compared with getResultList() only if you explicitly evict or clear each processed entity, for example entityManager.clear() or session.evict(entity) inside the scroll loop. Without explicit eviction, managed objects accumulate and memory usage is similar to holding the complete list.

Dialect silent-ignore cases

Some dialect and query combinations can omit OFFSET or LIMIT from generated SQL, particularly with DISTINCT, GROUP BY, or older ROWNUM-based translations. Hibernate may then apply pagination in memory under a different warning. Because this varies by Hibernate version, dialect, and database release, treat the generated SQL as authoritative.

Verification steps

1. Enable SQL logging, such as hibernate.show_sql=true, or attach a StatementInspector.
2. Run the paginated query with the collection fetch join and inspect whether the emitted SQL contains LIMIT, OFFSET, or the dialect equivalent.
3. Check the log for HHH000104; its presence confirms in-memory trimming.
4. Run the same query without the collection fetch join and compare the SQL. If OFFSET disappears only when the join is present, the dialect is not translating pagination for that construct.

Missing diagnostic detail: which Hibernate version and dialect are you using? That determines whether a specific dialect silently drops OFFSET and which replacement strategy is safe.