In a rare scenario, a SELECT statement on a stack of outer
joins may return incorrect result. The following conditions are
required for the problem.
1. There are at least 3 outer joins.
2. The join predicates in the 3 outer joins are equality joins.
3. The top outer join has the next two outer joins as the
null-producing table.
4. The second outer join from the top returns some column from
the null-producing table on the outer join.
5. There is an equality join predicate on the output column in
6. There is an equality predicate on the join column in a WHERE
clause on the outer joins.
7. There is a second equality predicate in a WHERE clause on the
output column in 4.
To demonstrate these conditions, an example is given below:
SET SCHEMA "TEST";
CREATE TABLE "TEST"."T1" (
"OBJECTID" CHAR(10) NOT NULL ,
"OWNER" VARCHAR(10) ,
"CODE1" VARCHAR(5) NOT NULL ,
"CODE2" VARCHAR(5) NOT NULL
);
INSERT INTO "TEST"."T1"
VALUES
('3UZ','USER1','99','KT'),
('9RY','ADMIN','99','KT')
;
CREATE TABLE "TEST"."T2" (
"CODE1" VARCHAR(5) NOT NULL ,
"CODE3" VARCHAR(4)
);
INSERT INTO "TEST"."T2"
VALUES
('99','2013'),
('-1','2013')
;
CREATE TABLE "TEST"."T0" (
"CODE1" VARCHAR(5) NOT NULL ,
"CODE3" VARCHAR(4) NOT NULL
);
INSERT INTO "TEST"."T0" SELECT * FROM "TEST"."T2"
;
SELECT V2.OBJECTID, V2.CODE1, V2.CODE3, V2.OWNER
FROM T0
LEFT OUTER JOIN /* Condition 1: OUTER JOIN #1 */
(
SELECT
T1.OBJECTID,
T1.OWNER,
T1.CODE1,
T2.CODE3 /* Condition 4: A column from the
null-producing table is an output column */
FROM T1
LEFT OUTER JOIN T2 /* Condition 1: OUTER JOIN #2 */
ON T1.CODE1 = T2.CODE1 /* Condition 2: Equality
join predicate */
LEFT OUTER JOIN T2 T3 /* Condition 1: OUTER JOIN #3
*/
ON T1.CODE1 = T3.CODE1 /* Condition 2: Equality
join predicate */
WHERE T1.CODE2 = 'KT'
) AS V2(OBJECTID, OWNER, CODE1, CODE3)
/* Condition 3: T0 LEFT JOIN (T1 LEFT JOIN T2 LEFT JOIN
T2) */
/* The top outer join has the two lower outer
joins as an input on the right table of
its LEFT OUTER JOIN, or, in other word, as
the null-producing table. */
ON T0.CODE1 = V2.CODE1 /* Condition 2: Equality join
predicate */
AND T0.CODE3 = V2.CODE3 /* Condition 5: Equality join
predicate on the column in Condition 4 */
WHERE T0.CODE1 = '99' /* Condition 6: An equality predicate on
the join column in the WHERE clause */
AND T0.CODE3 = '2013' /* Condition 7: An equality predicate
on the column from Condition 4 */
;
The result incorrectly returns the duplicate rows of the first
two records. The correct result is only the first two rows.
OBJECTID CODE1 CODE3 OWNER
---------- ----- ----- ----------
3UZ 99 2013 USER1
9RY 99 2013 ADMIN
9RY 99 2013 ADMIN
3UZ 99 2013 USER1 |
