IBM Support

LI73865: UNEXPECTED RESULTS WHEN SELECTING FROM A VIEW WITH OUTER JOIN AFTER UPGRADE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • This problem happens only to outer join query. The outer join
    operator has local equality predicate on the null producing
    side. In this local predicate, the literal is at the left hand
    side and null producing column is at the right hand side. On top
    of this outer join result, the query has multiple local
    predicates. One sample query is:
    
    SELECT * from (select * from t1 left outer join t2 on t1_c1 =
    t2_c1 and 2 = t2_c2) t where t1_c2 = 100 and t1_c3 = 200;
    
    The problem can be identified by checking if the access plan has
    predicate "2 = t2_c2". If that predicate is gone, the query
    result may be incorrect.
    

Local fix

  • The local fix is to swap the place of local predicate.
    
    
    Change "2 = t2_c2" to "t2_c2 = 2".
    

Problem summary

  • ****************************************************************
    USERS AFFECTED:
         All DB2 v9.5 users performing an outer join query.
    ****************************************************************
    PROBLEM DESCRIPTION:
         Unexpected results when performing outer join.
         Workaround is to swap the local predicate in the query.
    ****************************************************************
    RECOMMENDATION:
         Upgrade to DB2 Version 9.5 fix pack 4 or higher.
    ****************************************************************
    

Problem conclusion

  • First Fixed in DB2 Version 9.5 Fix Pack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI73865

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-11-04

  • Closed date

    2009-05-28

  • Last modified date

    2010-08-30

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

    LI73863

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

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
30 August 2010