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

Sort Order: Ascending order - Click to sort in descending order
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

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.

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!