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
Comments
Modified by: @dyemanovassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Modified by: @hvladassignee: Adriano dos Santos Fernandes [ asfernandes ] => Vlad Khorsun [ hvlad ] |
Modified by: @hvladstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 RC 1 [ 10930 ] |
Commented by: @hvlad Note, MERGE is still allows multiple updates of the "same" row if merge-target is a triggers-updatable VIEW. |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
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; recreate table t_payment_details(operation_id int primary key, person_id int, payment_sum int); insert into t_payment_details(operation_id, person_id, payment_sum) values(0, 10, 0); insert into t_payment_totals(person_id, payment_sum) values(10, 100); merge into t_payment_totals t select 'after merge into TABLE' as msg, v.* from v_payment_totals v; merge into v_payment_totals t select 'after merge into VIEW with check option' as msg, v.* from v_payment_totals v; Output will be:Statement failed, SQLSTATE = 21000 MSG PERSON_ID PAYMENT_SUM /* OK, expected */ MSG PERSON_ID PAYMENT_SUM /* ??? */ |
Commented by: @hvlad VIEW WITH CHECK OPTION is implemented using system triggers (BEFORE INSERT and BEFORE UPDATE), |
Commented by: @hvlad I was wrong, that triggers have nothing to do with UPDATE and DELETE. |
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
The text was updated successfully, but these errors were encountered: