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
Result of a MERGE statement may depend on the execution plan if multiple matches exist [CORE4824] #5121
Comments
Modified by: @dyemanovreporter: Dmitry Yemanov [ dimitr ] => Pavel Zotov [ tabloid ] |
Commented by: @dyemanov This is a side effect of the cursor stability. With a MERGE plan, every row is read once, resulting in {2, 2, 2} is the X column. With a JOIN plan, however, multiple matches cause the first two records being read and updated twice. At the second attempt, the original (not modified) values of OLD.X and OLD.Y are used due to cursor stability, causing different results. |
Commented by: @dyemanov Corruptions can be fixed for multiple matches. As for rejecting multiple matches at all, I'm for the standard behavior, but so far I don't see how to do that without reimplementing MERGE from scratch (as a separate BLR verb). |
Submitted by: @pavel-zotov
Is related to CORE4618
Is related to CORE2274
Copied from a comment in CORE4618:
Seems that recent fix leads to different results of MERGE statement when INDEX on joined field is added.
For example:
recreate table t(id int, x int, y int);
commit;
insert into t (id, x, y) values (1, 1, 1);
insert into t (id, x, y) values (3, 1, 0);
insert into t (id, x, y) values (2, 0, 2);
commit;
create index t_x on t(x); -- :::::::::::::::: NB ::::::::::::::::::::::::: this statement affects on result of merge
commit;
select 'before_merge' msg, t.* from t order by id;
set plan on;
merge into t
using t s
--(select * from t order by x) 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 order by id;
rollback;
Test-1: WITHOUT index `t_x`:
MSG ID X Y
============ ============ ============ ============
before_merge 1 1 1
before_merge 2 0 2
before_merge 3 1 0
PLAN HASH (T NATURAL, S NATURAL)
MSG ID X Y
=========== ============ ============ ============
after_merge 1 2 0
after_merge 2 2 2
after_merge 3 2 -1
Test-2: WITH index `t_x`:
MSG ID X Y
============ ============ ============ ============
before_merge 1 1 1
before_merge 2 0 2
before_merge 3 1 0
PLAN JOIN (S NATURAL, T INDEX (T_X))
MSG ID X Y
=========== ============ ============ ============
after_merge 1 1 0
after_merge 2 2 2
after_merge 3 1 -1
Rows with 'after_merge' in MSG column differ in 'X' field.
ISQL Version: WI-T3.0.0.31532 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31532 Firebird 3.0 Beta 2"
on disk structure version 12.0
The text was updated successfully, but these errors were encountered: