My Oracle Support Banner

ORA-00904 In Query Scenarios Requiring Multiple Outer Joins With DatabasePlatform.shouldPrintOuterJoinInWhereClause() Returning "false" (Doc ID 836338.1)

Last updated on MARCH 27, 2019

Applies to:

Oracle TopLink - Version 11.1.1.0.1 to 11.1.1.5.0 [Release Oracle11g]
Information in this document applies to any platform.

Symptoms

Problem Summary

In some more complex query scenarios EclipseLink assembles invalid SQL queries with multiple units of outer joins in the FROM clause if a DatabasePlatform was used with shouldPrintOuterJoinInWhereClause() returning false. A query according to the following scenario will run into

ORA-00904: "T4"."FK_Street": invalid identifier

Entity Object Model

Query Definition

public void testOuterJoin() {
    JpaEntityManager entityManager =
        JpaHelper.getEntityManager(persistenceContext);
    Session session = entityManager.getSession();
    ReadAllQuery query = new ReadAllQuery();
    query.setReferenceClass(Employee.class);
    ExpressionBuilder expression = query.getExpressionBuilder();

    query.addJoinedAttribute(expression.get("contacts"));
    query.addJoinedAttribute(expression.getAllowingNull("Projects"));
    query.addJoinedAttribute(expression.getAllowingNull("Address"));
    query.addJoinedAttribute(expression.getAllowingNull("Address")
       .getAllowingNull("Street"));

    Expression filter = expression.get("contacts").getAllowingNull("garage")
      .get("id").isNull();
    query.setSelectionCriteria(filter);
    System.out.println(query.getSelectionCriteria());
    session.executeQuery(query);
}

Generated SELECT Statement

SELECT
    t1.ID,
    t1.NAME,
    t1.FK_Address,
    t1.FK_Projects,
    t1.FK_contacts,
    t0.ID,
    t0.NAME,
    t0.FK_Projects,
    t2.ID,
    t2.NAME,
    t3.ID,
    t3.CLASSTYPE,
    t4.ID,
    t4.AddressNAME,
    t4.FK_Street,
    t5.ID,
    t5.NAME
FROM
    Employee t1 LEFT OUTER JOIN Projects t2 ON (t2.ID = t1.FK_Projects)
    LEFT OUTER JOIN (Location t3 JOIN Address t4 ON (t4.ID = t3.ID))
        ON (t3.ID = t1.FK_Address),
    contacts t0 LEFT OUTER JOIN Garage t6 ON (t6.ID = t0.FK_Projects)
    LEFT OUTER JOIN Street t5 ON (t5.ID = t4.FK_Street)
WHERE ((t6.ID IS NULL) AND (t0.ID = t1.FK_contacts))

Resulting Error Message

Internal Exception: java.sql.SQLException: ORA-00904: "T4"."FK_Street": invalid identifier

[EL Warning]: 2009-04-30 11:44:06.406--UnitOfWork(5612344)--Thread(Thread main,5,main])-- Exception
[EclipseLink-4002] (Eclipse Persistence Services - 1.1.1.qualifier):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00904: "T4"."FK_Street": invalid identifier

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.