From 870ac96b2e108657801bba703b0767c3c5d38c88 Mon Sep 17 00:00:00 2001 From: Nitin Motiani Date: Wed, 21 Oct 2026 06:40:31 +0000 Subject: [PATCH v2] Keep all cloned outer-join clauses When reconsider_outer_join_clauses() derives INNERVAR = CONSTANT from an outer-join clause, it replaces the clause with a constant-TRUE dummy. That is unsafe for a clone made for outer join identity 3: the clone can be evaluated above a pushed-down outer join that has already null-extended the OUTERVAR, but whose relid is not yet in the join's relids. The original strict clause is needed there to reject the null-extended rows. To fix, make the constant-TRUE dummy only for clauses that are not clones, and throw a clone back as-is, as v15 and earlier did. This can leave a redundant join filter in some plans, as seen in an existing regression test. Suggested-by: Richard Guo Reported-by: Viktor Leis Discussion: https://postgr.es/m/19765-5778b18918040583@postgresql.org Backpatch-through: 16 --- src/backend/optimizer/path/equivclass.c | 18 ++++++++++- src/test/regress/expected/join.out | 40 ++++++++++++++++++++++++- src/test/regress/sql/join.sql | 17 +++++++++++ 3 files changed, 73 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 553d9d13dc5..5b9091dedb8 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -2150,7 +2150,11 @@ create_join_clause(PlannerInfo *root, * seen as a clauseless join and avoided during join order searching. * We handle this by generating a constant-TRUE clause that is marked with * the same required_relids etc as the removed outer-join clause, thus - * making it a join clause between the correct relations. + * making it a join clause between the correct relations. But we don't do + * that for cloned clauses (see deconstruct_distribute_oj_quals): a clone may + * be evaluated above a pushed-down outer join that has already null-extended + * the OUTERVAR, whose relid is not yet included in the join's relids. There + * we need the original strict clause to reject the null-extended rows. */ void reconsider_outer_join_clauses(PlannerInfo *root) @@ -2176,6 +2180,12 @@ reconsider_outer_join_clauses(PlannerInfo *root) /* remove it from the list */ root->left_join_clauses = foreach_delete_current(root->left_join_clauses, cell); + /* throw back a cloned clause as-is (see notes above) */ + if (rinfo->has_clone || rinfo->is_clone) + { + distribute_restrictinfo_to_rels(root, rinfo); + continue; + } /* throw back a dummy replacement clause (see notes above) */ rinfo = make_restrictinfo(root, (Expr *) makeBoolConst(true, false), @@ -2204,6 +2214,12 @@ reconsider_outer_join_clauses(PlannerInfo *root) /* remove it from the list */ root->right_join_clauses = foreach_delete_current(root->right_join_clauses, cell); + /* throw back a cloned clause as-is (see notes above) */ + if (rinfo->has_clone || rinfo->is_clone) + { + distribute_restrictinfo_to_rels(root, rinfo); + continue; + } /* throw back a dummy replacement clause (see notes above) */ rinfo = make_restrictinfo(root, (Expr *) makeBoolConst(true, false), diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index dee85f92103..58d22a6fade 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6937,6 +6937,43 @@ where t1.f1 = ss2.f1; doh! | 4567890123456789 | 123 | 4567890123456789 | doh! | 4567890123456789 | doh! (1 row) +-- a redundant cloned outer-join clause must not become constant TRUE if it +-- can be evaluated above a pushed-down outer join (bug #19765) +explain (costs off) +select a.a, b.b, c.c, d.d from + (values (1),(2)) as a(a) + left join (values (1),(2)) as b(b) on a.a = b.b + left join (values (7),(2)) as c(c) on b.b = c.c + left join (values (1),(2)) as d(d) on c.c = d.d +where a.a = 1; + QUERY PLAN +-------------------------------------------------------------------- + Nested Loop Left Join + Join Filter: ("*VALUES*_2".column1 = "*VALUES*_3".column1) + -> Nested Loop Left Join + Join Filter: ("*VALUES*_1".column1 = "*VALUES*_2".column1) + -> Nested Loop Left Join + -> Values Scan on "*VALUES*" + Filter: (column1 = 1) + -> Values Scan on "*VALUES*_1" + Filter: (column1 = 1) + -> Values Scan on "*VALUES*_2" + Filter: (column1 = 1) + -> Values Scan on "*VALUES*_3" + Filter: (column1 = 1) +(13 rows) + +select a.a, b.b, c.c, d.d from + (values (1),(2)) as a(a) + left join (values (1),(2)) as b(b) on a.a = b.b + left join (values (7),(2)) as c(c) on b.b = c.c + left join (values (1),(2)) as d(d) on c.c = d.d +where a.a = 1; + a | b | c | d +---+---+---+--- + 1 | 1 | | +(1 row) + explain (verbose, costs off) select 1 from text_tbl as tt1 @@ -6962,6 +6999,7 @@ where tt1.f1 = ss1.c0; Output: tt4.f1 -> Nested Loop Left Join Output: tt4.f1 + Join Filter: (tt3.f1 = tt4.f1) -> Seq Scan on public.text_tbl tt3 Output: tt3.f1 Filter: (tt3.f1 = 'foo'::text) @@ -6979,7 +7017,7 @@ where tt1.f1 = ss1.c0; Output: (tt4.f1) -> Seq Scan on public.text_tbl tt5 Output: tt4.f1 -(32 rows) +(33 rows) select 1 from text_tbl as tt1 diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 437bb062f92..99a0ea93cb7 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2442,6 +2442,23 @@ select * from lateral (select ss1.* from text_tbl t3 limit 1) as ss2 where t1.f1 = ss2.f1; +-- a redundant cloned outer-join clause must not become constant TRUE if it +-- can be evaluated above a pushed-down outer join (bug #19765) +explain (costs off) +select a.a, b.b, c.c, d.d from + (values (1),(2)) as a(a) + left join (values (1),(2)) as b(b) on a.a = b.b + left join (values (7),(2)) as c(c) on b.b = c.c + left join (values (1),(2)) as d(d) on c.c = d.d +where a.a = 1; + +select a.a, b.b, c.c, d.d from + (values (1),(2)) as a(a) + left join (values (1),(2)) as b(b) on a.a = b.b + left join (values (7),(2)) as c(c) on b.b = c.c + left join (values (1),(2)) as d(d) on c.c = d.d +where a.a = 1; + explain (verbose, costs off) select 1 from text_tbl as tt1 --