DB2 - Problembeschreibung
Problem IC81476 | Status: Geschlossen |
FED: WRONG RESULT RETURNED BY FEDERATION SERVER | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
If there is an exists subquery in the head expression, this subquery will be pushed down and the original sql statement will be converted to a join sql statement. For example, in the following sql statement, nick1 and nick2 are nicknames and there is an exists subquery in the head expression: select ((case when exists(select * from nick1 where c2='aaa') then 'TRUE' else 'FALSE' end)) from nick2; DB2 compiler will rewrite it to: Optimized Statement: ------------------- SELECT CASE WHEN (1 = Q3.$C0) THEN 'TRUE' ELSE 'FALSE' END FROM (SELECT DISTINCT Q2.$C0 FROM (SELECT 1 FROM IIDEV22.NICK1 AS Q1 WHERE (Q1.C2 = 'aaa ')) AS Q2) AS Q3, IIDEV22.NICK2 AS Q4 = In this scenario, federation pda(pushdown analysis) component will push down all the subquery to remote data source which cause the wrong result. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * all platforms * **************************************************************** * PROBLEM DESCRIPTION: * * If there is an exists subquery in the head expression, this * * subquery will be pushed down and the original sql statement * * will * * be converted to a join sql statement. * * * * For example, in the following sql statement, nick1 and nick2 * * are * * nicknames and there is an exists subquery in the head * * expression: * * select ((case when exists(select * from nick1 where * * c2='aaa') * * then 'TRUE' else 'FALSE' end)) from nick2; * * * * DB2 compiler will rewrite it to: * * Optimized Statement: * * ------------------- * * SELECT * * CASE * * WHEN (1 = Q3.$C0) * * THEN 'TRUE' * * ELSE 'FALSE' END * * FROM * * (SELECT DISTINCT Q2.$C0 * * FROM * * (SELECT 1 * * FROM IIDEV22.NICK1 AS Q1 * * WHERE (Q1.C2 = 'aaa ')) AS Q2) AS Q3, * * IIDEV22.NICK2 * * AS Q4 = * * * * In this scenario, federation pda(pushdown analysis) * * component * * will push down all the subquery to remote data source which * * cause the wrong result. * **************************************************************** * RECOMMENDATION: * * upgrade to v95fp10 * **************************************************************** | |
Local-Fix: | |
Change the exists subquery to "count(*) > 0" clause | |
Lösung | |
upgrade to v95fp10 | |
Workaround | |
NA | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 16.02.2012 13.03.2012 13.03.2012 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP10 | |
Problem behoben lt. FixList in der Version | |
9.5.0.10 |