IZ09120: WRONG RESULT FROM STATEMENT WITH TWO OR MORE OLAP FUNCTIONS WITH COMPATIBLE PARTITION-BY CLAUSES WITH ONE BEING EMPTY

DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• This problem only occurs in a DPF or SMP enabled environment.
  
  An SQL statement that has two or more OLAP functions with
  compatible
  "partition-by" clauses where one contains an empty partition-by
  clause
  can produce wrong results. Moving the OLAP function with the
  empty
  partition-by clause into a nested table expression will avoid
  the wrong
  results.
  
  Simple example to illustrate the problem:
  
  -- this example requires the table to be partitioned across
  -- 3 database partitions
  create database partition group dbpg3 on dbpartitionnums
  (1,2,3);
  create tablespace ts3 in database partition group dbpg3 managed
  by system using ('ts3');
  create table t1(c1 int,c2 int,c3 int) partitioning key (c1) in
  ts3;
  
  insert into t1
  values (1,1,1),(1,1,2),(2,2,1);
  
  
  select c1, max(c2) over (partition by c1) "MAX(C2) PART (C1)",
  max(c3) over () "MAX(C3)"
  from t1;
  
  C1          MAX(C2) PART (C1) MAX(C3)
  ----------- ----------------- -----------
            1                 1           2
            1                 1           2
            2                 2           1   <--- MAX(C3) is
  wrong
  
    3 record(s) selected.
  
  -- workaround: move the OLAP function with the empty
  partition-by
  -- clause into a nested table expression
  
  select c1, max(c2) over (partition by c1) "MAX(C2) PART (C1)",
  c3 "MAX(C3)"
  from (select c1, c2, max(c3) over () c3 from t1) as TMP;
  
  C1          MAX(C2) PART (C1) MAX(C3)
  ----------- ----------------- -----------
            1                 1           2
            1                 1           2
            2                 2           2
  
    3 record(s) selected.
  
  With INTRA_PARALLEL enabled, the following example demonstrates
  the problem :
  
  create table t1(c1 int,c2 int,c3 int);
  
  insert into t1
  values (1,1,1),(1,1,2),(2,2,1);
  
  -- this setting is important to reproduce the problem
  -- for the data above, the degree of parallelism needs to be 3
  -- to reproduce the wrong results
  set current degree = '3';
  
  select c1, max(c2) over (partition by c1) "MAX(C2) PART (C1)",
  max(c3) over () "MAX(C3)"
  from t1;
  
  C1          MAX(C2) PART (C1) MAX(C3)
  ----------- ----------------- -----------
            1                 1           2
            1                 1           2
            2                 2           1   <--- MAX(C3) is
  wrong
  
    3 record(s) selected.
  

Local fix

• workaround : move the OLAP function with the empty
  partition-by clause into a nested table expresssion - see
  problem description for more detail
  

Problem summary

• Users Affected :  users in a DPF / SMP environment, using
  partition by clause  Problem Description : Wrong results may be
  returned  Problem Summary : This apar ensures that results are
  correct.
  

Problem conclusion

• First fixed in DB2 UDB Version 9.5, FixPak 1
  

Temporary fix

Comments

APAR Information

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

  IZ09118

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

  IZ09123

Fix information

• Fixed component name

  DB2 EDE AIX

• Fixed component ID

  5724N7600

Applicable component levels

• R950 PSY

     UP