Bug #24665 update+subselect+left join does not replicate correctly
Submitted: 28 Nov 2006 18:13 Modified: 29 Dec 2006 1:42
Reporter: Christian Neise Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version: 4.1.11-Debian_4sarge7-log OS:Linux (linux debian 3.1)
Assigned to: CPU Architecture:Any
Tags: left join, subselect, UPDATE

[28 Nov 2006 18:13] Christian Neise
Description:

The following statement affects _all_ rows on the slave, but only some rows on the master:
update A left join
    (SELECT b_id AS id FROM B
     UNION ALL
     SELECT c_id AS id FROM C) as X
     ON A.a_id=X.id
     SET val=''
     WHERE X.id IS NULL;

How to repeat:

You need a db replication with one master and one slave:

# on replication master
create table A (
    a_id int,
    val char
);

create table B(
    b_id int
);

create table C(
    c_id int
);

insert into A values (1,'a');
insert into A values (2,'a');
insert into A values (3,'a');
insert into A values (4,'a');
insert into A values (5,'a');

insert into B values (1);
insert into C values (2);

update A left join
    (SELECT b_id AS id FROM B
     UNION ALL
     SELECT c_id AS id FROM C) as X
     ON A.a_id=X.id
     SET val=''
     WHERE X.id IS NULL;

     
# also on replication master
mysql> select * from A;
+------+------+
| a_id | val  |
+------+------+
|    1 | a    |
|    2 | a    |
|    3 |      |
|    4 |      |
|    5 |      |
+------+------+
 
# on replication Slave
     mysql> select * from A;
+------+------+
| a_id | val  |
+------+------+
|    1 |      |
|    2 |      |
|    3 |      |
|    4 |      |
|    5 |      |
+------+------+
[28 Nov 2006 18:59] Christian Neise
Added the correct mysql server version.
[29 Nov 2006 1:42] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 4.1.22 (MySQL binaries!), and inform about the results.
[30 Dec 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".