|
Problem IC80380
|
Status: Closed |
DB2 MAY ABEND WHILE COMPILING QUERY WITH ALWAYS FALSE PREDICATE AT LEFT OUTER JOIN ON CLAUSE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
DB2 may crash during query compilation when the following
conditions are both met:
1. Query has left outer join "TABLE_A LEFT OUTER JOIN TABLE_B ON
..."
2. The ON clause includes always false predicate, e.g."'a' ||
col1 = 'b' || col2"
3. (Optional) The ON clause include OR predicates which can be
factorized into common parts, eg. "(col1=col2 and col3 < 10) or
(col1=col2 and col3 > 50)"
A known call stack of this crash is:
0 sqlnr_matchmaker::nav_assign_max_levels
1 sqlnr_matchmaker::nav_assign_max_levels
2 sqlnr_matchmaker::nav_assign_max_levels
3 sqlnr_matchmaker::nav_assign_max_levels
4 sqlnr_matchmaker::nav_assign_max_levels
5 sqlnr_matchmaker::nav_assign_max_levels
6 sqlnr_matchmaker::nav_init
7 sqlnr_matchmaker::form_matches
8 sqlnr_form_common_subexprs
9 sqlnr_optprep
10 sqlnr_optprep_action
11 sqlnr_comp
12 sqlnr_comp
13 sqlnr_seq
Another know call stack for this issue is the following:
0 sqlnq_qnc::is_RIDC_qnc
1 sqlnq_pid::UDT_colequiv_exp
2 sqlnr_build_equiv
3 sqlnr_coleq_class_action
4 sqlnr_comp
5 sqlnr_seq
6 sqlnr_rcc
7 sqlnr_endqtb_action
8 sqlnr_comp
9 sqlnr_seq
10 sqlnr_rcc
11 sqlnr_start_action
12 sqlnr_comp
13 sqlnr_seq
14 sqlnr_rcc
15 sqlnr_exe
16 sqlnn_cmpl |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* DB2 may crash during query compilation when the following *
* conditions are both met: *
* *
* 1. Query has left outer join "TABLE_A LEFT OUTER JOIN *
* TABLE_B ON *
* ..." *
* 2. The ON clause includes always false predicate, e.g."'a' *
* || *
* col1 = 'b' || col2". *
* *
* A known call stack of this crash is: *
* *
* 0 sqlnr_matchmaker::nav_assign_max_levels *
* 1 sqlnr_matchmaker::nav_assign_max_levels *
* 2 sqlnr_matchmaker::nav_assign_max_levels *
* 3 sqlnr_matchmaker::nav_assign_max_levels *
* 4 sqlnr_matchmaker::nav_assign_max_levels *
* 5 sqlnr_matchmaker::nav_assign_max_levels *
* 6 sqlnr_matchmaker::nav_init *
* 7 sqlnr_matchmaker::form_matches *
* 8 sqlnr_form_common_subexprs *
* 9 sqlnr_optprep *
* 10 sqlnr_optprep_action *
* 11 sqlnr_comp *
* 12 sqlnr_comp *
* 13 sqlnr_seq *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version V9.7 Fixpack 9 *
**************************************************************** |
| Local Fix: |
1) Rewrite the always false predicate "'a' || col1 = 'b' ||
col2" to "1=0".
2) Rewrite the query to contain factorized predicate "col1=col2
and (col3 < 10 or col3 > 50)" |
| available fix packs: |
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
See Problem Description above. |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC95529 IC95625
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 12.12.2011
27.09.2013
27.09.2013 |
| Problem solved at the following versions (IBM BugInfos) |
9.0.,
9.7.FP6,
9.7.FP9
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
| 9.7.0.9
|
|
| 9.7.0.9
|
|
