IBM Support

IT09831: INCORRECT RESULT WHEN QUERY HAS LEFT OUTER JOIN AND UNION ALL

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A query might return incorrect query result when it has all the
    following characteristics:
    - The query has a UNION ALL query block, e.g. (select * from
    table1 UNION ALL select * from table2), called uaSubQ.
    - The query has LEFT OUTER JOIN query block, whose row
    preserving side includes base table(s) and null producing side
    is uaSubQ, e.g.  (select ... from table3 LEFT OUTER JOIN uaSubQ
    on ... where <predUaT3>) , called lojSubQ.
    - <predUaT3> is a comparison predicate, which references
    column(s) from both uaSubQ and table3. The predicate expression
    consists of CASE expression that has WHEN clause predicate,
    which tests if the null producing side column is null, e.g.
    "WHEN uaSubQ.column IS NULL".
    
    To find whether a query hits this problem, you can generate
    db2exfmt plan and search if "ROWNUMBER" OnLine Analytical
    Processing (OLAP) function is present in the optimized statement
    section.
    

Local fix

  • Compile query at optimization level 0 or 1.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7.0.11.                             *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 9.7.0.11.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT09831

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-07-02

  • Closed date

    2015-10-06

  • Last modified date

    2015-10-06

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

    IT08504

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN UP

       FIX



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT09831

Modified date: 06 October 2015