MERGE on distributed tables gives incorrect result

[marcocitus]

I think it's not correct to do any kind of pruning based on an ON clause condition, because the source is the outer table of the outer join.

drop table if exists source, target;

create table source (                                                                   
    id   integer, z int
);
create table target (
    id   integer, z int 
);
insert into source values (1,2);
insert into source values (3,4);

merge into target sda
using source sdn
on sda.id = sdn.id AND sda.id = 1
when not matched then
  insert (id, z)
  values (sdn.id, 5);

select * from target;
┌────┬───┐
│ id │ z │
├────┼───┤
│  1 │ 5 │
│  3 │ 5 │
└────┴───┘
(2 rows)

truncate target;
select create_distributed_table('target','id'), create_distributed_table('source', 'id');


merge into target sda
using source sdn
on sda.id = sdn.id AND sda.id = 1
when not matched then
  insert (id, z)
  values (sdn.id, 5);

-- missing rows
select * from target;
┌────┬───┐
│ id │ z │
├────┼───┤
│  1 │ 5 │
└────┴───┘
(1 row)

[tejeswarm]

This is a bit of an oddity or expected behavior which I was planning to document as a cautionary note (only for this phase II). In your test case, id 3 is not inserted because we generate single-shard task, id-3 is not part of the shard we routed the query to, hence it never appears in the target. I knew this behavior before, ideally for phase II, the NON MATCHED should have DO NOTHING in Citus, else it's hard to interpret the results for MERGE.
 
 In your test, if we change
insert into source values (3,4)
to
insert into source values (5,4)
it will match the expected PG result

[tejeswarm]

Fixed in PR #6696