Please add reference to #2700 to "Compatibility Issues

[pavel-zotov]

New behavour of MERGE (when target table contains several matching rows) differs from old and causes code to be re-implemented. Unfortunately, current content of chapter "Compatibility Issues" in Release Notes does not contain any reference to this. It will be good to add such note.

[mrotteveel]

Could you be more specific as to what that difference is?

[pavel-zotov]

I mean that in FB 4.x we get "SQLSTATE = 21000 / Multiple source records cannot match the same target during MERGE" (and this is not so in FB 2.1, 2.5 and 3.x). Example:

set bail on;
set list on;

shell del c:\temp\tmp4test.fdb 2>nul;
create database 'localhost:c:\temp\tmp4test.fdb';
commit;

recreate table tmain(id int primary key, x int);
recreate table tdetl(id int primary key, parent_x int, y int);

insert into tmain(id, x) values(1, 100);
insert into tmain(id, x) values(2, 200);

insert into tdetl(id, parent_x, y) values(1, 100, 1001);
insert into tdetl(id, parent_x, y) values(2, 200, 2001);
insert into tdetl(id, parent_x, y) values(3, 200, 2002);
commit;

set echo on;

merge into tmain t
using (select parent_x, y from tdetl) s on s.parent_x = t.x
when matched then
    update set t.x = t.x + s.y;

[pavel-zotov]

PS. Yes, ticket #2700 was described as bug, but result of its fixing reflects on compatibility between FB 4.x and previous versions.

[mrotteveel]

I'm not sure if this is really something that warrants note in the compatibility section. @dyemanov what do you think?

[pavel-zotov]

The time spent searching for the cause of the wrong result would have been much less if this ticket had been mentioned in the RN

[mrotteveel]

The ticket is mentioned in the release notes, just not in that detail.

[mrotteveel]

Specifically, the release notes only mention:

(CORE-2274) — MERGE has a non-standard behaviour, accepts multiple matches.

[dyemanov]

While the issue looks relatively minor, I don't mind if it would be mentioned also in the compatibility chapter.