IT17385: INCORRECT QUERY RESULTS WHEN USING OFFSET-CLAUSE AND/OR FETCH-FIRST-CLAUSE

DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• 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.
  

Local fix

• 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
  

Problem summary

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

Problem conclusion

• First fixed in DB2 10.1 Fix Pack 6
  

Temporary fix

Comments

APAR Information

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

  IT16918

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

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN UP

     FIX