IT08504: INCORRECT RESULT WHEN QUERY HAS LEFT OUTER JOIN AND UNION ALL

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

APAR status

• Closed as program error.

Error description

• A query might return incorrect query result when it has all the
  following characteristics:
  - The query has a UNION ALL query block, e.g. (select * from
  table1 UNION ALL select * from table2), called uaSubQ.
  - The query has LEFT OUTER JOIN query block, whose row
  preserving side includes base table(s) and null producing side
  is uaSubQ, e.g.  (select ... from table3 LEFT OUTER JOIN uaSubQ
  on ... where <predUaT3>) , called lojSubQ.
  - <predUaT3> is a comparison predicate, which references
  column(s) from both uaSubQ and table3. The predicate expression
  consists of CASE expression that has WHEN clause predicate,
  which tests if the null producing side column is null, e.g.
  "WHEN uaSubQ.column IS NULL".
  
  To find whether a query hits this problem, you can generate
  db2exfmt plan and search if "ROWNUMBER" OnLine Analytical
  Processing (OLAP) function is present in the optimized statement
  section.
  

Local fix

• Compile query at optimization level 0 or 1.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 version 10.1.0.5.                             *
  ****************************************************************
  

Problem conclusion

• The problem was first fixed in DB2 version 10.1.0.5.
  

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:

  IT09419 IT09831

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP