DB2 FOR LUW / DB2FORLUW / A50 - DB2

A query may return wrong results under certain condition. The 
characteristics of the affected query are: 
a) it will contain a FETCH FIRST n ROWS clause 
b) the sub-select or full-select containing the FETCH FIRST n 
ROWS clause from (a) will contain a nested sub-select or EXISTS 
predicate or IN subquery predicate. 
c) The nested sub-select from (b) will contain an 
outer-reference to another input to the sub-select or 
full-select from (a) 
 
An example query could be like the following: 
 
SELECT DISTINCT a.c1 
FROM c 
       FULL JOIN 
       a 
       ON a.c1 = c.c1 
WHERE  a.c1 IS NOT NULL 
AND    EXISTS ( SELECT b.c1 
                FROM   b 
                WHERE  b.c1 = c.c1 
                FETCH FIRST 1 ROWS ONLY) 
ORDER BY a.c1 DESC 
 
 
After gathering the db2exfmt output for potential queries that 
may be impacted, it will typically contain a SORT operator that 
reduces the stream cardinality to the value specified in the 
FETCH FIRST n ROW clause and a NLJOIN operator without 
predicates above the SORT, one of whose inputs is the sub-select 
containing the outer-reference.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All platforms                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Please see Error Description                                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 fix pack 4.                      * 
****************************************************************

Rewrite the query to eliminate the outer-reference in the 
sub-select.

DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

The fix will be included in DB2 Version 10.5 fix pack 4.

Rewrite the query to eliminate the outer-reference in the 
sub-select.

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

25.11.2013
08.09.2014
08.09.2014