[eclipselink-users] Question about weird paging behavior

[Noah White <emailnbw@xxxxxxxxx>]

I have a bit of code that does nested paging with two different queries like so:

TypedQuery<String> dogNameQuery = entityManager.createNamedQuery("Dog.getAllDogsNames", String.class);
dogNameQuery.setParameter("kennel", kennel);

int offset = 0;
List<String> dogNameList;

do {
	dogNameQuery.setFirstResult(offset);
	dogNameQuery.setMaxResult(pageSize);
	dogNameList = dogNameQuery.getResultList();

	List<Owner> dogOwnerList;

	for(String dogName : dogNameList) {

		TypedQuery<Owner> dogOwnerQuery = entityManager.createNamedQuery("Owner.findOwnersWithDogName", Owner.class);
		dogOwnerQuery.setParameter("dogName", dogName);

		int ownerOffset = 0;
		do {

			dogOwnerQuery.setFirstResult(ownerOffset);
			dogOwnerQuery.setMaxResult(pageSize);
			dogOwnerList = dogOwnerQuery.getResultList();

			... do stuff ...

			ownerOffset += pageSize;
			// Flush and clear the EM to save memory per page iteration
			entityManager.flush();
			entityManager.clear();

		}while(!dogOwnerList.isEmpty());

	}

	 // If I comment this out the paging on dogNameQuery works as expected
	offset += pageSize;   

	// Flush and clear the EM to save memory per page iteration
	entityManager.flush();
	entityManager.clear();

}while(!dogNameList.isEmpty());

What seems to be bizarre is that the outer query (dogNameQuery) does not page properly if I increment offset.   For example, lets say the total result set count for that query is 31. If I set pageSize to 5 and run things it generates 3 full pages of results plus 1 page of results with 1 item for a total of 16 items, not the expected 31. 

If I instead leave offset = 0 it properly pages across all 31 items in pages of 5.  Nothing else is incrementing offset, except for the one line I mentioned.  I must be missing something obvious here because I used the pattern of incrementing the setFirstResult value before and never seen anything like this. 

This code is running inside a Stateless EJB 3.1 bean running in Glassfish 3.1.2 using Eclipselink 2.3.2.v20111125-r10461. The DB is Oracle and my persistence.xml has eclipselink.jdbc.native-sql set to true. The EM is an injected JTA @PersistenceContext. 

-Noah