Обсуждение: BUG #18043: Merge fails to insert when source and target are same table and table is empty (contains no rows)

The following bug has been logged on the website:

Bug reference:      18043
Logged by:          Clark Pearson
Email address:      clark.pearson@mail.com
PostgreSQL version: 15.2
Operating system:   Windows
Description:

I have checked the release notes for 5.3, FAQ & TO DO, and can find no
mention of this as a known bug, or as work in progress.  I am currently on
15.2.

This test case demonstrates failure to insert a row into an empty table when
source/target are the same table (I have not explored whether it applies in
a wider context, only when source/target are the same table):

postgres=> create table t (id integer, val integer);
CREATE TABLE

-- Table is empty, merge id=1 row :: does not insert
postgres=> merge into t n using t o on( n.id = 1 ) when matched then update
set val = o.val+1 when not matched then insert(id,val) values(1,1);
MERGE 0

-- Table is still empty, change join condition alias to 'o' (old) rather
than 'n' (new) :: still does not insert
postgres=> merge into t n using t o on( o.id = 1 ) when matched then update
set val = o.val+1 when not matched then insert(id,val) values(1,1);
MERGE 0

-- Insert a row, id=1
postgres=> insert into t values(1,1);
INSERT 0 1

-- Rerun original merge statement, id=1 row is found and merge-updated
(t.val column is set up by 1 to 2)
postgres=> merge into t n using t o on( n.id = 1 ) when matched then update
set val = o.val+1 when not matched then insert(id,val) values(1,1);
MERGE 1

-- Now run merge on row id=2 (new row), merge correctly inserts a row
postgres=> merge into t n using t o on( n.id = 2 ) when matched then update
set val = o.val+1 when not matched then insert(id,val) values(2,1);
MERGE 1