1 files changed, 294 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/t/rpl_sp_effects.test b/mysql-test/suite/rpl/t/rpl_sp_effects.test
new file mode 100644
index 00000000..08d806b1
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_sp_effects.test
@@ -0,0 +1,294 @@
+##########################################
+# Change Author: JBM
+# Change Date: 2006-05-02
+##########################################
+
+# Test of replication of stored procedures (WL#2146 for MySQL 5.0)
+-- source include/master-slave.inc
+
+# ****************************************************************
+connection master;
+
+SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
+
+# cleanup
+--disable_warnings
+drop procedure if exists p1;
+drop procedure if exists p2;
+drop function if exists f1;
+drop table if exists t1,t2;
+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()
+begin
+  declare spv int default 0;
+  while spv < 5 do
+    insert into t1 values(spv+1);
+    set spv=spv+1;
+  end while;
+end//
+delimiter ;//
+
+call p1();
+
+sync_slave_with_master;
+connection slave;
+SELECT * FROM t1 ORDER BY a;
+connection master;
+SELECT * FROM t1 ORDER BY a;
+
+# 2. Test SP variable name
+delimiter //;
+create procedure p2()
+begin
+  declare a int default 4;
+  create table t2 as select a;
+end//
+delimiter ;//
+
+call p2();
+SELECT * FROM t2 ORDER BY a;
+sync_slave_with_master;
+connection slave;
+SELECT * FROM t2 ORDER BY a;
+
+connection master;
+drop procedure p1;
+drop procedure p2;
+drop table t2;
+
+# 3. Test FUNCTIONs in various places
+
+delimiter //;
+create function f1(x int) returns int
+begin
+  insert into t1 values(x);
+  return x+1;
+end//
+
+create procedure p1(a int, b int)
+begin
+  declare v int default f1(5);
+  if (f1(6)) then
+    select 'yes';
+  end if;
+  set v = f1(7);
+  while f1(8) < 1 do 
+    select 'this cant be';
+  end while;
+
+end//
+delimiter ;//
+
+call p1(f1(1), f1(2));
+SELECT * FROM t1 ORDER BY a;
+
+create table t2(a int);
+insert into t2 values (10),(11);
+--disable_ps2_protocol
+SELECT a,f1(a) FROM t2 ORDER BY a;
+--enable_ps2_protocol
+
+# This shouldn't put separate 'call f1(3)' into binlog:
+insert into t2 select f1(3);
+SELECT 'master:',a FROM t1 ORDER BY a;
+
+sync_slave_with_master;
+connection slave;
+SELECT 'slave:',a FROM t1 ORDER BY a;
+
+connection master;
+drop procedure p1;
+delete from t1;
+delete from t2;
+
+# 4. VIEWs
+delete from t1;
+insert into t2 values(1),(2);
+create view v1 as select f1(a) as f from t2;
+--disable_ps2_protocol
+select * from v1 order by f;
+--enable_ps2_protocol
+SELECT 'master:',a FROM t1 ORDER BY a;
+
+sync_slave_with_master;
+connection slave;
+SELECT 'slave:',a FROM t1 ORDER BY a;
+
+connection master;
+drop view v1;
+delete from t1;
+
+# 5. Prepared statements.
+prepare s1 from 'select f1(?)';
+set @xx=123;
+execute s1 using @xx;
+SELECT 'master:',a FROM t1 ORDER BY a;
+
+sync_slave_with_master;
+connection slave;
+SELECT 'slave:',a FROM t1 ORDER BY a;
+
+connection master;
+delete from t1;
+
+# 5. Cursors.
+# t2 has (1),(2);
+delimiter //;
+create procedure p1(spv int)
+begin
+  declare c cursor for select f1(spv) from t2;
+  while (spv > 2) do
+    open c;
+    fetch c into spv;
+    close c;
+    set spv= spv - 10;
+  end while;
+end//
+delimiter ;//
+call p1(15);
+SELECT 'master:',a FROM t1 ORDER BY a;
+sync_slave_with_master;
+connection slave;
+SELECT 'slave:',a FROM t1 ORDER BY a;
+
+connection master;
+drop procedure p1;
+drop function f1;
+drop table t1,t2;
+
+# BUG#12637: User variables + SPs replication
+create table t1 (a int);
+delimiter //;
+create procedure p1()
+begin
+  insert into t1 values(@x);
+  set @x=@x+1;
+  insert into t1 values(@x);
+  if (f2()) then
+    insert into t1 values(1243);
+  end if;
+end//
+
+create function f2() returns int
+begin
+  insert into t1 values(@z);
+  set @z=@z+1;
+  insert into t1 values(@z);
+  return 0;
+end//
+
+create function f1() returns int
+begin
+  insert into t1 values(@y);
+  call p1();
+  return 0;
+end//
+
+delimiter ;//
+
+set @x=10;
+set @y=20;
+set @z=100;
+--disable_ps2_protocol
+select f1();
+--enable_ps2_protocol
+
+set @x=30;
+call p1();
+
+SELECT 'master', a FROM t1 ORDER BY a;
+sync_slave_with_master;
+connection slave;
+SELECT 'slave', a FROM t1 ORDER BY a;
+
+#
+# cleanup
+#
+
+connection master;
+drop table t1;
+drop function f1;
+drop function f2;
+drop procedure p1;
+sync_slave_with_master;
+
+#
+# bug#26199 Replication Failure on Slave when using stored procs
+#           with bit-type parameters
+
+connection master;
+
+create table t2 (b BIT(7));
+delimiter //;
+create procedure sp_bug26199(bitvalue BIT(7))
+begin
+  insert into t2 set b = bitvalue;
+end //
+
+create function sf_bug26199(b BIT(7)) returns int
+begin
+  insert into t2 values(b);
+  return 0;
+end//
+
+DELIMITER ;//
+
+
+
+call   sp_bug26199(b'1110');
+call   sp_bug26199('\0');
+--disable_ps2_protocol
+select sf_bug26199(b'1111111');
+SET STATEMENT sql_mode = '' FOR
+select sf_bug26199(b'101111111');
+select sf_bug26199('\'');
+--enable_ps2_protocol
+select hex(b) from t2;
+
+sync_slave_with_master;
+#connection slave;
+select hex(b) from t2;
+
+#
+# cleanup bug#26199
+#
+connection master;
+drop table t2;
+drop procedure sp_bug26199;
+drop function sf_bug26199;
+
+SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
+
+sync_slave_with_master;
+
+#
+# Bug#16056537: MYSQLD CRASHES IN ITEM_FUNC_GET_USER_VAR::FIX_LENGTH_AND_DEC()
+#
+set names utf8;
+--delimiter |
+CREATE FUNCTION f() RETURNS timestamp DETERMINISTIC
+BEGIN RETURN '2012-12-21 12:12:12'; END |
+CREATE PROCEDURE p(t timestamp) 
+BEGIN
+  SET @t = t;
+  PREPARE stmt FROM " 
+  UPDATE t1 SET a = @t WHERE '2012-12-31 08:00:00' < f() ";
+  EXECUTE stmt;
+  DEALLOCATE PREPARE stmt;
+END |
+--delimiter ;
+create table t1 (a timestamp);
+call p('2012-12-31 08:00:00');
+drop table t1;
+drop procedure p;
+drop function f;
+
+--echo end of the tests
+--source include/rpl_end.inc