IBM Support

IC85422: QUERY WITH A UNION AND TWO CORRELATED BRANCHES MIGHT RETURN INCORRECT RESULTS IN PARTITIONED DATABASE ENVIRONMENTS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • If you are using the partitioned database environment and
    your query satisfies the following conditions, then the results
    returned might be incorrect
    1. there is a table, T1, that is hash distributed across two or
    more database partitions;
    2. T1 is correlated to multiple branches below a UNION;
    3. each branch of the UNION is correlated to at least one
    different column in T1;
    4. one of the branches is correlated to the hash distribution
    key of T1;
    5. the columns in the select list of each UNION branch includes
    the correlation column.
    
    The following is an example of a query that satisfies the above
    conditions, where T1 is hash distributed on column X:
    
    SELECT *
      FROM T1,
           TABLE(SELECT X FROM T2 WHERE X = T1.X
                 UNION ALL
                 SELECT Y FROM T3 WHERE Y = T1.Y);
    

Local fix

  • You can avoid the problem by rewriting the query to remove the
    correlation below the UNION.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users of database partitioning feature.                  *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7.0.7.                              *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 9.7.0.7.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC85422

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / Pervasive

  • Submitted date

    2012-07-19

  • Closed date

    2012-10-18

  • Last modified date

    2012-12-07

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

    IC84690

  • 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 #: IC85422

Modified date: 07 December 2012