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. |
