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.


Andy Jefferson added a comment - 16/Nov/11 03:05 PM
Lack of testcase

Andy Jefferson made changes - 16/Nov/11 03:05 PM
Field Original Value New Value
Priority Major [ 3 ] Incomplete [ 6 ]
Alexander Ley added a comment - 16/Nov/11 03:19 PM
Test case

Alexander Ley made changes - 16/Nov/11 03:19 PM
Attachment testcase.zip [ 11525 ]
Alexander Ley added a comment - 16/Nov/11 03:44 PM
log file

Alexander Ley made changes - 16/Nov/11 03:44 PM
Attachment traceDN_20111116-151521.zip [ 11526 ]
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

Andy Jefferson made changes - 17/Nov/11 08:32 AM
Priority Incomplete [ 6 ] Major [ 3 ]
Andy Jefferson made changes - 17/Nov/11 08:32 AM
Summary Column ambiguously defined in SQL for JDOQuery.setRange Oracle : Column ambiguously defined in SQL for JDOQuery.setRange
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

Andy Jefferson made changes - 04/Apr/12 10:48 AM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.1.0.m2 [ 11509 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 04/Apr/12 05:31 PM
Status Resolved [ 5 ] Closed [ 6 ]