[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-1524) Error on left outer join with Oracle Dialect: ORA-00936: missing expression

[Yajun Shi (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1524?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

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