I have 2 entities and a relation many to many (example below).
I run the code below.
Query runs as "select * from Parent where id > 123 limit 10".
automatic query for child collection runs as "select * from Child ... where parent_id in (select id from Parent where id > 123)"
but expected as "select * from Child ... where parent_id in (select id from Parent where id > 123 limit 10)"
I understand that hibernate will filter all unrelated records, but without limit it selects almost all records of all 3 tables containing millions of records.
I had to split execution into 2 queries:
1. select id from Parent where id > 123 limit 10 - save list of ids
2. select * from Parent where id in (:ids)

model:
class Parent

class Child

code:

Query query = entityManager.createQuery("SELECT P FROM Parent P WHERE ID > 123)");
query.setMaxResults(10).
List<Parent> parentList = query.getResultList();
for (Parent parent : parentList) {
for (Child child : parent.children)

}