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-issue-importer opened this issue Nov 23, 2014 · 13 comments

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 23, 2014

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 23, 2014

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 24, 2014

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 30, 2014

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 30, 2014

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 2, 2014

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 31, 2015

Modified by: @pavel-zotov

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

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

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

Modified by: @dyemanov

Link: This issue relate to CORE4824 [ CORE4824 ]

@firebird-issue-importer
Copy link
Author

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

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

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

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 3, 2015

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 3, 2015

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 3, 2015

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