IT15312: QUERIES CONTAINING MULTIPLE OUTER JOIN OPERATIONS AND NESTED EXPRESSIONS MAY PRODUCE INCORRECT RESULTS

DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Queries containing multiple outer join operation may produce
  incorrect results under certain conditions.
  
  An OUTER JOIN used in a view, common table expression or nested
  sub-select and contains expressions that do not reference the
  input operands of the OUTER JOIN or complex scalar expressions
  such as
  - CASE expressions
  - COALESCE
  - DECODE
  - VALUE
  - or external user defined functions which are not defined as
  'RETURNS NULL ON NULL INPUT'.
  
  When such an view, common table expression, or nested sub-select
  is used as the NULL producing operand of a higher level OUTER
  JOIN then the query may produce incorrect results.
  

Local fix

• The following registry can be used to avoid the potential wrong
  result:
  db2set -im DB2COMPOPT=NO_OJLINEAR
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 10.1 Fix Pack 6                               *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 10.1 Fix Pack 6
  

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:

  IT15313

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN UP

     FIX