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

Last updated on JULY 29, 2017

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_ENGINE": 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(Person.class);
    ExpressionBuilder expression = query.getExpressionBuilder();

    query.addJoinedAttribute(expression.get("house"));
    query.addJoinedAttribute(expression.getAllowingNull("company"));
    query.addJoinedAttribute(expression.getAllowingNull("car"));
    query.addJoinedAttribute(expression.getAllowingNull("car")
       .getAllowingNull("engine"));

    Expression filter = expression.get("house").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_CAR,
    t1.FK_COMPANY,
    t1.FK_HOUSE,
    t0.ID,
    t0.NAME,
    t0.FK_COMPANY,
    t2.ID,
    t2.NAME,
    t3.ID,
    t3.CLASSTYPE,
    t4.ID,
    t4.CARNAME,
    t4.FK_ENGINE,
    t5.ID,
    t5.NAME
FROM
    Person t1 LEFT OUTER JOIN Company t2 ON (t2.ID = t1.FK_COMPANY)
    LEFT OUTER JOIN (Vehicle t3 JOIN CAR t4 ON (t4.ID = t3.ID))
        ON (t3.ID = t1.FK_CAR),
    House t0 LEFT OUTER JOIN Garage t6 ON (t6.ID = t0.FK_COMPANY)
    LEFT OUTER JOIN Engine t5 ON (t5.ID = t4.FK_ENGINE)
WHERE ((t6.ID IS NULL) AND (t0.ID = t1.FK_HOUSE))

Resulting Error Message

Internal Exception: java.sql.SQLException: ORA-00904: "T4"."FK_ENGINE": 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_ENGINE": invalid identifier

Changes

 

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