What happens?

Delete and Insert of same primary within a transaction doesn't work when using

insert into <table1> select * from <table2>;

Probably another edge case to handle in #15092

To Reproduce

create table test1 (id int primary key, payload varchar);
create table test2 (id int primary key, payload varchar);
insert into test1 values (1, 'row 1');
insert into test2 values (1, 'row 1 from test 2');

select * from test1;

┌───────┬─────────┐
│  id   │ payload │
│ int32 │ varchar │
├───────┼─────────┤
│   1   │ row 1   │
└───────┴─────────┘

select * from test2;

┌───────┬───────────────────┐
│  id   │      payload      │
│ int32 │      varchar      │
├───────┼───────────────────┤
│   1   │ row 1 from test 2 │
└───────┴───────────────────┘

begin;
delete from test1 where id = 1;
select * from test1;

┌───────┬─────────┐
│  id   │ payload │
│ int32 │ varchar │
├───────┴─────────┤
│     0 rows      │
└─────────────────┘

insert into test1 select * from test2; -- This fails, but should not have

Constraint Error:
Duplicate key "id: 1" violates primary key constraint.

OS:

OSX

DuckDB Version:

1.2, 1.2.1

DuckDB Client:

cli

Hardware:

M1

Full Name:

Basanth Jenu H B

Affiliation:

Intuit

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have