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

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

## APAR Information

JR31886

• ### Reported component name

DB2 UDB EXE WIN

5724E4901

910

CLOSED PER

NoPE

YesHIPER

NoSpecatt

2009-02-02

2009-04-01

2009-04-02

## Fix information

• ### Fixed component name

DB2 UDB EXE WIN

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"}]

Modified date:
07 October 2021