IT12810: QUERY WITH OUTER JOIN RETURNS WRONG RESULTS

APAR status

• Closed as program error.

Error description

• A 'select * from...' query with outer join returns wrong results
  but not each time it is run.
  
  Just replacing '*' by named columns will make a slightly
  different plan to be chosen (Key-Only is then used for some of
  the scans). The good number of rows is returned in that case.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 12.10.xC2 through 12.10.xC6.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query with outer join may return incorrect results under   *
  * the following conditions:                                    *
  *   - there are multiple subservient tables, e.g. FROM tab1,   *
  * OUTER (tab2, tab3, tab4)                                     *
  *   - only a subset of subservient tables has direct join      *
  * predicates with a dominant table, e.g. tab2.col1 = tab1.col1 *
  *   - other subservient tables only join with subservient      *
  * tables, not dominant tables, e.g. tab2.col2 = tab3.col2 and  *
  * tab3.col3 = tab4.col3                                        *
  *   - one of the subservient tables joins with more than one   *
  * other subservient tables, e.g. tab3 above joins with both    *
  * tab2 and tab4                                                *
  *   - in the chosen query plan the table that joins with       *
  * multiple other subservient tables comes in last in join      *
  * ordering after the other subservient tables,                 *
  *     e.g., tab1 - tab2 - tab4 - tab3                          *
  ****************************************************************
  * 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