IBM Support

IC84320: COALESCE EXPRESSION IN THE OUTER JOIN OPERATOR (+) MAY RETURN INCORRECT RESULTS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2 may return incorrect result when DB2_COMPATIBILITY_VECTOR is
    set to support the OUTER JOIN (+) syntax, and all the conditions
    below are met:
    
    1. WHERE clause contains a predicate with COALESCE function.
    2. The COALESCE function contains a column from the table used
    in the (+) operator.
    3. The column in 2) is defined NOT NULL.
    4. Another predicate AND to the predicate with COALESCE function
    in (1).
    
    For example:
    CREATE TABLE T1(C1 INTEGER, C2 INTEGER NOT NULL);
    CREATE TABLE T2(C1 INTEGER);
    
    INSERT INTO T1 VALUES (1,1);
    INSERT INTO T2 VALUES 1,2;
    
    SELECT T1.C1,T2.C1 FROM T1,T2 WHERE T1.C1 (+) = T2.C1 AND
    COALESCE(T1.C2,1) = 1 AND T2.C1=2;
    
    The correct result should return 1 row.
    
    T1.C1        T2.C1
    -----------  -----------
              -            2
    
    This problem is fixed in DB2 Version 9.7 Fix Pack 6.
    

Local fix

  • There are two possible workarounds,
    1) Use the ANSI syntax of the OUTER JOIN
    
    The example above can be written as
    SELECT T1.C1,T2.C1 FROM T1 RIGHT OUTER JOIN T2 ON T1.C1=T2.C1
    WHERE COALESCE(T1.C2,1) = 1 AND T2.C1=2;
    
    2) Separate the COALESCE predicate into a subselect after the
    OUTER JOIN (+) operator predicate
    
    The example above can be written as
    SELECT T1C1, T2C1
    FROM (SELECT T1.C1 as T1C1, T1.C2 as T1C2, T2.C1 as T2C1 FROM
    T1,T2 WHERE T1.C1(+) = T2.C1) AS Q
    WHERE COALESCE(T1C2, 1) = 1 AND T2C1=2;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.1 Fix Pack 1                       *
    ****************************************************************
    

Problem conclusion

  • Upgrade to DB2 version 10.1 Fix Pack 1
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC84320

  • 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

    2012-06-13

  • Closed date

    2012-11-07

  • Last modified date

    2013-09-25

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

    IC80899

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

  • RA10 PSY

       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:
19 September 2021