An impacted query might return the wrong results if it has the
OR predicate with two sub-terms. One predicate sub-term is "colX
IN (<literal1>, ...<literalN>)", and the other is "colX NOT IN
(<literal1>, ...<literalN>)". The symptom is that the optimized
statement section inside the db2exfmt plan file contains the
"colX IN (<literal1> sub-term, ...<literalN>)" but misses the
"colX NOT IN (<literal1>, ...<literalN>)" sub-term.
Furthermore, the impacted query also might return wrong results
if it has the following OR predicate with two sub-terms: one
sub-term is "colX LIKE <patternexp>", and the other is "colX NOT
LIKE <patternexp>". The symptom experienced by this issue is
that the optimized statement section inside the db2exfmt plan
file contains the "colX LIKE <patternexp>" sub-term, but misses
the "colX NOT LIKE <patternexp>" sub-term.
A query might trap when the query contains a compound-predicate
that meets the following conditions:
- the compound-predicate takes the form (pred1 OR (pred2 OR
pred3))
- pred1 and pred2 are exactly matched, or pred1 subsumes pred2
(For example, c1 in (1,2,3) subsumes c1 in (1,2))
When both conditions are satisfied, a query compilation might
trap and the call stack will show the sqlnr_or_preds_opt
function.
This only occurs in the 9.7 fix pack 8 release of the DB2
product.
On a Linux platform a trap with SEGV might occur, with the trap
file showing:
0x00002AAAAD5CF043 sqloEDUCodeTrapHandler
0x00002AAAABA681E6 _ZNK9sqlnq_pid2opEv
0x00002AAAAD3C9BBA _Z17sqlnr_or_pred_optP9sqlnq_pidP3locRb
0x00002AAAAD3C9ECF _Z18sqlnr_or_preds_optP9sqlnq_oprP3loc +
0x00002AAAABF6090B _Z20sqlnr_qrwprep_phase2P3locPi + 0x1493
0x00002AAAABF41A01
_Z18sqlnr_prep2_actionP10sqlnr_qrwaPiP14sqlnr_progr
0x00002AAAABF5E802
_Z10sqlnr_compPiiP16sqlnr_rule_stateP10sqlnr_qrwaP1
0x00002AAAABF5E62D
_Z9sqlnr_seqPiP10sqlnr_qrwaP14sqlnr_progressP12sqln
0x00002AAAABF5E49B _Z9sqlnr_rcciP10sqlnr_qrwaPiP14sqlnr_progress
0x00002AAAABF3FCD5 _Z9sqlnr_exeP9sqlnq_qur + 0x04b9
0x00002AAAABD3E842
_Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_comp
...
On an AIX platform a -901 error might be observed instead of a
trap:
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "New tree
is not given".)
SQLSTATE=58004
The db2diag.log log shows:
2013-04-09-10.18.20.879038+120 I4899A840 LEVEL: Severe
PID : 25886852 TID : 1544 PROC : db2sysc 0
INSTANCE: db2v978 NODE : 000 DB : FOO
APPHDL : 0-103 APPID: *LOCAL.db2v978.130409081812
AUTHID : DB2V978
EDUID : 1544 EDUNAME: db2agent (FOO) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
probe:300
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 21
sqlerrmc: New tree is not given
sqlerrp : SQLNQ21E
...
2013-04-09-10.18.20.921390+120 E60158A1518 LEVEL: Info
PID : 25886852 TID : 1544 PROC : db2sysc 0
INSTANCE: db2v978 NODE : 000 DB : FOO
APPHDL : 0-103 APPID:
*LOCAL.db2v978.130409081812
AUTHID : DB2V978
EDUID : 1544 EDUNAME: db2agent (FOO) 0
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
"unexpected error but state is OK"
...
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 21
sqlerrmc: New tree is not given
sqlerrp : SQLNQ21E
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFFF6 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
DATA #8 : Hex integer, 4 bytes
0x00000040
DATA #9 : String, 245 bytes
Compiler error stack for rc = -2144272209:
sqlnn_cmpl[370]
sqlnr_exe[600]
sqlnr_rcc[100]
sqlnr_seq[100]
sqlnr_comp[110]
sqlnr_prep2_action[100]
sqlnr_qrwprep_phase2[620]
sqlnr_or_preds_opt[100]
sqlnr_or_pred_opt[400]
sqlnq_pid::updated_tree[10] |
There are two ways to fix this issue:
(1) Reduce the query optimization level to below 5.
For example, the DB2 product set the current query optimization
level to 3.
(2) Manually rewrite the predicate to:
- remove the exact OR sub-term
select * from t where (c1 >= 1 or c1 >= 2);
Or, alternatively rewrite the predicate to:
- move the exact OR sub-terms to the end of the OR predicate
select * from t where (c1 >= 2 or c1 >= 1 or c1 >= 1); |
