DB2 FOR LUW / DB2FORLUW / A50 - DB2

Users might encounter incorrect result or SQL0811N error when 
the following conditions are met: 
 
1. There is a scalar subquery. 
2. There is a NOT EXISTS subquery. 
 
3. There are at least two equality predicates in the form of 
<col1> = <constant>, and <col2> = <constant> whereas the two 
predicates has the same constant value but the data types of 
<col1> and <col2> are different yet compatible (i.e., one data 
type can be promoted to the other data type.) 
4. There is an equality predicate in a scalar subquery in which 
the equality predicate contains a correlated column from the 
query block outside of the scalar subquery. In addition, the 
correlated column must be either <col1> or <col2> in condition 
3. 
 
An example query is as following: 
SELECT ... 
FROM   A, B, C 
WHERE  A.CHAR5COL = '10000'   /* condition 3 */ 
AND    B.CHAR10COL = '10000'   /* condition 3 - A.CHAR5COL and 
B.CHAR10COL have different but compatible types */ 
AND    C.C1 = (SELECT D.D1 
               FROM   D 
               WHERE  D.CHAR10COL = A.CHAR5COL) /* conditions 1 
and 4 */ 
AND    NOT EXISTS (SELECT 1 
                   FROM   E 
                   WHERE  E.E2 = A.A2) /* condition 2 */ 
AND    A.A3 = C.C3

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

The work around of this problem is to replace the right hand 
side of the scalar sub-query predicate with the constant it 
correlates to. The example query above will look like this with 
the work around: 
SELECT ... 
FROM   A, B, C 
WHERE  A.CHAR5COL = '10000' 
AND    B.CHAR10COL = '10000' 
AND    C.C1 = (SELECT D.D1 
               FROM   D 
               WHERE  D.CHAR10COL = '10000') /* replace 
A.CHAR5COL with the constant - '10000' */ 
AND    NOT EXISTS (SELECT 1 
                   FROM   E 
                   WHERE  E.E2 = A.A2) 
AND    A.A3 = C.C3

First fixed in Db2 10.5 Fix Pack 9

not known / see Local fix

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

03.05.2017
29.09.2017
12.10.2017

9.0.