Prepared query cache won't work if the start index (i.e setFirstResult) is
changed
----------------------------------------------------------------------------------
Key: OPENJPA-1684
URL: https://issues.apache.org/jira/browse/OPENJPA-1684
Project: OpenJPA
Issue Type: Bug
Components: query
Affects Versions: 2.0.0
Environment: Ubuntu 9.04, Sun jdk6, database: H2
Reporter: Waruna Ranasinghe
I have implemented pagination so that only 6 entries are fetched for a page.
First, I get the first 6 entries (First page: from 0 to 6) by setting ;
query = query.setFirstResult(startIndex); //startindex = 0
query = query.setMaxResults(pageSize); //pageSize = 6
Then the db query is sent as follow:
SELECT t0.id FROM ....... LIMIT ?
And the above sql is cached against its JPQL
When I try to get the next page (Second page: from 6 to 12) by setting;
query = query.setFirstResult(startIndex); //startindex = 6
query = query.setMaxResults(pageSize); //pageSize = 6
Then the db query is still sent as follow:
SELECT t0.id FROM ....... LIMIT ?
Where as it should be
SELECT t0.id FROM ....... LIMIT ? OFFSET ?
This problem occurs because it takes the SQL query from the cache against the
JPQL (JPQL is same as the first) which returns the old SQL query in which there
was no any OFFSET keyword set.
This can be fixed by setting the OFFSET value (in H2) to zero even if it is the
default value OR setting the relevant keyword (OFFSET in H2, LIMIT in MySQL) to
default, so that it can be taken from the cache and the changed offset values
will be set without a problem.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
