Latest versionsfixlist
11.1.0.7 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

DB2 - Problem description

Problem IC76992 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 / 910 - 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:                                         * 
* See APAR description                                         * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade toDB2 970 fixpack 5                           * 
****************************************************************
Local Fix:
available fix packs:
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

Solution
Problem is first fixed in DB2 970 fixpack 5
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
15.06.2011
12.12.2011
12.12.2011
Problem solved at the following versions (IBM BugInfos)
9.7.0
Problem solved according to the fixlist(s) of the following version(s)
9.1.0.11 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.