Hibernate Query pagination with JOIN FETCH and database limit/offset

Configuration Controlling In-Memory Pagination

The behavior is governed by the boolean property hibernate.query.fail_on_pagination_over_collection_fetch (default false). When false, Hibernate allows in‑memory pagination and logs warning HHH000104. Setting it to true forces Hibernate to throw a QueryException instead of falling back.

Dialect Capability Evaluation

Hibernate first checks whether the underlying dialect supports LIMIT/OFFSET. If supported, it then inspects the query shape: any JOIN FETCH on a collection‑valued association marks the query as unsafe for database‑level pagination because the join would produce a Cartesian product that multiplies root‑entity rows. For such queries the dialect’s limit capability is ignored and the fallback path is taken. Single‑ended fetches (ManyToOne/OneToOne) do not change the row count, so the dialect can safely apply LIMIT/OFFSET.

Documented Behavior When Limit/Offset Cannot Be Applied Safely

When the unsafe condition is detected, Hibernate:

1. Executes the query without LIMIT/OFFSET.
2. Loads the complete result set into the JVM.
3. Applies the requested setFirstResult() and setMaxResults() by slicing the list in memory.
4. Logs the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!.

This can lead to excessive memory usage and OutOfMemoryError for large datasets.

Verification Steps

• Enable SQL logging (hibernate.show_sql=true) and confirm that the generated SQL lacks LIMIT/OFFSET for a paginated query with a collection fetch join.
• Search logs for the warning code HHH000104.