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 (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	5.0.40BK	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	merge

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.