hibernate.query.fail_on_pagination_over_collection_fetch strict-mode adoption barrier in Hibernate 6.2

Page-size consistency guarantees in strict vs. lenient mode

With hibernate.query.fail_on_pagination_over_collection_fetch=false (the default), Hibernate applies the SQL LIMIT/OFFSET over a result set that includes one row per collection element fetched via a join. In-memory distinct-root deduplication then attempts to collapse duplicates, but when collection cardinality varies across entities, the number of distinct root entities returned can fall below the requested page size, producing unpredictable page sizes.

Setting the property to true causes Hibernate to throw an exception at query execution time when firstResult/maxResults are combined with a collection fetch join. This converts the silent data-integrity risk into an explicit failure. Strict mode does not itself fix pagination; it guarantees that no paginated query with an in-memory collection fetch executes silently. Every returned page is therefore produced by a query pattern that is safe by construction, which means page sizes are consistent by design.

Does the exception message suffice for DTO or scrollable-result replacement?

The exception identifies the offending pattern (pagination over a collection fetch) but does not prescribe a specific fix. Developers must choose between a two-query approach (paginate root IDs, then load collections by ID), a DTO projection that avoids the join entirely, or streaming with ScrollableResults--which is a streaming alternative, not a drop-in pagination fix. The message is sufficient to locate the query, but not to automatically replace it.

Can a phased configuration migrate teams from false to true without widespread query rewrites?

1. Enable the property in a test or staging persistence unit and run the existing paginated suite.
2. Inventory every query that throws. The exception type and message are version-sensitive across Hibernate 6.x; confirm the exact type for your minor release rather than assuming IllegalStateException.
3. Refactor each offending query using one of the canonical patterns:
   • Two-step pagination (paginated ID query + collection fetch by ID)
   • DTO projection with a flat result shape
   • Batch fetching via @BatchSize or hibernate.jdbc.batch_size
4. Re-run the suite to verify stable page sizes and identical result content.
5. Promote the property to production only after all queries pass.

Because the flag is global per SessionFactory/EntityManagerFactory, there is no standard per-query opt-out; the rollout therefore requires query-level remediation before the property can be safely true in production. If you can confirm the Hibernate 6.x minor version in use, I can tailor the exception-type expectations and migration timeline accordingly.