Re: pgsql: Avoid mislabeling of lateral references when pulling up a subque - Mailing list pgsql-committers

On Sat, Nov 30, 2024 at 6:31 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > The ideas I'd been toying with last night involved a pre-scan over
> > the join tree to calculate the potential nullingrels of each leaf RTE
> > (same idea as RelOptInfo.nulling_relids, but of course we don't have
> > any RelOptInfos yet).  That seems painful though because we'd have to
> > update the data structure somehow after each subquery pullup.
>
> I realized that we can make that work by doing the pre-calculation
> at the start of each pull_up_simple_subquery.  We only have to do
> it for subqueries marked LATERAL, so this doesn't seem too horribly
> inefficient.  Draft patch attached.  I didn't spend effort on
> devising test cases, but it works for your example.

This patch looks good to me.  I like that it will still work if,
someday, the restrictions of is_simple_subquery are relaxed to allow
lateral references to be outside the lowest outer join above the
subquery.

This patch can also simplify my other patch, which is to avoid
unnecessary wrapping for plain Vars/PHVs.  We can check the new
nullingrel_info to see if the nullingrels of the subquery RTE are a
subset of the nullingrels of the lateral referenced rel, to determine
if the referenced rel is under the same lowest nulling outer join.
And this eliminates the need to introduce lowest_nullable_side.

Thanks
Richard