Re: BUG #18634: Wrong varnullingrels with merge ... when not matched by source

On Mon, 30 Sept 2024 at 03:16, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> On Sun, Sep 29, 2024 at 3:49 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> > I spent some time trying to figure out why none of the existing tests
> > hit this error, and I think the reason is that all the previous tests
> > involved a plan where the ModifyTable node is directly on top of the
> > join node, so the top targetlist was the join node's targetlist, and
> > therefore wasn't marked. But in the example here, there is a one-time
> > filter Result node between the ModifyTable node and the join node,
> > which means the ModifyTable node pulls from the Result node, whose
> > output is marked as nullable, because it's above the join. That makes
> > the error somewhat rare, though maybe there are other cases that can
> > lead to a plan node being inserted between the ModifyTable node and
> > the join node.
> >
> > It feels a bit unsatisfactory that this wasn't detectable with a
> > ModifyTable node directly on top of the join node, making the bug hard
> > to spot, but I don't know whether it would be feasible to do anything
> > about that.
>
> For an outer join, any vars appearing in its targetlist (and qpqual)
> should be marked nullable if they are from the nullable side, because
> they are logically above the join. However, when we fix up the
> targetlist and qpqual, we don't have enough info available to identify
> the nullingrel bits added by the outer join. So we have to use
> superset matches rather than exact matches.
>
> This is why we don't hit this error in cases where the ModifyTable
> node is directly on top of the join node, even though we fail to mark
> the vars in targetlist correctly.
>

OK, that makes sense, I think.

I have pushed and back-patched both fixes.

Regards,
Dean