Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR

Fix tests for new behaviour: an error is thrown if a NON DETERMINISTIC
stored function (SF) is called during statement-based replication (SBR).


mysql-test/r/func_time.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/gis.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/r/grant2.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/innodb_notembedded.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/ps.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/r/query_cache.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/query_cache_notembedded.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/rpl_sp.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Move test for SF-calls in different replication modes to its own file, rpl_sf.
mysql-test/r/rpl_sp_effects.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/sp.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/r/timezone2.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/func_time.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/gis.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/t/grant2.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/innodb_notembedded.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/ps.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/query_cache.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/query_cache_notembedded.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/rpl_sp.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/rpl_sp_effects.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/sp.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/t/timezone2.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
sql/item_func.cc:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  If 'log_bin_trust_function_creators' variable is set, don't throw an error
  on calling a non-deterministc function in statement-based replication (SBR).
sql/sql_parse.cc:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Do not throw an error on calling a NON DETERMINISTIC stored procedure (SP)
  while doing statement-based replication (SBR), as the routine body is
  executed statement-by-statement.
mysql-test/r/rpl_sf.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Test for stored function (SF) calls in different replication modes.
  NON DETERMINISTIC SFs are not allowed while doing
  statement-based replication (SBR).
mysql-test/t/rpl_sf.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Test for stored function (SF) calls in different replication modes.
  NON DETERMINISTIC SFs are not allowed while doing
  statement-based replication (SBR).

12 files changed, 98 insertions, 36 deletions

diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index a6cbca61b84..b124ae905b3 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -580,6 +580,8 @@ select time_format('100:00:00', '%H %k %h %I %l');
 # Bug #12562: Make SYSDATE behave like it does in Oracle: always the current
 #             time, regardless of magic to make NOW() always the same for the
 #             entirety of a statement.
+SET GLOBAL log_bin_trust_function_creators = 1;
+
 create table t1 (a timestamp default '2005-05-05 01:01:01',
                  b timestamp default '2005-05-05 01:01:01');
 delimiter //;
@@ -609,6 +611,8 @@ drop trigger t_before;
 drop function t_slow_sysdate;
 drop table t1;
 
+SET GLOBAL log_bin_trust_function_creators = 0;
+
 create table t1 (a datetime, i int, b datetime);
 insert into t1 select sysdate(), sleep(1), sysdate() from dual;
 select a != b from t1;
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 7bba34be3ff..4e5a21c6789 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -393,7 +393,7 @@ drop table t1;
 --disable_warnings
 drop procedure if exists fn3;
 --enable_warnings
-create function fn3 () returns point return GeomFromText("point(1 1)"); 
+create function fn3 () returns point deterministic return GeomFromText("point(1 1)"); 
 show create function fn3;
 select astext(fn3());
 drop function fn3;
diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test
index 99da1fa1ee7..a533af995dc 100644
--- a/mysql-test/t/grant2.test
+++ b/mysql-test/t/grant2.test
@@ -484,6 +484,8 @@ flush privileges;
 # BUG#13310 incorrect user parsing by SP
 #
 
+SET GLOBAL log_bin_trust_function_creators = 1;
+
 grant all privileges on test.* to `a@`@localhost;
 grant execute on * to `a@`@localhost;
 connect (bug13310,localhost,'a@',,test);
@@ -494,11 +496,10 @@ insert into t2 values (1);
 drop function if exists f2;
 --enable_warnings
 delimiter //;
-create function f2 () returns int begin declare v int; select s1 from t2
-into v; return v; end//
+create function f2 () returns int
+begin declare v int; select s1 from t2 into v; return v; end//
 delimiter ;//
 select f2();
-
 drop function f2;
 drop table t2;
 disconnect bug13310;
@@ -506,3 +507,5 @@ disconnect bug13310;
 connection default;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost;
 drop user `a@`@localhost;
