IBM Support

IC98129: POSSIBLE INCORRECT RESULT ON MULTIPLE OUTER JOINS AND A COMBINATION OF EQUALITY JOIN PREDICATES AND LOCAL PREDICATES

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In a rare scenario, a SELECT statement on a stack of outer
    joins may return incorrect result. The following conditions are
    required for the problem.
    
    1. There are at least 3 outer joins.
    2. The join predicates in the 3 outer joins are equality joins.
    3. The top outer join has the next two outer joins as the
       null-producing table.
    4. The second outer join from the top returns some column from
       the null-producing table on the outer join.
    5. There is an equality join predicate on the output column in
    6. There is an equality predicate on the join column in a WHERE
       clause on the outer joins.
    7. There is a second equality predicate in a WHERE clause on the
       output column in 4.
    
      To demonstrate these conditions, an example is given below:
    
    SET SCHEMA "TEST";
    
    CREATE TABLE "TEST"."T1"  (
              "OBJECTID" CHAR(10) NOT NULL ,
              "OWNER" VARCHAR(10) ,
              "CODE1" VARCHAR(5) NOT NULL ,
             "CODE2" VARCHAR(5) NOT NULL
    );
    
    INSERT INTO "TEST"."T1"
    VALUES
    ('3UZ','USER1','99','KT'),
    ('9RY','ADMIN','99','KT')
    ;
    
    CREATE TABLE "TEST"."T2"  (
              "CODE1" VARCHAR(5) NOT NULL ,
              "CODE3" VARCHAR(4)
    );
    
    INSERT INTO "TEST"."T2"
    VALUES
    ('99','2013'),
    ('-1','2013')
    ;
    
    CREATE TABLE "TEST"."T0"  (
              "CODE1" VARCHAR(5) NOT NULL ,
              "CODE3" VARCHAR(4) NOT NULL
    );
    
    INSERT INTO "TEST"."T0" SELECT * FROM "TEST"."T2"
    ;
    
    
    SELECT V2.OBJECTID, V2.CODE1, V2.CODE3, V2.OWNER
    FROM   T0
           LEFT OUTER JOIN /* Condition 1: OUTER JOIN #1 */
           (
            SELECT
                    T1.OBJECTID,
                    T1.OWNER,
                    T1.CODE1,
                    T2.CODE3 /* Condition 4: A column from the
    null-producing table is an output column */
            FROM T1
                 LEFT OUTER JOIN T2 /* Condition 1: OUTER JOIN #2 */
                 ON T1.CODE1 = T2.CODE1 /* Condition 2: Equality
    join predicate */
                 LEFT OUTER JOIN T2 T3 /* Condition 1: OUTER JOIN #3
    */
                 ON T1.CODE1 = T3.CODE1 /* Condition 2: Equality
    join predicate */
            WHERE T1.CODE2 = 'KT'
           ) AS V2(OBJECTID, OWNER, CODE1, CODE3)
          /* Condition 3: T0 LEFT JOIN (T1 LEFT JOIN T2 LEFT JOIN
    T2) */
          /*           The top outer join has the two lower outer
    joins as an input on the right table of
                       its LEFT OUTER JOIN, or, in other word, as
    the null-producing table. */
    ON     T0.CODE1 = V2.CODE1 /* Condition 2: Equality join
    predicate */
    AND    T0.CODE3 = V2.CODE3 /* Condition 5: Equality join
    predicate on the column in Condition 4 */
    WHERE  T0.CODE1 = '99' /* Condition 6: An equality predicate on
    the join column in the WHERE clause */
    AND    T0.CODE3 = '2013' /* Condition 7: An equality predicate
    on the column from Condition 4 */
    ;
    
    
      The result incorrectly returns the duplicate rows of the first
    two records. The correct result is only the first two rows.
    
    OBJECTID   CODE1 CODE3 OWNER
    ---------- ----- ----- ----------
    3UZ        99    2013  USER1
    9RY        99    2013  ADMIN
    9RY        99    2013  ADMIN
    3UZ        99    2013  USER1
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to db2 Version 10.1 FixPack 4                        *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 10.1 FixPack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC98129

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-12-04

  • Closed date

    2014-05-08

  • Last modified date

    2014-05-26

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

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

    IC98160

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1"}]

Document Information

Modified date:
23 September 2021