IBM Support

IC78343: QUERY WITH CORRELATION PREDICATE AND IN/EXISTS PREDICATE MAY RETURN INCORRECT RESULT WHILE IT IS MATCHING TO MQT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A query may return incorrect result if all the following
    conditions are satisfied:
    
    1. Query has a SELECT subquery "SELECT <scalar_subquery> FROM
    <base_table_t>"
    2. <scalar_subquery> has a correlation predicate
    3. <scalar_subquery> also has an IN subquery predicate, or an
    EXISTS predicate. That predicate can be an OR predicate subterm.
    4. There exists an MQT <mqt_over_base_table_t> whose definition
    is "select <select_output_list> from <base_table_t> where
    <predicate>".
    
    For example,
    
    -- Base table
    create table t1(a int, b int, c int);
    create table t2(x int);
    
    -- MQT
    create table mqt1 as (select * from t1) data initially deferred
    refresh deferred;
    refresh table mqt1;
    
    -- Query
    SELECT (SELECT 1 FROM t1 AA WHERE AA.a = A.a AND (AA.b = 1 OR
    AA.c IN (SELECT x FROM t2)))
    FROM t1 A;
    
    the mapping of condition variables is:
    
    <scalar_subquery> = "(SELECT 1 FROM t1 AA WHERE AA.a = A.a AND
    (AA.b = 1 OR AA.c IN (SELECT x FROM t2)))"
    correlation predicate = "AA.a = A.a"
    IN subquery predicate = "AA.c IN (SELECT x FROM t2))"
    <base_table_t> = "t1"
    <mqt_over_base_table_t> = "mqt1"
    
    To verify if the problem is hit, generate db2exfmt plan file for
    the query. In that file, go to"Optimized Statement" section, and
    locate "= ANY (SELECT DISTINCT ". Check how many columns does
    "SELECT DISTINCT" include. If it has three columns, that is good
    and nothing else needs to be done. Otherwise, if it includes
    only one column, the problem might be hit. Now change query
    optimization level to 0 by issuing "db2 set current query
    optimization 0", and regenerate the db2exfmt plan against the
    same query, and check if "SELECT DISTINCT" contains three
    columns this time. If it does, then this problem is hit.
    

Local fix

  • MQT matching should be blocked for that query. It can be done by
    any of following change.
    (1) db2 drop table <mqt_over_base_table_t>.
    (2) db2 set integrity for <mqt_over_base_table_t> off.
    (3) reduce optimization level to 0 or 1.
    (4) Append "WITH Rs" or "WITH RR" to query statement, assuming
    MQT is created at default isolation level CS.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * QUERY WITH CORRELATION PREDICATE AND IN/EXISTS PREDICATE MAY *
    * RETURN INCORRECT RESULT WHILE IT IS MATCHING TO MQT          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 LUW Version 9.7 Fix Pack 5                    *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 Fix Pack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC78343

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-08-26

  • Closed date

    2011-12-15

  • Last modified date

    2011-12-15

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

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

    IC78345

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC78343

Modified date: 15 December 2011