Hi Sanjay, > my query is like this. > > String queryString = "SELECT a FROM Table_A a LEFT JOIN FETCH > a.collection_of_b WHERE my_condition > Query q = em.createQuery(queryString); > q.setFirstResult(0); > q.setMaxResults(25); > q.getResultList() > > above query does not return 25 records but it returns random number of > records.
Unfortunately you can't rely on using setFirstResult/setMaxResults with fetch joins. The JPA 1 spec indicates it: "The effect of applying setMaxResults or setFirstResult to a query involving fetch joins over collections is undefined." The reason might become clear when you look at the generated SQL. It would be difficult (or even impossible) to apply correct row limiting SQL syntax to such a query as the number of rows in SQL terms does not easily translate to number or rows you would expect in the JPA layer. > If I remove FETCH and just do LEFT JOIN, instead of returning > collection_of_b, it returns 25 but duplicate records. That's most probably correct. This works the same as SQL: if you join table A with table B and return only A columns, you will get as many A rows as there are rows in B. You might want to use DISTINCT in this case. Cheers, Milosz
