IBM Support

IC81814: QUERY WITH OUTER JOIN AND GROUP BY CLAUSE MIGHT RETURN INCORRECT RESULTS IF A RIGHT OUTER HASH JOIN IS USED

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • If you are using the database partitioning feature (DPF) and
    your query satisfies the following conditions, then the results
    returned might be incorrect if the DB2 query plan optimizer
    chose a right outer hash join operation:
    1. there is an outer join between two tables, T1 and T2;
    2. one or both T1 and T2 are hash distributed across 2 or more
    database partitions;
    3. the join is not performed on the hash distribution key;
    4. there is a group by clause on the columns the join is
    performed on.
    
    For example, consider tables T1 and T2, both hash distributed on
    column (A).  The following query satisfies the conditions and
    might return incorrect results, depending on the data in the
    tables and if a right outer hash join operation is used:
    
    SELECT T2.B, COUNT(*)
    FROM T1 LEFT JOIN T2  ON T1.B = T2.B
    GROUP BY T2.B
    
    You can identify if the results are incorrect if the expected
    groups includes duplicate null entries.
    You can identify if an right outer hash join operation is used
    by capturing an EXPLAIN of the query and verifying the
    following:
    1.  a HSJOIN operation is chosen to perform the outer join
    2.  the OUTERJN (Outer Join Type) argument of the HSJOIN is set
    to "RIGHT"
    

Local fix

  • You can workaround this defect by using one of the following
    options:
    1. an optimization class that does not consider hash join, which
    includes classes 0, 1, and 3; or
    2. create an optimization profile to use a different join
    method, such as NLJOIN or MSJOIN, or reverse the order of the
    inputs to the HSJOIN operator which will produce a "LEFT" outer
    join type.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users with database partitioning feature                 *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to version 9.7 Fix Pack 6                            *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in version 9.7 Fix Pack 6
    

Temporary fix

  • See Local Fix
    

Comments

APAR Information

  • APAR number

    IC81814

  • 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

    2012-03-05

  • Closed date

    2012-06-13

  • Last modified date

    2012-06-13

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC81814

Modified date: 13 June 2012