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

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.

Description

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.

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 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

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: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