|
Problem IC88675
|
Status: Closed |
Trap may occur when a Text Search function reference the result of an Outer join |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Query using text search will crash instance when text function
(SCORE, NUMBEROFMATCHES, CONTAINS) are present in a sub-select
containing an OUTER JOIN or when the input column to the text
function is produced from the result of an OUTER JOIN operator
in a sub-select or view.
Furthermore this occurs when the text function is used as an
input to another function or basic predicate where the text
function result is checked to be greater then a value, for
example
CONTAINS(...) > 0
CONTAINS(...) >= ?
? < CONTAINS(...)
1 <= CONTAINS(...)
In stack will see functions similar to the following
sqlnq_pid::myqncp
<sqlnr_tsoj_rewrite> <sqlnr_qrwprep.C:3313>
<sqlnr_qrwprep_phase2> <sqlnr_qrwprep.C:10113>
<sqlnr_prep2_action> <sqlnr_exe.C:10133>
<sqlnr_seq> <sqlnr_sys.C:602>
<sqlnr_rcc> <sqlnr_sys.C:768>
<sqlnr_exe> <sqlnr_exe.C:11057>
<sqlnn_cmpl> <sqlnn_cmpl.C:3345>
<sqlnn_cmpl> <sqlnn_cmpl.C:530>
<sqlra_compile_var> <sqlra_cmpl.C:1053>
<sqlra_find_var> <sqlra_dyn.C:4312>
<sqlra_get_var> <sqlra_var.C:1778>
<sqlrr_prepare> <sqlrr_req_sql.C:2987>
<sqljs_ddm_prpsqlstt> <sqljspds.C:633> |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Query using text search will crash instance when text *
* function *
* (SCORE, NUMBEROFMATCHES, CONTAINS) are present in a *
* sub-select *
* containing an OUTER JOIN or when the input column to the *
* text *
* function is produced from the result of an OUTER JOIN *
* operator *
* in a sub-select or view. *
* *
* *
* Furthermore this occurs when the text function is used as an *
* input to another function or basic predicate where the text *
* function result is checked to be greater then a value, for *
* example *
* *
* CONTAINS(...) > 0 *
* CONTAINS(...) >= ? *
* ? < CONTAINS(...) *
* 1 <= CONTAINS(...) *
* *
* In stack will see functions similar to the following *
* *
* sqlnq_pid::myqncp *
* <sqlnr_tsoj_rewrite> <sqlnr_qrwprep.C:3313> *
* <sqlnr_qrwprep_phase2> <sqlnr_qrwprep.C:10113> *
* <sqlnr_prep2_action> <sqlnr_exe.C:10133> *
* <sqlnr_seq> <sqlnr_sys.C:602> *
* <sqlnr_rcc> <sqlnr_sys.C:768> *
* <sqlnr_exe> <sqlnr_exe.C:11057> *
* <sqlnn_cmpl> <sqlnn_cmpl.C:3345> *
* <sqlnn_cmpl> <sqlnn_cmpl.C:530> *
* <sqlra_compile_var> <sqlra_cmpl.C:1053> *
* <sqlra_find_var> <sqlra_dyn.C:4312> *
* <sqlra_get_var> <sqlra_var.C:1778> *
* <sqlrr_prepare> <sqlrr_req_sql.C:2987> *
* <sqljs_ddm_prpsqlstt> <sqljspds.C:633> *
****************************************************************
* RECOMMENDATION: *
* Upgrade to V10.1 Fixpack 2 *
**************************************************************** |
| Local Fix: |
For the CONTAINS function the workaround to this problem is to
change the basic predicate so that the result of the text
function is compared less then a different value. For example
WHERE 0 < CONTAINS(T1.T1_DATA,'Example')
Instead use the following which will avoid the error.
WHERE 1 >= CONTAINS(T1.T1_DATA,'Example')
Similar workaround may be possible for the SCORE and
NUMBEROFMATCHES functions. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Upgrade to V10.1 Fixpack 2 |
| Workaround |
For the CONTAINS function the workaround to this problem is to
change the basic predicate so that the result of the text
function is compared less then a different value. For example
WHERE 0 < CONTAINS(T1.T1_DATA,'Example')
Instead use the following which will avoid the error.
WHERE 1 >= CONTAINS(T1.T1_DATA,'Example')
Similar workaround may be possible for the SCORE and
NUMBEROFMATCHES functions. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 28.11.2012
06.05.2013
06.05.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.2
|
 |
| 10.5.0.2
|
|
