DB2 FOR LUW / DB2FORLUW / B10 - 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 11.1 Mod 2 Fix Pack 2 iFix001 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

Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

First fixed in Db2 11.1 Mod 2 Fix Pack 2 iFix001

not known / see Local fix

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

03.05.2017
11.10.2017
11.10.2017