From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Finnerty, Jim" <jfinnert@amazon.com>
cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Making Vars outer-join aware
In-reply-to: <A6EE2336-84B3-4FC9-A452-519C097A5536@amazon.com>
References: <830269.1656693747@sss.pgh.pa.us>
 <A6EE2336-84B3-4FC9-A452-519C097A5536@amazon.com>
Comments: In-reply-to "Finnerty, Jim" <jfinnert@amazon.com>
	message dated "Fri, 01 Jul 2022 20:19:48 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <89525.1656708045.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 01 Jul 2022 16:40:45 -0400
Message-ID: <89526.1656708045@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/89526.1656708045%40sss.pgh.pa.us>
Precedence: bulk

"Finnerty, Jim" <jfinnert@amazon.com> writes:
>     Given that views are represented in a parsed representation, does an=
ything need to happen to the Vars inside a view when that view is outer-jo=
ined to?  =


No.  The markings only refer to what is in the same Query tree as the Var
itself.

Subquery flattening during planning does deal with this: if we pull up a
subquery (possibly inserted from a view) that was underneath an outer
join, the nullingrel marks on the upper-level Vars referring to subquery
outputs will get merged into what is pulled up, either by unioning the
varnullingrel bitmaps if what is pulled up is just a Var, or if what is
pulled up isn't a Var, by wrapping it in a PlaceHolderVar that carries
the old outer Var's markings.  We had essentially this same behavior
with PlaceHolderVars before, but I think this way makes it a lot more
principled and intelligible (and I suspect there are now cases where we
manage to avoid inserting unnecessary PlaceHolderVars that the old code
couldn't avoid).

>     If an outer join is converted to an inner join, must this informatio=
n get propagated to all the affected Vars, potentially across query block =
levels?

Yes.  The code is there in the patch to run around and remove nullingrel
bits from affected Vars.

One thing that doesn't happen (and didn't before, so this is not a
regression) is that if we strength-reduce a FULL JOIN USING to an outer
or plain join, it'd be nice if the "COALESCE" hack we represent the
merged USING column with could be replaced with the same lower-relation
Var that the parser would have used if the join weren't FULL to begin
with.  Without that, we're leaving optimization opportunities on the
table.  I'm hesitant to try to do that though as long as the COALESCE
structures look exactly like something a user could write.  It'd be
safer if we used some bespoke node structure for this purpose ...
but nobody's bothered to invent that.

			regards, tom lane