PI64779: SELECT DISTINCT RETURNS DUPLICATE VALUES.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output can occur when the DISTINCT keyword is used,
  matching index access is used on the IN-list, but the IN-list
  column is not in the select list and the statement is processed
  as a nested loop join.  The DISTINCT, in this case, is not
  removing the duplicate rows.
  
  Example SQL:
      SELECT DISTINCT  "B"."C1"
  FROM "T2" AS A
       , "T1" AS B
  WHERE ( "A"."C4" = 2
          AND "B"."C1" =  "A"."C1"
          AND "B"."C2" =  "A"."C2"
          AND "B"."C3" IN ( 1, 2, 3 )
        );
  
  Additional Keywords:
  INCORROUT DB2INCORR/K SQLINCORR SQLINCORROUT SQLDISTINCT
  SQLIN SQLNLJ NLJ
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 11 for z/OS users of queries which       *
  *                 contain more than one table and also INLIST  *
  *                 or IN subquery.                              *
  ****************************************************************
  * PROBLEM DESCRIPTION: The DB2 Optimizer may return incorrect  *
  *                      output when the following conditions    *
  *                      are met:                                *
  *                                                              *
  *                      1. The query contains more than one     *
  *                         table;                               *
  *                      2. There is an INLIST or IN subquery;   *
  *                      3. The query has DISTINCT or GROUP BY;  *
  *                      4. There is an index which can avoid    *
  *                         sort for the DISTINCT or GROUP BY.   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 may return incorrect output when a query with more than
  one table contains an INLIST or IN subquery. The query
  also has DISTINCT or GROUP BY, and there is an index which can
  avoid sort for the DISTINCT or GROUP BY.
  
  
  For example,
  
  SELECT DISTINCT  B.C1
  FROM T1 AS A, T2 AS B
  WHERE A.C4 = 2
    AND B.C1 = A.C1
    AND B.C2 = A.C2
    AND B.C3 IN (1,2,3);
  
  There are an indexes defined on T2 - IX1 on (C1, C2, C3).
  T2 is the inner table for nested loop join, and there is also
  an INLIST predicate B.C3 IN (1,2,3) on table T2. The index
  IX1 can avoid sort for the DISTINCT function. DB2 may
  return duplicate values for the DISTINCT function.
  
  
  Additional Keywords:
  SQLPERFORMANCE  SQLACCESSPATH  SQLDISTINCT  SQLGROUPBY
  SQLINCORR SQLINCORROUT INCORROUT DB2INCORR/K
  

Problem conclusion

• DB2 has been changed not to return incorrect output when a query
  with more than one table contains an INLIST or IN subquery.
  There is an index which can avoid sort for the DISTINCT or GROUP
  BY in the query.
  

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:

  UI40645

Modules/Macros

• DSNXOCSC DSNXOPTH
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI40645

     UP16/09/21 P F609 Ž

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.