ORA-01719 When Executing Outer Join Query With Isnull() On Referenced Object (Doc ID 825611.1)

Last updated on MARCH 01, 2016

Applies to:

Oracle TopLink - Version 11.1.1.0.1 to 11.1.1.0.2 [Release Oracle11g]
Information in this document applies to any platform.
**Checked for relevance 19-MAR-2013**
**Checked for relevance 22-Aug-2014**

Symptoms

An Entity (Person) has ManyToOne relationship to an Entity (Company).
An Outer join query checks referenced object for being NULL :

Session session = JpaHelper.getEntityManager(persistenceContext).getSession();
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Person.class);
ExpressionBuilder personE = query.getExpressionBuilder();
Expression expression =
personE.getAllowingNull("company").isNull().or(personE.getAllowingNull("company").get("name").like("Demo%"));

query.setSelectionCriteria(expression);
List<Company> result = (List<Company>) session.executeQuery(query);

 

When executing this query, the DatabaseException exception (related to "ORA-01719: outer join operator (+) not allowed in operand of OR or IN" error ) occurs - with the following generated wrong SQL query (in bold) :

[EL Warning]: 2009-03-13 16:45:15.015--UnitOfWork(3083761)--Thread(Thread[main,5,main])--Exception
[EclipseLink-4002] (Eclipse Persistence Services - 1.1.0.r3634):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-01719: outer join operator (+) not allowed in
operand of OR or IN

Error Code: 1719
Call: SELECT t0.ID, t0.NAME, t0.COMPANY_ID FROM PERSON t0, COMPANY t1 WHERE ((t0.COMPANY_ID (+) = ? OR (t1.NAME LIKE ?)) AND (t1.ID (+) = t0.COMPANY_ID))
bind => [null, Demo%]
Query: ReadAllQuery(referenceClass=Person sql="SELECT t0.ID, t0.NAME, t0.COMPANY_ID FROM PERSON
t0, COMPANY t1 WHERE ((t0.COMPANY_ID (+) = ? OR (t1.NAME LIKE ?)) AND (t1.ID (+) = t0.COMPANY_ID))")

A correct generated SQL query can be: 

SELECT t0.ID, t0.NAME, t0.COMPANY_ID
FROM PERSON t0, COMPANY t1
WHERE
(t1.ID (+) = t0.COMPANY_ID and t0.COMPANY_ID IS NULL)
or
(t1.ID (+) = t0.COMPANY_ID and t1.NAME LIKE 'Demo%')


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms