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

DB2 Version 9.5 Fix Pack 4  for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 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 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 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