[Hibernate-JIRA] Commented: (HHH-1524) Error on left outer join with Oracle Dialect: ORA-00936: missing expression
1:44 p.m.
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1524?page=c...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
6213
days inactive
6213
days old
hibernate-issues@lists.jboss.org
0 comments
1 participants
participants (1)
-
Konstantin Skabeev (JIRA)