9:59 a.m.
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
--
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