PM75486: INCORROUT LESS ROWS FOUND WHEN QUERY ON VIEW CONTAINS LIKE PREDICATE AND THE VIEW HAS UNION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Error Description:
  Incorrect output (less rows found) may happen when a query on a
  view contains the LIKE predicate and the VIEW has a UNION in its
  definition.  This incorrout output is illustrated below:
  
  In view TESTTB the column COL1 contains 13 rows of data 'woody'.
  The following two queries returns different results.
  
  "SELECT * FROM  TESTTB WHERE COL1 LIKE 'woody';"
  returns 7 rows.
  
  "SELECT * FROM  TESTTB WHERE COL1 = 'woody';"
  returns 13 rows.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and 10 for z/OS users of an SQL    *
  *                 statement that contains a table expression   *
  *                 or view with a set operator and a LIKE       *
  *                 predicate matching to an index on            *
  *                 expression.                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1. It contains a table expression or    *
  *                         view with a set operator, and the    *
  *                         table expression or view is          *
  *                         materialized.                        *
  *                      2. It contains a LIKE predicate that    *
  *                         can be pushed down to the            *
  *                         materialized table expression or     *
  *                         view.                                *
  *                      3. After push down, the LIKE            *
  *                         predicate contains an expression,    *
  *                         which matches to an index on         *
  *                         expression.                          *
  *                      4. The expressions in the different     *
  *                         set branches return data with        *
  *                         different nullability.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Incorrect output could happen for an SQL statement that
  satisfies all of the following conditions:
  
  1. It contains a table expression or view with a set operator,
     and the table expression or view is materialized.
  2. It contains a LIKE predicate that can be pushed down to the
     materialized table expression or view.
  3. After push down, the LIKE predicate contains an expression,
     which matches to an index on expression.
  4. The expressions in the different set branches return data
     with different nullability.
  
  An examples is shown below:
  
  CREATE TABLE T1 (
     C1 VARCHAR(10) NOT NULL,
     C2 VARCHAR(10)
  )
  CCSID UNICODE
  VOLATILE;
  
  CREATE INDEX IX1 ON T1 (
    LOWER(C1, 'De_DE')
  )
  NOT PADDED;
  
  INSERT INTO T1 VALUES('ABC', 'ABC');
  INSERT INTO T1 VALUES('ABC', 'ABC');
  
  SELECT *
  FROM (SELECT LOWER(C1, 'De_DE'), 1 FROM T1
        UNION
        SELECT LOWER(C2, 'De_DE'), 2 FROM T1
        ) TX(C1, C2)
  WHERE TX.C1 LIKE 'abc';
  
  The query above should return 2 rows, while it only returns 1
  row.
  
  
  DB2 did not correctly process the aforementioned SQL statements,
  which caused the incorrect output.
  

Problem conclusion

• DB2 has been modified to correctly process the aforementioned
  SQL statement, so there will be no incorrect output.
  
  Additional keywords: SQLUNION SQLPREDPUSHDOWN SQLINDEXONEXP
                       SQLINCORR INCORROUT SQLINCORROUT
                       DB2INCORR/K SQLLIKE SQLINTERSECT
                       SQLINTERSECTALL SQLEXCEPT SQLEXCEPTALL
                       SQLUNIONALL SQLTABLEEXPR SQLVIEW SQLCTE
  

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:

  UK90215 UK90216

Modules/Macros

• DSNXOW2D
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK90215

     UP12/12/27 P F212

• R910 PSY UK90216

     UP12/12/27 P F212

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.