DB2 FOR LUW / DB2FORLUW / 910 - DB2

A query may produce incorrect results if the following 
conditions are satisfied: 
1. The query containing a predicate on the output of a MAX or 
MIN function 
2. The subselect containing the MAX or MIN function has more 1 
or more GROUP BY and output columns 
3. The input to the MAX or MIN function is a CASE expression 
4. The else clause of the CASE Expression is NULL. 
 
Sample query: 
 
SELECT 
        T1.c1 ,T2.c2 
FROM 
        T1, T2 
        LEFT OUTER JOIN ( 
                select 
                        a.c1, a.c2, 
                        max(case when b.c1 = 1 then b.c2 else 
null end) AS col1,  <---------- input to MAX is a CASE 
expression 
                        max(case when b.c1 = 2 then b.c2 else 
null end) AS col2 
                from 
                        t1 a, t3 b 
                where 
                        a.c1 = b.c1 
                        and a.c2 = b.c2 
                group 
                        by a.c1, a.c2              <------------ 
Group by and output columns 
        ) tmp 
                ON 
                        t1.c1 = tmp.c1 
                        and t2.c2 = tmp.c2 
WHERE 
        ( t1.c1 = t2.c2 ) 
        AND  tmp.col1 = 'Y'            <---------- predicate on 
MAX/MIN 
 
Workaround: 
Rewrite the query to include other MAX columns in the output 
list. 
 
eg. 
SELECT 
        T1.c1 ,T2.c2, 
        TMP.COL2             <---------------- added column to 
output list 
FROM 
        T1, T2 
        LEFT OUTER JOIN ( 
                select 
                        a.c1, a.c2, 
                        max(case when b.c1 = 1 then b.c2 else 
null end) AS col1, 
                        max(case when b.c1 = 2 then b.c2 else 
null end) AS col2 
                from 
                        t1 a, t3 b 
                where 
                        a.c1 = b.c1 
                        and a.c2 = b.c2 
                group 
                        by a.c1, a.c2 
        ) tmp 
                ON 
                        t1.c1 = tmp.c1 
                        and t2.c2 = tmp.c2 
WHERE 
        ( t1.c1 = t2.c2 ) 
        AND  tmp.col1 = 'Y'

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See APAR description                                         * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade toDB2 970 fixpack 5                           * 
****************************************************************

DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows
 

Problem is first fixed in DB2 970 fixpack 5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

15.06.2011
12.12.2011
12.12.2011

9.7.0