Error on left outer join with Oracle Dialect: ORA-00936: missing expression
Description
Attachments
- 05 May 2007, 08:12 PM
- 05 Nov 2006, 04:27 PM
- 24 Feb 2006, 04:17 PM
is duplicated by
Activity
Brett MeyerJuly 8, 2014 at 3:11 PM
Bulk rejecting stale issues. If this is still a legitimate issue on ORM 4, feel free to comment and attach a test case. I'll address responses case-by-case. Thanks!
Brett MeyerApril 7, 2014 at 5:46 PM
In an effort to clean up, in bulk, tickets that are most likely out of date, we're transitioning all ORM 3 tickets to an "Awaiting Test Case" state. Please see http://in.relation.to/Bloggers/HibernateORMJIRAPoliciesAndCleanUpTactics for more information.
If this is still a legitimate bug in ORM 4, please provide either a test case that reproduces it or enough detail (entities, mappings, snippets, etc.) to show that it still fails on 4. If nothing is received within 3 months or so, we'll be automatically closing them.
Thank you!
Diego PlentzAugust 16, 2007 at 3:34 AM
https://hibernate.atlassian.net/browse/HHH-2189#icft=HHH-2189 (dup) have more info.
Konstantin SkabeevMay 31, 2007 at 8:43 PM
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.
Milosz TylendaMay 31, 2007 at 7:55 PM
Konstantin, sure, my patch is only a kludge but isn't it that Oracle 8 prohibits outer joins with OR and IN operators? From their docs:
A condition containing the
operator cannot be combined with another condition using the OR logical operator.A condition cannot use the IN comparison operator to compare a column marked with the
operator with an expression.
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.