IT16124: QUERY WITH 2 OR MORE LEFT OUTER JOINS WILL RETURN WRONG RESULTS

APAR status

• Closed as program error.

Error description

• A query with 2 or more left outer joins will return wrong
  results.  If only one outer join is used, the results are
  correct.
  
  It does return the correct results if the --+avoid_hash
  directive is used.
  

Local fix

• Use the +avoid_hash directive.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of 12.10.xC1 through 12.10.xC7.                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An ANSI OUTER JOIN query may return incorrect results under  *
  * the following conditions:                                    *
  *   - a hash join is being used to handle an outer join        *
  *   - the subservient table (which is the build side of the    *
  * hash join) is large such that the hash join build overflows  *
  *   - the subservient table is significantly larger than the   *
  * dominant side table(s) for this join                         *
  *   - a WHERE clause exists for the ANSI OUTER JOIN query, and *
  * it references a dominant side table                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IBM Informix Server 12.10.xC8                      *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IBM Informix Server 12.10.xC8
  

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