DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
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)