PM59899: INCORROUT ON OLAP MIN OR MAX WITH NULL VALUE IN ONE OF THE ROWS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The following SQL should return the minumum value 1 for the olap
  MIN specification.  It incorrectly returns null.
  
  CREATE TABLE TMIN (C1 INT  ,C2 INT);
  INSERT INTO TMIN VALUES (NULL,1);
  INSERT INTO TMIN VALUES (1,3);
  
  
  SELECT
  MIN(C1) OVER (ORDER BY C2   ROWS BETWEEN UNBOUNDED
                PRECEDING AND CURRENT ROW )
  C1
  FROM TMIN;
  
  
  The problem can occur for any data type and also olap MAX.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of OLAP            *
  *                 specifications                               *
  ****************************************************************
  * PROBLEM DESCRIPTION: OLAP specifications MAX and MIN may     *
  *                      return incorrect output if the input    *
  *                      expression is null.                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
   DB2 may return incorrect output for the OLAP specification
   result if the following is true:
  
   1. The OLAP specification is MIN or MAX.
   2. The first input value processed for the OLAP specification
      window is the null value and the result value for
      the row is null.
  
   The following is an example where the incorrout occurs:
  
    CREATE TABLE TMIN (C1 INT, C2 INT);
    INSERT INTO TMIN VALUES (NULL,1);
    INSERT INTO TMIN VALUES (1,2);
  
    SELECT  MAX(C1) OVER (ORDER BY C2
                          ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW ) AS OLAPMAX
            , C1
            FROM TMIN;
  
      +---------------------------------+
      |    OLAPMAX     |       C1       |
      +---------------------------------+
    1_| ?              | ?              |
    2_| ?              |              1 |
      +---------------------------------+
  
     OLAPMAX should be 1 for the second row.
  

Problem conclusion

• DB2 has been fixed to correctly process the null value for MIN
  and MAX OLAP specifications.
  
  Additional keywords : SQLOLAP DB2INCORR/K INCORROUT
                        SQLINCORR SQLINCORROUT SQLMIN SQLMAX
  

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:

  UK91474

Modules/Macros

•    DSNXGSFL DSNXRWND
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK91474

     UP13/02/22 P F302

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.