From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: Robert Haas <robertmhaas@gmail.com>,
        Andrey Lepikhov <a.lepikhov@postgrespro.ru>,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: Clause accidentally pushed down ( Possible bug in Making Vars
 outer-join aware)
In-reply-to: 
 <CAMbWs49xNW4xU=TQoYhhicvOyK6LLRm1GWr9xDDhCjuaqptWpw@mail.gmail.com>
References: <0b819232-4b50-f245-1c7d-c8c61bf41827@postgrespro.ru>
 <CAMbWs4_HJNuLv9HDReEBDrQrCPV-uqpVOecoJkurprnY+Do9Fg@mail.gmail.com>
 <CAMbWs4_8EZU4DetHyZGm1CwCxUhBdWpRnA9dtM-Z82i=bVKA6A@mail.gmail.com>
 <CAMbWs4_XcPHqc=A=36cxvJRhkjAgEeJtOFLOnBPEfQYGqnGOCw@mail.gmail.com>
 <efb33bee-145d-95a8-1048-2e7b8f282e2c@postgrespro.ru>
 <CAMbWs48FHw_tTXSbA-_MPaeu7S7JCt6Xs-gxwkx=pvLiwC6eTg@mail.gmail.com>
 <c69bd07e-1175-10da-9ac0-4bfe59235ac2@postgrespro.ru>
 <402433.1677524538@sss.pgh.pa.us>
 <2663884e-769a-b298-55b0-014c5a1ee55c@postgrespro.ru>
 <524303.1677538463@sss.pgh.pa.us> <999171.1677611407@sss.pgh.pa.us>
 <CAMbWs4-Pi5cM=V8B-g5Qfnr0t3EgAbhus0YNQN=XquJ8fNoatw@mail.gmail.com>
 <CAMbWs4-Pmh9umsUbfs8Lay=k6wFrsuLdLJ7ci=Af6DqAwsN1Hg@mail.gmail.com>
 <CA+TgmoZ9yQLYziHAbt_X3hpU8FKkPXGLV=OhoD-GejAUckQbrA@mail.gmail.com>
 <CA+TgmoaeccFHN9ouXiSQmMJVh6uF-ANbbDjrwopQ+icYyzJSJw@mail.gmail.com>
 <CAMbWs4_nudsuzd-bLLgu1WCzeV1pLv3SG1Ub8vo-ETJzvgc0Bg@mail.gmail.com>
 <1526312.1683912310@sss.pgh.pa.us> <27!
 66442.1684265014@sss.pgh.pa.us> <3014965.1684293045@sss.pgh.pa.us>
 <CAMbWs48iJprH69xQ27nigwODu1m_4U3Y2WMTxk47sTVdkW0Seg@mail.gmail.com>
 <3344781.1684337332@sss.pgh.pa.us>
 <CAMbWs4-EU9uBGSP7G-iTwLBhRQ=rnZKvFDhD+n+xhajokyPCKg@mail.gmail.com>
 <3732983.1684416513@sss.pgh.pa.us>
 <CAMbWs49xNW4xU=TQoYhhicvOyK6LLRm1GWr9xDDhCjuaqptWpw@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Fri, 19 May 2023 15:34:47 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <288015.1684522205.1@sss.pgh.pa.us>
Date: Fri, 19 May 2023 14:50:05 -0400
Message-ID: <288016.1684522205@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/288016.1684522205%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.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