glassfish
  1. glassfish
  2. GLASSFISH-3041

Invalid SQL generated when mixing inner and outer joins

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 9.1pe
    • Fix Version/s: 9.1pe_dev
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Operating System: All
      Platform: All

    • Issuezilla Id:
      3,041

      Description

      Glassfish V2 B42

      Glassfish is generating invalid SQL when inner and outer joins are used in the
      same query.

      EJBQL:
      SELECT t1.accountNumber , t1.status , t2.city , t3.name , t4.legacyNumber ,
      t2.addressLine1 , t1.id , t4.id , t2.id , t3.id
      FROM Business t0
      INNER JOIN t0.accounts t1
      LEFT OUTER JOIN t0.address t2
      LEFT OUTER JOIN t1.dBA t3
      LEFT OUTER JOIN t1.legacyNumbers t4
      WHERE UPPER(t3.name) LIKE ?1 AND t0.id = ?2

      Generated SQL:
      SELECT t0.ACCOUNTNUMBER, t0.STATUS, t2.CITY, t3.NAME, t4.LEGACYNUMBER,
      t2.ADDRESSLINE1, t0.ID, t4.ID, t2.ID, t3.ID
      FROM ACCOUNT t0
      LEFT OUTER JOIN DBA t3 ON (t3.ACCOUNT_ID = t0.ID),BUSINESS t1
      LEFT OUTER JOIN ADDRESS t2 ON (t2.ID = t1.ADDRESS_ID)
      LEFT OUTER JOIN LEGACYNUMBER t4 ON (t4.ACCOUNT_ID = t0.ID)
      WHERE (((UPPER(t3.NAME) LIKE ?) AND (t1.ID = ?)) AND (t0.BUSINESS_ID = t1.ID))

      Pay particular attention to this line:
      LEFT OUTER JOIN DBA t3 ON (t3.ACCOUNT_ID = t0.ID),BUSINESS t1

      To accommodate the inner join, glassfish seems to want to make the link between
      Account and Business in the FROM clause, but has inserted the first outer join
      in between Account and Business.

      The generated SQL should appear as:
      SELECT t0.ACCOUNTNUMBER, t0.STATUS, t2.CITY, t3.NAME, t4.LEGACYNUMBER,
      t2.ADDRESSLINE1, t0.ID, t4.ID, t2.ID, t3.ID
      FROM ACCOUNT t0,BUSINESS t1
      LEFT OUTER JOIN DBA t3 ON (t3.ACCOUNT_ID = t0.ID)
      LEFT OUTER JOIN ADDRESS t2 ON (t2.ID = t1.ADDRESS_ID)
      LEFT OUTER JOIN LEGACYNUMBER t4 ON (t4.ACCOUNT_ID = t0.ID)
      WHERE (((UPPER(t3.NAME) LIKE ?) AND (t1.ID = ?)) AND (t0.BUSINESS_ID = t1.ID))

      Note that the FROM clause should read as:
      FROM Account t0, Business t1

      If I remove the outer joins from the EJB-QL, the statement executes. If I remove
      the inner join, the statement executes. Combine the inner and outer joins, and
      you get the problem mentioned above.

      This is similar to issue 2723, in that the FROM clause and JOIN clauses are
      getting mixed together, but that issue seemed to be related to
      InheritanceType.JOINED, whereas this one seems to only have to do with the
      combination of inner and outer joins.

      Thanks in advance!

        Activity

        Hide
        mb124283 added a comment -

        Created an attachment (id=947)
        Test case to reproduce issue 3041

        Show
        mb124283 added a comment - Created an attachment (id=947) Test case to reproduce issue 3041
        Hide
        mb124283 added a comment -

        I was able to reproduce the exception with a simple Java SE test case. You find
        it attached.

        In SQL the OUTER JOIN clause must refer to the current identification variable.
        The issue is that the declaration of t4 refers to t0, but the current
        identification variable is t1.
        BUSINESS t1
        ...
        LEFT OUTER JOIN LEGACYNUMBER t4 ON (t4.ACCOUNT_ID = t0.ID)
        So the OUTER JOIN clause defining t4 must follow the declaration of t0 (and not
        t1).

        I figured out that the order of expressions in the JPQL SELECT- and WHERE clause
        matters. There are cases (like the query in question) where TopLink does not
        generate the dependent SQL identification variables in the correct order.

        Generating a SQL INNER JOIN might solve the problem. But I expect this to be a
        bigger change in the SQL generation. It effects the handling of the FROM clause
        and the WHERE clause.

        Show
        mb124283 added a comment - I was able to reproduce the exception with a simple Java SE test case. You find it attached. In SQL the OUTER JOIN clause must refer to the current identification variable. The issue is that the declaration of t4 refers to t0, but the current identification variable is t1. BUSINESS t1 ... LEFT OUTER JOIN LEGACYNUMBER t4 ON (t4.ACCOUNT_ID = t0.ID) So the OUTER JOIN clause defining t4 must follow the declaration of t0 (and not t1). I figured out that the order of expressions in the JPQL SELECT- and WHERE clause matters. There are cases (like the query in question) where TopLink does not generate the dependent SQL identification variables in the correct order. Generating a SQL INNER JOIN might solve the problem. But I expect this to be a bigger change in the SQL generation. It effects the handling of the FROM clause and the WHERE clause.
        Hide
        mb124283 added a comment -

        Reassign

        Show
        mb124283 added a comment - Reassign
        Hide
        mb124283 added a comment -

        Started looking into this.

        Show
        mb124283 added a comment - Started looking into this.
        Hide
        mb124283 added a comment -

        Reordered the join clauses in the generated SQL.

        Checking in
        entity-persistence/src/java/oracle/toplink/essentials/internal/expressions/SQLSelectStatement.java;
        new revision: 1.21; previous revision: 1.20

        Checking in
        entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/ejb/ejbqltesting/JUnitEJBQLComplexTestSuite.java;
        new revision: 1.25; previous revision: 1.24

        Show
        mb124283 added a comment - Reordered the join clauses in the generated SQL. Checking in entity-persistence/src/java/oracle/toplink/essentials/internal/expressions/SQLSelectStatement.java; new revision: 1.21; previous revision: 1.20 Checking in entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/ejb/ejbqltesting/JUnitEJBQLComplexTestSuite.java; new revision: 1.25; previous revision: 1.24

          People

          • Assignee:
            mb124283
            Reporter:
            bhar99328
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: