Re: BUG #18103: bugs of concurrent merge into when use different join plan

On Thu, Sep 14, 2023 at 3:30 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

>
> I agree this is a bug.
>
> What seems to be going on is that in ExecMergeMatched() we hit the
> TM_Updated case and when we try to fill the epqslot calling
> EvalPlanQual() the nested loop does not seem to scan to find the
> correct set of rows. It works ok for the 1=1 row (which is why we get
> 5 rows instead of 6), but on the 2=2 row, I see it finds rows 3=1 and
> returns that. The join type is LEFT, after all, so that's the EPQ row.
> Back in ExecMergeMatched(), since the DISTINCT subquery is the outer
> side of the join and t1 the inner side, the following code finds a
> NULL ctid:
>
> /*
> * If we got no tuple, or the tuple we get has a
> * NULL ctid, go back to caller: this one is not a
> * MATCHED tuple anymore, so they can retry with
> * NOT MATCHED actions.
> */
> if (TupIsNull(epqslot))
> return false;
>
> (void) ExecGetJunkAttribute(epqslot, resultRelInfo->ri_RowIdAttNo,
> &isNull);
> if (isNull)
> return false;
>
> Since the inner side of the (left) join is NULL, the ri_RowIdAttNo
> attr is NULL and we return false in the final condition.
>
> And that effectively means we run the NOT MATCHED code and do the INSERT.

I have the same observation. It seems that the EvalPlanQual recheck
does not work well with LEFT joins if there is concurrent update in a
WHEN MATCHED case. FWIW, if the optimizer chooses RIGHT join at last
for the MERGE command, we'd get the expected results.

Thanks
Richard