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