Bug #55211 INSERT...SELECT...LIMIT...UNION should be marked unsafe
Submitted: 13 Jul 2010 13:50 Modified: 13 Jul 2010 15:06
Reporter: Sven Sandberg Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: INSERT...SELECT, limit, UNION, unsafe

[13 Jul 2010 13:50] Sven Sandberg
Description:
INSERT ... SELECT ... LIMIT statements are marked unsafe, because the order that rows are retreived from the SELECT may differ between master and slave, and thus the LIMIT may pick a different set of rows on master and slave.

However, the following statement is not marked unsafe:

  INSERT INTO table (SELECT * FROM t1 LIMIT 1) UNION (SELECT * FROM t2);

How to repeat:
--source include/have_binlog_format_mixed_or_statement.inc
--source include/master-slave.inc

CREATE TABLE t (a INT);
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);

INSERT INTO t1 VALUES (1), (2);

--sync_slave_with_master
DELETE FROM t1;
INSERT INTO t1 VALUES (2), (1);

--connection master
INSERT INTO t (SELECT a FROM t1 LIMIT 1) UNION (SELECT a FROM t2);
SELECT * FROM t;

--sync_slave_with_master
SELECT * FROM t;

Suggested fix:
Make sure all statements that use a LIMIT are marked unsafe, even substatements inside UNIONs.

Add tests to check that unsafeness is propagated from subqueries occurring in joins, unions, subqueries in the FROM clause, and subqueries with EXISTS/NOT EXISTS/ANY/IN/SOME/ALL. Test both when LIMIT applies to the top-level statement adn when it applies to one or more of the sub-statements. Also add tests that combinations of these constructs propagate unsafeness correctly.
[13 Jul 2010 14:54] Sven Sandberg
see also BUG#42415