DB2 - Problem description
Problem IT34774 | Status: Closed |
For special queries DISTINCT is pulled up above the UNION level in the optimized statement, what may cause wrong results | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
Incorrect results may be observed for queries with following characteristic: - query with UNION - UNION is joined to SELECT DISTINCT with predicate - one leg of union is a join, rows in joined tables are not unique In optimized statement DISTINCT is pulled up above the UNION level, what may cause wrong results. Sample query: select * from (SELECT inf.T1C10 FROM T1 inf UNION ALL ( SELECT A.T2C1 FROM T2 a INNER JOIN T3 R ON A.T2C1 = r.T3C1 WHERE ( a.T2C4 IS NOT NULL ) ) ) MAIN INNER JOIN (SELECT DISTINCT T4C2,T4C3 FROM t4 WHERE T4C1 IN( 'A1', 'A2')) acc ON (1=1) ; Optimized Statement: ------------------- SELECT DISTINCT Q10.$C0 AS "T1C10", Q3.T4C2 AS "T4C2", Q3.T4C3 AS "T4C3" FROM REPRO.T4 AS Q3, (SELECT Q9.$C0 FROM (SELECT Q5.T2C1 FROM REPRO.T3 AS Q4, REPRO.T2 AS Q5 WHERE (Q5.T2C1 = Q4.T3C1) AND Q5.T2C4 IS NOT NULL UNION ALL SELECT Q7.T1C10 FROM REPRO.T1 AS Q7 ) AS Q9 ) AS Q10 WHERE Q3.T4C1 IN ('A1', 'A2') As a workaround DISTINCT can be rewritten to GROUP BY. select * from (SELECT inf.T1C10 FROM T1 inf UNION ALL ( SELECT A.T2C1 FROM T2 a INNER JOIN T3 R ON A.T2C1 = r.T3C1 WHERE ( a.T2C4 IS NOT NULL ) ) ) MAIN INNER JOIN (SELECT T4C2,T4C3 FROM t4 WHERE T4C1 IN( 'A1', 'A2') GROUP BY T4C2,T4C3 ) acc ON (1=1) ; | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 11.1 Fix Pack m4fp6 or later * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 11.1 Fix Pack m4fp6 or later * **************************************************************** | |
Comment | |
Problem was first fixed in DB2 Version 11.1 Fix Pack m4fp6 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 04.11.2020 15.03.2021 29.03.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |