Hibernate fetch‑join pagination without implicit DISTINCT

Direct answer

No. There is no global configuration property, query hint, or Criteria API setting in Hibernate 5.x or 6.x that automatically injects a DISTINCT clause when setFirstResult/setMaxResults are used together with a fetch join. The SQL LIMIT/OFFSET applies to the joined result rows, so duplicate parent entities reduce the effective page size.

Why Hibernate behaves this way

When a fetch join is present, the generated SQL returns one row per child entity. Applying pagination at the SQL level therefore limits the number of joined rows, not the number of distinct root entities. Hibernate deliberately avoids rewriting the query with an implicit DISTINCT because that would force the database to materialise a large temporary result set before paging, which can be disastrous for performance on big data volumes.

Supported workaround: two‑query pattern

The documented and reliable approach is to split the operation into two queries within the same transaction:

1. Pagination query – select only the root‑entity identifiers with setFirstResult/setMaxResults and no fetch joins.

   SELECT id FROM ParentEntity p WHERE ... ORDER BY p.id

2. Fetch query – load the full entity graph for the collected IDs using an IN clause, a JPA EntityGraph, or CriteriaBuilder with a fetch plan.

   SELECT p FROM ParentEntity p JOIN FETCH p.children WHERE p.id IN (:ids)

This guarantees that each page returns exactly pageSize distinct root entities (except the last page).

What about query hints and PASS_DISTINCT_THROUGH?

The hint org.hibernate.query.Hint.PASS_DISTINCT_THROUGH=false only controls whether a DISTINCT you wrote yourself is passed to the database; it does not add one. No other hint or Criteria setting changes this behaviour.

Hibernate 6.2+ alternative

Hibernate 6.2 introduced @Fetch(FetchMode.SELECT) combined with @BatchSize. This avoids the pagination join entirely by loading collections in separate batched selects. However, for strict page‑size guarantees you still need the two‑query pattern because the batched selects run after the paginated root query.

Verification steps for production‑like environments

1. Enable SQL logging: hibernate.show_sql=true, hibernate.format_sql=true.
2. Capture the generated queries for a paginated fetch‑join request.
3. Count distinct root‑entity IDs returned by the first query and compare with the requested page size.
4. Run a load test with a dataset where parents have variable child counts; assert each page returns exactly pageSize distinct parents.
5. Check second‑level cache hit rates (Statistics.getSecondLevelCacheHitCount) remain high when the two‑query pattern is used with cached entities.
6. Inspect execution plans for both queries to confirm index usage on the paginated ID query and efficient IN‑list or join on the fetch query.

Trade‑offs

• Second‑level cache: unaffected because entities are loaded by ID; enable cache on the entity and its collections for best performance.
• Database optimizer: the paginated ID query is narrow and index‑friendly; the fetch query runs on a small, known ID set.
• Transaction scope: both queries must run in the same transaction to avoid stale reads if data changes between them.

One missing diagnostic detail

Which database dialect and exact Hibernate version are you targeting? EntityGraph fetch plans are vendor‑specific, and the generated SQL for the second query can vary between PostgreSQL, MySQL, Oracle, and SQL Server.