From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: 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_HJNuLv9HDReEBDrQrCPV-uqpVOecoJkurprnY+Do9Fg@mail.gmail.com>
References: <0b819232-4b50-f245-1c7d-c8c61bf41827@postgrespro.ru>
 <CAMbWs4_HJNuLv9HDReEBDrQrCPV-uqpVOecoJkurprnY+Do9Fg@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Wed, 22 Feb 2023 18:24:11 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <598427.1677181685.1@sss.pgh.pa.us>
Date: Thu, 23 Feb 2023 14:48:05 -0500
Message-ID: <598428.1677181685@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/598428.1677181685%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> ISTM that for outer join identity 3, if we are given form
>     (A leftjoin B on (Pab)) leftjoin C on (Pbc)
> then references to C Vars in higher qual levels would be marked with the
> B/C join.  If we've transformed it to form
>     A leftjoin (B leftjoin C on (Pbc)) on (Pab)
> then references to C Vars in higher qual levels should be adjusted to
> include both B/C join and A/B join in their varnullingrels.

Hmm.  I remember having convinced myself that we didn't need to change
anything above the commuting OJs, but now I can't reconstruct the
reasoning :-(.

I wonder whether we could instead fix things by deeming that the result
of the pushed-down B/C join does not yet produce correct C* variables,
so that we won't allow conditions involving them to drop below the
pushed-up A/B join.  This would be a little bit messy in some places
because now we'd consider that the A/B join is adding two OJ relids
not just one to the output join relid set, while the B/C join is adding
no relids even though it must execute an outer join.  (But we already
have that notion for semijoins and some antijoins, so maybe it's fine.)

			regards, tom lane