|
Problem IC81476
|
Status: Closed |
FED: WRONG RESULT RETURNED BY FEDERATION SERVER |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| 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. |
| Problem Summary: |
****************************************************************
* 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 |
| Solution |
upgrade to v95fp10 |
| Workaround |
NA |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.02.2012
13.03.2012
13.03.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP10
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.10
|
 |
