IBM Support

JR31886: 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 T LEFT JOIN T2 ON T.c2 = T2.ok
    INNER JOIN T3 ON <InnerJoin_predicates>
    WHERE T.ok = <literal>;
    
    The query is susceptible to this problem if
    it satisfies all the following conditions:
    
    1. Table T has a unique column (or columns). In the example
    above, it is called T.ok.  The unique key is involved in a
    local equality predicate (or predicates) in the outer WHERE
    clause (for example, T.ok = <literal>").
    
    2. Table T2 has a unique column (or columns). In the example
    above, it is called T2.ok.  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, "T.c2 = T2.ok"). In
    the OUTER JOIN operator, T is the "row preserving" quantifier,
    and T2 is the "null producing" quantifier.
    
    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 "T.c2 = T2.ok".
    

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

  • USERS AFFECTED: All users executing queries similar to the one
    described in the ERROR DESCRIPTION.
    PROBLEM DESCRIPTION:
    If this APAR fix is not installed, you might receive incorrect
    results when querying your database.
    PROBLEM SUMMARY:
    This problem only occurs on a certain type of query. To
    determine if you are encountering (or will encounter) the
    problem described in this APAR, refer to the details in the
    ERROR DESCRIPTION.
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 9.1 FixPack 7
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR31886

  • Reported component name

    DB2 UDB EXE WIN

  • Reported component ID

    5724E4901

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-02-02

  • Closed date

    2009-04-01

  • Last modified date

    2009-04-02

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

    JR31883

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

Fix information

  • Fixed component name

    DB2 UDB EXE WIN

  • Fixed component ID

    5724E4901

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]

Document Information

Modified date:
07 October 2021