IBM Support

JR28168: IN DB2 LUW V9.5, A WRONG RESULT SET CAN BE RETURNED WHEN THERE ARE MULTIPLE SUBSELECTS CONSUMING A COMMON TABLE EXPRESSION

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In DB2 LUW V9.5, a wrong result set can be returned when there
    are
    multiple subselects consuming a common table expression (CTE).
    
    Here's a simplified scenario:
    
    CREATE TABLE METRICS_T (
            MCIID BIGINT NOT NULL,
            METRIC_ID VARCHAR(128) NOT NULL,
            STRING_VAL VARCHAR(128),
            INT_VAL BIGINT
        );
    INSERT INTO METRICS_T VALUES(1,'customerName','Smith',NULL);
    INSERT INTO METRICS_T VALUES(1,'customerID',NULL,1);
    INSERT INTO METRICS_T VALUES(1,'isCustomerActive',NULL,1);
    INSERT INTO METRICS_T VALUES(2,'customerName','Zucker',NULL);
    INSERT INTO METRICS_T VALUES(2,'customerID',NULL,2);
    INSERT INTO METRICS_T VALUES(2,'isCustomerActive',NULL,1);
    INSERT INTO METRICS_T VALUES(3,'customerName','store 1',NULL);
    INSERT INTO METRICS_T VALUES(3,'customerID',NULL,3);
    INSERT INTO METRICS_T VALUES(3,'isCustomerActive',NULL,1);
    WITH V (MCIID, METRIC_ID, STRING_VAL, INT_VAL)
        AS (SELECT MCIID, METRIC_ID, STRING_VAL, INT_VAL FROM
    METRICS_T)
    SELECT T1.MCIID
     FROM
           (SELECT MCIID,
                   MAX(CASE WHEN METRIC_ID = 'customerName'     THEN
    STRING_VAL END) AS M_CUSTOMERNAME,
                   MAX(CASE WHEN METRIC_ID = 'isCustomerActive' THEN
    INT_VAL END)    AS M_ISCUSTOMERACTIVE
             FROM  V
             GROUP BY MCIID
           ) T1,
           (SELECT DISTINCT
                     MAX(CASE WHEN METRIC_ID = 'isCustomerActive'
    THEN INT_VAL END)    AS M_ISCUSTOMERACTIVE
            FROM   V
            GROUP  BY MCIID
           ) T2,
           (SELECT DISTINCT
                   MAX(CASE WHEN METRIC_ID = 'customerName'     THEN
    STRING_VAL END) AS M_CUSTOMERNAME,
                   MAX(CASE WHEN METRIC_ID = 'isCustomerActive' THEN
    INT_VAL END)    AS M_ISCUSTOMERACTIVE
            FROM   V
            GROUP  BY MCIID
           ) T3
     WHERE T2.M_ISCUSTOMERACTIVE = T1.M_ISCUSTOMERACTIVE
       AND T3.M_CUSTOMERNAME     = T1.M_CUSTOMERNAME
       AND T3.M_ISCUSTOMERACTIVE = T1.M_ISCUSTOMERACTIVE;
    

Local fix

  • The problem can be worked around by altering the order in which
    the subselects appear.  In this example the two SELECT DISTINCTs
    are separated, where before they were ordered one after the
    other.
    
    Simplified Example:
    
    CREATE TABLE METRICS_T (
            MCIID BIGINT NOT NULL,
            METRIC_ID VARCHAR(128) NOT NULL,
            STRING_VAL VARCHAR(128),
            INT_VAL BIGINT
        );
    INSERT INTO METRICS_T VALUES(1,'customerName','Smith',NULL);
    INSERT INTO METRICS_T VALUES(1,'customerID',NULL,1);
    INSERT INTO METRICS_T VALUES(1,'isCustomerActive',NULL,1);
    INSERT INTO METRICS_T VALUES(2,'customerName','Zucker',NULL);
    INSERT INTO METRICS_T VALUES(2,'customerID',NULL,2);
    INSERT INTO METRICS_T VALUES(2,'isCustomerActive',NULL,1);
    INSERT INTO METRICS_T VALUES(3,'customerName','store 1',NULL);
    INSERT INTO METRICS_T VALUES(3,'customerID',NULL,3);
    INSERT INTO METRICS_T VALUES(3,'isCustomerActive',NULL,1);
    WITH V (MCIID, METRIC_ID, STRING_VAL, INT_VAL)
        AS (SELECT MCIID, METRIC_ID, STRING_VAL, INT_VAL FROM
    METRICS_T)
    SELECT T1.MCIID
     FROM
           (SELECT DISTINCT
                     MAX(CASE WHEN METRIC_ID = 'isCustomerActive'
    THEN INT_VAL END)    AS M_ISCUSTOMERACTIVE
            FROM   V
            GROUP  BY MCIID
           ) T2,
           (SELECT MCIID,
                   MAX(CASE WHEN METRIC_ID = 'customerName'     THEN
    STRING_VAL END) AS M_CUSTOMERNAME,
                   MAX(CASE WHEN METRIC_ID = 'isCustomerActive' THEN
    INT_VAL END)    AS M_ISCUSTOMERACTIVE
             FROM  V
             GROUP BY MCIID
           ) T1,
           (SELECT DISTINCT
                   MAX(CASE WHEN METRIC_ID = 'customerName'     THEN
    STRING_VAL END) AS M_CUSTOMERNAME,
                   MAX(CASE WHEN METRIC_ID = 'isCustomerActive' THEN
    INT_VAL END)    AS M_ISCUSTOMERACTIVE
            FROM   V
            GROUP  BY MCIID
           ) T3
     WHERE T2.M_ISCUSTOMERACTIVE = T1.M_ISCUSTOMERACTIVE
       AND T3.M_CUSTOMERNAME     = T1.M_CUSTOMERNAME
       AND T3.M_ISCUSTOMERACTIVE = T1.M_ISCUSTOMERACTIVE;
    

Problem summary

  • na
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.5, FixPak 1
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR28168

  • Reported component name

    DB2 UDB ESE WIN

  • Reported component ID

    5765F4101

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-12-21

  • Closed date

    2008-05-02

  • Last modified date

    2008-05-02

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

    JR27389

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

Fix information

  • Fixed component name

    DB2 UDB ESE WIN

  • Fixed component ID

    5765F4101

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
02 May 2008