Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MERGE non-standard behaviour, accepts multiple matches [CORE2274] #2700

Closed
firebird-issue-importer opened this issue Jan 13, 2009 · 10 comments
Closed

Comments

@firebird-issue-importer

Submitted by: @paulvink

Relate to CORE4824

Votes: 2

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).

Commits: b7c00f7 9cf05fc FirebirdSQL/fbt-repository@0c84d93

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 13, 2009

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 2, 2020

Modified by: @hvlad

assignee: Adriano dos Santos Fernandes [ asfernandes ] => Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 2, 2020

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 2, 2020

Commented by: @hvlad

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".

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 6, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 6, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 7, 2020

Commented by: @pavel-zotov

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

/* ??? */

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 7, 2020

Commented by: @hvlad

VIEW WITH CHECK OPTION is implemented using system triggers (BEFORE INSERT and BEFORE UPDATE),
so it is non-naturally updatable (at least technically).

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 7, 2020

Commented by: @hvlad

I was wrong, that triggers have nothing to do with UPDATE and DELETE.
Additional fix is committed, please check.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 8, 2020

Modified by: @hvlad

Link: This issue relate to CORE4824 [ CORE4824 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants