[Hibernate-issues] [Hibernate-JIRA] Created: (HHH-1729) Oracle9Dialect LockMode.UPGRADE conflicts with setMaxResults

[Mark L. (JIRA) <no...@at...>]

Oracle9Dialect LockMode.UPGRADE conflicts with setMaxResults
------------------------------------------------------------

         Key: HHH-1729
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1729
     Project: Hibernate3
        Type: Bug

    Versions: 3.1    
 Environment: Hibernate 1.1, Oracle 10g release 2 (also tested with 10g release 1).
    Reporter: Mark Lewis
    Priority: Minor


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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira