Issue Details (XML | Word | Printable)

Key: CORE-4618
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 5
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 23/Nov/14 12:40 PM   Updated: 03/Jun/15 12:50 PM
Component/s: Engine
Affects Version/s: 2.1.5, 2.5.2, 2.1.5 Update 1, 2.5.2 Update 1, 3.0 Alpha 1, 3.0 Alpha 2, 2.1.6, 2.5.3, 3.0 Beta 1
Fix Version/s: 2.5.4, 3.0 Beta 2

Issue Links:
Relate
 

QA Status: Done successfully
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.


 Description  « Hide
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


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 02/Jun/15 10:30 AM
I've moved the regression to a separate ticket CORE-4824 as it's unrelated to the subject of this ticket.