Pagination behavior change when upgrading Hibernate from 5.6 to 6.0 with fetch joins and ORDER BY

Recommended approach

To guarantee duplicate‑free pagination across the Hibernate 5.6 → 6.0 boundary when using fetch joins on a OneToMany association together with an ORDER BY clause, the safest and most portable solution is to explicitly add DISTINCT (or paginate on the root entity's primary key via a subquery) to the JPQL/HQL query. This works unchanged in both Hibernate versions and is independent of the underlying database dialect.

Why manual DISTINCT is preferred over relying on the new window‑function strategy

• Confirmed fact: Hibernate 6.0 introduced a new LimitHandler that attempts to apply LIMIT/OFFSET before the join (using window functions or a subquery) and added the property hibernate.query.fail_on_pagination_over_collection_fetch to detect problematic queries. However, the documentation notes that this behavior is not guaranteed for all query shapes and depends on the dialect and the presence of ORDER BY.
• Likely explanation: When the ORDER BY columns are not part of the select list or do not uniquely identify the root entity, the window‑function approach can still produce duplicate rows after distinct filtering, or it may reorder rows in a way that breaks pagination consistency.
• Enabling hibernate.query.fail_on_pagination_over_collection_fetch=true only makes Hibernate throw an exception when it cannot guarantee duplicate‑free results; it does not fix the duplication. You would still need to adjust the query.
• Manually adding DISTINCT (or using a subquery that first selects distinct root IDs) forces the database to eliminate duplicates before applying LIMIT/OFFSET, giving deterministic results in Hibernate 5.6, 6.0, and any future version.

Steps to implement the fix

1. Enable SQL logging to inspect the generated statement:

   # application.properties (or equivalent)
   hibernate.show_sql=true
   hibernate.format_sql=true
   

2. Run the paginated query in both Hibernate 5.6 and 6.0 environments and capture the SQL.
3. If the SQL lacks DISTINCT on the root entity (or a subquery on the primary key), rewrite the JPQL/HQL:
   • Option A – DISTINCT on the root:

     SELECT DISTINCT p FROM Parent p JOIN FETCH p.children WHERE ... ORDER BY p.name

   • Option B – paginate on IDs then join:

     SELECT p FROM Parent p WHERE p.id IN (
         SELECT DISTINCT p2.id FROM Parent p2 JOIN FETCH p2.children WHERE ... ORDER BY p2.name
     ) ORDER BY p.name

4. Verify the result:
   1. Execute the query and confirm that the number of rows equals the requested page size.
   2. Ensure the ordering matches the ORDER BY clause without unexpected duplicates.
5. Optional: If you prefer to be alerted when a query is potentially unsafe, set:

   hibernate.query.fail_on_pagination_over_collection_fetch=true

   This will cause Hibernate to throw an exception for queries that cannot be safely paginated, prompting you to apply the DISTINCT/subquery fix.

Performance and dialect considerations

• Adding DISTINCT may require the database to perform a duplicate‑eliminating step (e.g., using a temporary table or sort). For large result sets this can add overhead, but it is usually comparable to the cost of the window‑function approach in Hibernate 6.0.
• Paginating on the root entity’s primary key via a subquery often performs better because the inner query can use an index on the ID and the outer join fetches only the needed rows.
• The behavior of the window‑function strategy varies by dialect (MySQL, PostgreSQL, Oracle, etc.). Manual DISTINCT or ID‑based pagination yields consistent results across all supported databases.

Missing diagnostic detail

To confirm whether the manual DISTINCT approach will preserve deterministic ordering, we need to know: Are the columns referenced in the ORDER BY clause included in the SELECT list (or do they uniquely identify each root entity)? If they are not, you may need to add those columns to the SELECT (or rely on the primary‑key subquery) to avoid nondeterministic ordering.