MySQL Bugs: #27809: documentation says REPLACE not working with MERGE, but it does

Bug #27809	documentation says REPLACE not working with MERGE, but it does
Submitted:	13 Apr 2007 13:03	Modified:	22 Feb 2008 18:42
Reporter:	Shane Bester	Email Updates:
Status:	Closed
Category:	Server: Merge	Severity:	S3 (Non-critical)
Version:	5.0.40BK	OS:	Any
Assigned to:	Paul DuBois	Target Version:
Tags:	merge
Triage:	D4 (Minor)

Description:
According to http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html
"REPLACE does not work."

It does work, at least on the base table that is getting the inserted row.  So, REPLACE
works as well as INSERT would.  Should the parser not reject the REPLACE INTO statement
if it didn't work on MERGE?

Not sure if this is a documentation bug, or if REPLACE should be disallowed (like INSERT
DELAYED is not allowed).

It's known fact that merge cannot maintain uniqueness over all underlying tables.

How to repeat:
flush tables;
drop table if exists m1;
drop table if exists t1;
drop table if exists t2;
create table t1(id int,unique(id))engine=myisam;
create table t2(id int,unique(id))engine=myisam;
create table m1(id int,unique(id))engine=mrg_myisam insert_method=last union=(t1,t2);
insert into t1(id) values (2);
insert into t2(id) values (1);
replace into m1(id) values (1);
replace into m1(id) values (2);
select * from m1;

Suggested fix:
not sure.

Thank you for a bug report. Verified just as described with latest 5.0.40-BK on Linux.

The REPLACE statement is handled by the SQL layer, that is it is high level task which is
handled the same way for all engines. Per discussion with Serg and Ingo, it is not worth
to implement another engine flag and alter REPLACE code to fix this problem as it affects
only very specific MERGE engine. Reassigning this bug to docs team to extend manual.

Docs team: please update "13.3.1. MERGE Table Problems" section of manual. Currently it
states that replace doesn't work with MERGE engine. Whereas it should state that REPLACE
is allowed with MERGE engine, but it doesn't work as expected. The same is true for
INSERT ... ON DUPLICATE KEY UPDATE.

Details:
REPLACE doesn't work as expected as MERGE engine cannot enforce uniqueness over the set
of underlying tables. The two key facts are:
- REPLACE can detect unique key violation only in underlying table that it is going to
write to (which is determined by INSERT_METHOD). Whereas it is expected to detect such
violation in a MERGE table itself.
- in case it detects such violation, it will only change corresponding row in first
underlying table the row is present in, whereas row with the same unique key value may
present in all underlying tables.

Thank you for your bug report. This issue has been addressed in the documentation. The
updated documentation will appear on our website shortly, and will be included in the
next release of the relevant products.