IBM Support

IT08504: 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 10.1.0.5.                             *
    ****************************************************************
    

Problem conclusion

  • The problem was first fixed in DB2 version 10.1.0.5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT08504

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-04-23

  • Closed date

    2015-07-10

  • Last modified date

    2015-07-10

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

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

    IT09419 IT09831

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
10 July 2015