On Mon, 11 Sept 2023 at 01:03, PG Bug reporting form
<noreply@postgresql.org> wrote:
> session1:
> begin;
> session2:
> begin;
> set enable_hashjoin = off;
> set enable_mergejoin = off;
> merge into t1 p using (select distinct a,b from t2) q on p.a = q.a when
> matched then update set b = q.b when not matched then insert values(q.a,
> q.b);
> session1:
> set enable_hashjoin = off;
> set enable_mergejoin = off;
> merge into t1 p using (select distinct a,b from t2) q on p.a = q.a when
> matched then update set b = q.b when not matched then insert values(q.a,
> q.b);
> session2:
> commit;
> session1:
> select count(*) from t1; -- there are 5 rows;
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'm not really sure how EvalPlanQualNext() is meant to correctly find
the 2=2 row given that we end up doing ExecReScan() on the nested loop
and don't seem to find the row we intend to.
David