IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

 

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-07-01

  • 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

  • RC10 PSY UI63563

       UP19/06/26 P F906 ¢

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
01 July 2019