[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2929) Alias passed in createCriteria is not applied in where clause resulting in bad SQL
Ashish Tiwari (JIRA)
noreply at atlassian.com
Fri Nov 2 17:08:30 EDT 2007
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2929?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28680 ]
Ashish Tiwari commented on HHH-2929:
------------------------------------
Yes already passing alias in the createCriteria. This is the current code that resulted in the problem SQL. Please note that same code doesn't not always generate same SQL as mentioned above.
> 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
More information about the hibernate-issues
mailing list