BUG#49222: Mark RAND() as unsafe

Problem: When RAND() is binlogged in statement mode, the seed is
binlogged too, so the replication slave generates the same
sequence of random numbers. This makes replication work in many
cases, but not in all cases: the order of rows is not guaranteed
for, e.g., UPDATE or INSERT...SELECT statements, so the row data
will be different if master and slave retrieve the rows in
different orders.
Fix: Mark RAND() as unsafe. It will generate a warning if
binlog_format=STATEMENT and switch to row-logging if
binlog_format=ROW.


mysql-test/extra/rpl_tests/rpl_row_func003.test:
  updated test case to ignore new warnings
mysql-test/suite/binlog/r/binlog_unsafe.result:
  updated result file
mysql-test/suite/binlog/t/binlog_unsafe.test:
  Added test for RAND().
  Also clarified some old comments.
mysql-test/suite/rpl/r/rpl_misc_functions.result:
  updated result file
mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result:
  updated test case to ignore new warnings
mysql-test/suite/rpl/r/rpl_optimize.result:
  updated result file
mysql-test/suite/rpl/r/rpl_row_func003.result:
  updated result file
mysql-test/suite/rpl/t/rpl_misc_functions.test:
  updated test case to ignore new warnings
mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test:
  updated test case to ignore new warnings
mysql-test/suite/rpl/t/rpl_optimize.test:
  updated test case to ignore new warnings
mysql-test/suite/rpl/t/rpl_trigger.test:
  updated test case to ignore new warnings
mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result:
  updated result file
sql/item_create.cc:
  Mark RAND() unsafe.

11 files changed, 39 insertions, 11 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result
index 8bbf993a727..6b2a83c9483 100644
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result
@@ -379,6 +379,9 @@ Note	1592	Statement may not be safe to log in statement format.
 INSERT INTO t1 VALUES (VERSION());
 Warnings:
 Note	1592	Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (RAND());
+Warnings:
+Note	1592	Statement may not be safe to log in statement format.
 DELETE FROM t1;
 SET TIMESTAMP=1000000;
 INSERT INTO t1 VALUES
diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test
index 1acb4d090ca..5e399f3e602 100644
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test
@@ -47,6 +47,8 @@
 # BUG#34768: nondeterministic INSERT using LIMIT logged in stmt mode if binlog_format=mixed
 # BUG#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
 # BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
+# BUG#47995: Mark user functions as unsafe
+# BUG#49222: Mare RAND() unsafe
 #
 # ==== Related test cases ====
 #
@@ -391,6 +393,7 @@ SET @@SESSION.SQL_MODE = @save_sql_mode;
 
 #
 # BUG#47995: Mark user functions as unsafe
+# BUG#49222: Mare RAND() unsafe
 #
 # Test that the system functions that are supposed to be marked unsafe
 # generate a warning.  Each INSERT statement below should generate a
@@ -400,27 +403,28 @@ SET @@SESSION.SQL_MODE = @save_sql_mode;
 CREATE TABLE t1 (a VARCHAR(1000));
 INSERT INTO t1 VALUES (CURRENT_USER());       #marked unsafe before BUG#47995
 INSERT INTO t1 VALUES (FOUND_ROWS());         #marked unsafe before BUG#47995
-INSERT INTO t1 VALUES (GET_LOCK('tmp', 1));
-INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));
-INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));
-INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (GET_LOCK('tmp', 1));   #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));  #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));  #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe in BUG#39701
 INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
-INSERT INTO t1 VALUES (RELEASE_LOCK('tmp'));
+INSERT INTO t1 VALUES (RELEASE_LOCK('tmp'));  #marked unsafe in BUG#47995
 INSERT INTO t1 VALUES (ROW_COUNT());          #marked unsafe before BUG#47995
 INSERT INTO t1 VALUES (SESSION_USER());       #marked unsafe before BUG#47995
