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
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4618
URL: http://tracker.firebirdsql.org/browse/CORE-4618
Project: Firebird Core
Issue Type: Bug
Components: Engine
Reporter: Pavel Zotov
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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
