IBM Support

PH12229: INCORRECT OUTPUT RECEIVED FOR A QUERY LEFT JOIN WITH A NESTED TABLE EXPRESSION THAT CONTAINS LEFT OUTER JOIN

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

APAR status

  • Closed as program error.

Error description

  • Incorrect output is received on a query LEFT JOIN with a Nested
    Table Expression (NTE) that contains LEFT OUTER JOIN.  Only the
    rows that have a match with the NTE rows are returned (as with
    an INNER JOIN).
    
    Additional Keywords:
    SQLLEFT SQLLEFTJOIN SQLTABLEEXPR DB2INCORR/K INCORROUT SQLINCORR
    SQLINCORROUT
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Db2 12 for z/OS users of an SQL                          *
    * statement containing left or right outer                     *
    * joins.                                                       *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Incorrect output may happen for the                          *
    * following two cases.                                         *
    *                                                              *
    * Case 1:                                                      *
    * An SQL statement satisfying all of                           *
    * following conditions:                                        *
    * 1. It contains multiple left or                              *
    *    right outer joins.                                        *
    * 2. The ON clause contains an IS NOT                          *
    *    NULL predicate on a not null                              *
    *    column.                                                   *
    *                                                              *
    * Case 2:                                                      *
    * An SQL statement satisfying all of                           *
    * following conditions:                                        *
    * 1. It contains a table expression,                           *
    *    view or SQL table function which is                       *
    *    on the null-supplying side of a                           *
    *    left or right outer join.                                 *
    * 2. The table expression, view or                             *
    *    SQL table function contains                               *
    *    multiple left or right outer joins.                       *
    * 3. The left or right outer join                              *
    *    contains a non-Boolean term                               *
    *    predicate that references a column                        *
    *    from the null supplying side of the                       *
    *    outer join.                                               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    An example for case 1 is shown below:
    
    CREATE TABLE TBX(
      C1 INTEGER NOT NULL
    , C2 INTEGER NOT NULL
    , C3 INTEGER NOT NULL)
    ;
    CREATE TABLE TBY(
      C1 INTEGER NOT NULL
    , C2 INTEGER NOT NULL
    , C3 INTEGER NOT NULL)
    ;
    CREATE TABLE TBZ(
      C1 INTEGER NOT NULL
    , C2 INTEGER NOT NULL
    , C3 INTEGER NOT NULL)
    ;
    INSERT INTO TBX(C1,C2,C3) VALUES(1,2,3);
    INSERT INTO TBY(C1,C2,C3) VALUES(1,2,3);
    INSERT INTO TBZ(C1,C2,C3) VALUES(1,2,3);
    
    SELECT T1.C1 AS T1C1,T2.C1 AS T2C1,TW.C1 AS TWC1,T3.C1 AS T3C1
    FROM TBX T1
       LEFT JOIN
         TBX TW ON 1=1
       LEFT JOIN
         TBY T2 ON
     (T2.C3 = TW.C3     AND
      T2.C1 = 1         AND
      T2.C2 = 2
     )
       JOIN
         TBZ T3 ON
     (T2.C3 IS NOT NULL AND
      T3.C3 = T2.C3     AND
      T3.C2 = T2.C2     AND
      T3.C1 = T2.C1
     ) OR
     (T2.C3 IS NULL     AND
      0 = 0             AND
      T3.C3 = TW.C3     AND
      T3.C2 = 2         AND
       T3.C1 = 1
     )
    ;
    
    The SELECT statement returned wrong result for some of the
    columns.
    
    An example for case 2 is shown below:
    
    CREATE TABLE T1 (C1 INT, C2 INT);
    CREATE TABLE T2 (C1 INT, C2 INT);
    CREATE TABLE T3 (C1 INT, C2 INT);
    CREATE TABLE T4 (C1 INT, C2 INT);
    CREATE TABLE T5 (C1 INT, C2 INT);
    INSERT INTO T1 VALUES(1,10);
    INSERT INTO T1 VALUES(2,20);
    INSERT INTO T1 VALUES(3,30);
    INSERT INTO T2 VALUES(1,10);
    INSERT INTO T2 VALUES(4,40);
    INSERT INTO T3 VALUES(1,10);
    INSERT INTO T3 VALUES(4,40);
    INSERT INTO T3 VALUES(5,50);
    INSERT INTO T4 VALUES(1,10);
    INSERT INTO T4 VALUES(4,40);
    INSERT INTO T5 VALUES(1,10);
    INSERT INTO T5 VALUES(4,40);
    
    SELECT T1.C1, TX.CX, TX.CY
    FROM T1 LEFT JOIN
     (SELECT T2.C1 , T3.C1
      FROM T2 INNER JOIN T4
          ON T2.C1=T4.C1
        LEFT JOIN T3
          ON T2.C1=T3.C1
        LEFT JOIN T5
          ON T2.C1=T5.C1
      WHERE (T2.C1=4 OR T3.C1 IS NOT NULL)
     )TX(CX,CY)
    ON T1.C1=TX.CX
    ;
    
    The SELECT statement returned less rows.
    
    Db2 did not correctly process the left or right outer joins and
    resulted in the incorrect output.
    

Problem conclusion

  • Db2 has been modified to correctly process the aforementioned
    SQL statement, and correct result will be returned.
    
    Additional keywords: SQLINCORR INCORROUT SQLINCORROUT
                         Db2INCORR/K SQLTABLEEXP SQLVIEW
                         SQLTABLEUDF SQLTUDF SQLOUTERJOIN SQLOUTER
                         SQLJOIN SQLLEFTJOIN SQLLEFT SQLRIGHTJOIN
                         SQLRIGHT SQLNOTNULL SQLISNOTNULL
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PH12229

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-05-20

  • Closed date

    2019-06-11

  • Last modified date

    2019-06-11

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UI63563

Modules/Macros

  • DSNXOMQB
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels



Document information

More support for: DB2 for z/OS

Software version: 12.0

Reference #: PH12229

Modified date: 11 June 2019