+
+SET GLOBAL log_bin_trust_function_creators = 0;
diff --git a/mysql-test/t/innodb_notembedded.test b/mysql-test/t/innodb_notembedded.test
index 53332d9fda4..2731f8e33ed 100644
--- a/mysql-test/t/innodb_notembedded.test
+++ b/mysql-test/t/innodb_notembedded.test
@@ -13,6 +13,7 @@ connect (b,localhost,root,,);
 # BUG#11238 - in prelocking mode SELECT .. FOR UPDATE is changed to
 # non-blocking SELECT
 #
+SET GLOBAL log_bin_trust_function_creators = 1;
 create table t1 (col1 integer primary key, col2 integer) engine=innodb;
 insert t1 values (1,100);
 delimiter |;
@@ -38,3 +39,4 @@ drop table t1;
 drop function f1;
 disconnect a;
 disconnect b;
+SET GLOBAL log_bin_trust_function_creators = 0;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 1a19355406a..ce8bc12db49 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -1559,8 +1559,8 @@ call proc_1();
 call proc_1();
 delimiter |;
 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
-create function func_1() returns int begin reset query cache; return 1; end|
-create function func_1() returns int begin call proc_1(); return 1; end|
+create function func_1() returns int deterministic begin reset query cache; return 1; end|
+create function func_1() returns int deterministic begin call proc_1(); return 1; end|
 delimiter ;|
 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
 select func_1(), func_1(), func_1() from dual;
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 67c4c4cb20b..55a638e18e9 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -788,7 +788,9 @@ create procedure `p1`()
 begin
  select a, f1() from t1;
 end//
+SET GLOBAL log_bin_trust_function_creators = 1;
 call p1()//
+SET GLOBAL log_bin_trust_function_creators = 0;
 drop procedure p1//
 drop function f1//
 
diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test
index 97be9f9f7ca..802022d061b 100644
--- a/mysql-test/t/query_cache_notembedded.test
+++ b/mysql-test/t/query_cache_notembedded.test
@@ -183,6 +183,8 @@ drop table t1;
 #
 # bug#14767: INSERT in SF + concurrent SELECT with query cache
 #
+SET GLOBAL log_bin_trust_function_creators = 1;
+
 reset query cache;
 --disable_warnings
 drop function if exists f1;
@@ -222,3 +224,4 @@ disconnect con2;
 connection default;
 
 set GLOBAL query_cache_size=0;
+SET GLOBAL log_bin_trust_function_creators = 0;
diff --git a/mysql-test/t/rpl_sf.test b/mysql-test/t/rpl_sf.test
new file mode 100644
index 00000000000..2b1e0350f1b
--- /dev/null
+++ b/mysql-test/t/rpl_sf.test
@@ -0,0 +1,68 @@
+# Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
+
+# save status
+
+let $oblf=`select @@SESSION.BINLOG_FORMAT`;
+let $otfc=`select @@log_bin_trust_function_creators`;
+
+set global log_bin_trust_function_creators=0;
+
+
+
+# fail *on definition*
+
+set binlog_format=STATEMENT;
+
+delimiter |;
+--error ER_BINLOG_UNSAFE_ROUTINE
+create function fn16456()
+       returns int
+begin
+       return unix_timestamp();
+end|
+delimiter ;|
+
+
+
+# force in definition, so we can see whether we fail on call
+
+set global log_bin_trust_function_creators=1;
+
+delimiter |;
+create function fn16456()
+       returns int
+begin
+       return unix_timestamp();
+end|
+delimiter ;|
+
+set global log_bin_trust_function_creators=0;
+
+
+
+# allow funcall in RBR
+
+set binlog_format=ROW;
+
+--replace_column 1 timestamp
+select fn16456();
+
+
+
+# fail funcall in SBR
+
+set binlog_format=STATEMENT;
+
+--error ER_BINLOG_ROW_RBR_TO_SBR
+select fn16456();
+
+
+
+# restore status
+
+drop function fn16456;
+
+--disable_query_log
+eval set binlog_format=$oblf;
+eval set global log_bin_trust_function_creators=$otfc;
+--enable_query_log
diff --git a/mysql-test/t/rpl_sp.test b/mysql-test/t/rpl_sp.test
index 877c03af513..4ee3338ad01 100644
--- a/mysql-test/t/rpl_sp.test
+++ b/mysql-test/t/rpl_sp.test
@@ -173,15 +173,6 @@ select * from mysql.proc where name="foo4" and db='mysqltest1';
 sync_slave_with_master;
 select * from mysql.proc where name="foo4" and db='mysqltest1';
 
