The following bug has been logged on the website:
Bug reference: 18103
Logged by: luajk
Email address: luajk@qq.com
PostgreSQL version: 16rc1
Operating system: ANY
Description:
Use Case:
drop table t1;
drop table t2;
create table t1(a int not null, b int);
create table t2(a int not null, b int);
insert into t1 values(generate_series(1,3), generate_series(1,3));
insert into t2 values(generate_series(1,3), generate_series(1,3));
execute in two concurrent sessions and do as following:
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;
however:
session1:
begin;
session2:
begin;
set enable_nestloop = 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_nestloop = 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 3 rows;
nest loop join is the same as merge join in such scenes.