DB2 - Problem description
Problem IT28945 | Status: Closed |
DB2 MAY RETURN INCORRECT RESULT WHEN EXECUTING QUERY WITH UNION VIEW INCLUDE MULTIPLE TABLES | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
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. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 10.5 Fix Pack 11 or higher * **************************************************************** | |
Local Fix: | |
Rewrite the Query as per example in the Error Description. | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : follow-up : IT29099 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 26.04.2019 25.02.2020 25.02.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |