DB2 FOR LUW / DB2FORLUW / 970 - 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 to DB2 970 fixpack 5                          * 
****************************************************************

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 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