Hibernate pagination with fetch‑join: incomplete pages after setFirstResult/setMaxResults

Goal

Retrieve a paginated list of root entities using setFirstResult and setMaxResults while eagerly fetching associated collections.

Constraints

When a join‑fetch is used, Hibernate applies the limit/offset after the join operation. This can cause rows that belong to the next page to be discarded, leading to incomplete pages or duplicate root entities if a DistinctResultTransformer is not applied.

Unresolved Decision

Does Hibernate guarantee distinct root entities when pagination is combined with join‑fetch, or must developers explicitly apply a distinct transformer or switch to a different pagination strategy?

Specific Questions

• Is the distinctness of root entities preserved automatically by Hibernate when using setFirstResult/setMaxResults with a join‑fetch?
• What is the impact of different SQL dialects on the order in which the LIMIT/OFFSET is applied relative to the join?
• When should developers prefer ScrollableResults or keyset pagination over the standard offset/limit approach in this context?