[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3589) Sql generated for criteria intermittently causes "invalid identifier" error by assigning incorrect table alias to query elements

Sql generated for criteria intermittently causes "invalid identifier" error by assigning incorrect table alias to query elements -------------------------------------------------------------------------------------------------------------------------------- Key: HHH-3589 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3589 Project: Hibernate Core Issue Type: Bug Components: core Affects Versions: 3.3.1 Environment: Hibernate Core 3.3.1.GA Oracle 10.02.03 JRE Q1.5.0_16-b02 Reporter: Todd Currie Original Estimate: 4 hours Remaining Estimate: 4 hours Application is encountering intermittent ORA-00904 "invalid identifier" errors in production and test build environment. Error occurs approximately 1:460 executions in the test build environment. In each occurence it happens with a different test, but when it occurs every criteria query generated for that test class fails. In production typically causes application to crash because of the unexpected exception thrown. The issue appears to be caused during construction of the CriteriaJoinWalker. The CriteriaLoader creates a CriteriaQueryTranslator that is responsible for the where condition and order by for non-projection queries (or additionally the select and group by for projection queries). The translator constructor is passed the CriteriaQueryTranslator.ROOT_SQL_ALIAS, "this_", which it uses for its rootSQLAlias. But when the CriteriaLoader next creates the CriteriaJoinWalker instance that is responsible for the rest of the query elements, it does not use the constructor that takes the root alias. The CriteriaJoinWalker then calls super(persister, factory, enabledFilters, alias); with a null passed in the alias parameter. The constructor in the parent class, AbstractEntityJoinWalker, calls generateRootAlias when the alias parameter is null. The method generateRootAlias is implemented both in the parent class JoinWalker and the child class CriteriaJoinWalker. To implement this form of polymorphism, java uses dynamic method binding to determine which method to call for the instance at run time. It appears that most of the time it calls the CriteriaJoinWalker implementation of generateRootAlias function which is hard coded to return CriteriaQueryTranslator.ROOT_SQL_ALIAS, "this_". The queries then generated by the CriteriaJoinWalker then execute correctly. But intermittently it calls the JoinWalker implementation which uses the passed in persistor's entityName to generate the table alias. This table alias that is then used for the rest of the query is different then the alias used by the CriteriaQueryTranslator and the resulting sql generates the invalid identifier error seen upon execution. This can be fixed in a number of ways. 1) pass the CriteriaQueryTranslator.ROOT_SQL_ALIAS into the constructor for the CriteriaJoinWalker. 2) make the generateRootAlias abstract in the JoinWalker (less reliable). 3) use an AbstractFactory pattern to construct the objects (more work). I have implemented #1 in our environment and am working on generating a clean test case to submit here. But for those who are interested here is the patch: ### Eclipse Workspace Patch 1.0 #P hibernate-core Index: src/main/java/org/hibernate/loader/criteria/CriteriaLoader.java =================================================================== --- src/main/java/org/hibernate/loader/criteria/CriteriaLoader.java (revision 15443) +++ src/main/java/org/hibernate/loader/criteria/CriteriaLoader.java (working copy) @@ -94,7 +94,8 @@ factory, criteria, rootEntityName, - enabledFilters + enabledFilters, + CriteriaQueryTranslator.ROOT_SQL_ALIAS ); initFromWalker(walker); Example stack trace: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:615) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424) at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1051) at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1044) at com.onstation.service.PersistenceServiceImpl.findByCriteria(Unknown Source) at com.onstation.service.MailingServiceTest.shouldFailToFindStateConversions(MailingServiceTest.java:52) Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2231) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) at org.hibernate.loader.Loader.list(Loader.java:2120) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306) at org.springframework.orm.hibernate3.HibernateTemplate$36.doInHibernate(HibernateTemplate.java:1061) at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419) Caused by: java.sql.SQLException: ORA-00904: "THIS_"."SOURCE": 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:745) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1808) at org.hibernate.loader.Loader.doQuery(Loader.java:697) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228) Example query: Hibernate: select stateconve0_.FAILEDID as FAILEDID311_0_, stateconve0_.CREATED as CREATED311_0_, stateconve0_.LOG as LOG311_0_, stateconve0_.SOURCE as SOURCE311_0_ from STATECONVERTFAILED stateconve0_ where this_.SOURCE=?