[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)
Andras Soltesz (JIRA)
noreply at atlassian.com
Mon Jul 28 05:12:47 EDT 2008
[ http://opensource.atlassian.com/projects/hibernate/browse/EJB-363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_30733 ]
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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list