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.
