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

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