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

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

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 10.1.0.2.                             *
  ****************************************************************
  

Problem conclusion

• The problem is first fixed in DB2 version 10.1.0.2.
  

Temporary fix

Comments

APAR Information

• 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

• RA10 PSN

     UP