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

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
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
CON> when matched then update set =
CON> when not matched then insert values(,;
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

SQL> set count on;
SQL> merge into tdetl t using ( select 5 as id, 1 as pid from rdb$types rows 2 ) s on when matched then update set
 = when not matched then insert values(,;
Records affected: 1
SQL> select * from tdetl;

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

Records affected: 1

 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.

Dmitry Yemanov made changes - 27/Oct/15 09:39 AM
Field Original Value New Value
Fix Version/s 3.0.0 [ 10048 ]
Dmitry Yemanov made changes - 25/Jan/16 07:42 AM
Fix Version/s 3.0 RC2 [ 10048 ]