PI95824: AN INCORRECT OUTPUT WITH ONLY 1 ROW RETURNED FOR A QUERY WITH BOTH ISNULL PREDICATE AND JOIN PREDICATE ON A SAME COLUMN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• AN INCORRECT OUTPUT WITH ONLY 1 ROW INSTEAD OF AN EXPECTED
  OUTPUT WITH MANY ROWS RETURNED FOR A QUERY WITH AN INDEX WITH
  UNIQUE WHERE NOT NULL CHARACTERISTICS, AND BOTH ISNULL PREDICATE
  & JOIN PREDICATE ON A SAME COLUMN
  ZSA2
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 executing the                            *
  * queries with ON clause and with                              *
  * ISNULL predicate in the WHERE                                *
  * clause.                                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Incorrectout may occur for the                               *
  * queries with ON predicates and                               *
  * with ISNULL predicate in WHERE.                              *
  *                                                              *
  * For the queries above,                                       *
  * (1) The ON predicate and the                                 *
  * ISNULL predicate are on the                                  *
  * same column, and                                             *
  * (2) There is a UWNN index on                                 *
  * the column in (1), and                                       *
  * (3) The index in (2) is used                                 *
  *                                                              *
  * When the conditions above are                                *
  * met, INCORRECTOUT may occur.                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Incorrectout may occur for the queries with ON predicates
  and with ISNULL predicate in WHERE.
  
  For the queries above,
  (1) The ON predicate and the ISNULL predicate are on the
  same column, and
  (2) There is a UWNN index on the column in (1), and
  (3) The index in (2) is used
  
  When the conditions above are met, INCORRECTOUT may occur.
  
  For example of the following query
  
  SELECT T1.C1, T1.C4, T2.C1  FROM T1 LEFT OUTER JOIN T2
  ON T1.C1 = T2.C1
  WHERE
  T1.C1 IS NULL
  AND
  0 = 0
  WITH UR
  ;
  
  CREATE UNIQUE WHERE NOT NULL INDEX IDX1_T1
  ON T1
  (C1 ASC)   ;
  
  If the index IDX1_T1 is used on T1, INCORRECTOUT
  may occur. It's because DB2 may decide that
  to use the index IDX1_T1 can guarantee uniqueness.
  

Problem conclusion

• The problem described above is fixed by recognizing
  the index column is covered by an ISNULL predicate
  thus can't guarantee uniqueness.
  

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:

  UI56691

Modules/Macros

• DSNXOPRS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI56691

     UP18/07/06 P F807

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.