JR27716: WRONG RESULTS: POSSIBLE WRONG NULL VALUE RETURNED WHEN DB2COMPOPT IS SET

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
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 program error.

Error description

• Incorrect NULL values may be returned from the 'count(column)'
  or 'count(distinct column)' function present on the inner of
  an OUTERJOIN when the referenced columns is defined as
  'NOT NULL' and the registry variable DB2COMPOPT is set.
  The problematic plan will likely only be generated if the
  table statistics for the inner and outer tables indicate that
  the number of rows in the outer table is less then 20% of the
  number of rows in the inner table.
  
  For example the query below may get incorrect NULLs generated
  for the totcount function if the column AMOUNT is defined as
  NOT NULL and the table CONSUMER has less then 20% of the
  number of rows in the table ACCOUNT:
  
  select c.hsn, c.isn, a.toamount
   from consumer as c
    left outer join ( select hsn, isn, count(amount) as totamount
                       from account
                       group by hsn, isn) as a
    on c.hsn = a.hsn and c.isn = a.isn
  

Local fix

• unset the DB2COMPOPT registry variable
  

Problem summary

• USERS AFFECTED  All
  
  PROBLEM DESCRIPTION
  
  Incorrect NULL values may be returned from the 'count(column)'
  or 'count(distinct column)' function present on the inner of
  an OUTERJOIN when the referenced columns is defined as
  'NOT NULL' and the registry variable DB2COMPOPT is set.
  The problematic plan will likely only be generated if the
  table statistics for the inner and outer tables indicate that
  the number of rows in the outer table is less then 20% of the
  number of rows in the inner table.
  
  For example the query below may get incorrect NULLs generated
  for the totcount function if the column AMOUNT is defined as
  NOT NULL and the table CONSUMER has less then 20% of the
  number of rows in the table ACCOUNT:
  
  select c.hsn, c.isn, a.toamount
   from consumer as c
    left outer join ( select hsn, isn, count(amount) as totamount
                       from account
                       group by hsn, isn) as a
    on c.hsn = a.hsn and c.isn = a.isn
  
  PROBLEM SUMMARY
  
  see PROBLEM DESCRIPTION
  

Problem conclusion

• The complete fix for this problem first appears in DB2 UDB
  Version 9.5 FixPak 1.
  

Temporary fix

• unset the DB2COMPOPT registry variable
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  JR27524

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 UDB EXE WIN

• Fixed component ID

  5724E4901

Applicable component levels

• R810 PSN

     UP

• R820 PSN

     UP

• R910 PSN

     UP

• R950 PSN

     UP