|
Problem IT00386
|
Status: Closed |
SQL QUERY CONTAINS NOT EXISTS SUBQUERY AND NODENUMBER PREDICATE MIGHT PRODUCE AN INCORRECT RESULT |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Under rare scenarios, DB2 might produce incorrect results if
the following conditions are true:
1) The query consists of one or more NOT EXISTS or NOT IN
subqueries
2) The parent side table of the subquery contains a nodenumber
predicate
eg. select count(*)
from t1
where not exists (select 1 from t2 where t1.a=t2.a) and
<-------------- condition 1
nodenumber(t1.a)=1;
<-------------- condition 2 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 101 fixpack 4 *
**************************************************************** |
| Local Fix: |
1) Turn DB2_ANTIJOIN=NO
2) Manually rewrite the query to form:
select count(*) from t1 left join t2 on t1.a=t2.a where t2.a is
null and nodenumber(t1.a)=1; |
| available fix packs: |
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Problem is fixed in DB2 version 101 fixpack 4 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.03.2014
03.07.2014
07.04.2015 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.4
|
 |
