[hibernate-issues] [Hibernate-JIRA] Commented: (EJB-363) @OrderBy applied to collection property of derived class does not work properly when column used for ordering belongs to base class (using MySQL 5)

@OrderBy applied to collection property of derived class does not work properly when column used for ordering belongs to base class (using MySQL 5) --------------------------------------------------------------------------------------------------------------------------------------------------- Key: EJB-363 URL: http://opensource.atlassian.com/projects/hibernate/browse/EJB-363 Project: Hibernate Entity Manager Issue Type: Bug Components: EntityManager Affects Versions: 3.3.2.GA Environment: MySQL 5.0.18, Hibernate Core 3.2.6, Hibernate EntityManager 3.3.2, and the MySQL5InnoDBDialect. Test run with Tomcat 6.0.16 (Sun JDK 1.6.0_06) and Spring Framework 2.5.3. Relying on Hibernate automatic DDL generation (hibernate.hbm2ddl.auto=update), which appears to be working properly. Reporter: Rich Eggert Attachments: SampleBase.java, SampleDerived.java, SampleServiceImpl.java Original Estimate: 1 day Remaining Estimate: 1 day I applied the (JPA) @OrderBy annotation to a collection of entities belonging to an entity class (named SampleDerived in the attached sample code) of the same type (i.e., the class has a one-to-many parent-child relationship to itself). The property name passed as the argument to the @OrderBy annotation refers to a property of the base class (named SampleBase in the example), which employs the "JOINED" inheritence strategy. When I attempted to access the collection of a persisted instance of the entity class (in the example, this is accomplished by calling SampleServiceImpl.createSample() followed by calling SampleServiceImpl.findChildren() against the return value), the following (root cause) exception is generated: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'SampleBase.timestamp' in 'order clause' com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) com.mysql.jdbc.Connection.execSQL(Connection.java:3176) com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153) com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266) org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) org.hibernate.loader.Loader.getResultSet(Loader.java:1787) org.hibernate.loader.Loader.doQuery(Loader.java:674) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) org.hibernate.loader.Loader.loadCollection(Loader.java:1994) org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:36) org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:565) org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:63) org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1716) org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344) org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:86) org.hibernate.collection.PersistentBag.iterator(PersistentBag.java:249) org.frecklepuppy.bb.service.impl.SampleServiceImpl.findChildren(SampleServiceImpl.java:52) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) java.lang.reflect.Method.invoke(Method.java:597) org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310) org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) $Proxy209.findChildren(Unknown Source) org.frecklepuppy.bb.ui.controllers.IndexController.listForums(IndexController.java:66) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) java.lang.reflect.Method.invoke(Method.java:597) org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:413) org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:134) org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:310) org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:297) org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875) org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809) org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:523) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:453) javax.servlet.http.HttpServlet.service(HttpServlet.java:690) javax.servlet.http.HttpServlet.service(HttpServlet.java:803) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:359) org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109) org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.ui.ExceptionTranslationFilter.doFilterHttp(ExceptionTranslationFilter.java:101) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.providers.anonymous.AnonymousProcessingFilter.doFilterHttp(AnonymousProcessingFilter.java:105) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.ui.rememberme.RememberMeProcessingFilter.doFilterHttp(RememberMeProcessingFilter.java:109) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.wrapper.SecurityContextHolderAwareRequestFilter.doFilterHttp(SecurityContextHolderAwareRequestFilter.java:91) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.ui.basicauth.BasicProcessingFilter.doFilterHttp(BasicProcessingFilter.java:172) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.ui.AbstractProcessingFilter.doFilterHttp(AbstractProcessingFilter.java:268) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.ui.logout.LogoutFilter.doFilterHttp(LogoutFilter.java:87) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.ui.SessionFixationProtectionFilter.doFilterHttp(SessionFixationProtectionFilter.java:68) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.context.HttpSessionContextIntegrationFilter.doFilterHttp(HttpSessionContextIntegrationFilter.java:235) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.securechannel.ChannelProcessingFilter.doFilterHttp(ChannelProcessingFilter.java:116) org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53) org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:371) org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:174) org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:183) org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138) I verified (using MySQL Administrator) that the SampleBase.timestamp column does, in fact, exist. According to the org.hibernate.SQL log, the offending SQL statement looks like this: select children0_.parent_id as parent2_1_, children0_.id as id1_, children0_.id as id5_0_, children0_1_.timestamp as timestamp5_0_, children0_.parent_id as parent2_6_0_ from SampleDerived children0_ inner join SampleBase children0_1_ on children0_.id=children0_1_.id where children0_.parent_id=? order by SampleBase.timestamp asc I believe the problem is that MySQL 5 requires that the alias for the base class table used in the FROM clause (children0_1_ in this case) be used in the ORDER BY clause, instead of the actual name of the base class table. After browsing various MySQL discussions, it appears this is new to MySQL 5 (versus 4 or earlier) and was done in order to be more standards compliant. It's unclear to me whether this affects Hibernate Core or is limited to EntityManager. I'll try to narrow down the problem further as time permits (which it isn't likely to do any time soon).