DB2 - Problem description
Problem IT35943 | Status: Closed |
WRONG RESULT: QUERIES REFERENCING AN EXISTS SUB-QUERY WITH FETCHFIRST 1 ROW REFERENCED IN A CASE STATEMENT IN THE SELECT LIST | |
product: | |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 | |
Problem description: | |
Queries referencing an EXISTS sub-query with FETCH FIRST 1 ROW referenced in a CASE statement in the SELECT list may result in incorrect results. Steps to reproduce create table tab2 ( col1 integer ); create table tab3 ( col2 integer ); insert into tab2 values ( 123 ); insert into tab2 values ( 234 ); insert into tab2 values ( 345 ); insert into tab2 values ( NULL ); insert into tab3 values ( 234 ); insert into tab3 values ( 345 ); insert into tab3 values ( NULL ); select a.col1 , case when exists ( select 1 from tab3 b where a.col1 = b.col2 fetch first 1 rows only ) then 1 else 0 end C from tab2 a where col1 is not null Expected output COL1 C ----------- ----------- 123 0 234 1 345 1 With tables organized by column, we get this incorrect result COL1 C ----------- ----------- 123 0 234 1 345 0 1) Remove FETCH FIRST 1 ROW as it is not required in an existential sub-query 2) In DPF environment, apply the following guideline to the query: /* */ 3) In non-DPF, apply the following guideline: /* */ | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.5.6 * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.5.6 * **************************************************************** | |
Comment | |
Upgrade to 11.5.6 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 17.02.2021 11.06.2021 23.06.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |