IBM Support

IT19976: SQL QUERIES WITH IN OR NOT IN CLAUSE MAY PRODUCE INCORRECT RESULTS FOR A COLUMN-ORGANIZED TABLE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Additional space is added in rewritten query to string predicate
    when we use "NOT IN" clause,  thus we get wrong results from
    query.
    This only happens when we have table organized by columns
    
    Example:
         create table t(c varchar(5)) organize by column;
         insert into t values('x');
    
         select substr(c,1) from t where substr(c,1) not in
    (CHAR('abc'),CHAR('x'));
    
         1
         -----
         x
    
           1 record(s) selected. ==>incorrect result
    

Local fix

  • Rewrite the query to avoid this condition.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 105fp9 and higher version.                    *
    ****************************************************************
    

Problem conclusion

  • Upgrade to DB2 105fp9 and higher version.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT19976

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-03-31

  • Closed date

    2017-10-09

  • Last modified date

    2017-10-12

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

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

    IT21060

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