DB2 - Problem description
Problem IT28638 | Status: Closed |
DB2 MAY PRODUCE INCORRECT RESULTS FOR HSJN WITH PREDICATE THAT HAS CASE(COALESCE) AND NLJN OR ZZJOIN ON THE OUTER | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
DB2 MAY PRODUCE INCORRECT RESULTS FOR HSJN WHEN - HSJN HAS COMPLEX PREDICATE THAT HAS CASE ( COALESCE ) - HSJN HAS NLJN OR ZZJOIN ON THE OUTER - INTERNAL OPTIMIZATION IS APPLIED Example of the Complex Predicate in HSJN from the Plan that could produce wrong results: Predicate Text: -------------- (Q8.C1 = CASE WHEN (Q1.C2 = 'X') THEN COALESCE(Q1.C3, 'Y') WHEN NOT(Q1.C4 IN ('A', 'B')) THEN CASE WHEN UPPER(COALESCE(Q2.C5, 'D')) IN ('E', 'F', 'G') THEN 'H' WHEN (UPPER(COALESCE(Q2.C5, 'D')) = 'K') THEN 'L' ELSE COALESCE(Q2.C5, 'D') END ELSE COALESCE(Q2.C5, 'D') END) Example of HSJN with NLJN on the outer: 101225 HSJN ( 6) 552757 /------/ ---- 101225 46 NLJN ISCAN ( 7) (22) 552653 ... / 101224 1 SCAN ISCAN ( 8) (21) 548163 ... | ... | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher * **************************************************************** | |
Local Fix: | |
db2set DB2_TCG_DEFAULT_OPTIONS="set disable_pushdown on" instance restart is needed Static packages need to be rebound. | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : follow-up : IT30005 IT30006 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 02.04.2019 16.01.2020 16.01.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |