1include/master-slave.inc 2[connection master] 3connection master; 4SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; 5drop procedure if exists p1; 6drop procedure if exists p2; 7drop function if exists f1; 8drop table if exists t1,t2; 9drop view if exists v1; 10create table t1 (a int); 11SET GLOBAL log_bin_trust_function_creators = 1; 12create procedure p1() 13begin 14declare spv int default 0; 15while spv < 5 do 16insert into t1 values(spv+1); 17set spv=spv+1; 18end while; 19end// 20call p1(); 21connection slave; 22connection slave; 23SELECT * FROM t1 ORDER BY a; 24a 251 262 273 284 295 30connection master; 31SELECT * FROM t1 ORDER BY a; 32a 331 342 353 364 375 38create procedure p2() 39begin 40declare a int default 4; 41create table t2 as select a; 42end// 43call p2(); 44SELECT * FROM t2 ORDER BY a; 45a 464 47connection slave; 48connection slave; 49SELECT * FROM t2 ORDER BY a; 50a 514 52connection master; 53drop procedure p1; 54drop procedure p2; 55drop table t2; 56create function f1(x int) returns int 57begin 58insert into t1 values(x); 59return x+1; 60end// 61create procedure p1(a int, b int) 62begin 63declare v int default f1(5); 64if (f1(6)) then 65select 'yes'; 66end if; 67set v = f1(7); 68while f1(8) < 1 do 69select 'this cant be'; 70end while; 71end// 72call p1(f1(1), f1(2)); 73yes 74yes 75SELECT * FROM t1 ORDER BY a; 76a 771 781 792 802 813 824 835 845 856 867 878 88create table t2(a int); 89insert into t2 values (10),(11); 90SELECT a,f1(a) FROM t2 ORDER BY a; 91a f1(a) 9210 11 9311 12 94insert into t2 select f1(3); 95SELECT 'master:',a FROM t1 ORDER BY a; 96master: a 97master: 1 98master: 1 99master: 2 100master: 2 101master: 3 102master: 3 103master: 4 104master: 5 105master: 5 106master: 6 107master: 7 108master: 8 109master: 10 110master: 11 111connection slave; 112connection slave; 113SELECT 'slave:',a FROM t1 ORDER BY a; 114slave: a 115slave: 1 116slave: 1 117slave: 2 118slave: 2 119slave: 3 120slave: 3 121slave: 4 122slave: 5 123slave: 5 124slave: 6 125slave: 7 126slave: 8 127slave: 10 128slave: 11 129connection master; 130drop procedure p1; 131delete from t1; 132delete from t2; 133delete from t1; 134insert into t2 values(1),(2); 135create view v1 as select f1(a) as f from t2; 136select * from v1 order by f; 137f 1382 1393 140SELECT 'master:',a FROM t1 ORDER BY a; 141master: a 142master: 1 143master: 2 144connection slave; 145connection slave; 146SELECT 'slave:',a FROM t1 ORDER BY a; 147slave: a 148slave: 1 149slave: 2 150connection master; 151drop view v1; 152delete from t1; 153prepare s1 from 'select f1(?)'; 154set @xx=123; 155execute s1 using @xx; 156f1(?) 157124 158SELECT 'master:',a FROM t1 ORDER BY a; 159master: a 160master: 123 161connection slave; 162connection slave; 163SELECT 'slave:',a FROM t1 ORDER BY a; 164slave: a 165slave: 123 166connection master; 167delete from t1; 168create procedure p1(spv int) 169begin 170declare c cursor for select f1(spv) from t2; 171while (spv > 2) do 172open c; 173fetch c into spv; 174close c; 175set spv= spv - 10; 176end while; 177end// 178call p1(15); 179SELECT 'master:',a FROM t1 ORDER BY a; 180master: a 181master: 6 182master: 6 183master: 15 184master: 15 185connection slave; 186connection slave; 187SELECT 'slave:',a FROM t1 ORDER BY a; 188slave: a 189slave: 6 190slave: 6 191slave: 15 192slave: 15 193connection master; 194drop procedure p1; 195drop function f1; 196drop table t1,t2; 197create table t1 (a int); 198create procedure p1() 199begin 200insert into t1 values(@x); 201set @x=@x+1; 202insert into t1 values(@x); 203if (f2()) then 204insert into t1 values(1243); 205end if; 206end// 207create function f2() returns int 208begin 209insert into t1 values(@z); 210set @z=@z+1; 211insert into t1 values(@z); 212return 0; 213end// 214create function f1() returns int 215begin 216insert into t1 values(@y); 217call p1(); 218return 0; 219end// 220set @x=10; 221set @y=20; 222set @z=100; 223select f1(); 224f1() 2250 226set @x=30; 227call p1(); 228SELECT 'master', a FROM t1 ORDER BY a; 229master a 230master 10 231master 11 232master 20 233master 30 234master 31 235master 100 236master 101 237master 101 238master 102 239connection slave; 240connection slave; 241SELECT 'slave', a FROM t1 ORDER BY a; 242slave a 243slave 10 244slave 11 245slave 20 246slave 30 247slave 31 248slave 100 249slave 101 250slave 101 251slave 102 252connection master; 253drop table t1; 254drop function f1; 255drop function f2; 256drop procedure p1; 257connection slave; 258connection master; 259create table t2 (b BIT(7)); 260create procedure sp_bug26199(bitvalue BIT(7)) 261begin 262insert into t2 set b = bitvalue; 263end // 264create function sf_bug26199(b BIT(7)) returns int 265begin 266insert into t2 values(b); 267return 0; 268end// 269call sp_bug26199(b'1110'); 270call sp_bug26199('\0'); 271select sf_bug26199(b'1111111'); 272sf_bug26199(b'1111111') 2730 274SET STATEMENT sql_mode = '' FOR 275select sf_bug26199(b'101111111'); 276sf_bug26199(b'101111111') 2770 278Warnings: 279Warning 1264 Out of range value for column 'b' at row 1 280select sf_bug26199('\''); 281sf_bug26199('\'') 2820 283select hex(b) from t2; 284hex(b) 285E 2860 2877F 2887F 28927 290connection slave; 291select hex(b) from t2; 292hex(b) 293E 2940 2957F 2967F 29727 298connection master; 299drop table t2; 300drop procedure sp_bug26199; 301drop function sf_bug26199; 302SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; 303connection slave; 304set names utf8; 305CREATE FUNCTION f() RETURNS timestamp DETERMINISTIC 306BEGIN RETURN '2012-12-21 12:12:12'; END | 307CREATE PROCEDURE p(t timestamp) 308BEGIN 309SET @t = t; 310PREPARE stmt FROM " 311 UPDATE t1 SET a = @t WHERE '2012-12-31 08:00:00' < f() "; 312EXECUTE stmt; 313DEALLOCATE PREPARE stmt; 314END | 315create table t1 (a timestamp); 316call p('2012-12-31 08:00:00'); 317drop table t1; 318drop procedure p; 319drop function f; 320end of the tests 321include/rpl_end.inc 322