Oracle9Dialect LockMode.UPGRADE conflicts with setMaxResults
Description
When executing a simple HQL select from in Oracle, attempting to specify LockMode.UPGRADE to generate a "for update" clause while specifying a setMaxResults(10) causes an Oracle-00904: Invalid identifier exception. The same query works fine with several other tested dialects.
The reason is that the generated SQL refers to a column by its actual column name, instead of the column alias assigned to it. Consider the following example:
select * from ( select scheduleel0_.elementId as elementId22_ from ScheduleElement scheduleel0_ ) where rownum <= ? for update of scheduleel0_.elementId
This should be "for update of elementId22_", because "scheduleel0_.elementId" can't be referenced at this scope in the SQL.
I've dug through the code but I think the fix will require a more knowledge of Hibernate's guts than I've got. The offending SQL is inserted in org.hibernate.sql.ForUpdateFragment.toFragmentString(), but the ForUpdateFragment class doesn't know what the column aliases are for each real column, so it would need some way of getting that information.
The HQL which causes the problem is as follows: from ScheduleElement as element where element.runnable=true and element.scheduleHead=true
(I don't think it matters, but ScheduleElement is the root of a class hierarchy defined using joined-subclass)
– Selecting all columns and left joining on subclass tables omitted for brevity select * from ( select scheduleel0_.elementId as elementId22_ from ScheduleElement scheduleel0_ ) where rownum <= ? for update of scheduleel0_.elementId
See for further information on this "bug". Doesn't seem to be patched yet as of today.
K
Christian BauerSeptember 3, 2006 at 12:23 AM
Do you have a patch ready?
Olivier JollySeptember 2, 2006 at 11:23 PM
The problem is still present in version 3.2.0rc4 and we are also badly annoyed with this one.
Paul SmithMay 15, 2006 at 12:56 AM
Just wondering what the status of this issue is as it has a pretty heavy impact on our current project. Without it, we are forced to get all results to do any batch type updating which prevents us from implementing restartable batch updates.
When executing a simple HQL select from in Oracle, attempting to specify LockMode.UPGRADE to generate a "for update" clause while specifying a setMaxResults(10) causes an Oracle-00904: Invalid identifier exception. The same query works fine with several other tested dialects.
The reason is that the generated SQL refers to a column by its actual column name, instead of the column alias assigned to it. Consider the following example:
select * from ( select scheduleel0_.elementId as elementId22_ from ScheduleElement scheduleel0_ ) where rownum <= ? for update of scheduleel0_.elementId
This should be "for update of elementId22_", because "scheduleel0_.elementId" can't be referenced at this scope in the SQL.
I've dug through the code but I think the fix will require a more knowledge of Hibernate's guts than I've got. The offending SQL is inserted in org.hibernate.sql.ForUpdateFragment.toFragmentString(), but the ForUpdateFragment class doesn't know what the column aliases are for each real column, so it would need some way of getting that information.
The HQL which causes the problem is as follows:
from ScheduleElement as element where element.runnable=true and element.scheduleHead=true
(I don't think it matters, but ScheduleElement is the root of a class hierarchy defined using joined-subclass)
It is executed using the following Java code:
session.createQuery(seeAboveForTheQueryString)
.setLockMode("element", LockMode.UPGRADE)
.setMaxResults(10)
.list();
Here is the bad SQL created from the HQL:
– Selecting all columns and left joining on subclass tables omitted for brevity
select * from ( select scheduleel0_.elementId as elementId22_ from ScheduleElement scheduleel0_ ) where rownum <= ? for update of scheduleel0_.elementId