[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-759?page=co... ] Frederic Leitenberger commented on HHH-759: ------------------------------------------- Hello again, i have serous trouble with this. I need to use ORDER BY, MAXROWS and FOR UPDATE in one query. e.g.: Criteria criteria = getSession().createCriteria(MyClass.class) .add(Restrictions.eq("myField", false)) .add(Order.desc("oderField")) .setLockMode(LockMode.UPGRADE) .setMaxResults(1); What i want form this query is: - select the rows for my restricitions - order the result - limit the result - lock the result What it currently get (simplified) is: select * from ( select * from myClass where myField = 0 order by usage desc ) where rownum <= ? for update of this_.id As we already know this doesn't work: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2223) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283) at de.iccs.api.facades.ResellerBlockFacade.listResellerFreeBlocksForAssignment(ResellerBlockFacade.java:245) at de.iccs.api.facades.ResellerBlockFacade.allocateNumbersInternal(ResellerBlockFacade.java:1095) at de.iccs.api.facades.GeoNumberFacade.allocateGeoNumbersInternal(GeoNumberFacade.java:979) at de.iccs.api.facades.GeoNumberFacade.reserveGeoNumbersInternal(GeoNumberFacade.java:938) at de.iccs.api.facades.GeoNumberFacade.allocateGeoNumbersForAllocateEleven(GeoNumberFacade.java:798) at de.iccs.api.facades.GeoNumberFacade$$EnhancerByCGLIB$$cf19ce9a.CGLIB$allocateGeoNumbersForAllocateEleven$2(<generated>) at de.iccs.api.facades.GeoNumberFacade$$EnhancerByCGLIB$$cf19ce9a$$FastClassByCGLIB$$2ccb1367.invoke(<generated>) at net.sf.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:167) at de.iccs.api.facades.interceptors.FacadeInterceptor.intercept(FacadeInterceptor.java:17) at de.iccs.api.facades.interceptors.HibernateFacadeInterceptor.intercept(HibernateFacadeInterceptor.java:82) at de.iccs.api.facades.GeoNumberFacade$$EnhancerByCGLIB$$cf19ce9a.allocateGeoNumbersForAllocateEleven(<generated>) at de.iccs.api.facades.GeoNumberFacadeTest.testAllocateGeoNumbersForAllocateEleven(GeoNumberFacadeTest.java:74) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at junit.framework.TestCase.runTest(TestCase.java:154) at junit.framework.TestCase.runBare(TestCase.java:127) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) Caused by: java.sql.SQLException: ORA-00904: "THIS_"."ID": invalid identifier at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181) at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420) at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896) at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1787) at org.hibernate.loader.Loader.doQuery(Loader.java:674) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2220) ... 33 more When i change the setMaxRows-call to: if (maxResults > 0) criteria.add(sqlRestriction("rownum <= ?", maxResults, new IntegerType())); ... or simply ... criteria.add(Restrictions.sqlRestriction("rownum <= 1")); ... the following is generated: select * from myClass where myField = 0 and rownum <= ? order by usage desc for update of this_.id The statement can be executed, but does the following (in exaclty this order): - select the rows for my restricitions - limit the result (unordered) - order the result (the one selected row) - lock the result For comparison again what i wanted form this query: - select the rows for my restricitions - order the result - limit the result - lock the result Any idea how to solve this? I had an idea of selecting and locking only an id, but i am still not sure if that solves the problem in any way. Another idea was to drop the setMaxRows part and user iterate to get as many rows as needed an then close the iterator and cursor with Hibernate.closeIterator(iter). But HB-308 and HHH-249 indicate that this will not be available very soon considering the comments and the last-modified timestamps. And since it would we somewhat difficult and stupid to bypass the critera-functionality i am stuck here. Any help would be great ! -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira