IBM Support

IC77837: INCORRECT RESULT WHEN OUTER JOIN HAS GENERATED COLUMN'S DEFINITION COLUMN IN THE JOIN AND OTHER LOCAL PREDICATES

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • DB2 might produce wrong results under the following scenario:
    
    1. The query is an outer join.
    2. The NULL producing side table has a generated column
    3. The generate column's definition column is used in the join
    4. The row preserving side table has a join and a local
    predicate on the same column.
    5. The local predicate is such that it causes the generated
    column to have same value as the column it is derived from.
    
    Here's an example.
    
    CREATE TABLE RP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ');
    CREATE TABLE NP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ' ,
                                         GC CHAR(13) GENERATED
    ALWAYS AS (SUBSTR(C1, 1, 10)));
    
    INSERT INTO RP VALUES '123', '123456', '2';
    INSERT INTO NP (C1) VALUES '123', '123456', '2';
    
    SELECT * FROM RP LEFT OUTER JOIN NP ON RP.C1 = NP.C1 AND RP.C1 =
    '123';
    
    C1                   C1                   GC
    -------------------- -------------------- -------------
    123                  123                  123
    123456             123456            123456
    2                      2                     2
    
    The correct result should be
    
    C1                   C1
    -------------------- --------------------
    123                  123
    123456             -
    2                      -
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See APAR description                                         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to db2 version 970 fixpack 5                         *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in db2 version 970 fixpack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77837

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-07-29

  • Closed date

    2012-01-17

  • Last modified date

    2012-01-17

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

  • 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

  • R970 PSN

       UP

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC77837

Modified date: 17 January 2012