Inconsistent page size when using Hibernate fetch join with setMaxResults

Answer the Core Question First

When you use LEFT JOIN FETCH on a collection and add setFirstResult()/setMaxResults(), Hibernate applies the limit to the *raw SQL result set* before it removes duplicate root entities. The database returns a Cartesian product, the limit cuts that product, and then Hibernate deduplicates. The net effect is that the number of distinct root entities in the returned list can be more or less than the requested page size.

Should Hibernate paginate before or after deduplication?

Hibernate’s design is to paginate before deduplication. It cannot translate a collection fetch into a database‑level limit that preserves the root‑entity count because the SQL would need to aggregate the collection rows, which JPQL/HQL doesn’t support. The framework therefore performs in‑memory pagination when a collection fetch is present.

Is there a setting to enforce post‑deduplication limits?

No. Hibernate has no configuration flag to change this behaviour. The only way to get a consistent page size is to avoid applying setMaxResults while a collection fetch is in the query. Typical solutions are:

• Remove the fetch join from the paging query and load collections lazily or with a separate query.
• Use a SELECT DISTINCT on the root entity and then fetch collections in a second step (e.g., JOIN FETCH on a separate query).
• Switch to a DTO projection that only pulls the root fields, then load collections per page.
• Use @BatchSize or @Fetch(FetchMode.SUBSELECT) to batch load collections after pagination.

Impact of changing the limit‑application point

If you remove the fetch join from the paged query, the database will apply the limit correctly and you’ll get exactly maxResults root entities per page. However, you lose the eager loading benefit and may incur additional round‑trips when accessing collections. If you keep the fetch join, you must be prepared for in‑memory pagination and potentially large result sets, which can lead to OutOfMemoryError and degraded performance.

What to check in your logs

WARN  org.hibernate.loader.criteria.CriteriaQuerySpecification - firstResult/maxResults specified with collection fetch; applying in memory!

This warning confirms that Hibernate is falling back to in‑memory pagination. If you see it, consider refactoring the query as described above.

One Diagnostic Detail to Clarify

Does your query join multiple collections with FETCH? If so, you’ll also hit Hibernate’s MultipleBagFetchException, which forces you to split the fetches anyway.

Recommended Steps for Consistent Paging

1. Run the query without any FETCH on collections and verify that the SQL contains LIMIT/OFFSET and that the result size equals maxResults.
2. Load collections in a second query per page, e.g., SELECT c FROM Child c WHERE c.parent IN :parents, using IN (:parents) with the list of root entities.
3. Alternatively, use a DTO projection to fetch only the needed root columns, then batch‑load collections with @BatchSize or @Fetch(FetchMode.SUBSELECT).
4. If you must keep a fetch join, accept that page size may drift and monitor memory usage.

By separating pagination from eager loading, you maintain predictable page sizes while still benefiting from efficient collection loading.