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: 
 <CAMbWs4-Kx3ZHUByn6D2ZUTeF4Fm7uudkzgHNguDutpV8tepxtg@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> <CA!
 MbWs4-doWdYa3AJdw4wMAn7ApSSyRDQrWuFV1_xW5muY0mLJw@mail.gmail.com>
 <2489296.1684195396@sss.pgh.pa.us>
 <CAMbWs4-Kx3ZHUByn6D2ZUTeF4Fm7uudkzgHNguDutpV8tepxtg@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Tue, 16 May 2023 14:22:42 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2766441.1684265014.1@sss.pgh.pa.us>
Date: Tue, 16 May 2023 15:23:34 -0400
Message-ID: <2766442.1684265014@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2766442.1684265014%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> I doubt this is always right to add joins_below to all the vars
> belonging to sjinfo->syn_lefthand.  What if the joins in joins_below
> cannot commute with each other?  As a counterexample, consider the query
> below which causes assertion failure in search_indexed_tlist_for_var.
> The query is designed so that t1/t2 join cannot commute with t2/t3 join
> but can commute with t3/t4 join.

> explain (costs off)
> select * from t t1
>     left join t t2 on true
>     left join t t3 on true
>     left join t t4 on t2.a = t3.a;

Hm.  Actually, t1/t2 *can't* commute with the t4 join.  You can re-order
the t2 and t3 joins per identity 2:

select * from t t1
    left join t t3 on true
    left join t t2 on true
    left join t t4 on t2.a = t3.a;

but you're still stuck, identity 3 applies nowhere (because in either
case, the putative Pbc clause has also got a reference to A).

make_outerjoininfo is setting the t4 join's commute_below_l to include
t1/t2, but it seems to me that that indicates inadequate analysis in
make_outerjoininfo.  Is there still a problem here if we get rid of
that overoptimistic conclusion?  We should strive to do so even if
it's not fixing an observable problem, because in any case it's
causing the planner to waste time on unreachable join orders.

			regards, tom lane