[Hibernate-JIRA] Updated: (HHH-1524) Error on left outer join with Oracle Dialect: ORA-00936: missing expression
by Yajun Shi (JIRA)
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1524?page=c... ]
Yajun Shi updated HHH-1524:
---------------------------
Attachment: SUP-3701.zip
During my testing, there is no bug in Hibernate 3.1.3 and Hibernate 3.2.3 on this issue if the case is tested with Oracle 10g. My test case is attached here.
In brief, HQL:
from Company comp
left outer join comp.employees empl
with empl.birthday >= :date
then the SQL produced:
select
company0_.id as id0_0_,
employees1_.id as id1_1_,
company0_.companyName as companyN2_0_0_,
employees1_.name as name1_1_,
employees1_.birthday as birthday1_1_,
employees1_.companyId as companyId1_1_
from
TB_COMPANY_TEMP company0_
left outer join
TB_EMPLOYEE_TEMP employees1_
on company0_.id=employees1_.companyId
and (
employees1_.birthday>=?
)
and there is no exception thrown.
So you don't need the patch if Oracle 10g is used.
Regards,
Yajun Shi
SourceLabs - http://www.sourcelabs.com
Dependable Open Source Systems
> Error on left outer join with Oracle Dialect: ORA-00936: missing expression
> ---------------------------------------------------------------------------
>
> Key: HHH-1524
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1524
> Project: Hibernate3
> Issue Type: Bug
> Components: query-sql
> Affects Versions: 3.1.2
> Environment: Oracle 8.1.7, Hibernate 3.1.2
> Reporter: Marcelo Oliveira
> Priority: Critical
> Attachments: HHH-1524.patch, ORA-00936.zip, SUP-3701.zip
>
> Original Estimate: 1 day
> Remaining Estimate: 1 day
>
> With this HQL:
> from Company comp
> left outer join comp.employees empl
> with empl.birthday > :date
> Produce following SQL:
> select
> company0_.id as id0_0_,
> employees1_.id as id1_1_,
> company0_.companyName as companyN2_0_0_,
> employees1_.name as name1_1_,
> employees1_.birthday as birthday1_1_,
> employees1_.companyId as companyId1_1_
> from
> TB_COMPANY_TEMP company0_,
> TB_EMPLOYEE_TEMP employees1_
> where
> company0_.id=employees1_.companyId(+)
> and (employees1_.birthday(+)>(+)=?)
> When it is run, the stacktrace is:
> org.hibernate.exception.SQLGrammarException: could not execute query
> at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
> at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> at org.hibernate.loader.Loader.doList(Loader.java:2148)
> at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
> at org.hibernate.loader.Loader.list(Loader.java:2024)
> at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
> at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
> at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
> at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
> at mytest.OuterJoinTest.main(OuterJoinTest.java:28)
> Caused by: java.sql.SQLException: ORA-00936: missing expression
> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
> at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
> at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
> at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
> at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
> at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1870)
> at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314)
> at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
> at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
> 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:2145)
> ... 8 more
> The correct where clause should be :
> where
> company0_.id=employees1_.companyId(+)
> and (employees1_.birthday(+)>=?)
> This error don't occur with MySQLDialect or SQLServerDialect. Probably it considers >= as two operators instead of a single operator.
--
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
17 years, 8 months