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

[Olivier Jolly (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1729?page=comments#action_24291 ] 

Olivier Jolly commented on HHH-1729:
------------------------------------

The problem is still present in version 3.2.0rc4 and we are also badly annoyed with this one.

> 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