IBM Support

IT01197: QUERY MIGHT HAVE OR PREDICATE WRONGLY REMOVED RESULTING IN EXTRA ROWS IN THE RESULTS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A query might return additional rows when it has an OR predicate
    that meets the following conditions:
    - The query has an OR predicate, called orPrd, that is either
    present in a table inner join query block or simply applied to a
    single table scan.
    - The orPrd has an IN predicate, called inPrd.
    - The inPrd predicate has pattern of <simple_base_columnX> IN
    (<column_or_literal1>, <column_or_literal2>, ...), where one of
    the <column_or_literal> is <simple_base_columnX> itself. An
    example predicate is "col1 in (constant1, constant2, col1,
    col2)", where col1 is not only IN predicate column but also an
    INlist element.
    - The inPrd column,  <simple_base_columnX> as above for example,
    is defined as nullable. There is table rows whose columnX is the
    NULL value.
    - The query has no predicate other than orPrd that can reject
    the columnX Null value. For example, if query has relational
    comparison predicate such as columnX = columnY, columnX NULL
    values will be filtered out.
    
    To verify if the query hits this problem, use the db2exfmt or
    db2explin commands to generate db2exfmt a query explain plan.
    Search the query explain plan to verify if the original query's
    OR predicate is present in the section "Optimized Statement".
    
    This problem might cause the orPrd predicate to be incorrectly
    removed. This might result in the columX NULL value being
    incorrectly returned to the consumer query block. This error
    might make the final result inaccurate.
    

Local fix

  • There are two workarounds for this issue:
    - Reduce query optimization to 0 or 1. A sample command is "db2
    set current query optimization 0".
    - Manually rewrite that OR predicate. Change it from
    "<simple_base_columnX> IN (<column_or_literal1>,
    <column_or_literal2>, ...)" to "<simple_base_columnX> IS NOT
    NULL".
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 10.1.0.4.                             *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 10.1.0.4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT01197

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-04-22

  • Closed date

    2014-05-08

  • Last modified date

    2014-05-26

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1"}]

Document Information

Modified date:
24 September 2021