[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2929) Alias passed in createCriteria is not applied in where clause resulting in bad SQL

Will it be sufficient to demonstrate that the alias is ignored when passed in createCriteria?

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