Issue Details (XML | Word | Printable)

Key: NUCRDBMS-433
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
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

Support range using ROW_NUMBER() OVER() for DB2

Created: 14/Jul/10 09:46 PM   Updated: 03/Oct/11 07:38 AM   Resolved: 12/Sep/11 12:15 PM
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: 3.0.2

File Attachments: 1. Zip Archive NUCRDBMS-433.zip (3 kB)

Environment: Java 5, Linux

Datastore: IBM DB2
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 to be attached, 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"

It seems the previous fix was incomplete/obsolete:
HTTP://WWW.DataNucleus.org/servlet/jira/browse/CORE-2721

Just for the only purpose of Proof of Concept, refining both .rdbms.sqlSQLStatement & .rdbms.query.legacy.QueryStatement from
                final int dotIndex = selectedCol.indexOf(".");
                if (dotIndex != -1)
to
                int dotIndex = selectedCol.indexOf(" AS ");
                if (dotIndex != -1)
                {
                    selectedCol = selectedCol.substring(dotIndex + 4/* AS */);
                }
                else if ((dotIndex = selectedCol.indexOf('.')) != -1)
seems resolved the problem.

Now 2.1.1 generates
SELECT subq.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.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

Sort Order: Ascending order - Click to sort in descending order
Yang ZHONG added a comment - 14/Jul/10 10:16 PM

Andy Jefferson added a comment - 15/Jul/10 07:27 AM
JDOQL-Legacy will not be fixed for anything. It is deleted in 2.2. Any use of ranges ought to be using the default JDOQL implementation since that is the only one developed now

Andy Jefferson added a comment - 15/Jul/10 07:36 AM
Also refer to http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-370
Also, any updates to code ought to be in patch format; anything else is unreliable.

Andy Jefferson added a comment - 12/Sep/11 12:15 PM
Assumed to work.