Issue Details (XML | Word | Printable)

Key: NUCRDBMS-436
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Unassigned
Reporter: Yang ZHONG
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

JDO setRange starts w/ 0 while DB RowNum starts w/ 1

Created: 15/Jul/10 07:52 PM   Updated: 20/Oct/10 09:30 AM   Resolved: 16/Jul/10 09:51 AM
Component/s: Queries
Affects Version/s: 2.0.4, 2.1.0.m1, 2.1.0.m2, 2.1.0.m3, 2.1.0.release, 2.1.1
Fix Version/s: None

Environment: Java 5, Linux

Datastore: IBM DB2, Oracle
Severity: Production


 Description  « Hide
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.

Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 16/Jul/10 09:51 AM
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)