List:Commits« Previous MessageNext Message »
From:Rohit Kalhans Date:February 7 2012 7:09pm
Subject:bzr push into mysql-trunk branch (rohit.kalhans:3852 to 3853) Bug#11758263
View as plain text  
 3853 Rohit Kalhans	2012-02-08 [merge]
      merge from mysql-5.5 to trunk after the patch for bug#11758263.

    modified:
      mysql-test/extra/rpl_tests/rpl_insert_id_pk.test
      mysql-test/extra/rpl_tests/rpl_multi_update.test
      mysql-test/extra/rpl_tests/rpl_multi_update2.test
      mysql-test/extra/rpl_tests/rpl_multi_update3.test
      mysql-test/r/ps.result
      mysql-test/suite/binlog/r/binlog_unsafe.result
      mysql-test/suite/binlog/t/binlog_unsafe.test
      mysql-test/suite/rpl/r/rpl_auto_increment_11932.result
      mysql-test/suite/rpl/r/rpl_insert_id_pk.result
      mysql-test/suite/rpl/r/rpl_multi_update.result
      mysql-test/suite/rpl/r/rpl_multi_update2.result
      mysql-test/suite/rpl/r/rpl_multi_update3.result
      mysql-test/suite/rpl/r/rpl_rotate_logs.result
      mysql-test/suite/rpl/t/rpl_auto_increment_11932.test
      mysql-test/suite/rpl/t/rpl_multi_update2.test
      mysql-test/suite/rpl/t/rpl_multi_update3.test
      mysql-test/suite/rpl/t/rpl_optimize.test
      mysql-test/suite/rpl/t/rpl_rotate_logs.test
      mysql-test/suite/rpl/t/rpl_semi_sync_event.test
      mysql-test/suite/rpl/t/rpl_timezone.test
      mysql-test/t/multi_update.test
      mysql-test/t/ps.test
      sql/share/errmsg-utf8.txt
      sql/sql_base.cc
      sql/sql_lex.cc
      sql/sql_lex.h
      sql/sql_parse.cc
 3852 Guilhem Bichot	2012-02-07
      Fix for Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY HAS AN EMPTY RESULT
      Empty const table didn't cause the query to have an empty result set.
     @ mysql-test/include/subquery.inc
        Though the bug isn't exactly about subqueries (does not require
        a subquery, only a derived table), I put the testcase here
        because I want it tested with subquery materialization when
        merged in the wl6095 tree - it used to cause an assert in
        the wl6095 code).
        Without the code fix, both SELECTs using derived tables
        would return a row full of zeroes ("0" for int,
        "00:00:00" for time...), and "SELECT @var3" would
        return 12.
     @ sql/sql_executor.cc
        comments
     @ sql/sql_optimizer.cc
        Scenario of the bug follows (for the queries in subquery.inc).
        All tables are one-or-zero-row MyISAM tables.
        $subq is of the form:
          SELECT ... FROM t1 WHERE ... ,
        When run as a standalone query, it returns zero rows (because t1 is
        empty).
        But when $subq is put inside the definition of a derived table:
          SELECT ... FROM ($subq) AS alias3;
        then one row is returned, wrongly. Here is how it happens.
        * When we are materializing alias3, we start evaluating $subq; this
        finds that t1 is "an empty const table".
        * We thus come to this place in JOIN::optimize():
          if (const_table_map != found_const_table_map &&
              !(select_options & SELECT_DESCRIBE) &&
              (!conds ||
               !(conds->used_tables() & RAND_TABLE_BIT) ||
               select_lex->master_unit() == &thd->lex->unit))
          {
            zero_result_cause= "no matching row in const table";
            DBUG_PRINT("error",("Error: %s", zero_result_cause));
            goto setup_subq_exit;
          }
        * const_table_map != found_const_table_map (because there is an empty
        const table, which is in const_table_map and not in
        found_const_table_map)
        * this isn't EXPLAIN
        * this isn't the top query (we are inside a derived table)
        * the WHERE clause has RAND_TABLE_BIT because it uses a "random"
        construct, indeed:
        ** in the first testcase of subquery.inc, the trick of writing and
        reading @var3 in the same statement makes the optimizer correctly
        believe that @var3 is a changing value (thus, "random" - unpredictable
        for the optimizer)
        ** in the second testcase, both user variables and "NOT IN
        (inner subquery)" do the same trick.
        So, we don't enter the if() block. No short-circuit. Query is not
        marked as "empty result" in the optimization phase. t1's record buffer
        has been filled with NULLs (see mark_as_null_row() in
        join_read_system()).
        We then reach this place in do_select():
          if (join->tables == join->const_tables)
          {
            if (!join->conds || join->conds->val_int())
        We evaluate the WHERE (remember: on a pseudo-row of t1 filled with
        NULLs). In the first testcase, the WHERE is satisfied by the NULL
        row, so this row is written into the materialized derived temporary
        table. In the second testcase, in all logic the WHERE should not be
        satisfied (NULLs NOT IN (non-empty-inner-subq) should be UNKNOWN), but
        because columns of t1 are not NULLable, triggered conditions are not
        created in the inner subquery (see
        Item_in_subselect::row_value_transformer()), so things go further
        wrong.
        
        Afterwards, this temporary one-NULL-row table is read, and bad data is
        output (zeroes: there is conversion from NULL to zeroes at some stage,
        because columns of t1 are not NULLable; if I declare columns as
        NULLable I get NULLs in the output).
        
        The bugfix: in this code
          if (const_table_map != found_const_table_map &&
              !(select_options & SELECT_DESCRIBE) &&
              (!conds ||
               !(conds->used_tables() & RAND_TABLE_BIT) ||
               select_lex->master_unit() == &thd->lex->unit))
          {
            zero_result_cause= "no matching row in const table";
            DBUG_PRINT("error",("Error: %s", zero_result_cause));
            goto setup_subq_exit;
          }
        the two last conditions are wrong.
        If "const_table_map != found_const_table_map" it means that one empty
        const table has been found. It may have been found in two ways:
        * join_read_system() i.e. it's a table which has really zero rows
        (case of t1, in our scenario). In that case, the result of this query
        specification is guaranteed empty, no matter the WHERE condition, no
        matter if this query specification is a subquery or a top query.
        * join_read_const() i.e. it's a table which has a unique index, and we
        have in the WHERE something like unique_index=constant, and there is
        no match for "constant" in the index. Again, there is no need to check
        any other condition in the if(). make_join_statistics(), which
        extracts const tables, takes proper precautions to detect that "we
        have all information" to mark a table const. For example, if we have
        "unique_index=outer_table.column", where outer_table is outer to this
        query specification, then we won't mark as constant because we know
        that this equality depends on OUTER_REF_TABLE_BIT, which is "not an
        available table" at this stage (search for "// Found everything for
         ref"). Same for "unique_index=RAND()": RAND_TABLE_BIT is not
        available. So there is no need for the test on RAND_TABLE_BIT and
        on the placement of the query specification. By the way, those two
        conditions were added in 2004 with commit comment "af"...
        
        By deleting those wrong extra conditions, we don't let the NULL row go
        out.
        It would also be possible to delete '!(select_options &
        SELECT_DESCRIBE)' but it has been found that this condition
        - causes no wrong query result (it's only in EXPLAIN)
        - leads to a clearer EXPLAIN output: indeed, if we shortcut at this
        stage, EXPLAIN does not contain information about what table was found
        constant.
        - so it was decided to not touch this condition.

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_bka.result
      mysql-test/r/subquery_all_bka_nixbnl.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_bka.result
      mysql-test/r/subquery_nomat_nosj_bka_nixbnl.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_bka.result
      mysql-test/r/subquery_none_bka_nixbnl.result
      sql/sql_executor.cc
      sql/sql_optimizer.cc
=== modified file 'mysql-test/extra/rpl_tests/rpl_insert_id_pk.test'
--- a/mysql-test/extra/rpl_tests/rpl_insert_id_pk.test	2010-12-19 17:15:12 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_insert_id_pk.test	2012-02-07 13:29:56 +0000
@@ -10,6 +10,8 @@
 # We also check how the foreign_key_check variable is replicated
 
 -- source include/master-slave.inc
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
+
 #should work for both SBR and RBR
 
 create table t1(a int auto_increment, primary key(a));
@@ -50,6 +52,7 @@ create table t2(b int auto_increment, c
 insert into t1 values (10);
 insert into t1 values (null),(null),(null);
 insert into t2 values (5,0);
+--disable_warnings ONCE
 insert into t2 (c) select * from t1 ORDER BY a;
 select * from t2 ORDER BY b;
 sync_slave_with_master;

=== modified file 'mysql-test/extra/rpl_tests/rpl_multi_update.test'
--- a/mysql-test/extra/rpl_tests/rpl_multi_update.test	2010-12-19 17:07:28 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_multi_update.test	2012-02-07 13:29:56 +0000
@@ -1,5 +1,7 @@
 source include/master-slave.inc;
 
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
+
 eval CREATE TABLE t1 (
  a int unsigned not null auto_increment primary key,
  b int unsigned
@@ -11,6 +13,7 @@ eval CREATE TABLE t2 (
 ) ENGINE=$engine_type;
 
 INSERT INTO t1 VALUES (NULL, 0);
+--disable_warnings ONCE
 INSERT INTO t1 SELECT NULL, 0 FROM t1;
 
 INSERT INTO t2 VALUES (NULL, 0), (NULL,1);
@@ -18,6 +21,7 @@ INSERT INTO t2 VALUES (NULL, 0), (NULL,1
 SELECT * FROM t1 ORDER BY a;
 SELECT * FROM t2 ORDER BY a;
 
+--disable_warnings ONCE
 UPDATE t1, t2 SET t1.b = t2.b WHERE t1.a = t2.a;
 sync_slave_with_master;
 

=== modified file 'mysql-test/extra/rpl_tests/rpl_multi_update2.test'
--- a/mysql-test/extra/rpl_tests/rpl_multi_update2.test	2007-06-18 13:36:10 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_multi_update2.test	2012-02-07 13:29:56 +0000
@@ -17,6 +17,8 @@ eval CREATE TABLE t2 (
 ) ENGINE=$engine_type;
 
 INSERT INTO t1 VALUES (NULL, 0);
+
+--disable_warnings ONCE
 INSERT INTO t1 SELECT NULL, 0 FROM t1;
 
 INSERT INTO t2 VALUES (NULL, 0), (NULL,1);
@@ -24,6 +26,7 @@ INSERT INTO t2 VALUES (NULL, 0), (NULL,1
 SELECT * FROM t1 ORDER BY a;
 SELECT * FROM t2 ORDER BY a;
 
+--disable_warnings ONCE
 UPDATE t1, t2 SET t1.b = (t2.b+4) WHERE t1.a = t2.a;
 SELECT * FROM t1 ORDER BY a;
 SELECT * FROM t2 ORDER BY a;

=== modified file 'mysql-test/extra/rpl_tests/rpl_multi_update3.test'
--- a/mysql-test/extra/rpl_tests/rpl_multi_update3.test	2007-06-18 13:36:10 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_multi_update3.test	2012-02-07 13:29:56 +0000
@@ -18,6 +18,8 @@ eval CREATE TABLE t2 (
 ) ENGINE=$engine_type;
 
 INSERT INTO t1 VALUES (NULL, 0);
+
+--disable_warnings ONCE
 INSERT INTO t1 SELECT NULL, 0 FROM t1;
 
 INSERT INTO t2 VALUES (NULL, 0), (NULL,1);
@@ -25,6 +27,7 @@ INSERT INTO t2 VALUES (NULL, 0), (NULL,1
 SELECT * FROM t1 ORDER BY a;
 SELECT * FROM t2 ORDER BY a;
 
+--disable_warnings ONCE
 UPDATE t2, (SELECT a FROM t1 ORDER BY a) AS t SET t2.b = t.a+5 ;
 SELECT * FROM t1 ORDER BY a;
 SELECT * FROM t2 ORDER BY a;

=== modified file 'mysql-test/r/ps.result'
--- a/mysql-test/r/ps.result	2011-10-18 13:45:29 +0000
+++ b/mysql-test/r/ps.result	2012-02-07 19:06:57 +0000
@@ -1,3 +1,4 @@
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
 drop table if exists t1,t2,t3,t4;
 drop database if exists client_test_db;
 create table t1
@@ -230,8 +231,12 @@ execute stmt1;
 execute stmt1;
 prepare stmt1 from "create table t1 select 1 as i";
 execute stmt1;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE TABLE... SELECT...  on a table with an auto-increment column is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are inserted. This order cannot be predicted and may differ on master and the slave.
 drop table t1;
 execute stmt1;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE TABLE... SELECT...  on a table with an auto-increment column is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are inserted. This order cannot be predicted and may differ on master and the slave.
 prepare stmt1 from "insert into t1 select i from t1";
 execute stmt1;
 execute stmt1;
@@ -1713,6 +1718,8 @@ drop tables t1;
 prepare stmt from "create table t1 select ?";
 set @a=1.0;
 execute stmt using @a;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE TABLE... SELECT...  on a table with an auto-increment column is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are inserted. This order cannot be predicted and may differ on master and the slave.
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (

=== modified file 'mysql-test/suite/binlog/r/binlog_unsafe.result'
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result	2011-09-29 10:42:53 +0000
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result	2012-02-07 19:06:57 +0000
@@ -1760,7 +1760,7 @@ SELECT COUNT(*) FROM mysql.general_log;
 
 Invoking function func_sidef_1 invoking statement that is unsafe in many ways.
 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; RETURN 0; END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO t1 SELECT func_sidef_1();
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1769,12 +1769,13 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 
 Invoking function func_sidef_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO t2 SELECT func_sidef_2();
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1783,13 +1784,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP FUNCTION func_sidef_2;
 
 Invoking procedure proc_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 CALL proc_2();
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1798,13 +1800,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP PROCEDURE proc_2;
 
 Invoking trigger trig_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO trigger_table_2 VALUES (1);
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1813,6 +1816,7 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP TRIGGER trig_2;
@@ -1826,7 +1830,8 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
-* binlog_format = STATEMENT: expect 6 warnings.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO t2 SELECT * FROM view_sidef_2;
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1835,13 +1840,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP VIEW view_sidef_2;
 
 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 EXECUTE prep_2;
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1850,6 +1856,7 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP PREPARE prep_2;
@@ -1857,7 +1864,7 @@ DROP FUNCTION func_sidef_1;
 
 Invoking procedure proc_1 invoking statement that is unsafe in many ways.
 CREATE PROCEDURE proc_1() BEGIN INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; INSERT INTO ta1 VALUES (47); END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 CALL proc_1();
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1866,12 +1873,13 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 
 Invoking function func_sidef_2 invoking procedure proc_1 invoking statement that is unsafe in many ways.
 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO t2 SELECT func_sidef_2();
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1880,13 +1888,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP FUNCTION func_sidef_2;
 
 Invoking procedure proc_2 invoking procedure proc_1 invoking statement that is unsafe in many ways.
 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 CALL proc_2();
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1895,13 +1904,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP PROCEDURE proc_2;
 
 Invoking trigger trig_2 invoking procedure proc_1 invoking statement that is unsafe in many ways.
 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO trigger_table_2 VALUES (1);
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1910,13 +1920,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP TRIGGER trig_2;
 
 Invoking prepared statement prep_2 invoking procedure proc_1 invoking statement that is unsafe in many ways.
 PREPARE prep_2 FROM "CALL proc_1()";
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 EXECUTE prep_2;
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1925,6 +1936,7 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP PREPARE prep_2;
@@ -1932,7 +1944,7 @@ DROP PROCEDURE proc_1;
 
 Invoking trigger trig_1 invoking statement that is unsafe in many ways.
 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO trigger_table_1 VALUES (1);
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1941,12 +1953,13 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 
 Invoking function func_sidef_2 invoking trigger trig_1 invoking statement that is unsafe in many ways.
 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO t2 SELECT func_sidef_2();
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1955,13 +1968,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP FUNCTION func_sidef_2;
 
 Invoking procedure proc_2 invoking trigger trig_1 invoking statement that is unsafe in many ways.
 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 CALL proc_2();
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1970,13 +1984,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP PROCEDURE proc_2;
 
 Invoking trigger trig_2 invoking trigger trig_1 invoking statement that is unsafe in many ways.
 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT INTO trigger_table_2 VALUES (1);
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -1985,13 +2000,14 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP TRIGGER trig_2;
 
 Invoking prepared statement prep_2 invoking trigger trig_1 invoking statement that is unsafe in many ways.
 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 EXECUTE prep_2;
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -2000,6 +2016,7 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP PREPARE prep_2;
@@ -2007,7 +2024,7 @@ DROP TRIGGER trig_1;
 
 Invoking prepared statement prep_1 invoking statement that is unsafe in many ways.
 PREPARE prep_1 FROM "INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1";
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 EXECUTE prep_1;
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -2016,12 +2033,13 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 DROP PREPARE prep_1;
 
 Invoking statement that is unsafe in many ways.
-* binlog_format = STATEMENT: expect 6 warnings.
+* binlog_format = STATEMENT: expect 7 warnings.
 INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1;
 Warnings:
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
@@ -2030,6 +2048,7 @@ Note	1592	Unsafe statement written to th
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave.
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
 * binlog_format = MIXED: expect row events in binlog and no warning.
 

=== modified file 'mysql-test/suite/binlog/t/binlog_unsafe.test'
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test	2011-09-29 10:42:53 +0000
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test	2012-02-07 19:06:57 +0000
@@ -233,7 +233,7 @@ while ($unsafe_type < 9) {
     --let $value_0=
     --let $sel_sidef_0=
     --let $sel_retval_0=
-    --let $CRC_ARG_expected_number_of_warnings= 6
+    --let $CRC_ARG_expected_number_of_warnings= 7
   }
 
   if ($unsafe_type == 8) {

=== modified file 'mysql-test/suite/rpl/r/rpl_auto_increment_11932.result'
--- a/mysql-test/suite/rpl/r/rpl_auto_increment_11932.result	2011-11-19 08:08:03 +0000
+++ b/mysql-test/suite/rpl/r/rpl_auto_increment_11932.result	2012-02-07 19:06:57 +0000
@@ -3,6 +3,7 @@ Warnings:
 Note	1756	Sending passwords in plain text without SSL/TLS is extremely insecure.
 Note	1757	Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
 [connection master]
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
 drop database if exists test1;
 create database test1;
 use test1;

=== modified file 'mysql-test/suite/rpl/r/rpl_insert_id_pk.result'
--- a/mysql-test/suite/rpl/r/rpl_insert_id_pk.result	2011-11-19 08:08:03 +0000
+++ b/mysql-test/suite/rpl/r/rpl_insert_id_pk.result	2012-02-07 19:06:57 +0000
@@ -3,6 +3,7 @@ Warnings:
 Note	1756	Sending passwords in plain text without SSL/TLS is extremely insecure.
 Note	1757	Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
 [connection master]
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
 create table t1(a int auto_increment, primary key(a));
 create table t2(b int auto_increment, c int, primary key(b));
 insert into t1 values (1),(2),(3);

=== modified file 'mysql-test/suite/rpl/r/rpl_multi_update.result'
--- a/mysql-test/suite/rpl/r/rpl_multi_update.result	2011-11-19 08:08:03 +0000
+++ b/mysql-test/suite/rpl/r/rpl_multi_update.result	2012-02-07 19:06:57 +0000
@@ -3,6 +3,7 @@ Warnings:
 Note	1756	Sending passwords in plain text without SSL/TLS is extremely insecure.
 Note	1757	Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
 [connection master]
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
 CREATE TABLE t1 (
 a int unsigned not null auto_increment primary key,
 b int unsigned

=== modified file 'mysql-test/suite/rpl/r/rpl_multi_update2.result'
--- a/mysql-test/suite/rpl/r/rpl_multi_update2.result	2011-11-19 08:08:03 +0000
+++ b/mysql-test/suite/rpl/r/rpl_multi_update2.result	2012-02-07 19:06:57 +0000
@@ -3,6 +3,7 @@ Warnings:
 Note	1756	Sending passwords in plain text without SSL/TLS is extremely insecure.
 Note	1757	Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
 [connection master]
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT');
 drop table if exists t1,t2;
 CREATE TABLE t1 (
 a int unsigned not null auto_increment primary key,

=== modified file 'mysql-test/suite/rpl/r/rpl_multi_update3.result'
--- a/mysql-test/suite/rpl/r/rpl_multi_update3.result	2011-11-19 08:08:03 +0000
+++ b/mysql-test/suite/rpl/r/rpl_multi_update3.result	2012-02-07 19:06:57 +0000
@@ -3,6 +3,7 @@ Warnings:
 Note	1756	Sending passwords in plain text without SSL/TLS is extremely insecure.
 Note	1757	Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
 [connection master]
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
 
 -------- Test for BUG#9361 --------
 CREATE TABLE t1 (

=== modified file 'mysql-test/suite/rpl/r/rpl_rotate_logs.result'
--- a/mysql-test/suite/rpl/r/rpl_rotate_logs.result	2011-11-19 08:08:03 +0000
+++ b/mysql-test/suite/rpl/r/rpl_rotate_logs.result	2012-02-07 19:06:57 +0000
@@ -1,3 +1,4 @@
+CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
 CALL mtr.add_suppression("Failed to open the existing info file");
 CALL mtr.add_suppression("Error while reading from master info file");
 CALL mtr.add_suppression("Failed to create a new info file");

=== modified file 'mysql-test/suite/rpl/t/rpl_auto_increment_11932.test'
--- a/mysql-test/suite/rpl/t/rpl_auto_increment_11932.test	2010-12-19 17:15:12 +0000
+++ b/mysql-test/suite/rpl/t/rpl_auto_increment_11932.test	2012-02-07 13:29:56 +0000
@@ -8,6 +8,7 @@
 # Test supplied by Are Casilla
 
 source include/master-slave.inc;
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
 --disable_warnings
 connection master;
 drop database if exists test1;
@@ -42,12 +43,16 @@ CREATE PROCEDURE simpleproc3 ()
     $
 DELIMITER ;$
 
+--disable_warnings
 CALL simpleproc3();
+--enable_warnings
 
 select * from t2;
 
 TRUNCATE TABLE `t1`;
+--disable_warnings
 CALL simpleproc3();
+--enable_warnings
 
 select * from t1;
 

=== modified file 'mysql-test/suite/rpl/t/rpl_multi_update2.test'
--- a/mysql-test/suite/rpl/t/rpl_multi_update2.test	2010-12-19 17:07:28 +0000
+++ b/mysql-test/suite/rpl/t/rpl_multi_update2.test	2012-02-07 13:29:56 +0000
@@ -6,6 +6,7 @@
 #######################################################
 --source include/not_ndb_default.inc
 --source include/master-slave.inc
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT');
 let $engine_type=MyISAM;
 --source extra/rpl_tests/rpl_multi_update2.test
 --source include/rpl_end.inc

=== modified file 'mysql-test/suite/rpl/t/rpl_multi_update3.test'
--- a/mysql-test/suite/rpl/t/rpl_multi_update3.test	2010-12-19 17:07:28 +0000
+++ b/mysql-test/suite/rpl/t/rpl_multi_update3.test	2012-02-07 13:29:56 +0000
@@ -6,6 +6,7 @@
 #######################################################
 --source include/not_ndb_default.inc
 --source include/master-slave.inc
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
 let $engine_type=MyISAM;
 -- source extra/rpl_tests/rpl_multi_update3.test
 --source include/rpl_end.inc

=== modified file 'mysql-test/suite/rpl/t/rpl_optimize.test'
--- a/mysql-test/suite/rpl/t/rpl_optimize.test	2010-12-19 17:22:30 +0000
+++ b/mysql-test/suite/rpl/t/rpl_optimize.test	2012-02-07 19:06:57 +0000
@@ -19,6 +19,7 @@ enable_query_log;
 
 create table t1 (a int not null auto_increment primary key, b int, key(b));
 INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+--disable_warnings
 INSERT INTO t1 (a) SELECT null FROM t1;
 INSERT INTO t1 (a) SELECT null FROM t1;
 INSERT INTO t1 (a) SELECT null FROM t1;
@@ -32,6 +33,7 @@ INSERT INTO t1 (a) SELECT null FROM t1;
 INSERT INTO t1 (a) SELECT null FROM t1;
 INSERT INTO t1 (a) SELECT null FROM t1;
 INSERT INTO t1 (a) SELECT null FROM t1;
+--enable_warnings
 save_master_pos;
 # a few updates to force OPTIMIZE to do something
 --disable_warnings

=== modified file 'mysql-test/suite/rpl/t/rpl_rotate_logs.test'
--- a/mysql-test/suite/rpl/t/rpl_rotate_logs.test	2011-10-13 14:01:50 +0000
+++ b/mysql-test/suite/rpl/t/rpl_rotate_logs.test	2012-02-07 19:06:57 +0000
@@ -28,6 +28,7 @@ chmod 0000 $MYSQLD_SLAVE_DATADIR/master.
 connection slave;
 
 --disable_result_log
+CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
 CALL mtr.add_suppression("Failed to open the existing info file");
 CALL mtr.add_suppression("Error while reading from master info file");
 CALL mtr.add_suppression("Failed to create a new info file");

=== modified file 'mysql-test/suite/rpl/t/rpl_semi_sync_event.test'
--- a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test	2011-03-25 15:28:11 +0000
+++ b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test	2012-02-07 13:29:56 +0000
@@ -52,9 +52,10 @@ SET GLOBAL event_scheduler = ON;
 replace_result $engine_type ENGINE_TYPE;
 eval CREATE TABLE t1 (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f varchar(8)) ENGINE=$engine_type;
 INSERT INTO t1 (f) VALUES ('a'),('a'),('a'),('a'),('a');
+--disable_warnings
 INSERT INTO t1 SELECT i+5, f FROM t1;
 INSERT INTO t1 SELECT i+10, f FROM t1;
-
+--enable_warnings
 CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND
 DO INSERT INTO t1 VALUES (SLEEP(5),CONCAT('ev1_',CONNECTION_ID()));
 CREATE EVENT ev2 ON SCHEDULE EVERY 1 SECOND

=== modified file 'mysql-test/suite/rpl/t/rpl_timezone.test'
--- a/mysql-test/suite/rpl/t/rpl_timezone.test	2010-12-19 17:22:30 +0000
+++ b/mysql-test/suite/rpl/t/rpl_timezone.test	2012-02-07 19:06:57 +0000
@@ -13,14 +13,14 @@
 # timezone used in CONVERT_TZ is not binlogged. To debug (by Guilhem
 # and possibly Konstantin).
 
+source include/master-slave.inc;
+
 --disable_query_log
 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
 --enable_query_log
 
 --disable_ps_protocol
 
-source include/master-slave.inc;
-
 # Save original timezone
 set @my_time_zone= @@global.time_zone;
 
@@ -90,6 +90,7 @@ insert into t1 values ('20040101000000',
 # from originally inserted)
 #
 set time_zone='MET';
+--disable_warnings ONCE
 insert into t2 (select * from t1);
 SELECT * FROM t1 ORDER BY n;
 sync_slave_with_master;

=== modified file 'mysql-test/t/multi_update.test'
--- a/mysql-test/t/multi_update.test	2011-12-13 14:10:33 +0000
+++ b/mysql-test/t/multi_update.test	2012-02-07 19:06:57 +0000
@@ -352,6 +352,7 @@ create table `t2` (`c2_id` int(10) unsig
 insert into t1 values (0,'A01-Comp',1);
 insert into t1 values (0,'B01-Comp',1);
 insert into t2 values (0,1,'A Note',1);
+--disable_warnings ONCE
 update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2;
 select * from t1;
 select * from t2;

=== modified file 'mysql-test/t/ps.test'
--- a/mysql-test/t/ps.test	2011-10-20 11:25:01 +0000
+++ b/mysql-test/t/ps.test	2012-02-07 19:06:57 +0000
@@ -1,5 +1,6 @@
 -- source include/not_embedded.inc
 -- source include/have_log_bin.inc
+call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); 
 #
 # SQL Syntax for Prepared Statements test
 #
@@ -431,6 +432,7 @@ deallocate prepare stmt;
 create table t1 (a int); 
 insert into t1 values (1),(2),(3);
 create table t2 select * from t1;
+--disable_warnings
 prepare stmt FROM 'create table t2 select * from t1';
 drop table t2;
 execute stmt;
@@ -440,6 +442,7 @@ execute stmt;
 execute stmt;
 drop table t2;
 execute stmt;
+--enable_warnings
 drop table t1,t2;
 deallocate prepare stmt;
 
@@ -1172,6 +1175,7 @@ create database mysqltest character set
 prepare stmt1 from "create table mysqltest.t1 (c char(10))";
 prepare stmt2 from "create table mysqltest.t2 select 'test'";
 execute stmt1;
+--disable_warnings ONCE
 execute stmt2;
 show create table mysqltest.t1;
 show create table mysqltest.t2;
@@ -1179,6 +1183,7 @@ drop table mysqltest.t1;
 drop table mysqltest.t2;
 alter database mysqltest character set latin1;
 execute stmt1;
+--disable_warnings ONCE
 execute stmt2;
 show create table mysqltest.t1;
 show create table mysqltest.t2;

=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt	2012-01-19 15:55:36 +0000
+++ b/sql/share/errmsg-utf8.txt	2012-02-07 19:06:57 +0000
@@ -6624,3 +6624,10 @@ ER_INNODB_FT_LIMIT
 
 ER_INNODB_NO_FT_TEMP_TABLE
   eng "Cannot create FULLTEXT index on temporary InnoDB table"
+
+ER_BINLOG_UNSAFE_WRITE_AUTOINC_SELECT
+  eng "Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave."
+
+ER_BINLOG_UNSAFE_CREATE_SELECT_AUTOINC
+  eng "CREATE TABLE... SELECT...  on a table with an auto-increment column is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are inserted. This order cannot be predicted and may differ on master and the slave."
+

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2012-01-31 15:16:16 +0000
+++ b/sql/sql_base.cc	2012-02-07 19:06:57 +0000
@@ -224,7 +224,8 @@ static bool auto_repair_table(THD *thd,
 static void free_cache_entry(TABLE *entry);
 static bool
 has_write_table_with_auto_increment(TABLE_LIST *tables);
-
+static bool
+has_write_table_with_auto_increment_and_select(TABLE_LIST *tables);
 
 uint cached_open_tables(void)
 {
@@ -5856,6 +5857,11 @@ bool lock_tables(THD *thd, TABLE_LIST *t
     /* We have to emulate LOCK TABLES if we are statement needs prelocking. */
     if (thd->lex->requires_prelocking())
     {
+
+    if (thd->variables.binlog_format != BINLOG_FORMAT_ROW && tables &&
+        has_write_table_with_auto_increment_and_select(tables))
+      thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT);
+
       /*
         A query that modifies autoinc column in sub-statement can make the 
         master and slave inconsistent.
@@ -9349,6 +9355,39 @@ has_write_table_with_auto_increment(TABL
   return 0;
 }
 
+/*
+   checks if the tables have select tables in the table list and write tables
+   with auto-increment column.
+
+  SYNOPSIS
+   has_two_write_locked_tables_with_auto_increment_and_select
+      tables        Table list
+
+  RETURN VALUES
+
+   -true if the table list has atleast one table with auto-increment column
+         and atleast one table to select from.
+   -false otherwise
+*/
+
+static bool
+has_write_table_with_auto_increment_and_select(TABLE_LIST *tables)
+{
+  bool has_select= false;
+  bool has_auto_increment_tables = has_write_table_with_auto_increment(tables);
+  for(TABLE_LIST *table= tables; table; table= table->next_global)
+  {
+     if (!table->placeholder() &&
+        (table->lock_type <= TL_READ_NO_INSERT))
+      {
+        has_select= true;
+        break;
+      }
+  }
+  return(has_select && has_auto_increment_tables);
+}
+
+
 
 /*
   Open and lock system tables for read.

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2012-01-20 14:44:16 +0000
+++ b/sql/sql_lex.cc	2012-02-07 19:06:57 +0000
@@ -65,9 +65,11 @@ Query_tables_list::binlog_stmt_unsafe_er
   ER_BINLOG_UNSAFE_MIXED_STATEMENT,
   ER_BINLOG_UNSAFE_INSERT_IGNORE_SELECT,
   ER_BINLOG_UNSAFE_INSERT_SELECT_UPDATE,
+  ER_BINLOG_UNSAFE_WRITE_AUTOINC_SELECT,
   ER_BINLOG_UNSAFE_REPLACE_SELECT,
   ER_BINLOG_UNSAFE_CREATE_IGNORE_SELECT,
   ER_BINLOG_UNSAFE_CREATE_REPLACE_SELECT,
+  ER_BINLOG_UNSAFE_CREATE_SELECT_AUTOINC,
   ER_BINLOG_UNSAFE_UPDATE_IGNORE
 };
 

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2012-01-05 10:12:47 +0000
+++ b/sql/sql_lex.h	2012-02-07 19:06:57 +0000
@@ -1257,6 +1257,13 @@ public:
     BINLOG_STMT_UNSAFE_INSERT_SELECT_UPDATE,
 
     /**
+     Query that writes to a table with auto_inc column after selecting from 
+     other tables are unsafe as the order in which the rows are retrieved by
+     select may differ on master and slave.
+    */
+    BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT,
+
+    /**
       INSERT...REPLACE SELECT is unsafe because which rows are replaced depends
       on the order that rows are retrieved by SELECT. This order cannot be
       predicted and may differ on master and the slave.
@@ -1278,6 +1285,14 @@ public:
     BINLOG_STMT_UNSAFE_CREATE_REPLACE_SELECT,
 
     /**
+      CREATE TABLE...SELECT on a table with auto-increment column is unsafe
+      because which rows are replaced depends on the order that rows are
+      retrieved from SELECT. This order cannot be predicted and may differ on
+      master and the slave
+    */
+    BINLOG_STMT_UNSAFE_CREATE_SELECT_AUTOINC,
+
+    /**
       UPDATE...IGNORE is unsafe because which rows are ignored depends on the
       order that rows are updated. This order cannot be predicted and may differ
       on master and the slave.

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2012-01-31 15:16:16 +0000
+++ b/sql/sql_parse.cc	2012-02-07 19:06:57 +0000
@@ -2649,9 +2649,12 @@ case SQLCOM_PREPARE:
       select_result *result;
 
       /*
-        CREATE TABLE...IGNORE/REPLACE SELECT... can be unsafe, unless
-        ORDER BY PRIMARY KEY clause is used in SELECT statement. We therefore
-        use row based logging if mixed or row based logging is available.
+         - CREATE TABLE...IGNORE
+         - REPLACE SELECT...
+         - CREATE TABLE [with auto inc. column]...SELECT
+        can be unsafe, unless ORDER BY PRIMARY KEY clause is used in SELECT
+        statement. We therefore use row based logging if mixed or row based
+        logging is available.
         TODO: Check if the order of the output of the select statement is
         deterministic. Waiting for BUG#42415
       */
@@ -2661,6 +2664,9 @@ case SQLCOM_PREPARE:
       if(lex->duplicates == DUP_REPLACE)
         lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_CREATE_REPLACE_SELECT);
 
+      if (lex->type & AUTO_INCREMENT_FLAG)
+        lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_CREATE_SELECT_AUTOINC);
+
       /*
         If:
         a) we inside an SP and there was NAME_CONST substitution,

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (rohit.kalhans:3852 to 3853) Bug#11758263Rohit Kalhans8 Feb