IZ90691: DB2 MAY RETURN INCORRECT RESULT FROM SELECT DISTINCT LEFT OUTER JOIN UNION ALL VIEW

DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as fixed if next.

Error description

• An SQL statement to perform a left outer join between tables or
  views along with a UNION ALL view may return incorrect results.
  The UNION ALL view must be the right table or null-producing
  table of the left outer join. In addition, the SELECT clause
  must be specified with DISTINCT for the tables or views.
  
  
  The above mentioned scenario will likely cause DB2 to return
  more rows from the left outer joining process, than the optimum
  value.
  

Local fix

• Run the following commands to stop DB2 instance, set the
  registry variable and restart DB2:
  
  db2stop force
  db2set DB2_UNION_OPTIMIZATION=DISABLE_OJPPD_FOR_NP
  db2start
  
  This will avoid optimization that can lead to incorrect results.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Db2 V9.1, V9.5, V9.7                                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An SQL statement to perform a left outer join between tables *
  * or                                                           *
  * views along with a UNION ALL view may return incorrect       *
  * results.                                                     *
  * The UNION ALL view must be the right table or null-producing *
  *                                                              *
  * table of the left outer join. In addition, the SELECT clause *
  *                                                              *
  * must be specified with DISTINCT for the tables or views.     *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  *                                                              *
  * The above mentioned scenario will likely cause DB2 to return *
  *                                                              *
  * more rows from the left outer joining process, than the      *
  * optimum                                                      *
  * value.                                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to most current fix pack.                            *
  ****************************************************************
  

Problem conclusion

• Fixed in fix pack 8.
  

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:

  IC73692 IC73756

Fix information

Applicable component levels

• R910 PSN

     UP

• R910 PSY

     UP

• R950 PSN

     UP

• R950 PSY

     UP