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

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

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 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