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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM59899

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-03-07

  • Closed date

    2013-02-07

  • Last modified date

    2013-03-04

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



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM59899

Modified date:

2013-03-04

Translate my page

Machine Translation

Content navigation