[
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
