DB2 FOR LUW / DB2FORLUW / A10 - DB2

A query that specifies a fetch-first-clause may return wrong 
results if there is a predicate in the same subselect.  For 
example: 
 
  SELECT ID, NAME 
  FROM TABLE1 
  WHERE <predicate> 
  ORDER BY ID 
  OFFSET ? ROWS 
  FETCH NEXT ? ROWS ONLY 
 
In some circumstances <predicate> may be applied after the 
offset-clause and fetch-first-clause are processed, which 
results in fewer rows than expected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 10.1 Fix Pack 6                               * 
****************************************************************

Move the predicate into a nested subselect, e.g.: 
 
  SELECT ID, NAME 
  FROM (SELECT ID, NAME FROM TABLE1 WHERE <predicate>) 
  ORDER BY ID 
  OFFSET ? ROWS 
  FETCH NEXT ? ROWS ONLY

First fixed in DB2 10.1 Fix Pack 6

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

06.10.2016
02.03.2017
02.03.2017