PH34689: INCORRECT OUTPUT FROM SELECT WHEN NULL HOST VARIABLE WAS USED IN A PREDICATE AGAINST NOT NULL COLUMN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• For a select statement like below,
  
  SELECT xxx, xxx, ...
  FROM T1
  WHERE C1 = ?
  AND (C2 IS NULL OR C2 = xxx)
  AND (C3 IS NULL OR C3 = xxx)
  AND C4 = xxx;
  
  When there is an index created on (C1, C2, C3, C4) and C1 is a
  NOT NULL column, and range list access type ('NR') is used for
  the query, if in the same application, first run of the query is
  with NULL as input for host variable in (C1=?), then the second
  run of the query with a not null input host variable value
  might return 0 rows incorrectly.
  
  KEYWORDS: DB2INCORR/K INCORROUT SQLINCORR SQLINCORROUT SQLNULL
  

Local fix

• BYPASS/CIRCUMVENTION:
  N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 11 and Db2 12 for z/OS users of                      *
  * queries with range-list index scan access                    *
  * type.                                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * An incorrect result of less rows than                        *
  * expected might be returned for a query                       *
  * with range-list index scan access type                       *
  * (ACCESSTYPE='NR') and containing host                        *
  * variables or parameter markers which                         *
  * could be bound with NULL values as                           *
  * input for NOT NULL columns.                                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  An incorrect result of less rows than expected might be
  returned for a query with range-list index scan access type
  (ACCESSTYPE='NR') and containing host variables or parameter
  markers which could be bound with NULL values as input for
  NOT NULL columns.
  Here is an example query to help understand the problem.
    SELECT xxx
    FROM T1
    WHERE C4 = ?
    AND ((C1 = xx)
          OR (C1 = ? AND C2 IS NULL AND C3 = xx) );
  Given table T1, assume there is an index IDX1 created on column
  (C1, C2, C3), column C1 and C4 are NOT NULL, and range-list
  index scan on IDX1 is used for this query. If C1 and C4 are
  bound with null input values for the first run, and then bound
  with non-null values for the second run, less rows than
  expected might be returned for the second run of the same
  query.
  

Problem conclusion

• Db2 code has been modified to return the correct result.
  Additional Keywords: SQLRANGELIST SQLHOSTVAR SQLPMARKER SQLPMK
                       PARAMETERMARKER SQLINCORR SQLINCORROUT
                       DB2INCORR/K INCORROUT SQLNOTNULL
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI74758 UI74759

Modules/Macros

• DSNXRIHD DSNXRIVR DSNXRIHS DSNXRIHB DSNXRIHR DSNXRIHL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI74758

     UP21/04/10 P F104 ¢

• RC10 PSY UI74759

     UP21/04/12 P F104 ¢

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.