-INSERT INTO t1 VALUES (SLEEP(1));
-INSERT INTO t1 VALUES (SYSDATE());
+INSERT INTO t1 VALUES (SLEEP(1));             #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (SYSDATE());            #marked unsafe in BUG#47995
 INSERT INTO t1 VALUES (SYSTEM_USER());        #marked unsafe before BUG#47995
 INSERT INTO t1 VALUES (USER());               #marked unsafe before BUG#47995
 INSERT INTO t1 VALUES (UUID());               #marked unsafe before BUG#47995
 INSERT INTO t1 VALUES (UUID_SHORT());         #marked unsafe before BUG#47995
-INSERT INTO t1 VALUES (VERSION());
+INSERT INTO t1 VALUES (VERSION());            #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (RAND());               #marked unsafe in BUG#49222
 DELETE FROM t1;
 
 # Since we replicate the TIMESTAMP variable, functions affected by the
 # TIMESTAMP variable are safe to replicate.  So we check that the
-# following following functions depend on the TIMESTAMP variable and
-# don't generate a warning.
+# following following functions that depend on the TIMESTAMP variable
+# are not unsafe and don't generate a warning.
 
 SET TIMESTAMP=1000000;
 INSERT INTO t1 VALUES
diff --git a/mysql-test/suite/rpl/r/rpl_misc_functions.result b/mysql-test/suite/rpl/r/rpl_misc_functions.result
index 28b777822e4..6d69235927e 100644
--- a/mysql-test/suite/rpl/r/rpl_misc_functions.result
+++ b/mysql-test/suite/rpl/r/rpl_misc_functions.result
@@ -4,6 +4,7 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
 create table t1(id int, i int, r1 int, r2 int, p varchar(100));
 insert into t1 values(1, connection_id(), 0, 0, "");
 insert into t1 values(2, 0, rand()*1000, rand()*1000, "");
diff --git a/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result b/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result
index c4842a284cd..3b9b741e040 100644
--- a/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result
+++ b/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result
@@ -4,6 +4,7 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
 CREATE TABLE t1 (a VARCHAR(1000));
 INSERT INTO t1 VALUES (CONNECTION_ID());
 INSERT INTO t1 VALUES (CONNECTION_ID());
diff --git a/mysql-test/suite/rpl/r/rpl_optimize.result b/mysql-test/suite/rpl/r/rpl_optimize.result
index 79891169fbc..1ae94a3ca36 100644
--- a/mysql-test/suite/rpl/r/rpl_optimize.result
+++ b/mysql-test/suite/rpl/r/rpl_optimize.result
@@ -4,6 +4,7 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
 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);
 INSERT INTO t1 (a) SELECT null FROM t1;
diff --git a/mysql-test/suite/rpl/r/rpl_row_func003.result b/mysql-test/suite/rpl/r/rpl_row_func003.result
index a5fd46a2ce3..94d01b50ce5 100644
--- a/mysql-test/suite/rpl/r/rpl_row_func003.result
+++ b/mysql-test/suite/rpl/r/rpl_row_func003.result
@@ -4,6 +4,7 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
 DROP FUNCTION IF EXISTS test.f1;
 DROP TABLE IF EXISTS test.t1;
 CREATE TABLE test.t1 (a INT NOT NULL AUTO_INCREMENT, c CHAR(16),PRIMARY KEY(a))ENGINE=INNODB;
diff --git a/mysql-test/suite/rpl/t/rpl_misc_functions.test b/mysql-test/suite/rpl/t/rpl_misc_functions.test
index d2e61d579e3..b84042160cd 100644
--- a/mysql-test/suite/rpl/t/rpl_misc_functions.test
+++ b/mysql-test/suite/rpl/t/rpl_misc_functions.test
@@ -3,12 +3,16 @@
 #
 source include/master-slave.inc;
 
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
+
 create table t1(id int, i int, r1 int, r2 int, p varchar(100));
 insert into t1 values(1, connection_id(), 0, 0, "");
 # don't put rand and password in the same query, to see if they replicate
 # independently
 # Pure rand test
+--disable_warnings
 insert into t1 values(2, 0, rand()*1000, rand()*1000, "");
+--enable_warnings
 # change the rand suite on the master (we do this because otherwise password()
 # benefits from the fact that the above rand() is well replicated : 
 # it picks the same sequence element, which hides a possible bug in password() replication.
