IT29641: QUERY FAILS WITH ERROR SQL0901N "NLJN LOJ INNER NOT NULLABLE"

Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• DB2 may return SQL0901N when executing a query involving an
  expression index on a COALESCE function
  
  DB2 may produce -901 error with  "nljn LOJ inner not nullable"
  when the following conditions are satisfied:
     1.  Query involves an outer join.
     2.  The inner table has an expression index or function based
  index defined on it. And the expression index is created on a
  COALESCE function (or NVL and VALUE functions).
     3.  DB2 Optimizer has chosen an access plan using the
  expression index to scan the inner table and a nested loop join
  to perform the outer join.
  
  For example:
  CREATE TABLE outer901  (COL1 DATE NOT NULL) ORGANIZE BY ROW;
  CREATE TABLE inner901  (DAT DATE NULL) ORGANIZE BY ROW;
  CREATE INDEX IOE ON inner901(COALESCE(DAT, DATE('9999-12-31'))
  ASC);
  SELECT COALESCE(B.DAT, '9999-12-31')
  FROM outer901 V  LEFT OUTER JOIN inner901 B
  ON CURRENT_DATE = COALESCE(B.DAT, '9999-12-31')
  /*<OPTGUIDELINES><NLJOIN><ACCESS TABLE='V'/><IXSCAN
  TABLE='B'/></NLJOIN></OPTGUIDELINES>*/;
  

Local fix

• Adding guideline to disable the expression index or adding
  guideline to force a plan using HSJN.
  /*<OPTGUIDELINES>
      <REGISTRY>
              <OPTION NAME='DB2_STATVIEW' VALUE='false'/>
      </REGISTRY>
  </OPTGUIDELINES>*/
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * all                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.1.4.7                                          *
  ****************************************************************
  

Problem conclusion

• Upgrade to 11.1.4.7
  

Temporary fix

Comments

APAR Information

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

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

  IT38797

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP