IBM Support

IC69062: UNEXPECTED RESULTS FOR A QUERY THAT CONTAINS MULTIPLE DISTINCT'S AND REGISTRY SETTING FOR ENHANCED_MULTIPLE_DISTINCT IS ENABLED.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Incorrect results for a query that contains multiple distinct's
    and registry setting for ENHANCED_MULTIPLE_DISTINCT is turned
    on.
    .
    Description:
    If the following conditions are true:
    a) The DB2 instance is configured with DPF (multiple partitions)
    and the registry variable DB2_EXTENDED_OPTIMIZATION is set to
    ENHANCED_MULTIPLE_DISTINCT.
    b) A query is run that contains multiple distinct operations
    (see example)
    c) The query has predicates that need to be evaluated without an
    index.  That is, in order to qualify or disqualify the row
    during predicate evaluation, it has to fetch the row and read
    the columns to compare the predicate rather than reading just
    the index (A sargable predicate).
    Then the query may return incorrect results.
    For example:
    select sum(distinct col2), avg(distinct col3), sum(distinct
    col4)
    from test
    where col5.data = 'abcde'
    group by col1;
    The wrong results is that even rows that should be disqualified
    based on the predicate col5.data = 'abcde', are still included
    in the query incorrectly.  Thus, any sums or averages that are
    performed on the data may have extra data used in those
    aggregate operations.
    

Local fix

  • If all of the local predicates can be resolved by doing
    an index scan as start/stop key predicates, (and not as
    sargable predicates), then this is a possible workaround
    to the problem.
    

Problem summary

  • Incorrect results for a query that contains multiple distinct's
    and registry setting for ENHANCED_MULTIPLE_DISTINCT is turned
    on.
    .
    Description:
    If the following conditions are true:
    a) The DB2 instance is configured with DPF (multiple partitions)
    and the registry variable DB2_EXTENDED_OPTIMIZATION is set to
    ENHANCED_MULTIPLE_DISTINCT.
    b) A query is run that contains multiple distinct operations
    (see example)
    c) The query has predicates that need to be evaluated without an
    index.  That is, in order to qualify or disqualify the row
    during predicate evaluation, it has to fetch the row and read
    the columns to compare the predicate rather than reading just
    the index (A sargable predicate).
    Then the query may return incorrect results.
    For example:
    select sum(distinct col2), avg(distinct col3), sum(distinct
    col4)
    from test
    where col5.data = 'abcde'
    group by col1;
    The wrong results is that even rows that should be disqualified
    based on the predicate col5.data = 'abcde', are still included
    in the query incorrectly.  Thus, any sums or averages that are
    performed on the data may have extra data used in those
    aggregate operations.
    

Problem conclusion

  • Apar fixed in >= V97 fpk3
    

Temporary fix

  • If all of the local predicates can be resolved by doing
    an index scan as start/stop key predicates, (and not as
    sargable predicates), then this is a possible workaround
    to the problem.
    

Comments

APAR Information

  • APAR number

    IC69062

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-06-07

  • Closed date

    2010-09-23

  • Last modified date

    2010-09-23

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

    IC69060

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

Modules/Macros

  • ENG_SQRI
    

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
23 September 2010