pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19765: Wrong result from a chain of LEFT JOINs since v16
13+ messages / 4 participants
[nested] [flat]

Commits from this thread:
  2026-10-29 f400289946 4567d170f8 27a53c8573 ba7127f240 78bc3b96cc  Don't replace clone clauses with constant TRUE
* BUG #19765: Wrong result from a chain of LEFT JOINs since v16
@ 2026-10-16 05:35 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: PG Bug reporting form @ 2026-10-16 05:35 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: leis@in.tum.de

The following bug has been logged on the website:

Bug reference:      19765
Logged by:          Viktor Leis
Email address:      leis@in.tum.de
PostgreSQL version: 19rc1
Operating system:   Ubuntu 26.04.1
Description:        

Hi,

The following self-contained query returns a wrong answer on every release
since PostgreSQL 16 (verified on 16.0, 17.9, 18.6 and current master at
3c157ad1167; PostgreSQL 15.0 and 14.7 return the correct answer):

  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
  (1 row)

c.c is correctly null-extended: b.b is 1 and c contains only 7 and 2, so
the third join has no match.  But then "c.c = d.d" is NULL = d.d, which is
never true, so d.d must be NULL as well:

   a | b | c | d
  ---+---+---+---
   1 | 1 |   |
  (1 row)

Ordinary tables behave the same way; the VALUES lists are only for brevity.

EXPLAIN shows that all three join conditions have disappeared, leaving
three unqualified nested-loop left joins:

   Nested Loop Left Join
     ->  Values Scan on "*VALUES*"
           Filter: (column1 = 1)
     ->  Nested Loop Left Join
           ->  Nested Loop Left Join
                 ->  Values Scan on "*VALUES*_1"
                       Filter: (column1 = 1)
                 ->  Values Scan on "*VALUES*_2"
                       Filter: (column1 = 1)
           ->  Values Scan on "*VALUES*_3"
                 Filter: (column1 = 1)


Best regards,
Viktor Leis








^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-16 13:11 ` Tender Wang <tndrwang@gmail.com>
  2026-10-16 14:43   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Tender Wang @ 2026-10-16 13:11 UTC (permalink / raw)
  To: leis@in.tum.de; pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> 于2026年10月16日周五 17:50写道:
>
> EXPLAIN shows that all three join conditions have disappeared, leaving
> three unqualified nested-loop left joins:
>
>    Nested Loop Left Join
>      ->  Values Scan on "*VALUES*"
>            Filter: (column1 = 1)
>      ->  Nested Loop Left Join
>            ->  Nested Loop Left Join
>                  ->  Values Scan on "*VALUES*_1"
>                        Filter: (column1 = 1)
>                  ->  Values Scan on "*VALUES*_2"
>                        Filter: (column1 = 1)
>            ->  Values Scan on "*VALUES*_3"
>                  Filter: (column1 = 1)

I think the problem is in the interaction between constant propagation
in reconsider_outer_join_clauses() and outer-join identity 3.

To support join reordering, deconstruct_distribute_oj_quals()
generates variants of the join quals with different nullingrels
markings.
The least-nulled variants allow reconsider_outer_join_clause() to
propagate the constant through the chain: a=1 implies a restriction
b=1, then c=1, and finally d=1.
The corresponding join quals are replaced with constant-TRUE dummy clauses.

However, a restriction on the scan of C does not guarantee that c=1
still holds after C has been null-extended. In the chosen join order,

A LEFT JOIN ((B LEFT JOIN C) LEFT JOIN D)

the B/C join produces a row with c=NULL, since no C row passes c=1.
The original c=d qual would reject a match with D, whereas its TRUE
replacement allows d=1 to match.

Thus, deriving the inner-side restriction is not sufficient to
establish that the join qual is redundant.
In particular, replacing a strict qual with TRUE can invalidate the
reasoning that allowed the outer joins to commute.

I am considering guarding this optimization when a commuting lower
outer join could null-extend the outer operand.
Specifically, I would examine the joins in sjinfo->commute_below_l and
retain the original qual if one of their syn_righthand sets overlaps
the outer operand's relids.

It might also be possible to retain constant propagation while keeping
the original join qual, but that would require separating the
deduction from the decision to discard the qual.


-- 
Thanks,
Tender Wang






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
@ 2026-10-16 14:43   ` Nitin Motiani <nitinmotiani@google.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Nitin Motiani @ 2026-10-16 14:43 UTC (permalink / raw)
  To: Tender Wang <tndrwang@gmail.com>; +Cc: leis@in.tum.de; pgsql-bugs@lists.postgresql.org

Hi,

On Fri, Oct 16, 2026 at 6:41 PM Tender Wang <tndrwang@gmail.com> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> 于2026年10月16日周五 17:50写道:
> >
> > EXPLAIN shows that all three join conditions have disappeared, leaving
> > three unqualified nested-loop left joins:
> >
> >    Nested Loop Left Join
> >      ->  Values Scan on "*VALUES*"
> >            Filter: (column1 = 1)
> >      ->  Nested Loop Left Join
> >            ->  Nested Loop Left Join
> >                  ->  Values Scan on "*VALUES*_1"
> >                        Filter: (column1 = 1)
> >                  ->  Values Scan on "*VALUES*_2"
> >                        Filter: (column1 = 1)
> >            ->  Values Scan on "*VALUES*_3"
> >                  Filter: (column1 = 1)
>
> I think the problem is in the interaction between constant propagation
> in reconsider_outer_join_clauses() and outer-join identity 3.
>
Thanks for the analysis. I also found the same cause with LLM help.

>
> I am considering guarding this optimization when a commuting lower
> outer join could null-extend the outer operand.
> Specifically, I would examine the joins in sjinfo->commute_below_l and
> retain the original qual if one of their syn_righthand sets overlaps
> the outer operand's relids.
>

It seems that this might add an extra filter for a query like this

select b.b, c.c, d.d from (values (1),(2)) as 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 b.b = 1;

I considered instead keeping the qual if the clone is incompatible
with some other outer join whose commute_below_l is non-empty. It
seemed like a narrower check that should preserve the optimization in
the above scenario.

I'm attaching the patch with this idea. Please take a look and let me
know what you think.

Thanks & Regards,
Nitin Motiani
Google

Attachments:

v1-0001-Fix-wrong-results-from-cloned-outer-join-clauses-.patch (application/x-patch, 7.3K) download From 274e6cb3d13581b2883d1babbffe7d8f07f84e13 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <nitinmotiani@google.com> Date: Fri, 16 Oct 2026 13:44:20 +0000 Subject: [PATCH v1] Fix wrong results from cloned outer-join clauses replaced by TRUE 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 if 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. Keep the original clause in that case. This can only happen if the clone is incompatible with an outer join that has a nonempty commute_below_l. Reported-by: Viktor Leis <leis@in.tum.de> Discussion: https://postgr.es/m/19765-5778b18918040583@postgresql.org Backpatch-through: 16 --- src/backend/optimizer/path/equivclass.c | 45 ++++++++++++++++++++++++- src/test/regress/expected/join.out | 36 ++++++++++++++++++++ src/test/regress/sql/join.sql | 17 ++++++++++ 3 files changed, 97 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 553d9d13dc5..3f86db0fff8 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -75,6 +75,8 @@ static RestrictInfo *create_join_clause(PlannerInfo *root, EquivalenceMember *leftem, EquivalenceMember *rightem, EquivalenceClass *parent_ec); +static bool clone_needs_strict_clause(PlannerInfo *root, + OuterJoinClauseInfo *ojcinfo); static bool reconsider_outer_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo, bool outer_on_left); @@ -2150,7 +2152,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 that's not + * safe for a cloned clause (see deconstruct_distribute_oj_quals) that 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 +2182,12 @@ reconsider_outer_join_clauses(PlannerInfo *root) /* remove it from the list */ root->left_join_clauses = foreach_delete_current(root->left_join_clauses, cell); + /* keep the original clause if needed (see notes above) */ + if (clone_needs_strict_clause(root, ojcinfo)) + { + 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 +2216,12 @@ reconsider_outer_join_clauses(PlannerInfo *root) /* remove it from the list */ root->right_join_clauses = foreach_delete_current(root->right_join_clauses, cell); + /* keep the original clause if needed (see notes above) */ + if (clone_needs_strict_clause(root, ojcinfo)) + { + distribute_restrictinfo_to_rels(root, rinfo); + continue; + } /* throw back a dummy replacement clause (see notes above) */ rinfo = make_restrictinfo(root, (Expr *) makeBoolConst(true, false), @@ -2269,6 +2287,31 @@ reconsider_outer_join_clauses(PlannerInfo *root) } } +/* + * clone_needs_strict_clause + * Can this cloned outer-join clause be evaluated above a pushed-down outer + * join whose relid has been deferred? That's possible only for an outer + * join it is incompatible with that can be pushed into the RHS of a lower + * outer join, ie has nonempty commute_below_l (see + * add_outer_joins_to_relids). + */ +static bool +clone_needs_strict_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo) +{ + RestrictInfo *rinfo = ojcinfo->rinfo; + + if (!rinfo->has_clone && !rinfo->is_clone) + return false; + foreach_node(SpecialJoinInfo, othersj, root->join_info_list) + { + if (othersj != ojcinfo->sjinfo && othersj->ojrelid != 0 && + bms_is_member(othersj->ojrelid, rinfo->incompatible_relids) && + !bms_is_empty(othersj->commute_below_l)) + return true; + } + return false; +} + /* * reconsider_outer_join_clauses for a single LEFT/RIGHT JOIN clause * diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 042060f3aa4..67094ba331a 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6776,6 +6776,42 @@ 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 + -> Values Scan on "*VALUES*" + Filter: (column1 = 1) + -> Nested Loop Left Join + -> Values Scan on "*VALUES*_1" + Filter: (column1 = 1) + -> Values Scan on "*VALUES*_2" + Filter: (column1 = 1) + -> Values Scan on "*VALUES*_3" + Filter: (column1 = 1) +(12 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 diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index c373cdeaa52..68e85f84206 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2394,6 +2394,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 --
^ permalink raw reply [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
@ 2026-10-16 15:04   ` Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Richard Guo @ 2026-10-16 15:04 UTC (permalink / raw)
  To: Tender Wang <tndrwang@gmail.com>; +Cc: leis@in.tum.de; pgsql-bugs@lists.postgresql.org

On Fri, Oct 16, 2026 at 10:11 PM Tender Wang <tndrwang@gmail.com> wrote:
> Thus, deriving the inner-side restriction is not sufficient to
> establish that the join qual is redundant.
> In particular, replacing a strict qual with TRUE can invalidate the
> reasoning that allowed the outer joins to commute.

I came to the same conclusion.  To elaborate on it a bit more, the
problem is that when an outer join gets commuted into the RHS of a
lower outer join, we don't add its relid until the lower join is
formed (see add_outer_joins_to_relids).  So in the meantime, its RHS
Vars can already have gone to NULL without having its nulling bit.  If
a higher outer join that commutes with it gets joined on top at that
point, clone selection sees that the relid is missing and picks the
least-nulled version of its qual.

Normally that's fine, because the qual is strict and will reject the
null-extended rows anyway, which is what identity 3 depends on.  But
once we've replaced that qual with a constant-TRUE dummy, nothing
rejects them, and we get bogus matches.

I think we can fix this by only making the TRUE dummy for clauses that
are not clones.  For a clone, just throw the original clause back, as
v15 and earlier did.

- Richard






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
@ 2026-10-17 07:35     ` Nitin Motiani <nitinmotiani@google.com>
  2026-10-21 10:29       ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Nitin Motiani @ 2026-10-17 07:35 UTC (permalink / raw)
  To: Richard Guo <guofenglinux@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; leis@in.tum.de; pgsql-bugs@lists.postgresql.org

On Fri, Oct 16, 2026 at 8:34 PM Richard Guo <guofenglinux@gmail.com> wrote:
>
> I think we can fix this by only making the TRUE dummy for clauses that
> are not clones.  For a clone, just throw the original clause back, as
> v15 and earlier did.
>

I had first tried this change and it worked but for one of the tests
in join.sql, it also added a redundant filter.

On Fri, Oct 16, 2026 at 8:13 PM Nitin Motiani <nitinmotiani@google.com> wrote:
>
> select b.b, c.c, d.d from (values (1),(2)) as 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 b.b = 1;
>

And for the 3-join query like this, it looks like we will have an
extra filter. So in my patch [1], I skipped the TRUE dummy option if
the clone is incompatible with some other outer join whose
commute_below_l is non-empty as only such an outer join can have its
relid deferred (add_outer_joins_to_relids). That approach also seems
to work and avoids adding the extra filter in the above scenario.

Let me know what you think of this approach compared to the simpler
solution of removing the TRUE dummy for all the cases with clones.

Thanks,
Nitin Motiani
Google

[1] https://www.postgresql.org/message-id/CAH5HC94V8kr1OcSDkzh2kGTNE6PU3ZXeF_G1LZ76BnE0E-9gnQ%40mail.gma...






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-21 10:29       ` Nitin Motiani <nitinmotiani@google.com>
  2026-10-22 01:50         ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Nitin Motiani @ 2026-10-21 10:29 UTC (permalink / raw)
  To: Richard Guo <guofenglinux@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; leis@in.tum.de; pgsql-bugs@lists.postgresql.org

Hi,

I'm attaching the rebased patch.

On Sat, Oct 17, 2026 at 1:05 PM Nitin Motiani <nitinmotiani@google.com> wrote:
>
> On Fri, Oct 16, 2026 at 8:34 PM Richard Guo <guofenglinux@gmail.com> wrote:
> >
> > I think we can fix this by only making the TRUE dummy for clauses that
> > are not clones.  For a clone, just throw the original clause back, as
> > v15 and earlier did.
> >
>
> I had first tried this change and it worked but for one of the tests
> in join.sql, it also added a redundant filter.
>

I am also attaching a patch with this simpler approach you suggested.
(v2-0001-Keep-all-cloned-outer-join-clauses.patch.txt). This keeps the
original clause for all clones. It also changes one existing plan in
join.out, where a redundant "Join Filter: (tt3.f1 = tt4.f1)" appears.

And I'm attaching the rebased patch for my narrower check from v1
(v2-0001-Keep-cloned-outer-join-clauses-only-where-needed.patch). This
keeps the original clause only if the clone is incompatible with
another outer join whose commute_below_l is non-empty). No changes to
existing regression output.

Please take a look and let me know which alternative seems better to you.


Thanks & Regards,
Nitin Motiani
Google

Attachments:

v2-0001-Keep-all-cloned-outer-join-clauses.patch.txt (text/plain, 6.6K) download From 870ac96b2e108657801bba703b0767c3c5d38c88 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <nitinmotiani@google.com> 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 <guofenglinux@gmail.com> Reported-by: Viktor Leis <leis@in.tum.de> 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 --
v2-0001-Keep-cloned-outer-join-clauses-only-where-needed.patch (application/x-patch, 7.3K) download From c921d10d9cc50236a11e1e8f3ceaeebab1aaecb3 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <nitinmotiani@google.com> Date: Wed, 21 Oct 2026 10:14:25 +0000 Subject: [PATCH v2] Keep cloned outer-join clauses only where needed 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 if 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. Keep the original clause in that case. This can only happen if the clone is incompatible with an outer join that has a nonempty commute_below_l. Reported-by: Viktor Leis <leis@in.tum.de> Discussion: https://postgr.es/m/19765-5778b18918040583@postgresql.org Backpatch-through: 16 --- src/backend/optimizer/path/equivclass.c | 45 ++++++++++++++++++++++++- src/test/regress/expected/join.out | 36 ++++++++++++++++++++ src/test/regress/sql/join.sql | 17 ++++++++++ 3 files changed, 97 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 553d9d13dc5..3f86db0fff8 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -75,6 +75,8 @@ static RestrictInfo *create_join_clause(PlannerInfo *root, EquivalenceMember *leftem, EquivalenceMember *rightem, EquivalenceClass *parent_ec); +static bool clone_needs_strict_clause(PlannerInfo *root, + OuterJoinClauseInfo *ojcinfo); static bool reconsider_outer_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo, bool outer_on_left); @@ -2150,7 +2152,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 that's not + * safe for a cloned clause (see deconstruct_distribute_oj_quals) that 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 +2182,12 @@ reconsider_outer_join_clauses(PlannerInfo *root) /* remove it from the list */ root->left_join_clauses = foreach_delete_current(root->left_join_clauses, cell); + /* keep the original clause if needed (see notes above) */ + if (clone_needs_strict_clause(root, ojcinfo)) + { + 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 +2216,12 @@ reconsider_outer_join_clauses(PlannerInfo *root) /* remove it from the list */ root->right_join_clauses = foreach_delete_current(root->right_join_clauses, cell); + /* keep the original clause if needed (see notes above) */ + if (clone_needs_strict_clause(root, ojcinfo)) + { + distribute_restrictinfo_to_rels(root, rinfo); + continue; + } /* throw back a dummy replacement clause (see notes above) */ rinfo = make_restrictinfo(root, (Expr *) makeBoolConst(true, false), @@ -2269,6 +2287,31 @@ reconsider_outer_join_clauses(PlannerInfo *root) } } +/* + * clone_needs_strict_clause + * Can this cloned outer-join clause be evaluated above a pushed-down outer + * join whose relid has been deferred? That's possible only for an outer + * join it is incompatible with that can be pushed into the RHS of a lower + * outer join, ie has nonempty commute_below_l (see + * add_outer_joins_to_relids). + */ +static bool +clone_needs_strict_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo) +{ + RestrictInfo *rinfo = ojcinfo->rinfo; + + if (!rinfo->has_clone && !rinfo->is_clone) + return false; + foreach_node(SpecialJoinInfo, othersj, root->join_info_list) + { + if (othersj != ojcinfo->sjinfo && othersj->ojrelid != 0 && + bms_is_member(othersj->ojrelid, rinfo->incompatible_relids) && + !bms_is_empty(othersj->commute_below_l)) + return true; + } + return false; +} + /* * reconsider_outer_join_clauses for a single LEFT/RIGHT JOIN clause * diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index dee85f92103..ac377b72fc4 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6937,6 +6937,42 @@ 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 + -> Values Scan on "*VALUES*" + Filter: (column1 = 1) + -> Nested Loop Left Join + -> Values Scan on "*VALUES*_1" + Filter: (column1 = 1) + -> Values Scan on "*VALUES*_2" + Filter: (column1 = 1) + -> Values Scan on "*VALUES*_3" + Filter: (column1 = 1) +(12 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 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 --
^ permalink raw reply [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-21 10:29       ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-22 01:50         ` Richard Guo <guofenglinux@gmail.com>
  2026-10-23 03:42           ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-23 07:40           ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Richard Guo @ 2026-10-22 01:50 UTC (permalink / raw)
  To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Tender Wang <tndrwang@gmail.com>; leis@in.tum.de; pgsql-bugs@lists.postgresql.org

On Wed, Oct 21, 2026 at 7:30 PM Nitin Motiani <nitinmotiani@google.com> wrote:
> Please take a look and let me know which alternative seems better to you.

Thanks for the patch!  It's on my radar, but I'm currently occupied
with a few other items.  I'll do my best to get to it before the minor
release.

- Richard






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-21 10:29       ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-22 01:50         ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
@ 2026-10-23 03:42           ` Tender Wang <tndrwang@gmail.com>
  2026-10-23 06:47             ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Tender Wang @ 2026-10-23 03:42 UTC (permalink / raw)
  To: Richard Guo <guofenglinux@gmail.com>; +Cc: Nitin Motiani <nitinmotiani@google.com>; leis@in.tum.de; pgsql-bugs@lists.postgresql.org

Richard Guo <guofenglinux@gmail.com> 于2026年10月22日周四 09:50写道:
>
> On Wed, Oct 21, 2026 at 7:30 PM Nitin Motiani <nitinmotiani@google.com> wrote:
> > Please take a look and let me know which alternative seems better to you.
>
> Thanks for the patch!  It's on my radar, but I'm currently occupied
> with a few other items.  I'll do my best to get to it before the minor
> release.

Thanks for the patch. The v2 patch fixes the wrong-result case, but
while reviewing it I noticed that it can retain more join clauses than
necessary.

For example, I forced the following join order using pg_plan_advice:

JOIN_ORDER(t1 t2 (t3 t4))

for the query:

select t1.a, t2.b, t3.c, t4.d
from t1
left join t2 on t1.a = t2.b
left join t3 on t2.b = t3.c
left join t4 on t3.c = t4.d
where t1.a = 1;

With v2, the relevant part of the plan is:

                QUERY PLAN
-------------------------------------------
 Hash Left Join
   Hash Cond: (t2.b = t3.c)
   ->  Nested Loop Left Join
         ->  Seq Scan on t1
               Filter: (a = 1)
         ->  Materialize
               ->  Seq Scan on t2
                     Filter: (b = 1)
   ->  Hash
         ->  Hash Left Join
               Hash Cond: (t3.c = t4.d)
               ->  Seq Scan on t3
                     Filter: (c = 1)
               ->  Hash
                     ->  Seq Scan on t4
                           Filter: (d = 1)
 Supplied Plan Advice:
   JOIN_ORDER(t1 t2 (t3 t4)) /* matched */


The (t2.b = t3.c) condition is necessary at this join level: t2.b may
already have been null-extended by the t1/t2 outer join.

However, (t3.c = t4.d) is redundant in the directly formed t3/t4 join.
At that level, t3.c is still the value from the preserved base
relation, and constant propagation has already restricted both sides
to 1.
Thus, retaining every affected cloned clause whenever a commuting
outer join exists is somewhat conservative.

I think the underlying issue is that the redundancy of such a clause
is placement-dependent. Constant propagation itself is valid, but it
does not imply that the original strict join clause can be discarded
at every join level at which its clone might be applied.

The attached v3 patch therefore separates those two decisions.

During reconsider_outer_join_clauses(), constant propagation still
proceeds as before.
For a cloned clause whose outer operand can be null-extended by a
commuting lower outer join, however, the patch retains the original
RestrictInfo and records the relevant outer-join relids instead of
replacing the clause unconditionally with a constant-TRUE dummy.

When constructing the restrictlist for a particular joinrel, the
original clause is included only when a recorded outer join: has both
of its minimum inputs available, meaning that it may already have
performed null-extension;

does not yet have its outer-join relid in the available relids; and is
still waiting for a commuting lower outer join to be completed.

This is the state in which the outer operand may already be NULL even
though the least-nulled clone remains applicable, so the strict join
clause is still required. At other join levels the retained clause is
omitted as redundant.

With v3 version:

the original wrong-result query returns 1 | 1 | NULL | NULL;

for the forced t1 t2 (t3 t4) join order, t2.b = t3.c is retained;

t3.c = t4.d is removed from the direct t3/t4 join; and the tree builds
successfully with assertions enabled.

The v3 patch is attached.
Thoughts?


-- 
Thanks,
Tender Wang

Attachments:

v3-0001-Fix-wrong-result.patch (text/plain, 7.8K) download From 41a72c0f2f6ae7f53d54ac67a271ccc993ab5ae1 Mon Sep 17 00:00:00 2001 From: Tender Wang <tndrwang@gmail.com> Date: Fri, 23 Oct 2026 11:29:58 +0800 Subject: [PATCH v3] Fix wrong result --- src/backend/optimizer/path/equivclass.c | 58 ++++++++++++++++++++++++- src/backend/optimizer/util/relnode.c | 38 +++++++++++++++- src/include/nodes/pathnodes.h | 5 +++ 3 files changed, 99 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 0bacc24b58c..147637d0a90 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -75,6 +75,9 @@ static RestrictInfo *create_join_clause(PlannerInfo *root, EquivalenceMember *leftem, EquivalenceMember *rightem, EquivalenceClass *parent_ec); +static Relids find_deferred_const_oj_relids(PlannerInfo *root, + OuterJoinClauseInfo *ojcinfo, + bool outer_on_left); static bool reconsider_outer_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo, bool outer_on_left); @@ -2155,7 +2158,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 that's not + * safe for a cloned clause (see deconstruct_distribute_oj_quals) that 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) @@ -2181,6 +2188,14 @@ reconsider_outer_join_clauses(PlannerInfo *root) /* remove it from the list */ root->left_join_clauses = foreach_delete_current(root->left_join_clauses, cell); + /* keep the original clause if it can still be needed */ + rinfo->const_oj_relids = + find_deferred_const_oj_relids(root, ojcinfo, true); + if (rinfo->const_oj_relids) + { + distribute_restrictinfo_to_rels(root, rinfo); + continue; + } /* throw back a dummy replacement clause (see notes above) */ rinfo = make_restrictinfo(root, (Expr *) makeBoolConst(true, false), @@ -2209,6 +2224,14 @@ reconsider_outer_join_clauses(PlannerInfo *root) /* remove it from the list */ root->right_join_clauses = foreach_delete_current(root->right_join_clauses, cell); + /* keep the original clause if it can still be needed */ + rinfo->const_oj_relids = + find_deferred_const_oj_relids(root, ojcinfo, false); + if (rinfo->const_oj_relids) + { + distribute_restrictinfo_to_rels(root, rinfo); + continue; + } /* throw back a dummy replacement clause (see notes above) */ rinfo = make_restrictinfo(root, (Expr *) makeBoolConst(true, false), @@ -2274,6 +2297,39 @@ reconsider_outer_join_clauses(PlannerInfo *root) } } +/* + * find_deferred_const_oj_relids + * Find outer joins that can make this clause necessary even though + * constant propagation normally makes it redundant. + * + * Such an outer join must be able to null the clause's outer operand, and + * its relid must be one with which this clone is incompatible. A nonempty + * commute_below_l means that add_outer_joins_to_relids() can defer that + * relid after the join has physically null-extended the operand. + */ +static Relids +find_deferred_const_oj_relids(PlannerInfo *root, + OuterJoinClauseInfo *ojcinfo, + bool outer_on_left) +{ + RestrictInfo *rinfo = ojcinfo->rinfo; + Relids outer_relids; + Relids result = NULL; + + if (!rinfo->has_clone && !rinfo->is_clone) + return NULL; + outer_relids = outer_on_left ? rinfo->left_relids : rinfo->right_relids; + foreach_node(SpecialJoinInfo, othersj, root->join_info_list) + { + if (othersj != ojcinfo->sjinfo && othersj->ojrelid != 0 && + bms_is_member(othersj->ojrelid, rinfo->incompatible_relids) && + !bms_is_empty(othersj->commute_below_l) && + bms_overlap(othersj->syn_righthand, outer_relids)) + result = bms_add_member(result, othersj->ojrelid); + } + return result; +} + /* * reconsider_outer_join_clauses for a single LEFT/RIGHT JOIN clause * diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index f2ccba52f20..16c551c97a7 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -66,6 +66,9 @@ static List *build_joinrel_restrictlist(PlannerInfo *root, #ifdef USE_ASSERT_CHECKING static bool no_duplicate_clause_serials(List *clauses); #endif +static bool deferred_oj_makes_clause_needed(PlannerInfo *root, + RestrictInfo *rinfo, + Relids available_relids); static void build_joinrel_joinlist(RelOptInfo *joinrel, RelOptInfo *outer_rel, RelOptInfo *inner_rel); @@ -1453,6 +1456,30 @@ no_duplicate_clause_serials(List *clauses) } #endif +/* + * Is a constant-propagated outer-join clause needed at this level? + * + * The clause is needed when one of the outer joins that can null its outer + * operand has already had both minimum inputs formed, but its OJ relid is + * still absent because a commuting lower outer join has not been completed. + */ +static bool +deferred_oj_makes_clause_needed(PlannerInfo *root, RestrictInfo *rinfo, + Relids available_relids) +{ + foreach_node(SpecialJoinInfo, sjinfo, root->join_info_list) + { + if (!bms_is_member(sjinfo->ojrelid, rinfo->const_oj_relids)) + continue; + if (!bms_is_member(sjinfo->ojrelid, available_relids) && + bms_is_subset(sjinfo->min_lefthand, available_relids) && + bms_is_subset(sjinfo->min_righthand, available_relids) && + !bms_is_subset(sjinfo->commute_below_l, available_relids)) + return true; + } + return false; +} + /* * build_joinrel_restrictlist * build_joinrel_joinlist @@ -1599,7 +1626,10 @@ subbuild_joinrel_restrictlist(PlannerInfo *root, bms_is_subset(rinfo->required_relids, both_input_relids)); } - + if (rinfo->const_oj_relids && + !deferred_oj_makes_clause_needed(root, rinfo, + both_input_relids)) + continue; /* * OK, so add it to the list, being careful to eliminate * duplicates. (Since RestrictInfo nodes in different joinlists @@ -1828,6 +1858,9 @@ get_baserel_parampathinfo(PlannerInfo *root, RelOptInfo *baserel, if (bms_is_member(rinfo->rinfo_serial, pserials)) continue; } + if (rinfo->const_oj_relids && + !deferred_oj_makes_clause_needed(root, rinfo, joinrelids)) + continue; pclauses = lappend(pclauses, rinfo); pserials = bms_add_member(pserials, rinfo->rinfo_serial); @@ -1974,6 +2007,9 @@ get_joinrel_parampathinfo(PlannerInfo *root, RelOptInfo *joinrel, if (bms_is_member(rinfo->rinfo_serial, pserials)) continue; } + if (rinfo->const_oj_relids && + !deferred_oj_makes_clause_needed(root, rinfo, join_and_req)) + continue; pclauses = lappend(pclauses, rinfo); pserials = bms_add_member(pserials, rinfo->rinfo_serial); diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 8615db98a55..9400a368086 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2933,6 +2933,11 @@ typedef struct RestrictInfo /* Relids above which we cannot evaluate the clause (see comment above) */ Relids incompatible_relids; + /* + * For an outer-join clone made redundant by constant propagation, OJ + * relids whose deferred addition can make the original clause necessary. + */ + Relids const_oj_relids; /* If an outer-join clause, the outer-side relations, else NULL: */ Relids outer_relids; -- 2.43.0
^ permalink raw reply [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-21 10:29       ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-22 01:50         ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-23 03:42           ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
@ 2026-10-23 06:47             ` Richard Guo <guofenglinux@gmail.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Richard Guo @ 2026-10-23 06:47 UTC (permalink / raw)
  To: Tender Wang <tndrwang@gmail.com>; +Cc: Nitin Motiani <nitinmotiani@google.com>; leis@in.tum.de, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>

On Fri, Oct 23, 2026 at 12:42 PM Tender Wang <tndrwang@gmail.com> wrote:
> I think the underlying issue is that the redundancy of such a clause
> is placement-dependent. Constant propagation itself is valid, but it
> does not imply that the original strict join clause can be discarded
> at every join level at which its clone might be applied.

Right, the clause is redundant at the directly formed t3/t4 join, and
whether it is redundant depends on where it gets applied.

However, your v3 adds a new field to RestrictInfo, which breaks ABI
and can't be back-patched.

IMO keeping a redundant clause costs little if we mark it as having
selectivity 1.0, and it's no worse than what v15 and earlier did,
which always kept such clauses.  So I think we should accept some
redundant clauses in the bug fix.  The placement-aware redundancy
checks can be a possible improvement for master.

- Richard






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-21 10:29       ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-22 01:50         ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
@ 2026-10-23 07:40           ` Richard Guo <guofenglinux@gmail.com>
  2026-10-23 12:08             ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  1 sibling, 1 reply; 13+ messages in thread

From: Richard Guo @ 2026-10-23 07:40 UTC (permalink / raw)
  To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Tender Wang <tndrwang@gmail.com>; leis@in.tum.de, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>

On Thu, Oct 22, 2026 at 10:50 AM Richard Guo <guofenglinux@gmail.com> wrote:
> Thanks for the patch!  It's on my radar, but I'm currently occupied
> with a few other items.  I'll do my best to get to it before the minor
> release.

I reviewed your v2 patch.  It fixes this bug, but I don't think the
narrower check should rely on incompatible_relids.  AFAIU, that field
is a placement filter: it lists the outer joins above which a clone
must not be evaluated, so that only one version of the clause is
accepted at any join level.  The v2 patch reads it as "the set of
commuting joins whose nulling bit this clone lacks".  That happens to
be true today, because of how deconstruct_distribute_oj_quals() builds
it, but it's not what the field means.

What matters is whether OUTERVAR lacks the nulling bit of a commuting
outer join whose relid can be withheld, and that can be checked
directly on the outer side's relids, which include its nullingrels:

    if ((bms_is_member(othersj->ojrelid, sjinfo->commute_below_l) ||
         bms_is_member(othersj->ojrelid, sjinfo->commute_above_r)) &&
        !bms_is_member(othersj->ojrelid, outer_relids) &&
        !bms_is_empty(othersj->commute_below_l))
        return true;

That said, as Tender showed, the narrower check doesn't avoid all
redundant clauses either.  So I still lean slightly toward simply
keeping all cloned clauses as-is (marked as having selectivity 1.0),
as v15 and earlier did.  For example:

   /* keep a cloned clause as-is */
   if (rinfo->has_clone || rinfo->is_clone)
   {
           /* but the kept clause has no extra selectivity */
           rinfo->norm_selec = 1.0;
           rinfo->outer_selec = 1.0;
           distribute_restrictinfo_to_rels(root, rinfo);
           continue;
   }

It's simpler, and its correctness doesn't depend on mirroring the
rules in add_outer_joins_to_relids().  This is also in line with
commit 716a051aa, where we stopped performing NullTest deduction for
clone clauses because their nullingrel bits may not reflect reality.
I don't think the redundant clauses cost enough to justify the extra
complexity of the narrower check.  But this is a judgment call, and
neither approach is a clear win.

(Cc'ing Tom to have a look.)

- Richard






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-21 10:29       ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-22 01:50         ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-23 07:40           ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
@ 2026-10-23 12:08             ` Nitin Motiani <nitinmotiani@google.com>
  2026-10-26 07:01               ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Nitin Motiani @ 2026-10-23 12:08 UTC (permalink / raw)
  To: Richard Guo <guofenglinux@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; leis@in.tum.de, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>

On Fri, Oct 23, 2026 at 1:10 PM Richard Guo <guofenglinux@gmail.com> wrote:
>
> It's simpler, and its correctness doesn't depend on mirroring the
> rules in add_outer_joins_to_relids().  This is also in line with
> commit 716a051aa, where we stopped performing NullTest deduction for
> clone clauses because their nullingrel bits may not reflect reality.
> I don't think the redundant clauses cost enough to justify the extra
> complexity of the narrower check.  But this is a judgment call, and
> neither approach is a clear win.
>

Thanks for the explanation regarding incompatible_relids and the
selectivity fields. Since the narrow check doesn't cover all the
cases, it does seem better to keep it simple.

Attached is v4 based on your suggestions, which keeps all cloned
outer-join clauses and sets norm_selec = 1.0 and outer_selec = 1.0 on
them. I also added a second test case where the
c/d outer join is written inside the RHS of the b/c outer join, which
also returns wrong results on master.

Regards,
Nitin Motiani
Google

Attachments:

v4-0001-Keep-all-cloned-outer-join-clauses.patch (application/x-patch, 8.8K) download From a8a105890ca680322ea706210833f70a6e2df537 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <nitinmotiani@google.com> Date: Fri, 23 Oct 2026 10:50:08 +0000 Subject: [PATCH v4] 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, marked as having no extra selectivity, 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 <guofenglinux@gmail.com> Reported-by: Viktor Leis <leis@in.tum.de> Discussion: https://postgr.es/m/19765-5778b18918040583@postgresql.org Backpatch-through: 16 --- src/backend/optimizer/path/equivclass.c | 24 +++++++- src/test/regress/expected/join.out | 76 ++++++++++++++++++++++++- src/test/regress/sql/join.sql | 33 +++++++++++ 3 files changed, 131 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 0bacc24b58c..b2e51a29fb2 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -2155,7 +2155,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) @@ -2181,6 +2185,15 @@ 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) + { + /* but the kept clause has no extra selectivity */ + rinfo->norm_selec = 1.0; + rinfo->outer_selec = 1.0; + distribute_restrictinfo_to_rels(root, rinfo); + continue; + } /* throw back a dummy replacement clause (see notes above) */ rinfo = make_restrictinfo(root, (Expr *) makeBoolConst(true, false), @@ -2209,6 +2222,15 @@ 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) + { + /* but the kept clause has no extra selectivity */ + rinfo->norm_selec = 1.0; + rinfo->outer_selec = 1.0; + 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 ca4058c8d21..f801a5e7bd6 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6937,6 +6937,79 @@ 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) + +-- same, with the outer joins commuting in the other direction +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) + left join (values (1),(2)) as d(d) on c.c = d.d) on b.b = c.c +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) + left join (values (1),(2)) as d(d) on c.c = d.d) on b.b = c.c +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 +7035,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 +7053,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 037fb7fe093..027c8f71da7 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2442,6 +2442,39 @@ 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; + +-- same, with the outer joins commuting in the other direction +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) + left join (values (1),(2)) as d(d) on c.c = d.d) on b.b = c.c +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) + left join (values (1),(2)) as d(d) on c.c = d.d) on b.b = c.c +where a.a = 1; + explain (verbose, costs off) select 1 from text_tbl as tt1 --
^ permalink raw reply [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-21 10:29       ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-22 01:50         ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-23 07:40           ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-23 12:08             ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-26 07:01               ` Richard Guo <guofenglinux@gmail.com>
  2026-10-29 02:42                 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Richard Guo @ 2026-10-26 07:01 UTC (permalink / raw)
  To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Tender Wang <tndrwang@gmail.com>; leis@in.tum.de, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>

On Fri, Oct 23, 2026 at 9:09 PM Nitin Motiani <nitinmotiani@google.com> wrote:
> Attached is v4 based on your suggestions, which keeps all cloned
> outer-join clauses and sets norm_selec = 1.0 and outer_selec = 1.0 on
> them.

Setting norm_selec and outer_selec to 1.0 is not enough.  Those are
only cached values, and clause_selectivity_ext() skips the cache when
varRelid is nonzero and the clause references more than one base rel.
That is the case when estimating a parameterized scan, so the kept
clause gets its full selectivity there and is double-counted with the
derived INNERVAR = CONSTANT clause.

I think a simple way to fix this is to restore the redundancy marker
that v15 and earlier used and b448f1c8d removed: the kept clause gets
norm_selec = 2.0, and clause_selectivity_ext() returns 1.0 for any
clause with norm_selec > 1, before the cache check.

Also I think we should put the new tests in the session of "regression
test for improper pushing of constants across outer-join clauses", and
reuse the existing zt1/zt2/zt3 tables.

Hence, here is the v5 patch.

- Richard

Attachments:

v5-0001-Don-t-replace-clone-clauses-with-constant-TRUE.patch (application/octet-stream, 10.3K) download From 46b31c6b5d2efdebaf8b994ae099488c30f91add Mon Sep 17 00:00:00 2001 From: Richard Guo <guofenglinux@gmail.com> Date: Fri, 23 Oct 2026 10:00:38 +0900 Subject: [PATCH v5] Don't replace clone clauses with constant TRUE When reconsider_outer_join_clauses() finds an outer-join clause OUTERVAR = INNERVAR whose OUTERVAR is known to equal a constant, it pushes INNERVAR = CONSTANT into the inner side and replaces the clause with constant TRUE. The replacement assumes that OUTERVAR cannot have gone to NULL where the clause is evaluated. That assumption fails for a clone made for outer join identity 3. When an outer join is pushed into the RHS of a lower outer join, its relid is not added to the joinrel until the commuted pair is complete, so a version of a clause that lacks that join's nulling bit can be applied above it, after it has null-extended OUTERVAR. This is fine for the original clause, because it is strict, which is what allowed the commutation in the first place; but it is not fine for constant TRUE. As a result, inner rows could be joined to null-extended outer rows, producing wrong query results. To fix, throw back a clone as-is rather than the TRUE replacement, as v15 and earlier did. The derived INNERVAR = CONSTANT clause remains valid and is still pushed down. This can leave a redundant join filter in some plans, as seen in an existing regression test. To keep the thrown-back clause from affecting selectivity estimates, including for parameterized scans, where the cached selectivity is not used, restore the redundancy marker removed by b448f1c8d: a norm_selec greater than 1 marks a redundant clause, for which clause_selectivity_ext() always returns 1.0. Back-patch to v16, where clone clauses were introduced. Bug: #19765 Reported-by: Viktor Leis <leis@in.tum.de> Diagnosed-by: Tender Wang <tndrwang@gmail.com> Author: Richard Guo <guofenglinux@gmail.com> Co-authored-by: Nitin Motiani <nitinmotiani@google.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/19765-5778b18918040583@postgresql.org Backpatch-through: 16 --- src/backend/optimizer/path/clausesel.c | 6 +++ src/backend/optimizer/path/equivclass.c | 24 +++++++++ src/include/nodes/pathnodes.h | 5 +- src/test/regress/expected/join.out | 68 ++++++++++++++++++++++++- src/test/regress/sql/join.sql | 24 +++++++++ 5 files changed, 125 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c index 25c4d177ad9..c22cbfc9ca0 100644 --- a/src/backend/optimizer/path/clausesel.c +++ b/src/backend/optimizer/path/clausesel.c @@ -713,6 +713,12 @@ clause_selectivity_ext(PlannerInfo *root, return (Selectivity) 1.0; } + /* + * If the clause is marked redundant, always return 1.0. + */ + if (rinfo->norm_selec > 1) + return (Selectivity) 1.0; + /* * If possible, cache the result of the selectivity calculation for * the clause. We can cache if varRelid is zero or the clause diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index f0a35c56bae..598b62b6764 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -2156,6 +2156,12 @@ create_join_clause(PlannerInfo *root, * 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. + * + * However, we can't do that for a clone clause, since it may be applied + * above a commuted outer join that has already null-extended OUTERVAR but + * whose relid has not yet been added to the joinrel. The original strict + * clause is needed there to reject the null-extended rows, so we throw + * back a clone as-is, marked redundant for selectivity estimation. */ void reconsider_outer_join_clauses(PlannerInfo *root) @@ -2181,6 +2187,15 @@ 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 clone as-is (see notes above) */ + if (rinfo->has_clone || rinfo->is_clone) + { + /* but the thrown-back clause has no extra selectivity */ + rinfo->norm_selec = 2.0; + rinfo->outer_selec = 1.0; + distribute_restrictinfo_to_rels(root, rinfo); + continue; + } /* throw back a dummy replacement clause (see notes above) */ rinfo = make_restrictinfo(root, (Expr *) makeBoolConst(true, false), @@ -2209,6 +2224,15 @@ 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 clone as-is (see notes above) */ + if (rinfo->has_clone || rinfo->is_clone) + { + /* but the thrown-back clause has no extra selectivity */ + rinfo->norm_selec = 2.0; + rinfo->outer_selec = 1.0; + 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/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 1140c27356a..ff46a9f7419 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -3004,7 +3004,10 @@ typedef struct RestrictInfo /* eval cost of clause; -1 if not yet set */ QualCost eval_cost pg_node_attr(equal_ignore); - /* selectivity for "normal" (JOIN_INNER) semantics; -1 if not yet set */ + /* + * selectivity for "normal" (JOIN_INNER) semantics; -1 if not yet set; >1 + * means a redundant clause + */ Selectivity norm_selec pg_node_attr(equal_ignore); /* selectivity for outer join semantics; -1 if not yet set */ Selectivity outer_selec pg_node_attr(equal_ignore); diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 1e6aa380257..34a386237be 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4296,6 +4296,71 @@ where f2 = 53; 53 | | | (1 row) +-- the zt3/zt1b join clause must be kept when zt2/zt3 is commuted into zt1/zt2 +explain (costs off) +select * from + zt1 left join zt2 on (zt1.f1 = f2) + left join zt3 on (f2 = f3) + left join zt1 zt1b on (f3 = zt1b.f1) +where zt1.f1 = 53; + QUERY PLAN +--------------------------------------------------------- + Nested Loop Left Join + Join Filter: (zt3.f3 = zt1b.f1) + -> Nested Loop Left Join + Join Filter: (zt2.f2 = zt3.f3) + -> Nested Loop Left Join + -> Index Only Scan using zt1_pkey on zt1 + Index Cond: (f1 = 53) + -> Index Only Scan using zt2_pkey on zt2 + Index Cond: (f2 = 53) + -> Index Only Scan using zt3_pkey on zt3 + Index Cond: (f3 = 53) + -> Index Only Scan using zt1_pkey on zt1 zt1b + Index Cond: (f1 = 53) +(13 rows) + +select * from + zt1 left join zt2 on (zt1.f1 = f2) + left join zt3 on (f2 = f3) + left join zt1 zt1b on (f3 = zt1b.f1) +where zt1.f1 = 53; + f1 | f2 | f3 | f1 +----+----+----+---- + 53 | 53 | | +(1 row) + +-- likewise for the zt3/zt2 join clause when zt1b/zt3 is commuted into zt1/zt1b +explain (costs off) +select * from + zt1 left join zt1 zt1b on (zt1.f1 = zt1b.f1 and zt1b.f1 = 53) + left join (zt3 left join zt2 on (f3 = f2)) on (zt1b.f1 = f3); + QUERY PLAN +-------------------------------------------------------------------- + Hash Left Join + Hash Cond: (zt1.f1 = zt1b.f1) + -> Seq Scan on zt1 + -> Hash + -> Nested Loop Left Join + Join Filter: (zt3.f3 = zt2.f2) + -> Nested Loop Left Join + Join Filter: (zt1b.f1 = zt3.f3) + -> Index Only Scan using zt1_pkey on zt1 zt1b + Index Cond: (f1 = 53) + -> Index Only Scan using zt3_pkey on zt3 + Index Cond: (f3 = 53) + -> Index Only Scan using zt2_pkey on zt2 + Index Cond: (f2 = 53) +(14 rows) + +select * from + zt1 left join zt1 zt1b on (zt1.f1 = zt1b.f1 and zt1b.f1 = 53) + left join (zt3 left join zt2 on (f3 = f2)) on (zt1b.f1 = f3); + f1 | f1 | f3 | f2 +----+----+----+---- + 53 | 53 | | +(1 row) + -- -- regression test for improper extraction of OR indexqual conditions -- (as seen in early 8.3.x releases) @@ -6962,6 +7027,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 +7045,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 6264f23e081..0eb8862bb18 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1305,6 +1305,30 @@ select * from left join zv1 on (f3 = f1) where f2 = 53; +-- the zt3/zt1b join clause must be kept when zt2/zt3 is commuted into zt1/zt2 +explain (costs off) +select * from + zt1 left join zt2 on (zt1.f1 = f2) + left join zt3 on (f2 = f3) + left join zt1 zt1b on (f3 = zt1b.f1) +where zt1.f1 = 53; + +select * from + zt1 left join zt2 on (zt1.f1 = f2) + left join zt3 on (f2 = f3) + left join zt1 zt1b on (f3 = zt1b.f1) +where zt1.f1 = 53; + +-- likewise for the zt3/zt2 join clause when zt1b/zt3 is commuted into zt1/zt1b +explain (costs off) +select * from + zt1 left join zt1 zt1b on (zt1.f1 = zt1b.f1 and zt1b.f1 = 53) + left join (zt3 left join zt2 on (f3 = f2)) on (zt1b.f1 = f3); + +select * from + zt1 left join zt1 zt1b on (zt1.f1 = zt1b.f1 and zt1b.f1 = 53) + left join (zt3 left join zt2 on (f3 = f2)) on (zt1b.f1 = f3); + -- -- regression test for improper extraction of OR indexqual conditions -- (as seen in early 8.3.x releases) -- 2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 13+ messages in thread

* Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16
  2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
  2026-10-16 13:11 ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Tender Wang <tndrwang@gmail.com>
  2026-10-16 15:04   ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-17 07:35     ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-21 10:29       ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-22 01:50         ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-23 07:40           ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
  2026-10-23 12:08             ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Nitin Motiani <nitinmotiani@google.com>
  2026-10-26 07:01               ` Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16 Richard Guo <guofenglinux@gmail.com>
@ 2026-10-29 02:42                 ` Richard Guo <guofenglinux@gmail.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Richard Guo @ 2026-10-29 02:42 UTC (permalink / raw)
  To: Nitin Motiani <nitinmotiani@google.com>; +Cc: Tender Wang <tndrwang@gmail.com>; leis@in.tum.de, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>

On Mon, Oct 26, 2026 at 4:01 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Hence, here is the v5 patch.

I've pushed this patch and back-patched it to v16.  Thanks for the
report Viktor; it's a nice one.

- Richard






^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2026-10-29 02:42 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-16 05:35 BUG #19765: Wrong result from a chain of LEFT JOINs since v16 PG Bug reporting form <noreply@postgresql.org>
2026-10-16 13:11 ` Tender Wang <tndrwang@gmail.com>
2026-10-16 14:43   ` Nitin Motiani <nitinmotiani@google.com>
2026-10-16 15:04   ` Richard Guo <guofenglinux@gmail.com>
2026-10-17 07:35     ` Nitin Motiani <nitinmotiani@google.com>
2026-10-21 10:29       ` Nitin Motiani <nitinmotiani@google.com>
2026-10-22 01:50         ` Richard Guo <guofenglinux@gmail.com>
2026-10-23 03:42           ` Tender Wang <tndrwang@gmail.com>
2026-10-23 06:47             ` Richard Guo <guofenglinux@gmail.com>
2026-10-23 07:40           ` Richard Guo <guofenglinux@gmail.com>
2026-10-23 12:08             ` Nitin Motiani <nitinmotiani@google.com>
2026-10-26 07:01               ` Richard Guo <guofenglinux@gmail.com>
2026-10-29 02:42                 ` Richard Guo <guofenglinux@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox