PM26751: INCORRECT OUTPUT CAN BE RETURNED FOR VARIABLE LENGTH FIELDS IF THEY ARE USED AS PART OF MIN/MAX ALONG WITH MULTIPLE DISTINCTS

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Possible incorrect output can be returned for variable length
    fields if they are used as part of a MIN/MAX function, and
    multiple DISTINCTs exist in the query.
    
    For example:
    CREATE TABLE TBL1
       (C1       INT,
        C2       INT,
        C3       VARCHAR(5));
    COMMIT;
    INSERT INTO TBL1 VALUES (1, 1, 'ABCD');
    INSERT INTO TBL1 VALUES (2, 2, 'AB');
    COMMIT;
    SELECT MIN(C3), COUNT(DISTINCT C1), COUNT(DISTINCT C2) FROM
    TBL1;
    This returned ABCD, 2, 2 which is incorrect.  It should be
    AB, 2, 2.
    DB2INCORR/K
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS v8, DB2 9 for z/OS, and DB2 *
    *                 10 for z/OS users of queries that contain    *
    *                 multiple DISTINCTs.                          *
    ****************************************************************
    * PROBLEM DESCRIPTION: An incorrect result set can be returned *
    *                      for a query that includes variable      *
    *                      length fields if they are used as part  *
    *                      of a MIN/MAX function when the query    *
    *                      contains multiple DISTINCTs.            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    An incorrect result set can be returned for a query that uses
    variable length fields in a MIN/MAX function and contains
    multiple DISTINCTs.
    
    The following example presents a failing case.
    
    
    Step 1. Create a simple Table with a VARCHAR column.
    
      CREATE TABLE TBL1 (C1 INT, C2 INT, C3 VARCHAR(5));
    
    
    Step 2. Insert two rows of data into the Table.
    
      INSERT INTO TBL1 VALUES (1, 1, 'ABCD');
      INSERT INTO TBL1 VALUES (2, 2, 'AB');
    
    
    Step 3. Execute the following multiple DISTINCTs query.
    
      SELECT MIN(C3), COUNT(DISTINCT C1), COUNT(DISTINCT C2)
      FROM TBL1
    
    
    Step 4. Check the result.
    
    The above query returns 'ABCD', 2, 2 which is an incorrect
    result.  The correct result should be 'AB', 2, 2.
    

Problem conclusion

  • DB2 has been modified to correct the MIN/MAX processing for
    variable length fields when they are part of a query that
    contains multiple distincts.
    
    Additional Keywords: SQLDISTINCT SQLINCORR SQLMAX SQLMIN
                         SQLVARCHAR
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PM26751

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    810

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-11-15

  • Closed date

    2010-12-15

  • Last modified date

    2011-05-13

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

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

    UK63145 UK63146 UK63147

Modules/Macros

  •    DSNXSMRD DSNXSORM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK63145

       UP10/12/30 P F012

  • R810 PSY UK63146

       UP10/12/30 P F012

  • R910 PSY UK63147

       UP10/12/30 P F012

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:

810

Reference #:

PM26751

Modified date:

2011-05-13

Translate my page

Machine Translation

Content navigation