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

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.