HHH000104: firstResult/maxResults specified with collection fetch; applying in memory! #234
Description
When using page parameters to limit results of the GraphQL JPA query containing fetch join to load associated collections, Hibernate cannot generate SQL with row limit to truncate the result on the database server. Instead, it generates the following warning in the console, executes SQL query without any pagination limits in order to load the whole result from the database to do the pagination in memory:
HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
The reason why Hibernate does the pagination in memory is that it cannot just truncate the result set using SQL-level pagination, because it would be truncated in the middle of the associated collection rows, therefore returning a root entity with just a subset of data. Because Hibernate favors consistency, it fetches the entire result set and does the pagination in memory that causes JVM to run out of memory when the result set is very large.