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

[Konstantin Skabeev (JIRA)]

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

Konstantin Skabeev commented on HHH-1524:
-----------------------------------------

Milosz, that's exactly the problem: Hibernate inserts outer joins where it really shouldn't, that is in the conditions with OR and IN operators. This results in an SQL Grammar errors. Ideally, the whole condition should be parsed and (+) inserted only where it's safe, where there are no OR or IN operators. Certainly, this would require writing pretty sophisticated parser to take care of all the cases I described in my previous comment. 

> 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