IZ84783: Executing a query with left outer join and an IS NULL predicate may cause a reverse column output or produce a SQL0901N error

DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• 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
  

Local fix

• Perform the following action:
  db2set DB2_ANTIJOIN=[NO | OFF]
  
  and then restart the DB2 instance.
  
  Setting the above registry variable may impact performance of
  other queries using NOT EXISTS / NOT IN predicates.
  

Problem summary

• USERS AFFECTED: All
  
  PROBLEM DESCRIPTION:
  
  see ERROR DESCRIPTION
  
  PROBLEM SUMMARY:
  
  see ERROR DESCRIPTION
  

Problem conclusion

• The complete fix for this problem first appears in DB2 UDB
  Version 9.5 FixPak 6a.
  

Temporary fix

• Perform the following action:
  db2set DB2_ANTIJOIN=[NO | OFF]
  
  and then restart the DB2 instance.
  
  Setting the above registry variable may impact performance of
  other queries using NOT EXISTS / NOT IN predicates.
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IC71241 IC71333

Fix information

• Fixed component name

  DB2 UDB ESE AIX

• Fixed component ID

  5765F4100

Applicable component levels

• R810 PSN

     UP

• R820 PSN

     UP

• R910 PSN

     UP

• R950 PSN

     UP