DB2 FOR LUW / DB2FORLUW / A50 - DB2

DB2 might return incorrect results if the following conditions 
are true: 
1) The query consists of one or more NOT EXISTS or NOT IN 
subqueries. 
2) The parent side table of the subquery contains a predicate 
that uses the NODENUMBER function. 
 
The following is an example of a query that might meet these 
conditions: 
 
select count(*) 
from t1 
where not exists (select 1 from t2 where t1.a=t2.a) 
and nodenumber(t1.a)=1;

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Cancun Release 10.5.0.4                       * 
****************************************************************

There are two workarounds to this problem: 
 
1) Manually rewrite the query so that it does not contain a NOT 
EXISTS or NOT IN subquery. The example above may be rewritten 
as: 
 
select count(*) from t1 
left join t2 on t1.a=t2.a 
where t2.a is null 
and nodenumber(t1.a)=1; 
 
2) Execute the following, recycle the DB2 instance, and then 
rerun the query: 
db2set DB2_ANTIJOIN=NO

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
 

Fixed in DB2 Cancun Release 10.5.0.4

not known / see Local fix

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

12.03.2014
10.11.2014
07.04.2015