PH13981: INCORROUT - duplicate rows returned on Query when multiple tables joined

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• CTE query produces incorrect output - returns duplicate rows
  .
  A given Query consists of several CTE queries and nested table
   expressions as well.
  It does return duplicate rows, which is incorrect.
  .
  The problem is due to that Access Path Selection missed sort of
   composite table when access fourth table by sort merge join,
   when it has join predicate with second table, and second table
   is joined by nested loop join with sort composite.
  .
  The access plan was not built correctly when calculating one of
   the nested table expressions.
  The problem is that when joining table TABLE_four, Sort Merge
   Join has been used ( Method = 2 ).
  TABLE_four itself has been sorted ( SORTN_JOIN = 'Y') on the
   join keys (2 columns).
  However, its outer table part is not sorted (SORTC_UNIQ,
   SORTC_JOIN, SORTC_ORDERBY and SORTC_GROUPBY are all equals
   to 'N' ), and the join keys passed into TABLE_four are not
   completely ordered in both Join keys used.
  .
  Keywords:
  SQLINCORR SQLINCORROUT INCORROUT
  MSJ SMJ  SQLMSJ  SQLSMJ
  SQLTABLEEXPR SQLCTE
  

Local fix

• In this particular Case, replacing the first CTE by a View did
  circumvent the problem.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Db2 11 and Db2 12 for z/OS users who have a  *
  *                 query including multiple tables joined       *
  *                 together, and there are two or more join     *
  *                 predicates on one table.                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: Db2 may miss adding a sort composite    *
  *                      when sort merge join is used to         *
  *                      access a table, which may result in     *
  *                      incorrect output.                       *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available.         *
  ****************************************************************
  If a query includes multiple tables joined, and there are two
  or more join predicates on one table, Db2 may miss adding a sort
  composite when accessing the table by sort merge join if the
  second table is joined by nested loop join with sort composite.
  It may result in incorrect output.
  
  Example:
  SELECT * FROM T1, T2, T3
  WHERE T1.C1 = T2.C1
  AND T1.C1 = T3.C1
  AND T2.C2 = T3.C2;
  
  There is an index IX1(C1, C2) on T2, and there are two join
  predicates on T3. One join predicate references the second key
  of IX1. Db2 may select access path as T1 join T2 by nested loop
  join with sort composite, and IX1 is used on T2 with one
  matching column. When Db2 accesses T3 by sort merge join after
  T1, T2 are joined, Db2 may miss adding a sort composite after
  building the two sort keys as T3.C1, T3.C2. The result set
  after T1, T2 are joined is just driven by the order of C1.
  Db2 may get incorrect output for this query.
  
  Also, PH12014 is retrofitted from V12 to V11 in this APAR.
  
  Additional keywords:
  SQLACCESSPATH SQLPERFORMANCE SQLNLJ NLJ SQLSMJ SQLMSJ SMJ
  MSJ SQLINCORR INCORROUT SQLINCORROUT DB2INCORR/K
  

Problem conclusion

• Code is updated to get correct output for the above situation.
  

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:

  UI65680 UI65682

Modules/Macros

•    DSNXOCSK DSNXOCSM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI65682

     UP19/10/21 P F910 Ž

• RC10 PSY UI65680

     UP19/10/21 P F910 Ž

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.