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

[Ashish Tiwari (JIRA)]

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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira