[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2929) Alias passed in createCriteria is not applied in where clause resulting in bad SQL
Diego Pires Plentz (JIRA)
noreply at atlassian.com
Fri Nov 2 17:28:29 EDT 2007
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2929?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28681 ]
Diego Pires Plentz commented on HHH-2929:
-----------------------------------------
So, please attach a runnable testcase(java+mapping), reproducing just the problem. If you can, take a look at hibernate testsuite and make that way ;-)
> 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