Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Guo <guofenglinux(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)
Date: 2023-05-19 18:50:05
Message-ID: 288016.1684522205@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> Take the query shown upthread as an example, which would trigger an
> Assert.

> create table t (a int unique, b int);

> explain (costs off)
> select 1 from t t1
> left join (t t2 left join t t3 on t2.a = 1) on t2.a = 1;
> server closed the connection unexpectedly

> We'd be mistaken to think t1/t2 join is removable, because we are
> checking its syntactic relid set, which includes all rels, and so that
> are not aware that t2.a is used by t2/t3 join.

Ah, now your point has finally penetrated my thick skull: we can't
remove a join if its output is referenced at any join level that
we could potentially postpone past the join of interest, even
when that join is syntactically below it. It seems like there
might be more than one way to skin that cat; maybe we could remove
it anyway and restrict the join order later? But I agree that
three days before beta isn't the time to be rethinking the join
removal rules.

I remembered why it was that I wanted to change this logic, though.
It wasn't totally clear to me that min_lefthand + min_righthand
would account properly for outer joins below the one of interest.
However, considering it now it seems like that will hold, so now
I think your patch is correct.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2023-05-19 19:55:02 Re: initdb faild to initialize full text search dictionaries
Previous Message Magnus Hagander 2023-05-19 17:18:25 Re: BUG #17919: "client hello" message / SNI / Openshift Routes