Alias passed in createCriteria is not applied in where clause resulting in bad SQL ---------------------------------------------------------------------------------- Key: HHH-2929 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2929 Project: Hibernate3 Issue Type: Bug Components: query-criteria Affects Versions: 3.2.0.ga Environment: Linux, Oracle 10g, Hibernate 3.2.0 ga Reporter: Ashish Tiwari Alias passed in createCriteria is not replaced in the "where" clause of the SQL. This results in incorrect SQL. Source code: List aTypeSDLs = session.createCriteria(Dldefinition.class, "Dldefinition") .add(Expression.eq("Dldefinition.type", aSDLType.toString())) .add(Expression.eq("Dldefinition.name", aSDLName)).list(); SQL generated by hibernate: Hibernate: select dldefiniti0_.DLDEFINITIONID as DLDEFINI1_15_0_, dldefiniti0_.Version as Version15_0_, dldefiniti0_.NAME as NAME15_0_, dldefiniti0_.TYPE as TYPE15_0_, dldefiniti0_.DEFINITION as DEFINITION15_0_ from DLDEFINITION dldefiniti0_ where this_.TYPE=? and this_.NAME=? 2007-11-02 11:43:50,340 WARN org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:71) - SQL Error: 904, SQLState: 42000 2007-11-02 11:43:50,341 ERROR org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:72)- ORA-00904: "THIS_"."NAME": invalid identifier Problem: Notice that the alias in where clase is not replaced. The alias used in where is default "this_", resulting in incorrect SQL. Stack trace: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2147) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028) at org.hibernate.loader.Loader.list(Loader.java:2023) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:95) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283) at com.avaya.coreservice.admin.subsystem.metadata.SDLDataInterfaceHibernateImpl.addSDLInstance(SDLDataInterfaceHibernateImpl.java:197) at com.avaya.coreservice.admin.subsystem.metadata.MetaDataManager.addSDLInstance(MetaDataManager.java:304) at com.avaya.coreservice.admin.subsystem.install.ConfigureSDLData.loadSDL(ConfigureSDLData.java:114) at com.avaya.coreservice.admin.subsystem.install.ConfigureSDLData.loadSDL(ConfigureSDLData.java:89) at com.avaya.coreservice.admin.subsystem.install.ConfigureSDLData.configure(ConfigureSDLData.java:166) at com.avaya.coreservice.admin.subsystem.install.ConfigureSDLData.main(ConfigureSDLData.java:69)Caused by: java.sql.SQLException: ORA-00904: "THIS_"."NAME": 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:1039) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1668) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2144) ... 11 more This problem doesn't happen always. Interesting part is I do not always see my alias used in the SQL. When it works the same code results in following SQL (alias completly ignored): Hibernate: select this_.DLDEFINITIONID as DLDEFINI1_15_0_, this_.Version as Version15_0_, this_.NAME as NAME15_0_, this_.TYPE as TYPE15_0_, this_.DEFINITION as DEFINITION15_0_ from DLDEFINITION this_ where this_.TYPE=? and this_.NAME=? It is the same code that occassionaly results in the failure mentioned above. I don't know condition triggers alias getting used versus ignoring. I can try to look up myself if someone can provide some direction on what would cause this different SQL generation from the same code. How can I enforce Hibernate to always use alias? I appreciate any help with this. -Ashish -- 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