Problem with OneToMany and OrderBy

REggert · **Joined:** Wed Aug 30, 2006 3:17 pm **Posts:** 11

I have found what appears to be a bug in Hibernate.

I have an entity class, PostedMessage, that extends another entity class, Message:

Code:

@Entity
@Inheritance(strategy=JOINED)
public class Message
{

   private Long id;
   private Date whenSent = new Date();
   
   
   @Id
   @GeneratedValue(strategy=AUTO)
   public Long getId()
    {
       return id;
    }
   
   
   public void setId(Long id)
    {
       this.id = id;
    }
   
   

   @Temporal(TIMESTAMP)
   @Basic(optional=false)
   public Date getWhenSent()
    {
       return whenSent;
    }
   
   
   public void setWhenSent(Date whenSent)
    {
       this.whenSent = whenSent;
    }
   
}


@Entity
public class PostedMessage extends Message
{
   private DiscussionThread thread;
   private PostedMessage parent;
   private Collection<PostedMessage> replies = new LinkedList<PostedMessage>();

   @ManyToOne
   public PostedMessage getParent()
    {
       return parent;
    }

   public void setParent(PostedMessage parent)
    {
       this.parent = parent;
    }

   
   @ManyToOne(optional=false)
   public DiscussionThread getThread()
    {
       return thread;
    }

   public void setThread(DiscussionThread thread)
    {
       this.thread = thread;
    }

   
   @OneToMany(mappedBy="parent")
   @OrderBy("whenSent")
   public Collection<PostedMessage> getReplies()
    {
       return replies;
    }

   public void setReplies(List<PostedMessage> replies)
    {
       this.replies = replies;
    }
}

(I've omitted fields that are not relevant to this problem.)

When I try to access the "replies" collection, it generates this SQL:

Code:

select replies0_.parent_id as parent2_6_, replies0_.id as id6_, replies0_.id as id1_5_, replies0_1_.author_id as author7_1_5_, replies0_1_.body as body1_5_, replies0_1_.contentType as contentT3_1_5_, replies0_1_.lastModified as lastModi4_1_5_, replies0_1_.subject as subject1_5_, replies0_1_.whenSent as whenSent1_5_, replies0_.parent_id as parent2_5_5_, replies0_.thread_id as thread3_5_5_, author1_.id as id3_0_, author1_.emailAddress as emailAdd2_3_0_, author1_.fullName as fullName3_0_, author1_1_.rawAddress as rawAddress4_0_, author1_2_.name as name7_0_, author1_2_.password as password7_0_, case when author1_1_.id is not null then 1 when author1_2_.id is not null then 2 when author1_.id is not null then 0 end as clazz_0_, discussion2_.id as id0_1_, discussion2_.firstMessage_id as firstMes2_0_1_, discussion2_.forum_id as forum3_0_1_, discussion2_.lastMessage_id as lastMess4_0_1_, postedmess3_.id as id1_2_, postedmess3_1_.author_id as author7_1_2_, postedmess3_1_.body as body1_2_, postedmess3_1_.contentType as contentT3_1_2_, postedmess3_1_.lastModified as lastModi4_1_2_, postedmess3_1_.subject as subject1_2_, postedmess3_1_.whenSent as whenSent1_2_, postedmess3_.parent_id as parent2_5_2_, postedmess3_.thread_id as thread3_5_2_, forum4_.id as id6_3_, forum4_.canonicalName as canonica2_6_3_, forum4_.description as descript3_6_3_, forum4_.name as name6_3_, forum4_.parent_id as parent5_6_3_, postedmess5_.id as id1_4_, postedmess5_1_.author_id as author7_1_4_, postedmess5_1_.body as body1_4_, postedmess5_1_.contentType as contentT3_1_4_, postedmess5_1_.lastModified as lastModi4_1_4_, postedmess5_1_.subject as subject1_4_, postedmess5_1_.whenSent as whenSent1_4_, postedmess5_.parent_id as parent2_5_4_, postedmess5_.thread_id as thread3_5_4_ from PostedMessage replies0_ inner join Message replies0_1_ on replies0_.id=replies0_1_.id inner join Author author1_ on replies0_1_.author_id=author1_.id left outer join GuestAuthor author1_1_ on author1_.id=author1_1_.id left outer join User author1_2_ on author1_.id=author1_2_.id inner join DiscussionThread discussion2_ on replies0_.thread_id=discussion2_.id left outer join PostedMessage postedmess3_ on discussion2_.firstMessage_id=postedmess3_.id left outer join Message postedmess3_1_ on postedmess3_.id=postedmess3_1_.id left outer join Forum forum4_ on discussion2_.forum_id=forum4_.id left outer join PostedMessage postedmess5_ on discussion2_.lastMessage_id=postedmess5_.id left outer join Message postedmess5_1_ on postedmess5_.id=postedmess5_1_.id where replies0_.parent_id=? order by Message.whenSent asc

which results in this exception trace:

Code:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not initialize a collection: [org.frecklepuppy.bb.model.PostedMessage.replies#1]
   org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:535)
   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)

root cause 
org.hibernate.exception.SQLGrammarException: could not initialize a collection: [org.frecklepuppy.bb.model.PostedMessage.replies#1]
   org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   org.hibernate.loader.Loader.loadCollection(Loader.java:2001)
   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.ThreadLookupServiceImpl.forceLoadMessage(ThreadLookupServiceImpl.java:33)
   org.frecklepuppy.bb.service.impl.ThreadLookupServiceImpl.findThreadById(ThreadLookupServiceImpl.java:26)
   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)
   $Proxy31.findThreadById(Unknown Source)
   org.frecklepuppy.bb.ui.controllers.ViewThreadController.viewThread(ViewThreadController.java:37)
   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)

root cause 
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'Message.whenSent' 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.ThreadLookupServiceImpl.forceLoadMessage(ThreadLookupServiceImpl.java:33)
   org.frecklepuppy.bb.service.impl.ThreadLookupServiceImpl.findThreadById(ThreadLookupServiceImpl.java:26)
   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)
   $Proxy31.findThreadById(Unknown Source)
   org.frecklepuppy.bb.ui.controllers.ViewThreadController.viewThread(ViewThreadController.java:37)
   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)

I verified that the "whenSent" column does exist in the "Message" table.

I'm using MySQL 5.0.18, Hibernate Core 3.2.6, Hibernate EntityManager 3.3.2, and the MySQL5InnoDBDialect.

Am I doing something wrong that I'm just not seeing, or is this a bug?

REggert · **Joined:** Wed Aug 30, 2006 3:17 pm **Posts:** 11

No response? I guess it's time to submit it to JIRA.

REggert · **Joined:** Wed Aug 30, 2006 3:17 pm **Posts:** 11

Reported as http://opensource.atlassian.com/project ... se/EJB-363

sola · **Joined:** Wed Nov 05, 2003 7:51 am **Posts:** 22

I have the exact same problem.

Extremely annoying bug (I can't imagine this is a feature).

sola · **Joined:** Wed Nov 05, 2003 7:51 am **Posts:** 22

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.