PH44928: INCORRECT OUTPUT CAN OCCUR WHEN AN EXPRESSION-BASED INDEX IS USED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output may be experienced when an expression-based
  index is chosen by the optimizer, where the expression-based
  index contains built-in function DEC, DIGITS, FLOAT or datetime.
  
  -- For following example table (with not null columns):
  CREATE TABLE TBLTEST16
  (
   COL1 INTEGER,
   COL2 CHAR(6) NOT NULL,
   COL3 DECIMAL(10, 0) NOT NULL,
   COL4 CHAR(6) NOT NULL
  );
  COMMIT;
  
  -- and following example index (with IOE with DIGITS and DEC):
  CREATE UNIQUE INDEX XTEST16
  ON TBLTEST16
  (
   COL1,
   COL2 || COL4 || DIGITS(DEC(10000 - COL3, 6))
  );
  COMMIT;
  
  -- and following sample data:
  INSERT INTO TBLTEST16
  VALUES('1', 'STRING', -100, 'TEST11');
  INSERT INTO TBLTEST16
  VALUES('1', 'STRING', -100, 'TEST22');
  INSERT INTO TBLTEST16
  VALUES('1', 'STRING', -100, 'TEST33');
  COMMIT;
  
  -- such query may return all rows while one row is expected
  SELECT COL1,
         COL2 || COL4 || DIGITS(DEC(10000 - COL3, 6)) AS EXP
  FROM TBLTEST16
  WHERE COL1 = 1
  AND   COL2 || COL4 || DIGITS(DEC(10000 - COL3, 6))
  < 'STRINGTEST22010100'
  ;
  
  KNOWN IMPACT:
  Incorrect amount of rows might be returned due to filtering not
  applied correctly.
  
  ADDITIONAL SYMPTOMS:
  INCORROUT DB2INCORR/K SQLINCORR SQLINCORROUT SQLINDEXONEXP
  SQLFUNCTION
  

Local fix

• BYPASS/CIRCUMVENTION:
  Try adding 'OR 0=1' to force predicate pushdown and keep
  index-only access.
  It will get rid of second matching predicate but still use 'COL1
  = 1' as matching predicate.
  Example:
  SELECT COL1,
         COL2 || COL4 || DIGITS(DEC(10000 - COL3, 6)) AS EXP
  FROM TBLTEST16
  WHERE COL1 = 1
  AND   (COL2 || COL4 || DIGITS(DEC(10000 - COL3, 6))
  < 'STRINGTEST22010100' OR 0=1)
  ;
  
  Try adding explicit cast with CHAR() to get rid of index-only
  access.
  It will get rid of second matching predicate but still use 'COL1
  = 1' as matching predicate.
  Example:
  SELECT COL1,
         COL2 || COL4 || DIGITS(DEC(10000 - COL3, 6)) AS EXP
  FROM TBLTEST16
  WHERE COL1 = 1
  AND   CHAR(COL2 || COL4 || DIGITS(DEC(10000 - COL3, 6)))
  < 'STRINGTEST22010100'
  ;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of expression-                     *
  * based index and built-in function DEC,                       *
  * DIGITS, FLOAT or datetime                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Incorrect output may occur when an                           *
  * expression-based index is chosen                             *
  * by the optimizer, where the                                  *
  * expression-based index contains                              *
  * built-in function DEC, DIGITS, FLOAT                         *
  * or datetime.                                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An example is shown below:
  CREATE TABLE MYT
    (C1 DECIMAL(1,0) NOT NULL,
     C2 DECIMAL(1,0) NOT NULL);
  
  INSERT INTO MYT VALUES(1, 1);
  INSERT INTO MYT VALUES(1, 2);
  INSERT INTO MYT VALUES(1, 3);
  CREATE UNIQUE INDEX MYT_IOE
       ON MYT
        (DIGITS(C1) || DIGITS(DEC(10000 - C2)));
  SELECT DIGITS(C1) || DIGITS(DEC(10000 - C2))
    FROM MYT
   WHERE DIGITS(C1) || DIGITS(DEC(10000 - C2))
         < '1000000000009998';
  
  The above statement returns the following result:
              +------------------+
              |                  |
              +------------------+
            1_| 1000000000009997 |
            2_| 1000000000009998 |
            3_| 1000000000009999 |
              +------------------+
  where only the first row 1000000000009997 is expected.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement.
  Additional Keywords: SQLBIF SQLFUNCTION SQLINDEXONEXP
                       SQLINCORROUT INCORROUT SQLINCORR
                       DB2INCORR/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI80091

Modules/Macros

• DSNXOGP  DSNXOIN  DSNXOPB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI80091

     UP22/04/19 P F204 ¢

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.