setMaxResults vs ScrollableResults: Handling Duplicate Roots in Fetch Joins

Large‑Dataset Pagination with Fetch Joins

When querying a parent entity that eagerly fetches a collection, Hibernate can materialize the same root multiple times before deduplication. This becomes problematic when paginating a very large result set: the setMaxResults method applies a LIMIT/OFFSET clause, but the offset may be applied after the join, causing missing or duplicated rows across pages. The ScrollableResults API retrieves rows lazily via JDBC, potentially avoiding duplicate root materialization, yet it relies on driver support and still may expose the same issue if the fetch strategy is not handled correctly.

Unresolved Decision

Which approach provides reliable, distinct root entities while maintaining performance for large offsets?

Specific Questions

• Does setMaxResults correctly honor the offset when a fetch join to a collection is present, or does the offset get applied after the join expansion?
• Can ScrollableResults guarantee that each root entity appears only once per page, or does it inherit the same duplicate‑root problem?
• What pattern (e.g., distinctRootEntity transformer, keyset pagination, or a custom ResultTransformer) is currently recommended to ensure distinct roots without sacrificing performance?