Behavior limits of setFirstResult() with collection fetch joins

Hibernate ORM exposes setFirstResult() and setMaxResults() on Query and TypedQuery to paginate results, delegating to the configured dialect to emit LIMIT/OFFSET or ROWNUM clauses. When a query fetches a collection association, the framework may log HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! and shift pagination from the database into the persistence context.

This behavior raises a practical boundary question for applications managing large result sets: the documented API suggests SQL-level paging, yet the runtime path may load full result graphs before trimming. The decision matters most when combining fetch joins with high offsets, because the database scan cost and the in-memory filtering cost stack rather than replace each other.

Is the in-memory fallback determined exclusively by the presence of a collection fetch join, or do dialect capabilities and association mapping style change the outcome? How does ScrollableResults interact with setMaxResults() when the underlying query triggers HHH000104, and does it reduce persistence context growth compared to getResultList()? Are there documented cases where a dialect silently ignores setFirstResult() instead of translating it, requiring explicit verification of generated SQL?