(The database used is MySQL in our case, not sure if the problem is reproducible with other DBs)
MySQL has a LIMIT query of the form:
SELECT ___...___ LIMIT <start>, <row_count>
In eclipselink the query is formed as below:
String queryStr = "SELECT t FROM " + TableOne.class.getName() + " t ";
Query query = em.createQuery(queryStr);
result = query.getResultList();
When CompositePU is used consisting of one or more child persistence units, the LIMIT query is not framed correctly.
For start = 3 and range = 3:
SELECT ___...___ LIMIT 3, 3
SELECT ___...___ LIMIT 3, 6
For start = 3 and range = 1:
SELECT ___...___ LIMIT 3, 1
SELECT ___...___ LIMIT 3, 4
Eclipselink sets the row_count as
query.setFirstResult() + query.setMaxResults()
This issue occurs only when CompositePU is used.
Question: Could this be a bug or something we are doing wrong?