@@ -19,7 +23,9 @@ set sql_log_bin=1;
 # Pure password test
 insert into t1 values(3, 0, 0, 0, password('does_this_work?'));
 # "altogether now"
+--disable_warnings
 insert into t1 values(4, connection_id(), rand()*1000, rand()*1000, password('does_this_still_work?'));
+--enable_warnings
 select * into outfile 'rpl_misc_functions.outfile' from t1;
 let $MYSQLD_DATADIR= `select @@datadir`;
 sync_slave_with_master;
@@ -73,11 +79,13 @@ DELIMITER ;|
 
 # Exercise the functions and procedures then compare the results on
 # the master to those on the slave.
+--disable_warnings
 CALL test_replication_sp1();
 CALL test_replication_sp2();
 INSERT INTO t1 (col_a) VALUES (test_replication_sf());
 INSERT INTO t1 (col_a) VALUES (test_replication_sf());
 INSERT INTO t1 (col_a) VALUES (test_replication_sf());
+--enable_warnings
 
 --sync_slave_with_master
 
diff --git a/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test b/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test
index a30eb3be374..9ff2e2d081e 100644
--- a/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test
+++ b/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test
@@ -17,6 +17,8 @@
 
 --source include/master-slave.inc
 
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
+
 CREATE TABLE t1 (a VARCHAR(1000));
 
 # We replicate the connection_id in the query_log_event
@@ -41,7 +43,9 @@ INSERT INTO t1 VALUES
   (UTC_TIMESTAMP());
 
 # We replicate the random seed in a rand_log_event
+--disable_warnings
 INSERT INTO t1 VALUES (RAND());
+--enable_warnings
 # We replicate the last_insert_id in an intvar_log_event
 INSERT INTO t1 VALUES (LAST_INSERT_ID());
 
diff --git a/mysql-test/suite/rpl/t/rpl_optimize.test b/mysql-test/suite/rpl/t/rpl_optimize.test
index f4582ba1167..87eb9dce818 100644
--- a/mysql-test/suite/rpl/t/rpl_optimize.test
+++ b/mysql-test/suite/rpl/t/rpl_optimize.test
@@ -13,6 +13,8 @@
 -- source include/not_ndb_default.inc
 -- source include/master-slave.inc
 
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
+
 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);
 INSERT INTO t1 (a) SELECT null FROM t1;
@@ -30,8 +32,8 @@ INSERT INTO t1 (a) SELECT null FROM t1;
 INSERT INTO t1 (a) SELECT null FROM t1;
 save_master_pos;
 # a few updates to force OPTIMIZE to do something
-update t1 set b=(a/2*rand());
 --disable_warnings
+update t1 set b=(a/2*rand());
 delete from t1 order by b limit 10000;
 --enable_warnings
 
diff --git a/mysql-test/suite/rpl/t/rpl_trigger.test b/mysql-test/suite/rpl/t/rpl_trigger.test
index 4b40fbea719..e296da01bad 100644
--- a/mysql-test/suite/rpl/t/rpl_trigger.test
+++ b/mysql-test/suite/rpl/t/rpl_trigger.test
@@ -40,10 +40,12 @@ insert into t3 values(100,"log",0,0,0);
 SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
 
 # Emulate that we have rows 2-9 deleted on the slave
+--disable_warnings
 insert into t1 values(1,1,rand()),(NULL,2,rand());
 insert into t2 (b) values(last_insert_id());
 insert into t2 values(3,0),(NULL,0);
 insert into t2 values(NULL,0),(500,0);
+--enable_warnings
 
 select a,b, truncate(rand_value,4) from t1;
 select * from t2;
diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result
index ad72a3fb244..7e750f2ce2a 100644
--- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result
+++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result
@@ -4,6 +4,7 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
 DROP FUNCTION IF EXISTS test.f1;
 DROP TABLE IF EXISTS test.t1;
 CREATE TABLE test.t1 (a INT NOT NULL AUTO_INCREMENT, c CHAR(16),PRIMARY KEY(a))ENGINE=NDB;