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

When using Hibernate’s setFirstResult() and setMaxResults() to paginate a query that also employs join fetch on a @OneToMany association, the ORM may emit the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!. This indicates that Hibernate cannot push the limit/offset clauses to the database and instead loads the full join result into the JVM for slicing.

The goal is to determine whether the pagination can be kept entirely in SQL without triggering the in‑memory fallback, considering constraints such as the need to avoid duplicate rows, preserve fetch semantics, and maintain compatibility across dialects.

Uncertainty remains around whether adding DISTINCT, adjusting fetch mode, or switching to keyset pagination eliminates the warning while still delivering the intended result set.

• Can Hibernate generate a SQL query with LIMIT/OFFSET when join fetch is used on a @OneToMany association?
• Does applying DISTINCT or using @BatchSize prevent the in‑memory fallback?
• Is keyset pagination compatible with join fetch without triggering the warning?