Corrected by

This seems to still be an issue in 3.2

I am facing the same problem as reported by Frederic Leitenberger (I also need to use ORDER BY, MAXROWS and FOR UPDATE in one query. ) .

Is there an estimate of when there will be a solution?

Thanks!

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 and 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 !

As Frederic Leitenberger said, it's possible to do a work around this bug, also in Criteria type of query this way :

Criteria criteria = getSession().createCriteria(MyClass.class)
.add(Restrictions.eq("myField", false))
.setLockMode(LockMode.UPGRADE);

// This won't work under hibernate : criteria.setMaxResults(1);

// I've provided the Dialect, but any other mean of detecting it's Oracle would work
if(hibernateDialect.contains("Oracle")) {
criteria.add(Restrictions.sqlRestriction("rownum <= 1"));
}

Adapt this code to other kind of Criteria as needed, this one is for returning a single result.
K