IBM Support

JR31883: A QUERY MAY RETURN INCORRECT RESULTS WHEN ITS OUTER JOIN OPERATOR IS EXPECTED TO OUTPUT AT MOST ONE ROW.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Queries that can encounter this problem are typically similar to
    the following:
    
    SELECT T2.c2
    FROM T1 LEFT JOIN T2 ON T1.c2 = T2.pk
    INNER JOIN T3 ON <InnerJoin_predicates>
    WHERE T1.pk = <literal>;
    
    The query is susceptible to this problem if
    it satisfies all the following conditions:
    
    1. Table T1 has a unique column (or columns). In the example
    above, it is called T1.pk.  The unique key is involved in a
    local equality predicate (or predicates) in the outer WHERE
    clause (for example, T1.pk = <literal>").
    
    2. Table T2 has a unique column (or columns). In the example
    above, it is called T2.pk.  The unique key is involved in an
    equality join predicate (or predicates) in the ON clause of a
    LEFT or RIGHT OUTER JOIN, (for example, "T1.c2 = T2.pk"). In
    the OUTER JOIN operator, T1 is the ROW PRESERVING side, and T2
    is the NULL PRODUCING side.
    
    NOTE:
    * A "row preserving quantifier" (for example, base table) of a
    LEFT or RIGHT OUTER JOIN is the quantifier where the value of a
    row will be returned in the result set even if that row does not
    meet the join predicate.
    
    * A "null producing quantifier" (for example, base table) of
    a LEFT or RIGHT OUTER JOIN is the quantifier whose row returns
    NULL in the result set if that row does not match the join
    predicate.
    
    
    3. The outer SELECT list contains a T2 column, (for example,
    "T2.c2").
    
    To further determine if a query will encounter this problem and
    be incorrectly optimized, generate a query explain plan and see
    if the INNER JOIN predicate is applied before the OUTER JOIN
    predicate.  In the example above, the INNER JOIN predicate is
    the "<InnerJoin_predicates>" and the OUTER JOIN predicate is
    "T1.c2 = T2.pk".
    

Local fix

  • Manually rewrite the query to execute the INNER join before the
    OUTER join.
    
    For example, that means rewriting the above query to:
    SELECT T2.c2
    FROM T1 INNER JOIN T3 ON <InnerJoin_predicates>
    LEFT JOIN T2 ON T1.c2 = T2.pk
    WHERE T1.pk = <literal>.
    

Problem summary

  • Queries that can encounter this problem are typically similar to
    the following:
    
    SELECT T2.c2
    FROM T1 LEFT JOIN T2 ON T1.c2 = T2.pk
    INNER JOIN T3 ON <InnerJoin_predicates>
    WHERE T1.pk = <literal>;
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 9.5 FixPack 4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR31883

  • Reported component name

    DB2 UDB EXE WIN

  • Reported component ID

    5724E4901

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-02-02

  • Closed date

    2009-05-28

  • Last modified date

    2009-05-28

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

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

    JR31886

Fix information

  • Fixed component name

    DB2 UDB EXE WIN

  • Fixed component ID

    5724E4901

Applicable component levels

  • R950 PSN UP

       JR31883

[{"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:
28 May 2009