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

Rollback doesn`t undo changes when MERGE statement updates the same target rows multiple times and PLAN MERGE is used [CORE4618] #4933

Closed
firebird-automations opened this issue Nov 23, 2014 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 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 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE4824 [ CORE4824 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I've moved the regression to a separate ticket CORE4824 as it's unrelated to the subject of this ticket.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment