Hibernate throws an exception when executing a select statement with order by clause

Description

Hibernate throws an exception when executing a select statement (see below) with order by clause.
select
escalation0_.ESCALATIONCASE as ESCALATI6_0_1_,
escalation0_.GUID as GUID1_,
escalation0_.GUID as GUID1_0_,
escalation0_.creationDate as creation3_1_0_,
escalation0_.OPTLOCK as OPTLOCK1_0_,
escalation0_.description as descript5_1_0_,
escalation0_.ESCALATIONCASE as ESCALATI6_1_0_
from esk_history
escalation0_ where escalation0_.ESCALATIONCASE in (select escalation0_.GUID from esk_case escalation0_) order by esk_history.creationDate asc

The exception:
2011-02-04 11:06:23,010 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not load collection by subselect: [org.jboss.testcase.EscalationCaseImpl.escalationCaseHistories#<4, 3, 5, 2, 1>] [select escalation0_.ESCALATIONCASE as ESCALATI6_0_1_, escalation0_.GUID as GUID1_, escalation0_.GUID as GUID1_0_, escalation0_.creationDate as creation3_1_0_, escalation0_.OPTLOCK as OPTLOCK1_0_, escalation0_.description as descript5_1_0_, escalation0_.ESCALATIONCASE as ESCALATI6_1_0_ from esk_history escalation0_ where escalation0_.ESCALATIONCASE in (select escalation0_.GUID from esk_case escalation0_) order by esk_history.creationDate asc]
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'esk_history.creationDate' in 'order clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
at com.mysql.jdbc.Util.getInstance(Util.java:382)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2273)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:255)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:187)
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.loadCollectionSubselect(Loader.java:2066)
at org.hibernate.loader.collection.SubselectOneToManyLoader.initialize(SubselectOneToManyLoader.java:58)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:565)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1716)
at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:454)
at org.hibernate.engine.StatefulPersistenceContext.initializeNonLazyCollections(StatefulPersistenceContext.java:844)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:241)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:341)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:65)
at org.jboss.testcase.SOEscalationCaseServiceBean.findEscalations(SOEscalationCaseServiceBean.java:54)
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:597)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:57)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.timer.TimerTxRegistrationInterceptor.invoke(TimerTxRegistrationInterceptor.java:38)
at org.jboss.ejb3.timer.TimerTxRegistrationInterceptor.invoke(TimerTxRegistrationInterceptor.java:29)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:191)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:94)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:70)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:139)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.interceptor.EJB3TCCLInterceptor.invoke(EJB3TCCLInterceptor.java:86)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:281)
at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:106)
at org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:862)
at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:634)
at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:409)
at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:176)
2011-02-04 11:06:23,010 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22
2011-02-04 11:06:23,010 ERROR [org.hibernate.util.JDBCExceptionReporter] Unknown column 'esk_history.creationDate' in 'order clause'
2011-02-04 11:06:23,011 DEBUG [org.hibernate.ejb.AbstractEntityManagerImpl] mark transaction for rollback
2011-02-04 11:06:23,012 DEBUG [org.jboss.ejb3.entity.ManagedEntityManagerFactory] ************** closing entity managersession *************

Environment

JBoss EAP 4.3 CP08

Activity

Show:
Tom Ross
February 7, 2011, 2:43 PM

I would like to stress that the test case does work against JBoss 5.1. It only fails with JBoss 4.3.

Strong Liu
February 7, 2011, 3:09 PM

Tom,

some questions:
1) which HQL or SQL (please confirm) w/ order by causes the exception, please note that the query statement you used in the issue description and comment are different (table name v.s. alias)
2) I just ran your test case with eap 4.3 cp09, and I don't see any exception, it would better to create a hibernate style test case if you think i'm missing something

thanks

Steve Ebersole
February 7, 2011, 7:16 PM

The problem is not the HQL query per-se. The error is occuring in a subselect fetch subsequent to that initial load. If this is really the SQL in play then the issue seems to be that the same corelation name (alias) is used both in the subquery and the outer query. This is based on the exception.

Where does the order by come from?

Strong Liu
February 14, 2011, 8:40 AM

this issue does not exist in the hibernate 3.5/3.6, so reject

Steve Ebersole
March 21, 2011, 7:09 PM

Bulk closing stale resolved issues

Assignee

Strong Liu

Reporter

Tom Ross

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure