Issue Details (XML | Word | Printable)

Key: CORE-2274
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Paul Vinkenoog
Votes: 2
Watchers: 1
Operations

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

MERGE non-standard behaviour, accepts multiple matches

Created: 13/Jan/09 11:10 AM   Updated: 08/Sep/20 02:53 PM
Component/s: Engine
Affects Version/s: 2.1.1
Fix Version/s: 4.0 RC 1

Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
If the WHEN MATCHED clause is present and a record in the target table matches multiple records in the source, an exception should be raised ("cardinality violation").

Firebird's behaviour is to execute the UPDATE for every match found, each one of them overwriting the previous one, wasting resources and giving an unpredictable result (i.e., you don't know which of the source records will eventually determine the outcome).


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 02/Jun/20 11:30 AM
Note, MERGE is still allows multiple updates of the "same" row if merge-target is a triggers-updatable VIEW.
In this case there is no general way to correctly define "same row".

Pavel Zotov added a comment - 07/Jun/20 10:46 AM
I've found one example where MERGE still can change records multiple times: it happens when target is a naturally updatable VIEW declared WITH CHECK OPTION.

Consider this script:
================

shell del c:\temp\tmp4test.fdb 2>nul;
create database 'localhost:c:\temp\tmp4test.fdb' user 'SYSDBA' password 'masterkey';

recreate table t_payment_details(operation_id int primary key, person_id int, payment_sum int);
recreate table t_payment_totals(person_id int, payment_sum int);
recreate view v_payment_totals as select * from t_payment_totals where true with check option;
commit;

insert into t_payment_details(operation_id, person_id, payment_sum) values(0, 10, 0);
insert into t_payment_details(operation_id, person_id, payment_sum) values(1, 11, 11);
insert into t_payment_details(operation_id, person_id, payment_sum) values(2, 22, 222);
insert into t_payment_details(operation_id, person_id, payment_sum) values(3, 11, 3333);
insert into t_payment_details(operation_id, person_id, payment_sum) values(7, 17, 77777);

insert into t_payment_totals(person_id, payment_sum) values(10, 100);
insert into t_payment_totals(person_id, payment_sum) values(11, 111);
insert into t_payment_totals(person_id, payment_sum) values(22, 222);
commit;

merge into t_payment_totals t
using t_payment_details s on s.person_id = t.person_id
when NOT matched then
    insert(person_id, payment_sum) values( s.person_id, s.payment_sum )
when MATCHED then
    update set t.payment_sum = t.payment_sum + s.payment_sum
;

select 'after merge into TABLE' as msg, v.* from v_payment_totals v;
rollback;

merge into v_payment_totals t
using t_payment_details s on s.person_id = t.person_id
when NOT matched then
    insert(person_id, payment_sum) values( s.person_id, s.payment_sum )
when MATCHED then
    update set t.payment_sum = t.payment_sum + s.payment_sum
;

select 'after merge into VIEW with check option' as msg, v.* from v_payment_totals v;
quit;


Output will be:
===========
Statement failed, SQLSTATE = 21000
Multiple source records cannot match the same target during MERGE


MSG PERSON_ID PAYMENT_SUM
====================== ============ ============
after merge into TABLE 10 100
after merge into TABLE 11 111
after merge into TABLE 22 222

/* OK, expected */


MSG PERSON_ID PAYMENT_SUM
======================================= ============ ============
after merge into VIEW with check option 10 100
after merge into VIEW with check option 11 3444
after merge into VIEW with check option 22 444
after merge into VIEW with check option 17 77777

/* ??? */

Vlad Khorsun added a comment - 07/Jun/20 11:14 AM
VIEW WITH CHECK OPTION is implemented using system triggers (BEFORE INSERT and BEFORE UPDATE),
so it is non-naturally updatable (at least technically).

Vlad Khorsun added a comment - 07/Jun/20 12:24 PM
I was wrong, that triggers have nothing to do with UPDATE and DELETE.
Additional fix is committed, please check.