IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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:
    
    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 conclusion

  • The complete fix (APAR JR27524) for this problem first appears
    in DB2 UDB Version 9.1 FixPak 4.
    

Temporary fix

  • unset the DB2COMPOPT registry variable
    

Comments

APAR Information

  • APAR number

    JR27524

  • Reported component name

    DB2 UDB EXE WIN

  • Reported component ID

    5724E4901

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-09-27

  • Closed date

    2007-11-27

  • Last modified date

    2007-11-27

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

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

    JR27525 JR27716

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

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 January 2022