-# fail if non-deterministic SP is called in SBR, bug#16456
-let $oblf=`select @@SESSION.BINLOG_FORMAT`;
-set binlog_format=STATEMENT;
---error ER_BINLOG_ROW_RBR_TO_SBR
-call foo();
---disable_query_log
-eval set binlog_format=$oblf;
---enable_query_log
-
 # ********************** PART 2 : FUNCTIONS ***************
 
 connection master;
@@ -326,25 +317,6 @@ sync_slave_with_master;
 # check that this failed-in-the-middle replicated right:
 select * from t2;
 
-# fail if non-deterministic SF is called in SBR, bug#16456
-connection master;
-let $oblf=`select @@SESSION.BINLOG_FORMAT`;
-set binlog_format=STATEMENT;
-delimiter |;
-create function fn16456()
-       returns int
-begin
-       return unix_timestamp();
-end|
-delimiter ;|
---error ER_BINLOG_ROW_RBR_TO_SBR
-select fn16456();
---disable_query_log
-eval set binlog_format=$oblf;
---enable_query_log
-drop function fn16456;
-
-
 # ********************** PART 3 : TRIGGERS ***************
 
 connection con1;
@@ -555,5 +527,5 @@ set global log_bin_trust_function_creators=0;
 connection master;
 set global log_bin_trust_function_creators=0;
 --echo End of 5.0 tests
---echo End of 5.1 tests
 
+--echo End of 5.1 tests
diff --git a/mysql-test/t/rpl_sp_effects.test b/mysql-test/t/rpl_sp_effects.test
index e1746682b76..027bfd69f36 100644
--- a/mysql-test/t/rpl_sp_effects.test
+++ b/mysql-test/t/rpl_sp_effects.test
@@ -20,6 +20,8 @@ drop view if exists v1;
 --enable_warnings
 create table t1 (a int);
 
+SET GLOBAL log_bin_trust_function_creators = 1;
+
 # 1. Test simple variables use.
 delimiter //;
 create procedure p1()
@@ -205,3 +207,5 @@ drop function f1;
 drop function f2;
 drop procedure p1;
 sync_slave_with_master;
+
+SET GLOBAL log_bin_trust_function_creators = 0;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index f8d540b4750..0fdee9406a8 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -358,7 +358,7 @@ end|
 --disable_warnings
 drop procedure if exists sub3|
 --enable_warnings
-create function sub3(i int) returns int
+create function sub3(i int) returns int deterministic
   return i+1|
 
 call sub1("sub1a", (select 7))|
diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test
index 862b9cc58d1..5e1f74d388f 100644
--- a/mysql-test/t/timezone2.test
+++ b/mysql-test/t/timezone2.test
@@ -228,6 +228,8 @@ drop table t1;
 # Test for bug #11081 "Using a CONVERT_TZ function in a stored function
 # or trigger fails".
 #
+SET GLOBAL log_bin_trust_function_creators = 1;
+
 create table t1 (ldt datetime, udt datetime);
 create function f1(i datetime) returns datetime
   return convert_tz(i, 'UTC', 'Europe/Moscow');
@@ -241,4 +243,6 @@ select ldt, f1(udt) as ldt2 from t1;
 drop table t1;
 drop function f1;
 
+SET GLOBAL log_bin_trust_function_creators = 0;
+
 # End of 5.0 tests