Hibernate reports in-memory pagination warning when using setFirstResult/setMaxResults with join fetch

Direct answer

No. With a join fetch on a @OneToMany collection, Hibernate cannot safely push setFirstResult/setMaxResults to SQL. The ORM emits HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! and performs pagination in the JVM after loading the full join result.

Confirmed fact: the warning is emitted by Hibernate when a collection is fetched via join and pagination is requested. The generated SQL will not contain a reliable LIMIT/OFFSET for the root entities, and the full Cartesian product is materialized in memory before slicing.

Likely explanation: a SQL LIMIT applied to a join that multiplies parent rows by collection rows would truncate children of a parent and change entity integrity. Hibernate therefore falls back to in-memory pagination to preserve the mapping semantics.

What does not prevent the fallback

• DISTINCT in HQL/JPQL removes duplicate root objects in Java, but does not change the SQL pagination problem. The warning remains and the full result set is still loaded.
• Keyset pagination on the root table still collides with the collection join. The LIMIT/OFFSET must apply to the root entities, not to the multiplied rows, so the same in-memory fallback is triggered if the collection is join fetched in the same query.

Steps that keep pagination in SQL for this case

1. Verify the behavior. Enable hibernate.show_sql=true and check logs for HHH000104. Compare execution time for page 1 vs a high offset; linear growth indicates in-memory slicing.
2. Do not join fetch the @OneToMany in a paginated query. Paginate the root only.
3. Use two-step pagination, the standard pattern for collection fetches:

   // 1. Page the identifiers only
   List<Long> ids = session.createQuery("select e.id from Entity e order by e.id", Long.class)
     .setFirstResult(offset).setMaxResults(limit).getResultList();
   
   // 2. Load entities with join fetch by the id set
   List<Entity> entities = session.createQuery("select distinct e from Entity e join fetch e.children where e.id in :ids", Entity.class)
     .setParameter("ids", ids).getResultList();

   This keeps LIMIT/OFFSET on the first query and allows a safe join fetch on a bounded id set.
4. Alternative if you can accept lazy loading: remove the join fetch and rely on @BatchSize on the collection. The root query can then use SQL pagination, and children are loaded in batches in a second round trip.

Assumption: the goal is to paginate root entities while still needing the collection initialized. If the collection can be loaded lazily after pagination, the two-step approach can be simplified to batch loading.

One missing diagnostic that changes the recommendation: do you require the @OneToMany collection to be fully initialized in the same transactional query, or is post-load initialization acceptable? The answer determines whether the two-step ID query or a @BatchSize lazy strategy is preferred.