PM77518: INCORROUT INCORRECT OUTPUT WITH RANGE LIST AND OR PREDICATES

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • No Row Found issue with range list access
    .
    the problem could happen when all of the following are true:
    - Query contains OR predicates
    - Every OR predicate refers to the same table.
    - Every OR predicate has at least one matching predicate.
    - Every OR predicate is mapped to the same index.
    - Range list access chosen (ACCESSTYPE='NR' in the PLAN_TABLE)
    - Two or more legs of the OR can be merged together into a
    single range
    

Local fix

  • use a hint to choose index scan, or other access path, instead
    of NR.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 10 for z/OS who have        *
    *                 queries containing OR predicates where       *
    *                 range-list index scan is chosen              *
    *                 (ACCESSTYPE='NR' in the PLAN_TABLE)          *
    ****************************************************************
    * PROBLEM DESCRIPTION: Incorrect output (no rows or fewer      *
    *                      than expected rows returned) can occur  *
    *                      for a query containing OR predicates    *
    *                      where the predicates are all on the     *
    *                      same table and map to the same index    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Incorrect output (no rows or fewer than expected rows returned)
    can occur for a query containing OR predicates where the
    predicates are all on the same table and map to the same index.
    When multiple overlapping ranges reference some of the same
    literal values but have different operators, incorrect output
    can occur.
    
    For example:
    
    SELECT C1, C2, C3, C4
    FROM T1
    WHERE C1 = 100 AND
          C2 = 'A' AND
          ((C3 >= 'TEST' AND
            C3 <  'ZZZZ') OR
           (C3 >  'TEST' AND
            C3 <= 'ZZZZ'))
    
    An index exists on (C1,C2,C3).
    Ranges (C3 >= 'TEST' AND C3 < 'ZZZZ') and
    (C3 > 'TEST' AND 'C3 <= 'ZZZZ') both reference literal values
    'TEST' and 'ZZZZ' but use different operators.  The operators
    are not handled properly in this situation.
    
    Additional Keywords:
    DB2INCORR/K SQLINCORR SQLINCORROUT INCORROUT RANGELIST SQLOR
    SQLGT SQLGE SQLLT SQLLE
    

Problem conclusion

  • Code is changed to handle the operators correctly in the
    situation described above.
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    PM77518

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-21

  • Closed date

    2012-12-30

  • Last modified date

    2013-02-04

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

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

    UK90662

Modules/Macros

  •    DSNXOSRL
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK90662

       UP13/01/15 P F301

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

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM77518

Modified date:

2013-02-04

Translate my page

Machine Translation

Content navigation