IT15179: 'WHERE EXISTS (SUBQUERY)' FILTER POTENTIALLY INCREASING RESULT S ET BY PRODUCING DUPLICATE ROWS IF SUBQUERY RETURNS MORE THAN ONE

APAR status

• Closed as program error.

Error 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 <columns_from_A>
     FROM A, B
   WHERE A.join_col = B.join_col
       AND <more_conditions_on_A>
       AND EXISTS (SELECT C.col_X from C WHERE C.join_col =
  A.join_col AND <condition_on_C>);
  
  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 <columns_from_A>
     FROM A, B, C
   WHERE A.join_col = B.join_col
       AND A.join_col = C.join_col
       AND <more_conditions_on_A>
       AND <condition_on_C>;
  

Local fix

• Use IN clause instead where possible.
  

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                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IBM Informix Server 12.10.xC7
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels

• RC10 PSN

     UP