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-565
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Unassigned
Reporter: Alexander Ley
Votes: 0
Watchers: 0
Operations

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

Oracle : Column ambiguously defined in SQL for JDOQuery.setRange

Created: 16/Nov/11 02:26 PM   Updated: 04/Apr/12 05:31 PM   Resolved: 04/Apr/12 10:48 AM
Component/s: Queries
Affects Version/s: 3.0.3
Fix Version/s: 3.1.0.m2

File Attachments: 1. Zip Archive testcase.zip (2 kB)
2. Zip Archive traceDN_20111116-151521.zip (7 kB)

Environment: Oracle 10g

Datastore: Oracle
Severity: Production


 Description  « Hide
The generated SQL for queries with setRange could contain ambigous column names if more than 1 table is involved.
This is especially true if used with optimistic locking.

Example of generated SQL (stripped-down):

SELECT * FROM
 (SELECT subq.*,ROWNUM rn FROM
  (SELECT 'Order' AS NUCLEUS_TYPE, A0.BLOCKING_REASON, B0.CITY,
      B0.VER, A0.ID AS NUCORDER0,
      A0.VER FROM ORDERHEAD A0
      LEFT OUTER JOIN ADDRESS B0 ON A0.DELIVERY_ADDRESS = B0.ID
      WHERE A0.CUSTOMER = <'650024'> ORDER BY NUCORDER0 DESC
  ) subq
 ) WHERE rn <= 1

Problem:
subq.* contains A0.VER and B0.VER, both with the column name VER. This leads to
java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

This is not only a problem with optimitic locking. The problem with setRange will occur always, if two joined tables contain equal named columns.


Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 16/Nov/11 03:05 PM
Lack of testcase

Alexander Ley added a comment - 16/Nov/11 03:19 PM
Test case

Alexander Ley added a comment - 16/Nov/11 03:44 PM
log file

Andy Jefferson added a comment - 17/Nov/11 08:32 AM
Workaround : just remove the range and process ranges from the returned List which is the same as what happened in all previous versions.

Obviously since you want efficient ranges (and I have many other priorities) then you could easily look at the code and work out what the SQL ought to be and provide a (generic) fix. The place to look at is
org.datanucleus.store.rdbms.sql.SQLStatement line 1635-1664

Alexander Ley added a comment - 17/Nov/11 11:27 AM
I do understand that it is not so easy to achieve a generic solution.

The only idea I have is to give all not aliased columns in the inner SELECT a generic alias. I do not know enough about the Datanucleus implementation to decide whether this would be possible.
The SQL would look like
SELECT * FROM
  (SELECT subq.*,ROWNUM rn FROM
   (SELECT 'Order' AS NUCLEUS_TYPE, A0.BLOCKING_REASON AS C1, B0.CITY AS C2,
       B0.VER as C3, A0.ID AS NUCORDER0,
       A0.VER as C4 FROM ORDERHEAD A0
       LEFT OUTER JOIN ADDRESS B0 ON A0.DELIVERY_ADDRESS = B0.ID
       WHERE A0.CUSTOMER = <'650024'> ORDER BY NUCORDER0 DESC
   ) subq
  ) WHERE rn <= 1

Alexander Ley added a comment - 17/Nov/11 11:33 AM
BTW: In case of rangeOffset <= 0 the outermost "SELECT * FROM (" could be removed to get better performance.

Andy Jefferson added a comment - 04/Apr/12 10:48 AM
SVN trunk works on that