PI87200: NO ROWS RETURNED FOR QUERY REFERENCING A VARBINARY COLUMN WITH UNICODE, OR A QUERY USING INDEX ON EXPRESSION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Incorrect output can occur for a query referencing a VARBINARY
  column with UNICODE, or a query using index-on-expression
  when running on function mode V12R1M100.
  No rows are returned.                                 (d49314)
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of an SQL          *
  *                 statement containing a materialized table    *
  *                 expression, or a view and a predicate that   *
  *                 matches an expression-based index is         *
  *                 pushed down.                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: Incorrect output may happen for an      *
  *                      SQL statement satisfying all of the     *
  *                      following conditions:                   *
  *                      1. It contains a materialized table     *
  *                      expression, or a view.                  *
  *                      2. The select list of the               *
  *                      materialized table expression           *
  *                      or view contains an expression          *
  *                      that matches an expression-based        *
  *                      index.                                  *
  *                      3. It contains a predicate which is     *
  *                      pushed down into the materialized       *
  *                      table expression or view and the        *
  *                      column in the local predicate is        *
  *                      derived from the select list item       *
  *                      that matches to the expression-based    *
  *                      index.                                  *
  *                                                              *
  *                      This problem is fixed in DB2 12 by APAR *
  *                      PI82830.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An example is shown below:
  
  CREATE TABLE T1(C1 VARCHAR(200)    WITH DEFAULT,
                  C2 VARCHAR(200)    WITH DEFAULT ) ;
  
  INSERT INTO T1 VALUES('zz', 'zz');
  INSERT INTO T1 VALUES('ZZ', 'ZZ');
  
  CREATE INDEX IX2 ON T1(C1, SUBSTR(C2, 1, 2));
  SELECT T1.C1, T2.C2
   FROM T1,
        (SELECT SUBSTR(C2, 1, 2) AS C2,
               (SELECT 1 FROM SYSIBM.SYSDUMMY1)
         FROM T1) AS T2
    WHERE T2.C2 >= 'aa'
      AND T1.C1 >= 'aa'
      AND T2.C2 = T1.C2
  ;
  
  DB2 did not correctly process the aforementioned SQL
  and resulted in incorrect output.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement.
  
  Additional Keywords: SQLVIEW SQLTABLEEXPR SQLINDEXONEXP
                       SQLPREDPUSHDOWN SQLJOIN DB2INCORR/K
  

Temporary fix

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

Comments

APAR Information

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

  PI82830

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

  UI51071

Modules/Macros

• DSNXOVP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI51071

     UP17/10/30 P F710 Ž

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.