DB2 FOR LUW / DB2FORLUW / 950 - 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:                                         * 
* DB2 MAY PRODUCE INCORRECT RESULTS EXECUTING A QUERY          * 
* CONTAINING A PREDICATE ON MAX/MIN APPLIED ON A CASE          * 
* EXPRESSION                                                   * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB Version 9.5 fix pack 9                    * 
****************************************************************

DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 UDB Version 9.5 fix pack 9

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC76992 IC76993 
follow-up : 

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

15.06.2011
09.04.2012
09.04.2012

9.5.FP9