IT09137: WRONG RESULT FROM STATEMENT WITH TWO OR MORE OLAP FUNCTIONS WITH COMPATIBLE PARTITION-BY CLAUSES WITH ONE EQUATING TO CONSTANTS

DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• This problem only occurs in a DPF or SMP parallelism enabled
  environment.
  
  You might encounter an incorrect result set for a SQL statement
  that contains two or more OLAP functions with compatible
  partition-by clauses wherein at least one OLAP function contains
  a partition-by clause where all columns equate to constant
  values. A column can equate to a constant value by specifying
  the constant values explicitly in the partition by clause or the
  columns in the partition by clause are referenced in equality
  predicates that equate to constants.
  
  The following example specifies the constant value explicitly
  SELECT ... SUM(X) OVER (PARTITION BY 1 ...) ...
  
  and the following example references the partition by column in
  an equality predicate
  SELECT ... SUM(X) OVER (PARTITION BY Y ...) ... WHERE Y = 1 ...
  

Local fix

• You can rewrite your query to move the OLAP function with
  constant partition-by clause into a nested table expression.
  For example, if you have two compatible OLAP functions in a
  query as follows:
  
  SELECT SUM(X) OVER (PARTITION BY A), SUM(Y) OVER (PARTITION BY
  A,B) FROM T1 WHERE T1.A=1
  
  then you can rewrite it as a nested table expression to avoid
  the incorrect result:
  
  SELECT SUMX, SUM(Y) OVER (PARTITION BY A,B) FROM (SELECT A,B,Y,
  SUM(X) OVER (PARTITION BY A) SUMX FROM T1 WHERE A=1) AS TMP
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * DPF or SMP parallelism enabled environments                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 version 10.1.0.5.                             *
  ****************************************************************
  

Problem conclusion

• The problem is first fixed in DB2 version 10.1.0.5.
  

Temporary fix

Comments

APAR Information

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

  IT05825

• 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