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

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

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