setMaxResults vs ScrollableResults: Handling Duplicate Roots in Fetch Joins

Direct answer

setMaxResults / setFirstResult does not give you a reliable page of distinct parent roots when the query contains a fetch join to a collection. ScrollableResults does not fix the duplicate-root problem either; it only changes how the joined rows are streamed from JDBC.

In practice both approaches limit or scroll joined rows, not distinct roots. For large offsets this causes missing parents, repeated parents across pages, and pages that contain fewer distinct roots than requested.

Confirmed facts

• Hibernate translates setMaxResults and setFirstResult to SQL LIMIT/OFFSET after the join is built. The database limits the joined row set, not the number of distinct root entities.
• A fetch join to a collection expands one parent into N rows, one per child. LIMIT/OFFSET therefore operates on those expanded rows.
• ScrollableResults scrolls the same JDBC result set lazily. It requires a scrollable cursor, an open transaction and connection, and driver support. It does not deduplicate roots and does not change offset semantics.
• HQL/Criteria DISTINCT can deduplicate roots in memory after execution, but setMaxResults still truncates the joined row set before deduplication, so pagination remains unsafe.

Likely explanation for the observed behavior

With a collection fetch join the offset boundary often falls inside the child rows of a single parent. The parent is then split across pages or skipped entirely. Because the limit is applied to joined rows, a page can contain the same root multiple times if it has many children, or contain fewer distinct roots than the page size.

ScrollableResults inherits the same row-level semantics. Scrolling row by row still yields repeated root identifiers for multi-child parents. No automatic root-level paging is performed.

Recommended pattern for reliable distinct roots with large offsets

1. Page on roots only. Query only the root identifiers with a keyset predicate on a stable, unique, sortable column, e.g. where root.id > :lastSeenId order by root.id asc and a limit equal to the page size. Avoid offset for large data sets.
2. Load the page of roots and their collections in a second step limited to the identified IDs. Use a join fetch or batch fetching restricted to root.id in (:ids). The ID set is small, so row multiplication is bounded.
3. Keep fetch joins for non-paginated or small result sets only. For paginated access prefer root-first pagination plus batch size or entity graphs for the children.

Practical verification

• Enable SQL logging and run the fetch-join query with setFirstResult/setMaxResults. Compare the number of joined rows returned to the number of distinct root identifiers in the result.
• Scroll the same query with ScrollableResults and print the root identifier per row. Repeated identifiers confirm duplicate-root exposure.
• Test root-only keyset pagination against offset pagination on a representative data set and assert no skipped or repeated root identifiers across page boundaries.

Close ScrollableResults promptly and avoid holding a cursor open across requests. Large IN lists may hit driver or database parameter limits; chunk IDs if needed.

One missing diagnostic detail that changes the recommendation: does the root entity have a stable, unique, sortable key suitable for keyset pagination? If the sort order is not deterministic or the identifier is not monotonic, the keyset approach needs an alternative ordering column.