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
Rollback doesn`t undo changes when MERGE statement updates the same target rows multiple times and PLAN MERGE is used [CORE4618] #4933
Comments
Modified by: @dyemanovVersion: 2.5.3 [ 10461 ] Version: 2.1.6 [ 10460 ] Version: 3.0 Alpha 2 [ 10560 ] Version: 3.0 Alpha 1 [ 10331 ] Version: 2.5.2 Update 1 [ 10521 ] Version: 2.1.5 Update 1 [ 10522 ] Version: 2.5.2 [ 10450 ] Version: 2.1.5 [ 10420 ] Fix Version: 3.0 Beta 1 [ 10332 ] summary: ROLLBACK doesn`t work when use MERGE with several rows in target for some record from source and PLAN MERGE (SORT (T NATURAL), SORT (S NATURAL)) is in use => Rollback doesn`t undo changes when MERGE statement updates the same target rows multiple times and PLAN MERGE is used |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @dyemanovVersion: 3.0 Beta 1 [ 10332 ] Fix Version: 3.0 Beta 2 [ 10586 ] Fix Version: 2.5.4 [ 10585 ] Fix Version: 3.0 Beta 1 [ 10332 ] => |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] Test Details: Seems that trouble was NOT only because of PLAN MERGE. WI-T3.0.0.31374 Firebird 3.0 Beta 1 - uses also 'PLAN HASH' as current builds, but result is wrong. |
Submitted by: @pavel-zotov
Relate to CORE4824
DDL:
####
set term ^;
execute block as begin
begin execute statement 'create sequence g'; when any do begin end end
end
^ set term ;^
commit;
alter sequence g restart with 0;
commit;
recreate table t(id int, x int, y int);
commit;
insert into t(id) select gen_id(g,1) from rdb$types rows 3;
update t set x=mod(id,2), y=mod(id,3);
commit;
Test:
####
-- not affects, result the same: commit; set transaction no auto undo;
set echo on;
select 'before_merge' msg, t.* from t;
set plan on;
merge into t
using t s
on t.x=s.x
when matched then update set t.x = t.x+s.y, t.y = t.y - s.x;
set plan off;
select 'after_merge' msg, t.* from t;
rollback; -- <<<<<<<<<<<<<<<<<<<<<<< ::: NB ::: must restore data to previous state
select 'after_rollback' msg, t.* from t;
Result:
######
select 'before_merge' msg, t.* from t;
MSG ID X Y
============ ============ ============ ============
before_merge 1 1 1
before_merge 2 0 2
before_merge 3 1 0
set plan on;
merge into t
using t s
on t.x=s.x
when matched then update set t.x = t.x+s.y, t.y = t.y - s.x;
PLAN MERGE (SORT (T NATURAL), SORT (S NATURAL))
set plan off;
select 'after_merge' msg, t.* from t;
MSG ID X Y
=========== ============ ============ ============
after_merge 1 2 -1
after_merge 2 2 2
after_merge 3 2 -2
rollback; --- ::: NB :::
select 'after_rollback' msg, t.* from t;
MSG ID X Y
============== ============ ============ ============
after_rollback 1 2 -255
after_rollback 2 0 2
after_rollback 3 2 0
PS. Reproduced on:
WI-V2.5.3.26790
WI-T3.0.0.31395
Commits: 8744b80 0e11867 9b0a24f c80929e FirebirdSQL/fbt-repository@63488cd FirebirdSQL/fbt-repository@993fe86 FirebirdSQL/fbt-repository@d86448a FirebirdSQL/fbt-repository@9988bf5
====== Test Details ======
Seems that trouble was NOT only because of PLAN MERGE. WI-T3.0.0.31374 Firebird 3.0 Beta 1 - uses also 'PLAN HASH' as current builds, but result is wrong.
The text was updated successfully, but these errors were encountered: