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, 44 insertions, 40 deletions

diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index d7f65e84462..9635af18c57 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1057,6 +1057,7 @@ Note	1003	select timestampdiff(WEEK,_latin1'2001-02-01',_latin1'2001-05-01') AS
 select time_format('100:00:00', '%H %k %h %I %l');
 time_format('100:00:00', '%H %k %h %I %l')
 100 100 04 04 4
+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');
 drop function if exists t_slow_sysdate;
@@ -1080,6 +1081,7 @@ a != b
 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/r/gis.result b/mysql-test/r/gis.result
index 6a90ad27625..3848368fedd 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -679,10 +679,11 @@ insert into t1 values (null,null);
 ERROR 23000: Column 's1' cannot be null
 drop table t1;
 drop procedure if exists fn3;
-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;
 Function	sql_mode	Create Function
 fn3		CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point
+    DETERMINISTIC
 return GeomFromText("point(1 1)")
 select astext(fn3());
 astext(fn3())
diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result
index 4b089c1d5e9..03019bd5c1f 100644
--- a/mysql-test/r/grant2.result
+++ b/mysql-test/r/grant2.result
@@ -365,13 +365,14 @@ insert into mysql.user select * from t1;
 drop table t1, t2;
 drop database TESTDB;
 flush privileges;
+SET GLOBAL log_bin_trust_function_creators = 1;
 grant all privileges on test.* to `a@`@localhost;
 grant execute on * to `a@`@localhost;
 create table t2 (s1 int);
 insert into t2 values (1);
 drop function if exists f2;
-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//
 select f2();
 f2()
 1
@@ -379,3 +380,4 @@ drop function f2;
 drop table t2;
 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/r/innodb_notembedded.result b/mysql-test/r/innodb_notembedded.result
index cc13a429dfc..36714eb62fb 100644
--- a/mysql-test/r/innodb_notembedded.result
+++ b/mysql-test/r/innodb_notembedded.result
@@ -1,4 +1,5 @@
 drop table if exists t1;
+SET GLOBAL log_bin_trust_function_creators = 1;
 create table t1 (col1 integer primary key, col2 integer) engine=innodb;
 insert t1 values (1,100);
 create function f1 () returns integer begin
@@ -18,3 +19,4 @@ rollback;
 rollback;
 drop table t1;
 drop function f1;
+SET GLOBAL log_bin_trust_function_creators = 0;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 617e289d30d..062d9428f43 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1500,9 +1500,9 @@ create procedure proc_1() reset query cache;
 call proc_1();
 call proc_1();
 call proc_1();
-create function func_1() returns int begin reset query cache; return 1; end|
+create function func_1() returns int deterministic begin reset query cache; return 1; end|
 ERROR 0A000: RESET is not allowed in stored function or trigger
-create function func_1() returns int begin call proc_1(); return 1; end|
+create function func_1() returns int deterministic begin call proc_1(); return 1; end|
 select func_1(), func_1(), func_1() from dual;
 ERROR 0A000: RESET is not allowed in stored function or trigger
 drop function func_1;
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index d5996ee8163..39cc8f84085 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1077,10 +1077,12 @@ create procedure `p1`()
 begin
 select a, f1() from t1;
 end//
+SET GLOBAL log_bin_trust_function_creators = 1;
 call p1()//
 a	f1()
 1	2
 2	2
+SET GLOBAL log_bin_trust_function_creators = 0;
 drop procedure p1//
 drop function f1//
 drop table t1//
diff --git a/mysql-test/r/query_cache_notembedded.result b/mysql-test/r/query_cache_notembedded.result
index 8e5df012cfb..05ef28a3180 100644
--- a/mysql-test/r/query_cache_notembedded.result
+++ b/mysql-test/r/query_cache_notembedded.result
@@ -314,6 +314,7 @@ drop procedure f2;
 drop procedure f3;
 drop procedure f4;
 drop table t1;
+SET GLOBAL log_bin_trust_function_creators = 1;
 reset query cache;
 drop function if exists f1;
 create table t1 (id int);
@@ -345,3 +346,4 @@ id
 drop table t1;
 drop function f1;
 set GLOBAL query_cache_size=0;
+SET GLOBAL log_bin_trust_function_creators = 0;
diff --git a/mysql-test/r/rpl_sf.result b/mysql-test/r/rpl_sf.result
new file mode 100644
index 00000000000..46defc6908a
--- /dev/null
+++ b/mysql-test/r/rpl_sf.result
@@ -0,0 +1,23 @@
+set global log_bin_trust_function_creators=0;
+set binlog_format=STATEMENT;
+create function fn16456()
+returns int
+begin
+return unix_timestamp();
+end|
+ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
+set global log_bin_trust_function_creators=1;
+create function fn16456()
+returns int
+begin
+return unix_timestamp();
+end|
+set global log_bin_trust_function_creators=0;
+set binlog_format=ROW;
+select fn16456();
+fn16456()
+timestamp
+set binlog_format=STATEMENT;
+select fn16456();
+ERROR HY000: Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events
+drop function fn16456;
diff --git a/mysql-test/r/rpl_sp.result b/mysql-test/r/rpl_sp.result
index 803472c8bc5..dd85d932c66 100644
--- a/mysql-test/r/rpl_sp.result
+++ b/mysql-test/r/rpl_sp.result
@@ -124,9 +124,6 @@ select * from mysql.proc where name="foo4" and db='mysqltest1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
 select * from mysql.proc where name="foo4" and db='mysqltest1';
 db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
-set binlog_format=STATEMENT;
-call foo();
-ERROR HY000: Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events
 drop procedure foo;
 drop procedure foo2;
 drop procedure foo3;
@@ -255,15 +252,6 @@ select * from t2;
 a
 20
 100
-set binlog_format=STATEMENT;
-create function fn16456()
-returns int
-begin
-return unix_timestamp();
-end|
-select fn16456();
-ERROR HY000: Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events
-drop function fn16456;
 create trigger trg before insert on t1 for each row set new.a= 10;
 ERROR 42000: TRIGGER command denied to user 'zedjzlcsjhd'@'localhost' for table 't1'
 delete from t1;
@@ -376,12 +364,6 @@ return 10;
 end
 master-bin.000001	#	Query	1	#	use `mysqltest1`; SELECT `fn1`(100)
 master-bin.000001	#	Query	1	#	use `mysqltest1`; SELECT `fn1`(20)
-master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` function fn16456()
-returns int
-begin
-return unix_timestamp();
-end
-master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function fn16456
 master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t1
 master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost` trigger trg before insert on t1 for each row set new.a= 10
 master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values (1)
diff --git a/mysql-test/r/rpl_sp_effects.result b/mysql-test/r/rpl_sp_effects.result
index b42fe64e603..c2c44b06972 100644
--- a/mysql-test/r/rpl_sp_effects.result
+++ b/mysql-test/r/rpl_sp_effects.result
@@ -10,6 +10,7 @@ drop function if exists f1;
 drop table if exists t1,t2;
 drop view if exists v1;
 create table t1 (a int);
+SET GLOBAL log_bin_trust_function_creators = 1;
 create procedure p1()
 begin
 declare spv int default 0;
@@ -234,3 +235,4 @@ drop table t1;
 drop function f1;
 drop function f2;
 drop procedure p1;
+SET GLOBAL log_bin_trust_function_creators = 0;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 2dd7030caed..7a28201823e 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -243,7 +243,7 @@ set x = (select sum(t.i) from test.t2 t);
 insert into test.t1 values (id, x);
 end|
 drop procedure if exists sub3|
-create function sub3(i int) returns int
+create function sub3(i int) returns int deterministic
 return i+1|
 call sub1("sub1a", (select 7))|
 call sub1("sub1b", (select max(i) from t2))|
@@ -4802,22 +4802,6 @@ i
 0
 drop table t3|
 drop procedure bug16887|
-create table t3 (f1 int, f2 varchar(3), primary key(f1)) engine=innodb|
-insert into t3 values (1,'aaa'),(2,'bbb'),(3,'ccc')|
-CREATE FUNCTION bug13575 ( p1 integer ) 
-returns varchar(3) 
-BEGIN 
-DECLARE v1 VARCHAR(10) DEFAULT null;
-SELECT f2 INTO v1 FROM t3 WHERE f1 = p1; 
-RETURN v1;
-END|
-select distinct f1, bug13575(f1) from t3 order by f1|
-f1	bug13575(f1)
-1	aaa
-2	bbb
-3	ccc
-drop function bug13575;
-drop table t3|
 drop procedure if exists bug16474_1|
 drop procedure if exists bug16474_2|
 delete from t1|
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result
index 6fd67d00259..f7631e9657a 100644
--- a/mysql-test/r/timezone2.result
+++ b/mysql-test/r/timezone2.result
@@ -269,6 +269,7 @@ select * from t1;
 convert_tz(NULL, NULL, NULL)
 NULL
 drop table t1;
+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');
@@ -283,3 +284,4 @@ ldt	ldt2
 2006-04-19 16:30:00	2006-04-19 16:30:00
 drop table t1;
 drop function f1;
+SET GLOBAL log_bin_trust_function_creators = 0;