You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Waruna Ranasinghe (JIRA)" <ji...@apache.org> on 2010/06/06 10:16:55 UTC

[jira] Created: (OPENJPA-1684) Prepared query cache won't work if the start index (i.e setFirstResult) is changed

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.

[jira] Commented: (OPENJPA-1684) Prepared query cache won't work if the start index (i.e setFirstResult) is changed

Posted by "Pinaki Poddar (JIRA)" <ji...@apache.org>.
    [ https://issues.apache.org/jira/browse/OPENJPA-1684?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12876456#action_12876456 ] 

Pinaki Poddar commented on OPENJPA-1684:
----------------------------------------

The prepared query cache detects whether a query is using range and if so, does not cache the query precisely because of the reason you mentioned i.e. the target SQL for the same JPQL is different  across different range parameters.
That was the design intent and we have few simple tests to verify it. I double checked that those tests are passing. If you are not observing the same effect, then please submit a reproducible test case for us to follow up this issue.  

> 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.

[jira] Closed: (OPENJPA-1684) Prepared query cache won't work if the start index (i.e setFirstResult) is changed

Posted by "Rick Curtis (JIRA)" <ji...@apache.org>.
     [ https://issues.apache.org/jira/browse/OPENJPA-1684?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Curtis closed OPENJPA-1684.
--------------------------------

      Assignee: Rick Curtis
    Resolution: Cannot Reproduce

Closing this issue as I'm unable to reproduce the reported problem. Please reopen if you're able to put together a unit test.

> 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
>            Assignee: Rick Curtis
>
> 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.