Thanks for reply Milosz, how my query will be so it will return 25 records from TABLE_A with collection_of_b populated in each record.
-sanjay -----Original Message----- From: Miłosz Tylenda [mailto:mtyle...@o2.pl] Sent: Thursday, July 30, 2009 11:02 AM To: users@openjpa.apache.org Subject: Re: setMaxResults does not return specified number of records 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
