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

Result of a MERGE statement may depend on the execution plan if multiple matches exist [CORE4824] #5121

Open
firebird-automations opened this issue Jun 2, 2015 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

reporter: Dmitry Yemanov [ dimitr ] => Pavel Zotov [ tabloid ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE4618 [ CORE4618 ]

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

Whole bunch of issues, including database corruption by CORE4369 can be fixed at once if multiple matches will produce error as it is required bby standard.

@firebird-automations
Copy link
Collaborator Author

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).

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Multiply matches is rejected in fb4, see CORE2274

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue is related to CORE2274 [ CORE2274 ]

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

No branches or pull requests

1 participant