Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC76986 Status: Closed

DB2 MAY PRODUCE INCORRECT RESULTS EXECUTING A QUERY CONTAINING A PREDICATE
ON MAX/MIN APPLIED ON A CASE EXPRESSION

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
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'
Problem Summary:
**************************************************************** 
* 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                    * 
****************************************************************
Local Fix:
available fix packs:
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
First fixed in DB2 UDB Version 9.5 fix pack 9
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC76992 IC76993 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
15.06.2011
09.04.2012
09.04.2012
Problem solved at the following versions (IBM BugInfos)
9.5.FP9
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.9 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.