IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    IT20438

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-05-03

  • Closed date

    2017-10-11

  • Last modified date

    2017-10-11

  • 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

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020