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

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM75486

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-22

  • Closed date

    2012-12-11

  • Last modified date

    2013-01-02

  • 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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM75486

Modified date:

2013-01-02

Translate my page

Machine Translation

Content navigation