diff options
| -rw-r--r-- | src/backend/optimizer/plan/initsplan.c | 7 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 42 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 17 |
3 files changed, 63 insertions, 3 deletions
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index a7655e4a710..00b2625d342 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -1165,9 +1165,9 @@ make_outerjoininfo(PlannerInfo *root, * For a lower OJ in our RHS, if our join condition does not use the * lower join's RHS and the lower OJ's join condition is strict, we * can interchange the ordering of the two OJs; otherwise we must add - * lower OJ's full syntactic relset to min_righthand. Here, we must - * preserve ordering anyway if either the current join is a semijoin, - * or the lower OJ is either a semijoin or an antijoin. + * the lower OJ's full syntactic relset to min_righthand. Also, we + * must preserve ordering anyway if either the current join or the + * lower OJ is either a semijoin or an antijoin. * * Here, we have to consider that "our join condition" includes any * clauses that syntactically appeared above the lower OJ and below @@ -1184,6 +1184,7 @@ make_outerjoininfo(PlannerInfo *root, { if (bms_overlap(clause_relids, otherinfo->syn_righthand) || jointype == JOIN_SEMI || + jointype == JOIN_ANTI || otherinfo->jointype == JOIN_SEMI || otherinfo->jointype == JOIN_ANTI || !otherinfo->lhs_strict || otherinfo->delay_upper_joins) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index e4f3f22e0b9..ed9ad0e135d 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2284,6 +2284,48 @@ WHERE d.f1 IS NULL; (3 rows) -- +-- regression test for proper handling of outer joins within antijoins +-- +create temp table tt4x(c1 int, c2 int, c3 int); +explain (costs off) +select * from tt4x t1 +where not exists ( + select 1 from tt4x t2 + left join tt4x t3 on t2.c3 = t3.c1 + left join ( select t5.c1 as c1 + from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1 + ) a1 on t3.c2 = a1.c1 + where t1.c1 = t2.c2 +); + QUERY PLAN +--------------------------------------------------------- + Hash Anti Join + Hash Cond: (t1.c1 = t2.c2) + -> Seq Scan on tt4x t1 + -> Hash + -> Merge Right Join + Merge Cond: (t5.c1 = t3.c2) + -> Merge Join + Merge Cond: (t4.c2 = t5.c1) + -> Sort + Sort Key: t4.c2 + -> Seq Scan on tt4x t4 + -> Sort + Sort Key: t5.c1 + -> Seq Scan on tt4x t5 + -> Sort + Sort Key: t3.c2 + -> Merge Left Join + Merge Cond: (t2.c3 = t3.c1) + -> Sort + Sort Key: t2.c3 + -> Seq Scan on tt4x t2 + -> Sort + Sort Key: t3.c1 + -> Seq Scan on tt4x t3 +(24 rows) + +-- -- regression test for problems of the sort depicted in bug #3494 -- create temp table tt5(f1 int, f2 int); diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index d0cf0a041d6..5b65ea8c922 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -448,6 +448,23 @@ LEFT JOIN ( WHERE d.f1 IS NULL; -- +-- regression test for proper handling of outer joins within antijoins +-- + +create temp table tt4x(c1 int, c2 int, c3 int); + +explain (costs off) +select * from tt4x t1 +where not exists ( + select 1 from tt4x t2 + left join tt4x t3 on t2.c3 = t3.c1 + left join ( select t5.c1 as c1 + from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1 + ) a1 on t3.c2 = a1.c1 + where t1.c1 = t2.c2 +); + +-- -- regression test for problems of the sort depicted in bug #3494 -- |