IT20438: INCORRECT RESULT OR SQL0811N ARE POSSIBLE WHEN SQL CONTAINS SCALAR NOT EXISTS SUBQUERY

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
Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• 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
  

Local fix

• 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
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 2 Fix Pack 2 iFix001 or higher       *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 11.1 Mod 2 Fix Pack 2 iFix001
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  IT20436

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels