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

On Fri, Sep 27, 2024 at 6:41 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > The following script:
> > CREATE TABLE t (a int);
> > INSERT INTO t VALUES(1), (2);
> > CREATE VIEW v AS SELECT a FROM t WHERE EXISTS (SELECT 1 FROM t);
>
> > MERGE INTO v USING (SELECT * FROM generate_series(1,1)) AS s(a)
> > ON s.a = v.a WHEN NOT MATCHED BY SOURCE THEN DELETE;
>
> > produces:
> > ERROR: XX000: wrong varnullingrels (b) (expected (b 4)) for Var 5/1
> > LOCATION: search_indexed_tlist_for_var, setrefs.c:2847
>
> I haven't run this fully to ground, but what it looks like
> is that preprocess_targetlist is generating row identity
> Vars that lack required varnullingrels. I don't understand
> though why this only seems to affect MERGE.

It looks like that preprocess_targetlist will add any vars used in
parse->mergeJoinCondition that belong to the source relation to the
processed tlist. This logic was introduced to support WHEN NOT
MATCHED BY SOURCE actions (see 0294df2f1). For such actions, the
source relation is on the nullable side of the outer join. But when
adding the vars used in the join condition that belong to source
relation to the tlist, we fail to mark them as nullable by the join.

I think we can check the jointype of the join between the target and
the source relation when adding the vars in mergeJoinCondition. If it
is JOIN_LEFT, we mark the vars that belong to source as nullable by
this join.

With this routine, ISTM we'd need a way for preprocess_targetlist to
access the JoinExpr that we build in transform_MERGE_to_join.

Thanks
Richard