PM53062: INCORROUT ON QUERY WITH COUNT DISTINCT AND EQUAL PREDICATE AND INDEX ACCESS PLAN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• COUNT(DISTINCT colname) may return an incorrect value when the
  query contains equal predicates on the columns of the selected
  index.
  
  Additional Keywords: SQLINCORR SQLINCORROUT DB2INCORR/K
                       SQLDISTINCT SQLCOUNT
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS users whose queries have    *
  *                 GROUP BY clause and a set function with      *
  *                 DISTINCT.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect output may be returned by a   *
  *                      query with GROUP BY clause and a set    *
  *                      function like COUNT function with       *
  *                      DISTINCT.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Incorrect output may be returned by a query with GROUP BY
  clause and a set function like COUNT function with DISTINCT.
  
  The problem may occur when the following conditions are
  satisfied:
  1. The GROUP BY columns are the prefix of an index;
  2. All the columns in the index except the GROUP BY columns are
     covered by equal predicates;
  3. The DISTINCT column is not in the index.
  
  For example,
  
  SELECT C1
       , COUNT(DISTINCT C4)
  FROM T1
  WHERE C2 = ?
  AND C3 = ?
  GROUP BY C1;
  
  If there is an index created on T1(C1, C2, C3) and the index is
  selected in the access path for the above query, the optimizer
  may improperly choose to avoid sort for the set function with
  DISTINCT. Consequently, incorrect output may be returned.
  
  Additional Keywords:
  SQLINCORR SQLINCORROUT DB2INCORR/K SQLGROUPBY SQLDISTINCT
  SQLSETFUNCTION
  

Problem conclusion

• Code has been modified to perform sort for the set function
  with DISTINCT.
  

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:

  UK75592 UK75593

Modules/Macros

• DSNXOPRP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK75592

     UP12/02/07 P F202

• R910 PSY UK75593

     UP12/02/07 P F202

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.