IBM Support

IT13665: QUERIES CONTAINING BOTH AN IN (SUBQUERY) AND NOT IN (SUBQUERY) MAY EXPERIENCE INCORRECT RESULTS.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Certain queries may experience incorrect results under the
    following conditions
    - The SELECT list contains a scalar expression derived from the
    columns of a single table.
    - The FROM clause references at least two tables.
    - The primary key of at least one of the tables referenced is
    greater then 16 bytes wide.
    - The WHERE clause contains either an IN subquery or a
    correlated EXISTS subquery predicate.
    - The WHERE clause contains a NOT IN subquery or a correlated
    NOT EXISTS subquery predicate.
    
    As an example a query of the following form may be susceptible
    to the problem
    
    SELECT
         LEFT(datelist,4)
      FROM a
      JOIN b
        ON a1                = b1
       AND a2                = b2
       AND a3                = b3
       AND a4                = b4
     WHERE LEFT(DATELIST, 4) = '2014'
       AND DATELIST BETWEEN '20140101' AND '20151031'
       AND bk in (
        SELECT c1
          FROM c
           )
       AND bk not in (
        SELECT f1
          FROM f
         WHERE f2 between 1 and 2000
           )
    

Local fix

  • - Use a primary key index that is less than or equal to 16 bytes
    Or
    - (FP7 only) db2set -im DB2COMPOPT=NO_SELPJ4DIST
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 LUW v10.5 fixpak 8.                           *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 LUW v10.5 fixpak 8.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT13665

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-02-08

  • Closed date

    2016-11-18

  • Last modified date

    2016-12-04

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

  • 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

  • RA50 PSN

       UP

  • RA50 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 10.5

Reference #: IT13665

Modified date: 04 December 2016