PM88703: INCORRECT OUTPUT (INCORROUT) WITH RANKING SPECIFICATION AND CHAR DATA TYPE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • SQL with RANKING specification and char data type may get
    incorrect output
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of the RANK and    *
    *                 DENSE_RANK window functions in the OLAP      *
    *                 specification.                               *
    ****************************************************************
    * PROBLEM DESCRIPTION: An incorrect result can be returned for *
    *                      an SQL statement that contains the      *
    *                      RANK and DENSE_RANK window functions.   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The RANK and DENSE_RANK window functions can return an incorrect
    result set when the argument contains a padded string.
    
    The following simple example illustrates a failing case.
    
    Step 1. Create Table T1 and insert three rows.
    
      CREATE TABLE T1(C_CHA CHAR(2), C_VARC VARCHAR(2));
    
      INSERT INTO T1 VALUES('A','AA');
      INSERT INTO T1 VALUES('A','AA');
      INSERT INTO T1 VALUES('AA','A');
    
    
    Step 2. Select from Table T1 using the RANK and DENSE_RANK
    OLAP functions.
    
      SELECT C_CHA AS OBY,
             C_VARC||C_CHA AS PBY,
             RANK() OVER(
                         PARTITION BY C_VARC||C_CHA
                         ORDER BY C_CHA
                         ) AS RANK1,
             DENSE_RANK() OVER(
                         PARTITION BY C_VARC||C_CHA
                         ORDER BY C_CHA
                         ) AS DENSE_RANK1
      FROM T1;
    
    
    Step 3. Check the results.
    
    The following incorrect result is returned.  The 3rd row is not
    as expected.  The RANK1 and DENSE_RANK1 values for the 3rd row
    should be 3 and 2.
    
      +--------------------------------------------------
      | OBY | PBY  | RANK1          | DENSE_RANK1       |
      +--------------------------------------------------
    1_| A   | AAA  |              1 |                 1 |
    2_| A   | AAA  |              1 |                 1 |
    3_| AA  | AAA  |              1 |                 1 |
      +--------------------------------------------------
    
    This problem occurs because DB2 doesn't handle the padding
    character properly for the input argument to the RANK and
    DENSE_RANK window functions.
    

Problem conclusion

  • DB2 code is modified to use correct padding character for the
    argument for RANK and DENSE_RANK window functions.
    
    Additional Keywords: SQLINCORR SQLINCORROUT INCORROUT
                         DB2INCORR/K SQLRANK SQLDENSERANK
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PM88703

  • 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

    2013-05-09

  • Closed date

    2013-06-05

  • Last modified date

    2013-07-17

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

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

    UK94861

Modules/Macros

  • DSNXRWND
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK94861

       UP13/06/20 P F306 ½

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

PM88703

Modified date:

2013-07-17

Translate my page

Machine Translation

Content navigation