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

[Diego Pires Plentz (JIRA)]

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

Diego Pires Plentz commented on HHH-1524:
-----------------------------------------

HHH-2189 (dup) have more info.

> 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