Issue Details (XML | Word | Printable)

Key: NUCRDBMS-370
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Andy Jefferson
Votes: 0
Watchers: 0
Operations

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

Oracle range was never fully implemented, needs "ROWNUM" keyword

Created: 19/Apr/10 04:05 PM   Updated: 05/Apr/12 03:28 PM   Resolved: 12/Sep/11 06:27 PM
Component/s: Queries
Affects Version/s: 2.0.0.release, 2.0.1, 2.0.2, 2.0.3, 2.1.0.m1
Fix Version/s: 3.0.2

File Attachments: 1. Text File oracleRownumDatanuclesu2.1.0-m2.txt (18 kB)
2. Zip Archive simpleTestCaseRownumOracle.zip (5 kB)



 Description  « Hide
A JPOX issue some time back added the code to (QueryStatement) but didn't add the method to OracleAdapter. See also
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

Andy Jefferson made changes - 19/Apr/10 04:07 PM
Field Original Value New Value
Status Open [ 1 ] Resolved [ 5 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 19/Apr/10 04:13 PM
Comment [ SVN trunk has this for JDOQL2 queries. Not added to legacy. See also
http://www.datanucleus.org/servlet/forum/viewthread_thread,6073
which suggests further improvements to this but no time here. ]
Andy Jefferson made changes - 19/Apr/10 04:13 PM
Resolution Fixed [ 1 ]
Status Resolved [ 5 ] Reopened [ 4 ]
Andy Jefferson made changes - 19/Apr/10 04:14 PM
Assignee Andy Jefferson [ andy ]
Fix Version/s 2.1.0.m2 [ 10912 ]
jerome added a comment - 19/Apr/10 08:46 PM
Hello,

I provide in attachement a patch and some "simple" testCases.
The patch is tested only with Oracle9i database.
Moreover it can be (had to be) improved by
 - using bind variable for ROWNUM parameter(s).
 - avoid parsing the select rows with regexp. (I have done it because it was the simplest way for me).
 - ...

Do not hesitate to contact me if you want more explanation.

Best Regards,

  Jérôme

jerome added a comment - 19/Apr/10 08:48 PM
Patch proposed for ROWNUM and "simple" testcase

jerome made changes - 19/Apr/10 08:48 PM
Attachment oracleRownumDatanuclesu2.1.0-m2.txt [ 11143 ]
Attachment simpleTestCaseRownumOracle.zip [ 11144 ]
Andy Jefferson made changes - 21/Apr/10 11:07 AM
Description A JPOX issue some time back added the code to (QueryStatement) but didn't add the method to OracleAdapter. A JPOX issue some time back added the code to (QueryStatement) but didn't add the method to OracleAdapter. See also
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
Andy Jefferson added a comment - 17/May/10 02:40 PM

Yang ZHONG added a comment - 15/Jul/10 10:32 PM
It seems working that uncommenting this back into 2.1(.1) .rdbms.adapter.OracleAdapter or adding this into 2.0(.4) version
    public String getRangeByRowNumberColumn()
    {
        return "ROWNUM";
    }

after both "fix"es @
HTTP://WWW.DataNucleus.org/servlet/jira/browse/NUCRDBMS-433
HTTP://WWW.DataNucleus.org/servlet/jira/browse/NUCRDBMS-436

Andy Jefferson added a comment - 16/Jul/10 09:54 AM
As this JIRA and the attached patch says, there is way more to this (for Oracle) than just uncommenting that method. The provided patch (from jerome) does all sorts of ugly string regexp parsing, and that needs rewriting using the SQLStatement object before it gets applied.

Andy Jefferson added a comment - 12/Sep/11 06:27 PM
SVN trunk has an initial version using ROWNUM; works on very simple case.

Andy Jefferson made changes - 12/Sep/11 06:27 PM
Status Reopened [ 4 ] Resolved [ 5 ]
Assignee Andy Jefferson [ andy ]
Fix Version/s 3.0.2 [ 11310 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 03/Oct/11 07:38 AM
Status Resolved [ 5 ] Closed [ 6 ]
Daniel Baldes added a comment - 05/Apr/12 03:05 PM
There is a problem with this implementation. Unfortunately I failed to provide a simple test case so far, but with fetch groups etc we might end up with a query like this:

SELECT * FROM (SELECT subq.*, ROWNUM rn FROM (SELECT ..., a0.id, ..., b0.id FROM table1 a0, table2 b0 ...) subq ) WHERE rn < ...;

I.e. a query which joins two tables which have partly the same column names. Now the problem is, in the outer query, the column names lose their table qualifier, i.e. you end up with two columns named "id" and oracle complains; I can give this simple example query:

select * from (select a.id, b.id from table1 a inner join table2 b on a.id = b.a_id);

ORA-00918: column ambiguously defined (DBD ERROR: error possibly near <*> indicator at char 7 in 'select <*>* from (select a.id, b.id from table1 a inner join table1 b on a.id = b.a_id)')

We could say this is an oracle error, but I'm not sure how long it would take oracle to fix this.

Andy Jefferson added a comment - 05/Apr/12 03:26 PM

Daniel Baldes added a comment - 05/Apr/12 03:28 PM
Great, thanks!