[hibernate-issues] [Hibernate-JIRA] Updated: (ANN-651) Bad SQL generated when using the @OrderBy annotation

[Emmanuel Bernard (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/ANN-651?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Emmanuel Bernard updated ANN-651:
---------------------------------

    Attachment: ANN-651_test.patch

> Bad SQL generated when using the @OrderBy annotation
> ----------------------------------------------------
>
>                 Key: ANN-651
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/ANN-651
>             Project: Hibernate Annotations
>          Issue Type: Bug
>    Affects Versions: 3.3.0.ga
>         Environment: Hibernate Annotations 3.3.0.GA
> Hibernate 3.2.5
> EntityManager 3.3.1.GA
> Hibernate Validator 3.0.0.GA
> Oracle 10g
>            Reporter: Eyal Lupu
>         Attachments: ANN-651_test.patch, orderBy-bug-test-case.zip
>
>
> When upgrading to the versions above a code which works for months now stopped working, the reason is that Hibernate generates a bad SQL statment. 
> Attached is a test case:
> Having the following classes
>        Parent
>        /          \
>    Child1   Child2
> The parent has a property named 'elementType'. Child1 has a list of Child2 (its sibling) which is ordered using the 
> @OrderBy("elementType") annotation. When fetching a Child1 instance, using the EntityManager.find(...) method, Hibernate generates an 
> invalid SQL:
> Hibernate: /* load bug.Child1 */ select child1x0_.ID as ID0_1_, child1x0_.ELEMENT_TYPE as ELEMENT3_0_1_, elements1_.PARENT_ID as PARENT4_3_, elements1_.ID as ID3_, elements1_.ID as ID0_0_, elements1_.ELEMENT_TYPE as ELEMENT3_0_0_, elements1_.PARENT_ID as PARENT4_0_0_ from BUG_TST child1x0_ left outer join BUG_TST elements1_ on child1x0_.ID=elements1_.PARENT_ID where child1x0_.ID=? and child1x0_.DTYPE='Child1' order by BUG_TST.ELEMENT_TYPE asc
> The error is in the order by clause, you can't use the table name if you gave it an alias - you should use the alias.
> * If I move the 'elementType' property down to Child2 it works!
> *If I remove the @OrderBy it also works
> -- It is hard to know if this is an Hibernate core or Hibernate annotation bug.
> This is the exception:
> Caused by: org.hibernate.exception.SQLGrammarException: could not load an entity: [bug.Child1#1]
> 	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
> 	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> 	at org.hibernate.loader.Loader.loadEntity(Loader.java:1874)
> 	at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:48)
> 	at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:42)
> 	at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3044)
> 	at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:395)
> 	at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:375)
> 	at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:139)
> 	at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:195)
> 	at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:103)
> 	at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)
> 	at org.hibernate.impl.SessionImpl.get(SessionImpl.java:815)
> 	at org.hibernate.impl.SessionImpl.get(SessionImpl.java:808)
> 	at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:175)
> 	... 1 more
> Caused by: java.sql.SQLException: ORA-00904: "BUG_TST"."ELEMENT_TYPE": invalid identifier
> 	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
> 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
> 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
> 	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
> 	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
> 	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
> 	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1038)
> 	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
> 	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1133)
> 	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
> 	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
> 	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
> 	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.loadEntity(Loader.java:1860)
> 	... 13 more
> -- Attached is a test case

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