Issue Details (XML | Word | Printable)

Key: CORE-4796
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Pavel Zotov
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 16/May/15 05:54 PM   Updated: 25/Jan/16 07:42 AM
Component/s: Engine
Affects Version/s: 3.0 Beta 2
Fix Version/s: None


 Description  « Hide
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 t.id=s.id
CON> when matched then update set t.pid = s.pid
CON> when not matched then insert values( 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 t.id=s.id when matched then update set t.pid
 = s.pid when not matched then insert values( s.id, s.pid);
Records affected: 1
SQL> select * from tdetl;

          ID PID
============ ============
           5 1

Records affected: 1
SQL>

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dimitry Sibiryakov added a comment - 16/May/15 06:04 PM
Multiple updates must be prohibited.

Pavel Zotov added a comment - 16/May/15 06:12 PM
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).

Simonov Denis added a comment - 17/May/15 09:48 AM
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.

Adriano dos Santos Fernandes added a comment - 17/May/15 04:42 PM
I don't think this is a bug.

Multiple matches for the same source record should be considered a undefined behavior. It must be prohibited, but so far I'm unable to do it myself.