Issue Details (XML | Word | Printable)

Key: CORE-4824
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

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

Result of a MERGE statement may depend on the execution plan if multiple matches exist

Created: 02/Jun/15 10:28 AM   Updated: 02/Jun/15 10:51 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

Issue Links:
Relate
 

QA Status: No test


 Description  « Hide
Copied from a comment in CORE-4618:

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


 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:35 AM
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.

Dimitry Sibiryakov added a comment - 02/Jun/15 10:39 AM
Whole bunch of issues, including database corruption by CORE-4369 can be fixed at once if multiple matches will produce error as it is required bby standard.

Dmitry Yemanov added a comment - 02/Jun/15 10:51 AM
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).