[jira] [Comment Edited] (OPENJPA-2356) Use new Offset/Limit/ROWNUM features in DB2 family of databases for range queries

["Di Wu Lau (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-2356?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13702518#comment-13702518 ] 

Di Wu Lau edited comment on OPENJPA-2356 at 7/8/13 10:50 PM:
-------------------------------------------------------------

Fixed TestRangeQuery problem. Test now passes against Derby, DB2, Oracle, Mysql, Postgresql and Hsql. Attached is the patch for fixing this issue.

The problem is database specific, in this case it is Derby. Derby uses "Fetch First . . . Rows Only" to return the number of rows. This results in a different number than the actual number of rows should get returned. That's why Derby needs to undergo the changes in applyRange() method.  

On the other hand, the rest of the databases all have their own ways to return the number of rows. The syntax/mechanisms they are using though, always return the actual number of rows. (eg. "Limit", "Offset", "Fetch") So they should not undergo the changes in method applyRange().

Therefore, we moved applyRange() from SelectImpl to DerbyDictionary to be specific only to Derby. This fixed the problem. 
                
      was (Author: dlau):
    Fixed TestRangeQuery problem. Test now passes against Derby, DB2, Oracle, Mysql, Postgresql and Hsql. Attached is the patch for fixing this issue.

The problem is database specific, in this case it is Derby. The number of rows get return from Derby depends on the start and end index that was passed through setFirstResult and setMaxResults. It then returns the total number of rows = endIdx - startIdx. This is not the actual row number gets returned if end index < setMaxResults. That's why it needs the applyRange() method to modify its total number of rows (count). 

On the other hand, the rest of the databases are not affected by end index. The mechanisms they are using will always return the actual number of rows. (eg. "Limit", "Offset", "Fetch") So they should not undergo the changes in method applyRange().

Therefore, we moved applyRange() from SelectImpl to DerbyDictionary to be specific only to Derby. This fixed the problem. 
                  
> Use new Offset/Limit/ROWNUM features in DB2 family of databases for range queries
> ---------------------------------------------------------------------------------
>
>                 Key: OPENJPA-2356
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2356
>             Project: OpenJPA
>          Issue Type: Improvement
>            Reporter: Pinaki Poddar
>            Assignee: Pinaki Poddar
>             Fix For: 2.3.0
>
>         Attachments: graycol.gif, OPENJPA-2356.patch
>
>
> JPA queries that use setFirstResult() and/or setMaxResult() translate to some form of Offset and Limit syntax in database SQL query. Recently DB2 and Derby has added ROWNUM and FETCH offset respectively.
> OpenJPA should use these facilities when appropriate.   

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira