Informix - Problem description
Problem IT15179 | Status: Closed |
'WHERE EXISTS (SUBQUERY)' FILTER POTENTIALLY INCREASING RESULT S ET BY PRODUCING DUPLICATE ROWS IF SUBQUERY RETURNS MORE THAN ONE | |
product: | |
INFORMIX SERVER / 5725A3900 / B70 - IDS 11.70 | |
Problem description: | |
A query of following form might return more rows with an additional EXISTS condition than without if the correlated subquery returns more than one row for certain correlations: SELECT FROM A, B WHERE A.join_col = B.join_col AND AND EXISTS (SELECT C.col_X from C WHERE C.join_col = A.join_col AND ); This even would be true when adding UNIQUE to the subquery. A '(Skip Duplicate)' seems to be of no effect. The result set would be the same as for SELECT FROM A, B, C WHERE A.join_col = B.join_col AND A.join_col = C.join_col AND AND ; | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of 12.10.xC1 through 12.10.xC6. * **************************************************************** * PROBLEM DESCRIPTION: * * A query with correlated subquery may produce incorrect * * resultS under the following conditions: * * - query contains an EXISTS or '= ANY' corrected subquery * * - subquery has a single table, with an index on the join * * column of the subquery table (joining with a main query * * table) * * - a skip-duplicate index scan is chosen by the optimizer * * to scan the subquery table * * - there are multiple (> 1) main query tables * * - the optimizer chose a hash join between 2 main query * * tables (both after accessing the subquery table) * * - the skip-duplicate index scan appears on the build side * * of the hash join, or if it appears on the probe side of the * * hash join, then hash join overflowed * **************************************************************** * RECOMMENDATION: * * Update to IBM Informix Server 12.10.xC7 * **************************************************************** | |
Local Fix: | |
Use IN clause instead where possible. | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 09.05.2016 29.06.2016 29.06.2016 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC7 | |
Problem solved according to the fixlist(s) of the following version(s) |