DB2 FOR LUW / DB2FORLUW / A50 - DB2

Db2 may return incorrect result when executing query with union
view include multiple tables.

For example, the query reference a view V1.

SELECT C1,
       C2,
       ...
FROM   V1
WHERE  C1 = constant
AND    C2 = constant
;

V1 looks like following, with Q1 including multiple subqueries
and UNIONs which does OUTER JOIN with other tables including T3
on column C1.

CREATE VIEW V1
AS
    SELECT
            Q1.C1,
            Q1.C2
            ...
    FROM (
            SELECT
                    T1.C3,
                    T1.C4,
                    ...
                    T2.C1,
                    T2.C2
                    ...
            FROM    T1
                    LEFT OUTER JOIN T2 ON (T1.C5 = T2.T5)
            WHERE
                    ...
            UNION ALL
            SELECT
                    T1.C3,
                    T1.C4,
                    ...
                    T2.C1,
                    T2.C2
                    ...
            FROM    T1
                    LEFT OUTER JOIN T2 ON (T1.C5 = T2.T5)
            WHERE
                    ...

            UNION ALL
            SELECT
                    T1.C3,
                    T1.C4,
                    ...
                    T2.C1,
                    T2.C2
                    ...
            FROM    T1
                    LEFT OUTER JOIN T2 ON (T1.C5 = T2.T5)
            WHERE
                    ...
        ) AS Q1
    LEFT OUTER JOIN T3
        ON (Q1.C1 = T3.C1 AND ...)
    ...
    LEFT OUTER JOIN T4
    ...

Because there's an equal predicate on column C1, Db2 Rewrite
pushes down a local predicate 'Q1.C1 = constant' under T3,
which makes 'T3.C1 = constant'. During pushing down this
predicate with other join predicates under the UNION,
the predicate 'T3.C1 = constant' is removed in error and causing
a wrong result.

The workaround is to explicitly reference T3.C1 instead of Q1.C1
as below:

CREATE VIEW V1
AS
    SELECT
            T3.C1,
            Q1.C2
...

with this rewrite the predicate will not be removed.

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 10.5 Fix Pack 11 or higher                    *
****************************************************************

Rewrite the Query as per example in the Error Description.

not known / see Local fix

forerunner  : 
follow-up : IT29099 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

26.04.2019
25.02.2020
25.02.2020