You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Regression: MERGE fails with violation of PK./UK on target <T> when source <S> contains two rows with similar key for PK and join condition is <S>.pk_field = <T>.pk_field [CORE4796]
#5094
SQL> create table tdetl(id int constraint tdetl_pk primary key, pid int); commit;
SQL> set count on;
SQL> merge into tdetl t using ( select 5 as id, 1 as pid from rdb$types rows 2 ) s
CON> on http://t.id=s.id
CON> when matched then update set t.pid = s.pid
CON> when not matched then insert values( http://s.id, s.pid);
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "TDETL_PK" on table "TDETL"
-Problematic key value is ("ID" = 5)
Records affected: 1
WI-V2.5.5.26870:
SQL> set count on;
SQL> merge into tdetl t using ( select 5 as id, 1 as pid from rdb$types rows 2 ) s on http://t.id=s.id when matched then update set t.pid
= s.pid when not matched then insert values( http://s.id, s.pid);
Records affected: 1
SQL> select * from tdetl;
ID PID
============ ============
5 1
Records affected: 1
SQL>
The text was updated successfully, but these errors were encountered:
In this sample row firstly is INSERTED and after this - UPDATED.
BTW, if we add row before merge, and than run it, no exception occurs (and this merge will update the same record multiple times).
This is a consequence of the stability of the cursor. MERGE does not see that we have already inserted a row into a table tdetl. And rightly so. However, the index is seeing.
Submitted by: @pavel-zotov
WI-T3.0.0.31839:
SQL> create table tdetl(id int constraint tdetl_pk primary key, pid int); commit;
SQL> set count on;
SQL> merge into tdetl t using ( select 5 as id, 1 as pid from rdb$types rows 2 ) s
CON> on http://t.id=s.id
CON> when matched then update set t.pid = s.pid
CON> when not matched then insert values( http://s.id, s.pid);
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "TDETL_PK" on table "TDETL"
-Problematic key value is ("ID" = 5)
Records affected: 1
WI-V2.5.5.26870:
SQL> set count on;
SQL> merge into tdetl t using ( select 5 as id, 1 as pid from rdb$types rows 2 ) s on http://t.id=s.id when matched then update set t.pid
= s.pid when not matched then insert values( http://s.id, s.pid);
Records affected: 1
SQL> select * from tdetl;
============ ============
5 1
Records affected: 1
SQL>
The text was updated successfully, but these errors were encountered: