[ http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-436?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Andy Jefferson resolved NUCRDBMS-436.
-------------------------------------
Resolution: Won't Fix
Please move your comments onto the related JIRA - NUCRDBMS-434. There is no point in having 2 JIRAs both saying "support setRange natively for DB2" (which is what this is also)
> JDO setRange starts w/ 0 while DB RowNum starts w/ 1
> ----------------------------------------------------
>
> Key: NUCRDBMS-436
> URL: http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-436
> Project: DataNucleus RDBMS
> Issue Type: Bug
> Components: Queries
> Affects Versions: 2.0.4, 2.1.0.m1, 2.1.0.m2, 2.1.0.m3, 2.1.0.release, 2.1.1
> Environment: Java 5, Linux
> Reporter: Yang ZHONG
>
> Having implemented DatabaseAdapter#getRangeByRowNumberColumn()/*"row_number()over()" for DB2*/ by following
> HTTP://WWW.DataNucleus.org/extensions/datastore_adapter.html
> and given the Test Case attached to
> HTTP://WWW.DataNucleus.org/servlet/jira/browse/NUCRDBMS-433
> 2.1(.1) generates
> SELECT subq.datanucleus.test.Bool' AS NUCLEUS_TYPE,subq.BOOL,subq.BOOL_ID FROM (SELECT row_number()over() rn, 'org.datanucleus.test.Bool' AS NUCLEUS_TYPE,A0.BOOL,A0.BOOL_ID,row_number()over() FROM BOOL A0 ) subq WHERE subq.rn>=1 AND subq.rn<3
> and "datanucleus.query.JDOQL.implementation=JDOQL-Legacy" generates
> SELECT subq.datanucleus.test.Bool' AS NUCLEUS_TYPE,subq.BOOL,subq.BOOL_ID FROM (SELECT row_number()over() rn, 'org.datanucleus.test.Bool' AS NUCLEUS_TYPE,THIS.BOOL,THIS.BOOL_ID,row_number()over() FROM BOOL THIS ) subq WHERE subq.rn>=1 AND subq.rn<3
> To see generated SQLs, modify $JAVA_HOME/jre/lib/logging.properties:
> 2-1. add "DataNucleus.Datastore.Native.level=FINE"
> 2-2. Assure "java.util.logging.ConsoleHandler.level" at least "FINE"
> The problem is, JDO setRange starts w/ 0, while both DB2 & oracle RowNum starts w/ 1.
> Just for the only purpose of Proof of Concept, refining both .rdbms.sqlSQLStatement & .rdbms.query.legacy.QueryStatement from
> if (rangeOffset > -1)
> {
> sql.append("subq.rn").append(">=").append("" + rangeOffset);
> }
> if (rangeCount > -1)
> {
> if (rangeOffset > -1)
> {
> sql.append(" AND ");
> }
> sql.append("subq.rn").append("<").append("" + (rangeCount + rangeOffset));
> to
> if (rangeOffset > 0)
> {
> sql.append("subq.rn>").append(String.valueOf(rangeOffset));
> }
> if (rangeCount > -1)
> {
> if (rangeOffset > 0)
> {
> sql.append(" AND ");
> }
> sql.append("subq.rn<=").append(String.valueOf(rangeCount + rangeOffset));
> seems resolved the problem.
> Now 2.1.1 generates
> SELECT subq.datanucleus.test.Bool' AS NUCLEUS_TYPE,subq.BOOL,subq.BOOL_ID FROM (SELECT row_number()over() rn, 'org.datanucleus.test.Bool' AS NUCLEUS_TYPE,A0.BOOL,A0.BOOL_ID,row_number()over() FROM BOOL A0 ) subq WHERE subq.rn>1 AND subq.rn<=3
> and "JDOQL-Legacy" generates
> SELECT subq.datanucleus.test.Bool' AS NUCLEUS_TYPE,subq.BOOL,subq.BOOL_ID FROM (SELECT row_number()over() rn, 'org.datanucleus.test.Bool' AS NUCLEUS_TYPE,THIS.BOOL,THIS.BOOL_ID,row_number()over() FROM BOOL THIS ) subq WHERE subq.rn>1 AND subq.rn<=3
> If other DB having different RowNum definition, we may need additional protocol into DatabaseAdapter.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://www.datanucleus.org/servlet/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|