Hibernate JOIN FETCH Pagination Falls Back to In-Memory Processing

Hibernate ORM supports pagination through the Query interface using setFirstResult and setMaxResults. When a query includes a JOIN FETCH on a collection association, Hibernate may apply pagination in memory rather than generating SQL LIMIT/OFFSET clauses. This fallback is intended to avoid Cartesian product data loss, but it can introduce significant memory pressure and performance degradation on large result sets.

The decision to switch from database-level pagination to in-memory pagination depends on the shape of the fetched associations and the configured dialect. It remains unclear under exactly which mapping or query configurations Hibernate will emit the firstResult/maxResults warning versus silently loading the full result set before truncating it. Version-specific differences in how recent Hibernate releases handle this boundary add further uncertainty.

Open questions

1. Under what precise entity mapping or query conditions does Hibernate decide to apply firstResult/maxResults in memory instead of delegating to the database dialect?
2. Is there a reliable way to determine before execution whether a given JOIN FETCH query will trigger in-memory pagination?
3. How does this behavior vary across current Hibernate 6.x releases when paginating queries that fetch multiple collection associations?