DB2 FOR LUW / DB2FORLUW / A50 - DB2

Wrong results might be observed when a scalar sub-query is on 
the left hand side of a NOT IN predicate. 
This can happen using db2 version 10.5 and later when: 
 
   1. a query statement contains a NOT IN predicate; 
   2. the left hand side of the NOT IN predicate is a scalar 
sub-query; 
   3. the evaluation of the NOT IN predicate returns an empty 
result set 
 
   The following use case can reproduce this problem: 
   create table t(c1 int); 
   insert into t values 1; 
   create table ssq(c2 int not null); 
   insert into ssq values 2; 
   create table sq(c3 int not null); 
   insert into sq values 2; 
   select c1 from t where (select c2 from ssq) not in (select c3 
from sq); 
   C1 
   ----------- 
             1 
 
     1 record(s) selected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Db2 10.5 Fix Pack 9 or higher                     * 
****************************************************************

Please, rewrite the query

First fixed in Db2 10.5 Fix Pack 9

not known / see Local fix

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

15.05.2017
29.09.2017
12.10.2017

9.0.