|
Problem IT37911
|
Status: Closed |
INNER JOIN OF AT LEAST 4 COLUMN-ORGANIZED TABLES MIGHT RETURN WRONG RESULTS UNDER SPECIFIC CONDITIONS
|
| product: |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 |
| Problem description: |
Under the following conditions, the query might return fewer
rows than expected.
1. at least 4 column-organized tables are joined using inner
joins
2. at least 3 of those tables have a primary key or unique index
3. simple 'column=column' join predicates reference all the
columns in the primary keys/unique indexes
4. some of the columns appear in more than one 'column=column'
join predicate
5. it is NOT the case that all the keys are identical and all
join to each other on all the columns
The problem is dependent on the choice of access plan and the
data.
If the access plan (obtained via the EXPLAIN facility and
db2exfmt)
has the following specific characteristics, the query _might_ be
vulnerable:
FILTER |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Db2 Version 11.5 *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Db2 Version 11.5.7.0 (11.5 Mod 7 Fix Pack 0) *
**************************************************************** |
| Local Fix: |
The problem can be avoided by setting registry variable:
db2set -immediate DB2_EXTENDED_OPTIMIZATION="COLJOIN 1"
If the registry variable is already set to a value,
append "COLJOIN 1" to the existing setting, separated
by a comma (no spaces). |
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* Db2 Version 11.5 *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Db2 Version 11.5.7.0 (11.5 Mod 7 Fix Pack 0) *
**************************************************************** |
| Comment |
Problem was first fixed in Db2 Version 11.5.7.0 (11.5 Mod 7 Fix
Pack 0) |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.08.2021
22.11.2021
09.12.2021 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
