PK79632: FETCH FIRST N ROWS ONLY WHEN USED WITHIN A SUBSELECT PRODUCES AN INCORROUT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When using a query with a subquery and a FETCH FIRST n ROW ONLY
  and incorrout will occur.  Example query is as follows:
  
  SELECT * FROM DSN8810.EMP WHERE (WORKDEPT) IN
  ( SELECT WORKDEPT FROM DSN8810.EMP GROUP BY WORKDEPT ORDER BY
  WORKDEPT
  FETCH FIRST 2 ROWS ONLY);
   DB2INCORR/K
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS users of subqueries       *
  *                 containing fetch-first-clause with Group By. *
  ****************************************************************
  * PROBLEM DESCRIPTION: An incorrect result set (fewer rows     *
  *                      than expected) can be returned for a    *
  *                      query that uses the fetch-first-clause  *
  *                      in a subquery containing a GROUP BY.    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 can return an incorrect result set (fewer rows than
  expected) for a query that uses the fetch-first-clause and a
  GROUP BY clause in a subselect.
  
  In the following example, the IN subquery is expected to
  return the first two  WORKDEPT  values, i.e. 'A00' and 'B01'.
  However, only 'A00' gets returned as the FETCH FIRST rows
  get counted before the grouping operation. So, the end result
  is missing a group.
  
  Given the following example query.
  
    SELECT EMPNO,WORKDEPT
    FROM DSN8910.EMP
    WHERE (WORKDEPT) IN
                (SELECT WORKDEPT
                 FROM DSN8910.EMP
                 GROUP BY WORKDEPT
                 FETCH FIRST 2 ROWS ONLY);
  
  Five rows are returned but this is incorrect.  The group of
  values for WORKDEPT 'B01' are missing.
  
    EMPNO  WORKDEPT
    ------ --------
    000010 A00
    000110 A00
    000120 A00
    200010 A00
    200120 A00
  
  
  Instead, the following 6 rows are expected.
  
    EMPNO  WORKDEPT
    ------ --------
    000010 A00
    000020 B01
    000110 A00
    000120 A00
    200010 A00
    200120 A00
  
  Note the presence of group 'B01' for WORKDEPT.
  

Problem conclusion

• The code in DB2 has been modified to process the Fetch First
  clause after other subselect clauses such as Group By, Having,
  and Order By have been processed.  This will allow the correct
  result set to be returned.
  
  Additional Keywords: SQLINSUBQ SQLSUBQUERY SQLGROUPBY FETCHFIRST
  

Temporary fix

Comments

APAR Information

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

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

  UK44930

Modules/Macros

• DSNXREXI DSNXRGBJ DSNXRGRP DSNXRRPJ DSNXRSGB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK44930

     UP09/04/01 P F903

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.