Native SQL Pagination Limits with setMaxResults
26.5K reputation · 22 Mar 2021, 04:08 UTC
Investigate how setMaxResults interacts with Hibernate query execution when native SQL is employed, given that the first-level cache does not support pagination parameters. The documented behavior specifies that setFirstResult defines a zero-based index and setMaxResults limits result count, but exact SQL generation varies by dialect. A precise goal is to determine whether omitting an explicit orderBy clause with setFirstResult produces non-deterministic ordering when entities are cached across Hibernate 5.x and 6.x. Constraints include the requirement for ordered result sets to ensure deterministic pagination and the performance impact of large offset values due to database row scanning. Uncertainty remains about how dialect-specific LIMIT/OFFSET syntax affects result boundaries when the cache bypasses first/max results entirely.
Does the absence of cache support for setFirstResult/setMaxResults introduce ordering inconsistencies when native SQL pagination is combined with a default entity identifier order? Can dialect-dependent LIMIT/OFFSET generation cause silent result set truncation at high offset values? Is there a documented strategy to enforce deterministic pagination without explicit orderBy when using native SQL queries?
npss1 answer
1 question comment
Use comments to ask for clarification. Post a solution as an answer.
26,525 reputation · 22 Mar 2021, 15:22 UTC
One clarification worth adding: the in-memory pagination pitfall that affects HQL queries with collection fetch joins does not apply to native SQL. Hibernate logs a warning and applies setMaxResults in memory for joined entity fetches because it must deduplicate parent rows; with createNativeQuery, it never rewrites your SQL, so the limit applies to raw result rows exactly as the dialect emits it. That also means a native query joining a one-to-many collection can return fewer distinct parents than maxResults suggests — the limit counts joined rows, not entities.
On the deep-offset concern, a practical escape hatch is keyset (seek) pagination: WHERE id > :lastSeenId ORDER BY id with setMaxResults. It avoids the scan-and-discard cost entirely and works identically for native and HQL. Verify with hibernate.show_sql that the expected LIMIT/FETCH FIRST clause appears, and test paging against the real database — dialect output differs between Hibernate 5.x and 6.x.