A query using a left outer join with an IS NULL predicate on one
of the columns, from the null producing side (manual version of
anti-join), may produce the following symptoms which are first
introduced in DB2 Version 9.5 Fix Pack 6. This problem may
happen
only if the outer join and IS NULL predicates is done as the
last operation in the query.
1. The result of the output columns in the query may be reversed
eg.
CREATE TABLE T1 ( "C1_PARENT_ID" INTEGER NOT NULL ,
"C2_CHILD_ID" INTEGER NOT NULL );
CREATE TABLE T2 ( "C1_ID" INTEGER NOT NULL , "C2_FLAG" CHAR(1)
NOT NULL );
insert into T1 values (1,11),(2,22),(3,33);
insert into T2 values
(11,'X'),(22,'X'),(33,'X'),(44,'X'),(55,'X');
RESULT ON DB2 Version 9.5 Fix Pack 6:
SELECT T2.C1_ID, T2.C2_FLAG FROM T2 LEFT OUTER JOIN T1 ON
T2.C1_ID = T1.C2_CHILD_ID WHERE T1.C1_PARENT_ID IS NULL
C1_ID C2_FLAG
------------- -----------
x'58' 44
x'58' 55
RESULT ON DB2 Version 9.5 earlier than Fix Pack 6:
SELECT T2.C1_ID, T2.C2_FLAG FROM T2 LEFT OUTER JOIN T1 ON
T2.C1_ID = T1.C2_CHILD_ID WHERE T1.C1_PARENT_ID IS NULL
C1_ID C2_FLAG
----------- -------
55 X
44 X
2. The query may fail to execute due to 'invalid svar len'
error. The following error message will be
returned:
SQL0901N The SQL statement failed because of a non-severe
system error. Subsequent SQL statements can be processed.
(Reason "Describe: Invalid svar len".) SQLSTATE=58004
Db2diag.log will show an entry similar to this one:
2010-09-10-10.48.35.198352+060 I146072A857 LEVEL: Severe
PID : 667818 TID : 11114 PROC : db2sysc
0
INSTANCE: xxxxxxx NODE : 000 DB : xxxxxxx
APPHDL : 0-2434 APPID:
172.22.212.98.25262.10091009483
AUTHID : xxxxxxxx
EDUID : 11114 EDUNAME: db2agent (xxxxxxx) 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: 26
sqlerrmc: Describe: Invalid svar len
sqlerrp : SQLRA003
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFDA8 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Stack trace may be similar to the following:
------Function + Offset------
pthread_kill + 0xB0
sqloDumpEDU + 0x74
sqldDumpContext__FP8sqeAgentiN42PCcPvT2 + 0x118
sqldDumpContext__FP8sqeAgentiN42PCcPvT2@glueAA5 + 0x94
sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x358
sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x24
sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x120
sqlrrSqlCode + 0x338
sqlrrSqlCode@glue906 + 0xA4
sqlraFixupSqlDD__FP8sqlrr_cbPUcP14dataDescriptorl + 0x420
sqlra_fill_var__FP8sqlrr_cbP14sqlrr_cmpl_envP5sqlcaUcbP16sqlra_c
ached_var
+ 0xB8C
sqlra_fill_var__FP8sqlrr_cbP14sqlrr_cmpl_envP5sqlcaUcbP16sqlra_c
ached_var@glueDAF
+ 0x78
sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P16sqlra
_cached_varPiPUl
+ 0x92C
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
T4PUcT4UsUcP14sqlra_cmpl_enviPi
sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x444
sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0x138 |
