[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)

Monday, 28 July 2008

    [
http://opensource.atlassian.com/projects/hibernate/browse/EJB-363?page=co...
] 

Andras Soltesz commented on EJB-363:
------------------------------------

By the way: I have this problem on a Derby database with Annotations and I don't use
EntityManager. It is very likely !NOT! MySQL dependent.

My failing SQL:
-----------------------------------
    select
        images0_.estate_id as estate3_3_,
        images0_.DOCLINK_ID as DOCLINK2_3_,
        images0_.DOCLINK_ID as id23_2_,
        images0_1_.created as created23_2_,
        images0_1_.lastModified as lastModi3_23_2_,
        images0_1_.version as version23_2_,
        images0_1_.comments as comments23_2_,
        images0_1_.documentId as documentId23_2_,
        images0_1_.orderNum as orderNum23_2_,
        images0_1_.repo_id as repo9_23_2_,
        images0_1_.title as title23_2_,
        images0_.estate_id as estate3_24_2_,
        images0_.orderNum2 as orderNum1_24_2_,
        images0_.type_id as type4_24_2_,
        repository1_.id as id12_0_,
        repository1_.created as created12_0_,
        repository1_.lastModified as lastModi3_12_0_,
        repository1_.version as version12_0_,
        repository1_.imageFolder as imageFol5_12_0_,
        repository1_.name as name12_0_,
        repository1_.publicWebAccess as publicWe7_12_0_,
        repository1_.publicWebURLTemplate as publicWe8_12_0_,
        repository1_.reference as reference12_0_,
        repository1_.storingNewAllowed as storing10_12_0_,
        repository1_1_.rootDirPath as rootDirP1_13_0_,
        repository1_2_.nameSizeEquals as nameSize1_14_0_,
        repository1_2_.password as password14_0_,
        repository1_2_.port as port14_0_,
        repository1_2_.rootDirPath as rootDirP4_14_0_,
        repository1_2_.serverName as serverName14_0_,
        repository1_2_.userName as userName14_0_,
        case 
            when repository1_1_.id is not null then 1 
            when repository1_2_.id is not null then 2 
            when repository1_.id is not null then 0 
            else -1 
        end as clazz_0_,
        imagetype2_.id as id5_1_,
        imagetype2_.created as created5_1_,
        imagetype2_.lastModified as lastModi4_5_1_,
        imagetype2_.version as version5_1_,
        imagetype2_.defaultOrderNum as defaultO6_5_1_,
        imagetype2_.description as descript7_5_1_,
        imagetype2_.internalCode as internal8_5_1_,
        imagetype2_.lang as lang5_1_,
        imagetype2_.numericValue as numeric10_5_1_,
        imagetype2_.selector as selector5_1_,
        imagetype2_.shortTitle as shortTitle5_1_,
        imagetype2_.title as title5_1_ 
    from
        EST_ESTATE_IMG images0_ 
    inner join
        DOCMAN_DOC_LINK images0_1_ 
            on images0_.DOCLINK_ID=images0_1_.id 
    inner join
        DOCMAN_REPO repository1_ 
            on images0_1_.repo_id=repository1_.id 
    left outer join
        DOCMAN_LFSREPO repository1_1_ 
            on repository1_.id=repository1_1_.id 
    left outer join
        DOCMAN_FTPREPO repository1_2_ 
            on repository1_.id=repository1_2_.id 
    left outer join
        LOOKUP_ITEM imagetype2_ 
            on images0_.type_id=imagetype2_.id 
    where
        images0_.estate_id=? 
    order by
        DOCMAN_DOC_LINK.orderNum asc
---------------------------------------
In my case the EstateImage (EST_ESTATE_IMG) is a subclass of DocumentLink
(DOCMAN_DOC_LINK).  EstateImage is an embedded list of Estate (OneToMany mapping is used).
This SQL is fired when Hibernate tries to fetch the EstateImage collection from the
database for an Estate.

It seems that the way Hibernate constructs the SQL query is not acceptable for this
situation. In cases like this, no Inner join could be used for joining the superclass. 

------------------------
WORKAROUND:
As a temporary workaround I created an orderNum2 field in the subclass which mirrors the
orderNUm field of the superclass. This is ugly but works:
  /**
   * A duplication of the orderNum property. 
   * 
   * This is needed for technical reason (an issue in Hibernate) which 
   * results in the inability to order the image list by orderNum (because 
   * it is in the superclass) so we need to create a mirror field for it.
   */
  public int getOrderNum2() {
    // returning the original field
    return getOrderNum();
  }
  public void setOrderNum2(int orderNum) {
    // dummy
  }
In the database, there will be an additional, ORDERNUM2 field but it will be filled
automatically.

...
 @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). 
-- 
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

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

[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)