summaryrefslogtreecommitdiff
path: root/mysql-test/r/sp.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/sp.result')
-rw-r--r--mysql-test/r/sp.result2196
1 files changed, 2196 insertions, 0 deletions
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
new file mode 100644
index 00000000000..bcabf693e4c
--- /dev/null
+++ b/mysql-test/r/sp.result
@@ -0,0 +1,2196 @@
+use test;
+drop table if exists t1,t2,t3,t4;
+drop procedure if exists goto1;
+drop procedure if exists goto2;
+drop procedure if exists goto3;
+drop procedure if exists goto4;
+drop procedure if exists goto5;
+drop procedure if exists goto6;
+drop procedure if exists into_outfile;
+drop procedure if exists into_dumpfile;
+drop procedure if exists create_select;
+drop procedure if exists bar;
+drop procedure if exists hndlr1;
+drop procedure if exists hndlr2;
+drop procedure if exists hndlr3;
+drop procedure if exists hndlr4;
+drop procedure if exists cur1;
+drop procedure if exists cur2;
+drop procedure if exists chistics;
+drop procedure if exists chistics2;
+drop procedure if exists modes;
+drop procedure if exists dummy;
+drop procedure if exists bug;
+drop procedure if exists bug2;
+drop function if exists fac;
+create table t1 (
+id char(16) not null,
+data int not null
+);
+create table t2 (
+s char(16),
+i int,
+d double
+);
+create procedure foo42()
+insert into test.t1 values ("foo", 42);
+call foo42();
+select * from t1;
+id data
+foo 42
+delete from t1;
+drop procedure foo42;
+create procedure bar(x char(16), y int)
+insert into test.t1 values (x, y);
+call bar("bar", 666);
+select * from t1;
+id data
+bar 666
+delete from t1;
+create procedure empty()
+begin
+end|
+call empty()|
+drop procedure empty|
+create procedure scope(a int, b float)
+begin
+declare b int;
+declare c float;
+begin
+declare c int;
+end;
+end|
+drop procedure scope|
+create procedure two(x1 char(16), x2 char(16), y int)
+begin
+insert into test.t1 values (x1, y);
+insert into test.t1 values (x2, y);
+end|
+call two("one", "two", 3)|
+select * from t1|
+id data
+one 3
+two 3
+delete from t1|
+drop procedure two|
+create procedure locset(x char(16), y int)
+begin
+declare z1, z2 int;
+set z1 = y;
+set z2 = z1+2;
+insert into test.t1 values (x, z2);
+end|
+call locset("locset", 19)|
+select * from t1|
+id data
+locset 21
+delete from t1|
+drop procedure locset|
+create procedure setcontext()
+begin
+declare data int default 2;
+insert into t1 (id, data) values ("foo", 1);
+replace t1 set data = data, id = "bar";
+update t1 set id = "kaka", data = 3 where t1.data = data;
+end|
+call setcontext()|
+select * from t1|
+id data
+foo 1
+kaka 3
+delete from t1|
+drop procedure setcontext|
+create table t3 ( d date, i int, f double, s varchar(32) )|
+create procedure nullset()
+begin
+declare ld date;
+declare li int;
+declare lf double;
+declare ls varchar(32);
+set ld = null, li = null, lf = null, ls = null;
+insert into t3 values (ld, li, lf, ls);
+insert into t3 (i, f, s) values ((ld is null), 1, "ld is null"),
+((li is null), 1, "li is null"),
+((li = 0), null, "li = 0"),
+((lf is null), 1, "lf is null"),
+((lf = 0), null, "lf = 0"),
+((ls is null), 1, "ls is null");
+end|
+call nullset()|
+select * from t3|
+d i f s
+NULL NULL NULL NULL
+NULL 1 1 ld is null
+NULL 1 1 li is null
+NULL NULL NULL li = 0
+NULL 1 1 lf is null
+NULL NULL NULL lf = 0
+NULL 1 1 ls is null
+drop table t3|
+drop procedure nullset|
+create procedure mixset(x char(16), y int)
+begin
+declare z int;
+set @z = y, z = 666, max_join_size = 100;
+insert into test.t1 values (x, z);
+end|
+call mixset("mixset", 19)|
+show variables like 'max_join_size'|
+Variable_name Value
+max_join_size 100
+select id,data,@z from t1|
+id data @z
+mixset 666 19
+delete from t1|
+drop procedure mixset|
+create procedure zip(x char(16), y int)
+begin
+declare z int;
+call zap(y, z);
+call bar(x, z);
+end|
+create procedure zap(x int, out y int)
+begin
+declare z int;
+set z = x+1, y = z;
+end|
+call zip("zip", 99)|
+select * from t1|
+id data
+zip 100
+delete from t1|
+drop procedure zip|
+drop procedure bar|
+call zap(7, @zap)|
+select @zap|
+@zap
+8
+drop procedure zap|
+create procedure c1(x int)
+call c2("c", x)|
+create procedure c2(s char(16), x int)
+call c3(x, s)|
+create procedure c3(x int, s char(16))
+call c4("level", x, s)|
+create procedure c4(l char(8), x int, s char(16))
+insert into t1 values (concat(l,s), x)|
+call c1(42)|
+select * from t1|
+id data
+levelc 42
+delete from t1|
+drop procedure c1|
+drop procedure c2|
+drop procedure c3|
+drop procedure c4|
+create procedure iotest(x1 char(16), x2 char(16), y int)
+begin
+call inc2(x2, y);
+insert into test.t1 values (x1, y);
+end|
+create procedure inc2(x char(16), y int)
+begin
+call inc(y);
+insert into test.t1 values (x, y);
+end|
+create procedure inc(inout io int)
+set io = io + 1|
+call iotest("io1", "io2", 1)|
+select * from t1|
+id data
+io2 2
+io1 1
+delete from t1|
+drop procedure iotest|
+drop procedure inc2|
+create procedure incr(inout x int)
+call inc(x)|
+select @zap|
+@zap
+8
+call incr(@zap)|
+select @zap|
+@zap
+9
+drop procedure inc|
+drop procedure incr|
+create procedure cbv1()
+begin
+declare y int default 3;
+call cbv2(y+1, y);
+insert into test.t1 values ("cbv1", y);
+end|
+create procedure cbv2(y1 int, inout y2 int)
+begin
+set y2 = 4711;
+insert into test.t1 values ("cbv2", y1);
+end|
+call cbv1()|
+select * from t1|
+id data
+cbv2 4
+cbv1 4711
+delete from t1|
+drop procedure cbv1|
+drop procedure cbv2|
+insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
+create procedure sub1(id char(16), x int)
+insert into test.t1 values (id, x)|
+create function sub3(i int) returns int
+return i+1|
+call sub1("sub1a", (select 7))|
+call sub1("sub1b", (select max(i) from t2))|
+call sub1("sub1c", (select i,d from t2 limit 1))|
+call sub1("sub1d", (select 1 from (select 1) a))|
+select * from t1|
+id data
+sub1a 7
+sub1b 3
+sub1c 1
+sub1d 1
+select sub3((select max(i) from t2))|
+sub3((select max(i) from t2))
+4
+drop procedure sub1|
+drop function sub3|
+delete from t2|
+create procedure a0(x int)
+while x do
+set x = x-1;
+insert into test.t1 values ("a0", x);
+end while|
+call a0(3)|
+select * from t1|
+id data
+sub1a 7
+sub1b 3
+sub1c 1
+sub1d 1
+a0 2
+a0 1
+a0 0
+delete from t1|
+drop procedure a0|
+create procedure a(x int)
+while x > 0 do
+set x = x-1;
+insert into test.t1 values ("a", x);
+end while|
+call a(3)|
+select * from t1|
+id data
+a 2
+a 1
+a 0
+delete from t1|
+drop procedure a|
+create procedure b(x int)
+repeat
+insert into test.t1 values (repeat("b",3), x);
+set x = x-1;
+until x = 0 end repeat|
+call b(3)|
+select * from t1|
+id data
+bbb 3
+bbb 2
+bbb 1
+delete from t1|
+drop procedure b|
+create procedure b2(x int)
+repeat(select 1 into outfile 'b2');
+insert into test.t1 values (repeat("b2",3), x);
+set x = x-1;
+until x = 0 end repeat|
+drop procedure b2|
+create procedure c(x int)
+hmm: while x > 0 do
+insert into test.t1 values ("c", x);
+set x = x-1;
+iterate hmm;
+insert into test.t1 values ("x", x);
+end while hmm|
+call c(3)|
+select * from t1|
+id data
+c 3
+c 2
+c 1
+delete from t1|
+drop procedure c|
+create procedure d(x int)
+hmm: while x > 0 do
+insert into test.t1 values ("d", x);
+set x = x-1;
+leave hmm;
+insert into test.t1 values ("x", x);
+end while|
+call d(3)|
+select * from t1|
+id data
+d 3
+delete from t1|
+drop procedure d|
+create procedure e(x int)
+foo: loop
+if x = 0 then
+leave foo;
+end if;
+insert into test.t1 values ("e", x);
+set x = x-1;
+end loop foo|
+call e(3)|
+select * from t1|
+id data
+e 3
+e 2
+e 1
+delete from t1|
+drop procedure e|
+create procedure f(x int)
+if x < 0 then
+insert into test.t1 values ("f", 0);
+elseif x = 0 then
+insert into test.t1 values ("f", 1);
+else
+insert into test.t1 values ("f", 2);
+end if|
+call f(-2)|
+call f(0)|
+call f(4)|
+select * from t1|
+id data
+f 0
+f 1
+f 2
+delete from t1|
+drop procedure f|
+create procedure g(x int)
+case
+when x < 0 then
+insert into test.t1 values ("g", 0);
+when x = 0 then
+insert into test.t1 values ("g", 1);
+else
+insert into test.t1 values ("g", 2);
+end case|
+call g(-42)|
+call g(0)|
+call g(1)|
+select * from t1|
+id data
+g 0
+g 1
+g 2
+delete from t1|
+drop procedure g|
+create procedure h(x int)
+case x
+when 0 then
+insert into test.t1 values ("h0", x);
+when 1 then
+insert into test.t1 values ("h1", x);
+else
+insert into test.t1 values ("h?", x);
+end case|
+call h(0)|
+call h(1)|
+call h(17)|
+select * from t1|
+id data
+h0 0
+h1 1
+h? 17
+delete from t1|
+drop procedure h|
+create procedure i(x int)
+foo:
+begin
+if x = 0 then
+leave foo;
+end if;
+insert into test.t1 values ("i", x);
+end foo|
+call i(0)|
+call i(3)|
+select * from t1|
+id data
+i 3
+delete from t1|
+drop procedure i|
+create procedure goto1()
+begin
+declare y int;
+label a;
+select * from t1;
+select count(*) into y from t1;
+if y > 2 then
+goto b;
+end if;
+insert into t1 values ("j", y);
+goto a;
+label b;
+end|
+call goto1()|
+id data
+id data
+j 0
+id data
+j 0
+j 1
+id data
+j 0
+j 1
+j 2
+drop procedure goto1|
+create procedure goto2(a int)
+begin
+declare x int default 0;
+declare continue handler for sqlstate '42S98' set x = 1;
+label a;
+select * from t1;
+b:
+while x < 2 do
+begin
+declare continue handler for sqlstate '42S99' set x = 2;
+if a = 0 then
+set x = x + 1;
+iterate b;
+elseif a = 1 then
+leave b;
+elseif a = 2 then
+set a = 1;
+goto a;
+end if;
+end;
+end while b;
+select * from t1;
+end|
+call goto2(0)|
+id data
+j 0
+j 1
+j 2
+id data
+j 0
+j 1
+j 2
+call goto2(1)|
+id data
+j 0
+j 1
+j 2
+id data
+j 0
+j 1
+j 2
+call goto2(2)|
+id data
+j 0
+j 1
+j 2
+id data
+j 0
+j 1
+j 2
+id data
+j 0
+j 1
+j 2
+drop procedure goto2|
+delete from t1|
+create procedure goto3()
+begin
+label L1;
+begin
+end;
+goto L1;
+end|
+drop procedure goto3|
+create procedure goto4()
+begin
+begin
+label lab1;
+begin
+goto lab1;
+end;
+end;
+end|
+drop procedure goto4|
+create procedure goto5()
+begin
+begin
+begin
+goto lab1;
+end;
+label lab1;
+end;
+end|
+drop procedure goto5|
+create procedure goto6()
+begin
+label L1;
+goto L5;
+begin
+label L2;
+goto L1;
+goto L5;
+begin
+label L3;
+goto L1;
+goto L2;
+goto L3;
+goto L4;
+goto L5;
+end;
+goto L2;
+goto L4;
+label L4;
+end;
+label L5;
+goto L1;
+end|
+drop procedure goto6|
+insert into t1 values ("foo", 3), ("bar", 19)|
+insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)|
+create procedure sel1()
+begin
+select * from t1;
+end|
+call sel1()|
+id data
+foo 3
+bar 19
+drop procedure sel1|
+create procedure sel2()
+begin
+select * from t1;
+select * from t2;
+end|
+call sel2()|
+id data
+foo 3
+bar 19
+s i d
+x 9 4.1
+y -1 19.2
+z 3 2.2
+drop procedure sel2|
+delete from t1|
+delete from t2|
+create procedure into_test(x char(16), y int)
+begin
+insert into test.t1 values (x, y);
+select id,data into x,y from test.t1 limit 1;
+insert into test.t1 values (concat(x, "2"), y+2);
+end|
+call into_test("into", 100)|
+select * from t1|
+id data
+into 100
+into2 102
+delete from t1|
+drop procedure into_test|
+create procedure into_test2(x char(16), y int)
+begin
+insert into test.t1 values (x, y);
+select id,data into x,@z from test.t1 limit 1;
+insert into test.t1 values (concat(x, "2"), y+2);
+end|
+call into_test2("into", 100)|
+select id,data,@z from t1|
+id data @z
+into 100 100
+into2 102 100
+delete from t1|
+drop procedure into_test2|
+create procedure into_test3()
+begin
+declare x char(16);
+declare y int;
+select * into x,y from test.t1 limit 1;
+insert into test.t2 values (x, y, 0.0);
+end|
+insert into t1 values ("into3", 19)|
+call into_test3()|
+call into_test3()|
+select * from t2|
+s i d
+into3 19 0
+into3 19 0
+delete from t1|
+delete from t2|
+drop procedure into_test3|
+create procedure into_test4()
+begin
+declare x int;
+select data into x from test.t1 limit 1;
+insert into test.t3 values ("into4", x);
+end|
+delete from t1|
+drop table if exists t3|
+create table t3 ( s char(16), d int)|
+call into_test4()|
+Warnings:
+select * from t3|
+s d
+into4 NULL
+insert into t1 values ("i4", 77)|
+call into_test4()|
+select * from t3|
+s d
+into4 NULL
+into4 77
+delete from t1|
+drop table t3|
+drop procedure into_test4|
+create procedure into_outfile(x char(16), y int)
+begin
+insert into test.t1 values (x, y);
+select * into outfile "/tmp/spout" from test.t1;
+insert into test.t1 values (concat(x, "2"), y+2);
+end|
+call into_outfile("ofile", 1)|
+delete from t1|
+drop procedure into_outfile|
+create procedure into_dumpfile(x char(16), y int)
+begin
+insert into test.t1 values (x, y);
+select * into dumpfile "/tmp/spdump" from test.t1 limit 1;
+insert into test.t1 values (concat(x, "2"), y+2);
+end|
+call into_dumpfile("dfile", 1)|
+delete from t1|
+drop procedure into_dumpfile|
+create procedure create_select(x char(16), y int)
+begin
+insert into test.t1 values (x, y);
+create table test.t3 select * from test.t1;
+insert into test.t3 values (concat(x, "2"), y+2);
+end|
+drop table if exists t3|
+call create_select("cs", 90)|
+select * from t1, t3|
+id data id data
+cs 90 cs 90
+cs 90 cs2 92
+drop table if exists t3|
+delete from t1|
+drop procedure create_select|
+create function e() returns double
+return 2.7182818284590452354|
+set @e = e()|
+select e(), @e|
+e() @e
+2.718281828459 2.718281828459
+create function inc(i int) returns int
+return i+1|
+select inc(1), inc(99), inc(-71)|
+inc(1) inc(99) inc(-71)
+2 100 -70
+create function mul(x int, y int) returns int
+return x*y|
+select mul(1,1), mul(3,5), mul(4711, 666)|
+mul(1,1) mul(3,5) mul(4711, 666)
+1 15 3137526
+create function append(s1 char(8), s2 char(8)) returns char(16)
+return concat(s1, s2)|
+select append("foo", "bar")|
+append("foo", "bar")
+foobar
+create function fac(n int unsigned) returns bigint unsigned
+begin
+declare f bigint unsigned default 1;
+while n > 1 do
+set f = f * n;
+set n = n - 1;
+end while;
+return f;
+end|
+select fac(1), fac(2), fac(5), fac(10)|
+fac(1) fac(2) fac(5) fac(10)
+1 2 120 3628800
+create function fun(d double, i int, u int unsigned) returns double
+return mul(inc(i), fac(u)) / e()|
+select fun(2.3, 3, 5)|
+fun(2.3, 3, 5)
+176.58213176229
+insert into t2 values (append("xxx", "yyy"), mul(4,3), e())|
+insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))|
+select * from t2 where s = append("a", "b")|
+s i d
+ab 24 1324.36598821719
+select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)|
+s i d
+xxxyyy 12 2.71828182845905
+ab 24 1324.36598821719
+select * from t2 where d = e()|
+s i d
+xxxyyy 12 2.71828182845905
+select * from t2|
+s i d
+xxxyyy 12 2.71828182845905
+ab 24 1324.36598821719
+delete from t2|
+drop function e|
+drop function inc|
+drop function mul|
+drop function append|
+drop function fun|
+create procedure hndlr1(val int)
+begin
+declare x int default 0;
+declare foo condition for 1146;
+declare bar condition for sqlstate '42S98'; # Just for testing syntax
+declare zip condition for sqlstate value '42S99'; # Just for testing syntax
+declare continue handler for foo set x = 1;
+insert into test.t666 values ("hndlr1", val); # Non-existing table
+if (x) then
+insert into test.t1 values ("hndlr1", val); # This instead then
+end if;
+end|
+call hndlr1(42)|
+select * from t1|
+id data
+hndlr1 42
+delete from t1|
+drop procedure hndlr1|
+create procedure hndlr2(val int)
+begin
+declare x int default 0;
+begin
+declare exit handler for sqlstate '42S02' set x = 1;
+insert into test.t666 values ("hndlr2", val); # Non-existing table
+end;
+insert into test.t1 values ("hndlr2", x);
+end|
+call hndlr2(42)|
+select * from t1|
+id data
+hndlr2 1
+delete from t1|
+drop procedure hndlr2|
+create procedure hndlr3(val int)
+begin
+declare x int default 0;
+declare continue handler for sqlexception # Any error
+begin
+declare z int;
+set z = 2 * val;
+set x = 1;
+end;
+if val < 10 then
+begin
+declare y int;
+set y = val + 10;
+insert into test.t666 values ("hndlr3", y); # Non-existing table
+if x then
+insert into test.t1 values ("hndlr3", y);
+end if;
+end;
+end if;
+end|
+call hndlr3(3)|
+select * from t1|
+id data
+hndlr3 13
+delete from t1|
+drop procedure hndlr3|
+drop table if exists t3|
+create table t3 ( id char(16), data int )|
+create procedure hndlr4()
+begin
+declare x int default 0;
+declare val int; # No default
+declare continue handler for sqlstate '02000' set x=1;
+select data into val from test.t3 where id='z' limit 1; # No hits
+insert into test.t3 values ('z', val);
+end|
+call hndlr4()|
+select * from t3|
+id data
+z NULL
+drop table t3|
+drop procedure hndlr4|
+create procedure cur1()
+begin
+declare a char(16);
+declare b int;
+declare c double;
+declare done int default 0;
+declare c cursor for select * from test.t2;
+declare continue handler for sqlstate '02000' set done = 1;
+open c;
+repeat
+fetch c into a, b, c;
+if not done then
+insert into test.t1 values (a, b+c);
+end if;
+until done end repeat;
+close c;
+end|
+insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)|
+call cur1()|
+select * from t1|
+id data
+foo 40
+bar 15
+zap 663
+drop procedure cur1|
+drop table if exists t3|
+create table t3 ( s char(16), i int )|
+create procedure cur2()
+begin
+declare done int default 0;
+declare c1 cursor for select id,data from test.t1;
+declare c2 cursor for select i from test.t2;
+declare continue handler for sqlstate '02000' set done = 1;
+open c1;
+open c2;
+repeat
+begin
+declare a char(16);
+declare b,c int;
+fetch from c1 into a, b;
+fetch next from c2 into c;
+if not done then
+if b < c then
+insert into test.t3 values (a, b);
+else
+insert into test.t3 values (a, c);
+end if;
+end if;
+end;
+until done end repeat;
+close c1;
+close c2;
+end|
+call cur2()|
+select * from t3|
+s i
+foo 40
+bar 3
+zap 663
+delete from t1|
+delete from t2|
+drop table t3|
+drop procedure cur2|
+create procedure chistics()
+language sql
+modifies sql data
+not deterministic
+sql security definer
+comment 'Characteristics procedure test'
+ insert into t1 values ("chistics", 1)|
+show create procedure chistics|
+Procedure sql_mode Create Procedure
+chistics CREATE PROCEDURE `test`.`chistics`()
+ MODIFIES SQL DATA
+ COMMENT 'Characteristics procedure test'
+insert into t1 values ("chistics", 1)
+call chistics()|
+select * from t1|
+id data
+chistics 1
+delete from t1|
+alter procedure chistics sql security invoker|
+show create procedure chistics|
+Procedure sql_mode Create Procedure
+chistics CREATE PROCEDURE `test`.`chistics`()
+ MODIFIES SQL DATA
+ SQL SECURITY INVOKER
+ COMMENT 'Characteristics procedure test'
+insert into t1 values ("chistics", 1)
+drop procedure chistics|
+create function chistics() returns int
+language sql
+deterministic
+sql security invoker
+comment 'Characteristics procedure test'
+ return 42|
+show create function chistics|
+Function sql_mode Create Function
+chistics CREATE FUNCTION `test`.`chistics`() RETURNS int
+ DETERMINISTIC
+ SQL SECURITY INVOKER
+ COMMENT 'Characteristics procedure test'
+return 42
+select chistics()|
+chistics()
+42
+alter function chistics
+no sql
+comment 'Characteristics function test'|
+show create function chistics|
+Function sql_mode Create Function
+chistics CREATE FUNCTION `test`.`chistics`() RETURNS int
+ NO SQL
+ DETERMINISTIC
+ SQL SECURITY INVOKER
+ COMMENT 'Characteristics function test'
+return 42
+drop function chistics|
+insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
+set @@sql_mode = 'ANSI'|
+create procedure modes(out c1 int, out c2 int)
+begin
+declare done int default 0;
+declare x int;
+declare c cursor for select data from t1;
+declare continue handler for sqlstate '02000' set done = 1;
+select 1 || 2 into c1;
+set c2 = 0;
+open c;
+repeat
+fetch c into x;
+if not done then
+set c2 = c2 + 1;
+end if;
+until done end repeat;
+close c;
+end$
+set @@sql_mode = ''|
+set sql_select_limit = 1|
+call modes(@c1, @c2)|
+set sql_select_limit = default|
+select @c1, @c2|
+@c1 @c2
+12 3
+delete from t1|
+drop procedure modes|
+create database sp_db1|
+drop database sp_db1|
+create database sp_db2|
+use sp_db2|
+create table t3 ( s char(4), t int )|
+insert into t3 values ("abcd", 42), ("dcba", 666)|
+use test|
+drop database sp_db2|
+create database sp_db3|
+use sp_db3|
+create procedure dummy(out x int)
+set x = 42|
+use test|
+drop database sp_db3|
+select type,db,name from mysql.proc where db = 'sp_db3'|
+type db name
+create procedure rc()
+begin
+delete from t1;
+insert into t1 values ("a", 1), ("b", 2), ("c", 3);
+end|
+call rc()|
+select row_count()|
+row_count()
+3
+update t1 set data=42 where id = "b";
+select row_count()|
+row_count()
+1
+delete from t1|
+select row_count()|
+row_count()
+3
+delete from t1|
+select row_count()|
+row_count()
+0
+select * from t1|
+id data
+select row_count()|
+row_count()
+-1
+drop procedure rc|
+create procedure bug(a_id char(16), a_data int)
+begin
+declare n int;
+select count(*) into n from t1 where id = a_id and data = a_data;
+if n = 0 then
+insert into t1 (id, data) values (a_id, a_data);
+end if;
+end|
+call bug('foo', 42)|
+call bug('foo', 42)|
+call bug('bar', 666)|
+select * from t1|
+id data
+foo 42
+bar 666
+delete from t1|
+drop procedure bug|
+create procedure bug()
+begin
+declare x int;
+select data into x from t1 order by id limit 1;
+if x > 10 then
+insert into t1 values ("less", x-10);
+else
+insert into t1 values ("more", x+10);
+end if;
+end|
+insert into t1 values ('foo', 12)|
+call bug()|
+delete from t1 where id='foo'|
+insert into t1 values ('bar', 7)|
+call bug()|
+delete from t1 where id='bar'|
+select * from t1|
+id data
+less 2
+more 17
+delete from t1|
+drop procedure bug|
+create procedure bug(s char(16))
+begin
+declare x int;
+select data into x from t1 where s = id limit 1;
+if x > 10 then
+insert into t1 values ("less", x-10);
+else
+insert into t1 values ("more", x+10);
+end if;
+end|
+insert into t1 values ("foo", 12), ("bar", 7)|
+call bug("foo")|
+call bug("bar")|
+select * from t1|
+id data
+foo 12
+bar 7
+less 2
+more 17
+delete from t1|
+drop procedure bug|
+drop table if exists t70|
+create table t70 (s1 int,s2 int)|
+insert into t70 values (1,2)|
+create procedure bug(out p1 int, out p2 int)
+select * into p1, p1 from t70|
+call bug(@1, @2)|
+select @1, @2|
+@1 @2
+2 NULL
+drop table t70|
+drop procedure bug|
+drop table if exists t3|
+create table t3(a int)|
+create procedure bug()
+begin
+insert into t3 values(2);
+flush tables;
+end|
+call bug()|
+call bug()|
+select * from t3|
+a
+2
+2
+drop table t3|
+drop procedure bug|
+create procedure bug()
+begin
+declare x int;
+declare y double;
+select max(data) into x from t1;
+insert into t2 values ("max", x, 0);
+select min(data) into x from t1;
+insert into t2 values ("min", x, 0);
+select sum(data) into x from t1;
+insert into t2 values ("sum", x, 0);
+select avg(data) into y from t1;
+insert into t2 values ("avg", 0, y);
+end|
+insert into t1 (data) values (3), (1), (5), (9), (4)|
+call bug()|
+select * from t2|
+s i d
+max 9 0
+min 1 0
+sum 22 0
+avg 0 4.4
+delete from t1|
+delete from t2|
+drop procedure bug|
+create procedure bug()
+begin
+declare v1 int;
+declare c1 cursor for select data from t1;
+declare continue handler for not found set @x2 = 1;
+open c1;
+fetch c1 into v1;
+set @x2 = 2;
+close c1;
+end|
+call bug()|
+select @x2|
+@x2
+2
+drop procedure bug|
+create procedure bug2267_1()
+begin
+show procedure status;
+end|
+create procedure bug2267_2()
+begin
+show function status;
+end|
+create procedure bug2267_3()
+begin
+show create procedure bug2267_1;
+end|
+create procedure bug2267_4()
+begin
+show create function fac;
+end|
+call bug2267_1()|
+Db Name Type Definer Modified Created Security_type Comment
+test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+call bug2267_2()|
+Db Name Type Definer Modified Created Security_type Comment
+test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+call bug2267_3()|
+Procedure sql_mode Create Procedure
+bug2267_1 CREATE PROCEDURE `test`.`bug2267_1`()
+begin
+show procedure status;
+end
+call bug2267_4()|
+Function sql_mode Create Function
+fac CREATE FUNCTION `test`.`fac`(n int unsigned) RETURNS bigint unsigned
+begin
+declare f bigint unsigned default 1;
+while n > 1 do
+set f = f * n;
+set n = n - 1;
+end while;
+return f;
+end
+drop procedure bug2267_1|
+drop procedure bug2267_2|
+drop procedure bug2267_3|
+drop procedure bug2267_4|
+create procedure bug(x int)
+begin
+declare y float default 2.6;
+declare z char(16) default "zzz";
+select 1.3, x, y, 42, z;
+end|
+call bug(9)|
+1.3 x y 42 z
+1.3 9 2.6 42 zzz
+drop procedure bug|
+create procedure bug()
+begin
+drop table if exists t3;
+create table t3 (id int default '0' not null);
+insert into t3 select 12;
+insert into t3 select * from t3;
+end|
+call bug()|
+call bug()|
+drop table t3|
+drop procedure bug|
+create function bug () returns int
+return @@sort_buffer_size|
+set @osbs = @@sort_buffer_size|
+set @@sort_buffer_size = 262000|
+select bug()|
+bug()
+262000
+drop function bug|
+set @@sort_buffer_size = @osbs|
+create procedure bug3259_1 () begin end|
+create procedure BUG3259_2 () begin end|
+create procedure Bug3259_3 () begin end|
+call BUG3259_1()|
+call BUG3259_1()|
+call bug3259_2()|
+call Bug3259_2()|
+call bug3259_3()|
+call bUG3259_3()|
+drop procedure bUg3259_1|
+drop procedure BuG3259_2|
+drop procedure BUG3259_3|
+create function bug() returns char(10) character set latin2
+return 'a'|
+select bug()|
+bug()
+a
+drop function bug|
+create procedure bug(out x int)
+begin
+declare v int;
+set v = default;
+set x = v;
+end|
+create procedure bug2(out x int)
+begin
+declare v int default 42;
+set v = default;
+set x = v;
+end|
+set @x = 1|
+call bug(@x)|
+select @x|
+@x
+NULL
+call bug2(@x)|
+select @x|
+@x
+42
+drop procedure bug|
+drop procedure bug2|
+drop table if exists t3|
+create table t3 (s1 smallint)|
+insert into t3 values (123456789012)|
+Warnings:
+Warning 1264 Out of range value adjusted for column 's1' at row 1
+create procedure bug()
+begin
+declare exit handler for sqlwarning set @x = 1;
+set @x = 0;
+insert into t3 values (123456789012);
+insert into t3 values (0);
+end|
+call bug()|
+select @x|
+@x
+1
+select * from t3|
+s1
+32767
+32767
+drop procedure bug|
+drop table t3|
+create table t3 (content varchar(10) )|
+insert into t3 values ("test1")|
+insert into t3 values ("test2")|
+create table t4 (f1 int, rc int, t3 int)|
+create procedure bug1863(in1 int)
+begin
+declare ind int default 0;
+declare t1 int;
+declare t2 int;
+declare t3 int;
+declare rc int default 0;
+declare continue handler for 1065 set rc = 1;
+drop table if exists temp_t1;
+create temporary table temp_t1 (
+f1 int auto_increment, f2 varchar(20), primary key (f1)
+);
+insert into temp_t1 (f2) select content from t3;
+select f2 into t3 from temp_t1 where f1 = 10;
+if (rc) then
+insert into t4 values (1, rc, t3);
+end if;
+insert into t4 values (2, rc, t3);
+end|
+call bug1863(10)|
+Warnings:
+call bug1863(10)|
+Warnings:
+select * from t4|
+f1 rc t3
+2 0 NULL
+2 0 NULL
+drop procedure bug1863|
+drop table t3, t4|
+drop table if exists t3, t4|
+create table t3 (
+OrderID int not null,
+MarketID int,
+primary key (OrderID)
+)|
+create table t4 (
+MarketID int not null,
+Market varchar(60),
+Status char(1),
+primary key (MarketID)
+)|
+insert t3 (OrderID,MarketID) values (1,1)|
+insert t3 (OrderID,MarketID) values (2,2)|
+insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")|
+insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")|
+create procedure bug2656_1()
+begin
+select
+m.Market
+from t4 m JOIN t3 o
+ON o.MarketID != 1 and o.MarketID = m.MarketID;
+end |
+create procedure bug2656_2()
+begin
+select
+m.Market
+from
+t4 m, t3 o
+where
+m.MarketID != 1 and m.MarketID = o.MarketID;
+end |
+call bug2656_1()|
+Market
+MarketID Two
+call bug2656_1()|
+Market
+MarketID Two
+call bug2656_2()|
+Market
+MarketID Two
+call bug2656_2()|
+Market
+MarketID Two
+drop procedure bug2656_1|
+drop procedure bug2656_2|
+drop table t3, t4|
+create procedure bug3426(in_time int unsigned, out x int)
+begin
+if in_time is null then
+set @stamped_time=10;
+set x=1;
+else
+set @stamped_time=in_time;
+set x=2;
+end if;
+end|
+call bug3426(1000, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+@i time
+2 01-01-1970 03:16:40
+call bug3426(NULL, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+@i time
+1 01-01-1970 03:00:10
+alter procedure bug3426 sql security invoker|
+call bug3426(NULL, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+@i time
+1 01-01-1970 03:00:10
+call bug3426(1000, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+@i time
+2 01-01-1970 03:16:40
+drop procedure bug3426|
+drop table if exists t3, t4|
+create table t3 (
+a int primary key,
+ach char(1)
+) engine = innodb|
+create table t4 (
+b int primary key ,
+bch char(1)
+) engine = innodb|
+insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
+Warnings:
+Warning 1265 Data truncated for column 'ach' at row 1
+Warning 1265 Data truncated for column 'ach' at row 2
+insert into t4 values (1 , 'bCh1' )|
+Warnings:
+Warning 1265 Data truncated for column 'bch' at row 1
+create procedure bug3448()
+select * from t3 inner join t4 on t3.a = t4.b|
+select * from t3 inner join t4 on t3.a = t4.b|
+a ach b bch
+1 a 1 b
+call bug3448()|
+a ach b bch
+1 a 1 b
+call bug3448()|
+a ach b bch
+1 a 1 b
+drop procedure bug3448|
+drop table t3, t4|
+drop table if exists t3|
+create table t3 (
+id int unsigned auto_increment not null primary key,
+title VARCHAR(200),
+body text,
+fulltext (title,body)
+)|
+insert into t3 (title,body) values
+('MySQL Tutorial','DBMS stands for DataBase ...'),
+('How To Use MySQL Well','After you went through a ...'),
+('Optimizing MySQL','In this tutorial we will show ...'),
+('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+('MySQL vs. YourSQL','In the following database comparison ...'),
+('MySQL Security','When configured properly, MySQL ...')|
+create procedure bug3734 (param1 varchar(100))
+select * from t3 where match (title,body) against (param1)|
+call bug3734('database')|
+id title body
+5 MySQL vs. YourSQL In the following database comparison ...
+1 MySQL Tutorial DBMS stands for DataBase ...
+call bug3734('Security')|
+id title body
+6 MySQL Security When configured properly, MySQL ...
+drop procedure bug3734|
+drop table t3|
+create procedure bug3863()
+begin
+set @a = 0;
+while @a < 5 do
+set @a = @a + 1;
+end while;
+end|
+call bug3863()|
+select @a|
+@a
+5
+call bug3863()|
+select @a|
+@a
+5
+drop procedure bug3863|
+create table t3 (
+id int(10) unsigned not null default 0,
+rid int(10) unsigned not null default 0,
+msg text not null,
+primary key (id),
+unique key rid (rid, id)
+)|
+create procedure bug2460_1(in v int)
+begin
+( select n0.id from t3 as n0 where n0.id = v )
+union
+( select n0.id from t3 as n0, t3 as n1
+where n0.id = n1.rid and n1.id = v )
+union
+( select n0.id from t3 as n0, t3 as n1, t3 as n2
+where n0.id = n1.rid and n1.id = n2.rid and n2.id = v );
+end|
+call bug2460_1(2)|
+id
+call bug2460_1(2)|
+id
+insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')|
+call bug2460_1(2)|
+id
+2
+1
+call bug2460_1(2)|
+id
+2
+1
+create procedure bug2460_2()
+begin
+drop table if exists t3;
+create table t3 (s1 int);
+insert into t3 select 1 union select 1;
+end|
+call bug2460_2()|
+call bug2460_2()|
+select * from t3|
+s1
+1
+drop procedure bug2460_1|
+drop procedure bug2460_2|
+drop table t3|
+set @@sql_mode = ''|
+create procedure bug2564_1()
+comment 'Joe''s procedure'
+ insert into `t1` values ("foo", 1)|
+set @@sql_mode = 'ANSI_QUOTES'|
+create procedure bug2564_2()
+insert into "t1" values ('foo', 1)|
+set @@sql_mode = ''$
+create function bug2564_3(x int, y int) returns int
+return x || y$
+set @@sql_mode = 'ANSI'$
+create function bug2564_4(x int, y int) returns int
+return x || y$
+set @@sql_mode = ''|
+show create procedure bug2564_1|
+Procedure sql_mode Create Procedure
+bug2564_1 CREATE PROCEDURE `test`.`bug2564_1`()
+ COMMENT 'Joe''s procedure'
+insert into `t1` values ("foo", 1)
+show create procedure bug2564_2|
+Procedure sql_mode Create Procedure
+bug2564_2 ANSI_QUOTES CREATE PROCEDURE "test"."bug2564_2"()
+insert into "t1" values ('foo', 1)
+show create function bug2564_3|
+Function sql_mode Create Function
+bug2564_3 CREATE FUNCTION `test`.`bug2564_3`(x int, y int) RETURNS int
+return x || y
+show create function bug2564_4|
+Function sql_mode Create Function
+bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI CREATE FUNCTION "test"."bug2564_4"(x int, y int) RETURNS int
+return x || y
+drop procedure bug2564_1|
+drop procedure bug2564_2|
+drop function bug2564_3|
+drop function bug2564_4|
+create function bug3132(s char(20)) returns char(50)
+return concat('Hello, ', s, '!')|
+select bug3132('Bob') union all select bug3132('Judy')|
+bug3132('Bob')
+Hello, Bob!
+Hello, Judy!
+drop function bug3132|
+create procedure bug3843()
+analyze table t1|
+call bug3843()|
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+call bug3843()|
+Table Op Msg_type Msg_text
+test.t1 analyze status Table is already up to date
+select 1+2|
+1+2
+3
+drop procedure bug3843|
+drop table if exists t3|
+create table t3 ( s1 char(10) )|
+insert into t3 values ('a'), ('b')|
+create procedure bug3368(v char(10))
+begin
+select group_concat(v) from t3;
+end|
+call bug3368('x')|
+group_concat(v)
+x,x
+call bug3368('yz')|
+group_concat(v)
+yz,yz
+drop procedure bug3368|
+drop table t3|
+drop table if exists t3|
+create table t3 (f1 int, f2 int);
+insert into t3 values (1,1);
+create procedure bug4579_1 ()
+begin
+declare sf1 int;
+select f1 into sf1 from t3 where f1=1 and f2=1;
+update t3 set f2 = f2 + 1 where f1=1 and f2=1;
+call bug4579_2();
+end|
+create procedure bug4579_2 ()
+begin
+end|
+call bug4579_1()|
+call bug4579_1()|
+Warnings:
+call bug4579_1()|
+Warnings:
+drop procedure bug4579_1|
+drop procedure bug4579_2|
+drop table t3|
+drop table if exists t3|
+create table t3 (f1 int, f2 int, f3 int)|
+insert into t3 values (1,1,1)|
+create procedure bug4726()
+begin
+declare tmp_o_id INT;
+declare tmp_d_id INT default 1;
+while tmp_d_id <= 2 do
+begin
+select f1 into tmp_o_id from t3 where f2=1 and f3=1;
+set tmp_d_id = tmp_d_id + 1;
+end;
+end while;
+end|
+call bug4726()|
+call bug4726()|
+call bug4726()|
+drop procedure bug4726|
+drop table t3|
+drop table if exists t3|
+create table t3 (s1 int)|
+insert into t3 values (3), (4)|
+create procedure bug4318()
+handler t3 read next|
+handler t3 open|
+call bug4318()|
+s1
+3
+call bug4318()|
+s1
+4
+handler t3 close|
+drop procedure bug4318|
+drop table t3|
+create procedure bug4902()
+begin
+show charset like 'foo';
+show collation like 'foo';
+show column types;
+show create table t1;
+show create database test;
+show databases like 'foo';
+show errors;
+show columns from t1;
+show grants for 'root'@'localhost';
+show keys from t1;
+show open tables like 'foo';
+show privileges;
+show status like 'foo';
+show tables like 'foo';
+show variables like 'foo';
+show warnings;
+end|
+call bug4902()|
+Charset Description Default collation Maxlen
+Collation Charset Id Default Compiled Sortlen
+Type Size Min_Value Max_Value Prec Scale Nullable Auto_Increment Unsigned Zerofill Searchable Case_Sensitive Default Comment
+tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer
+tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` char(16) NOT NULL,
+ `data` int(11) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Database Create Database
+test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
+Database (foo)
+Level Code Message
+Field Type Null Key Default Extra
+id char(16)
+data int(11)
+Grants for root@localhost
+GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+Database Table In_use Name_locked
+Privilege Context Comment
+Alter Tables To alter the table
+Create Databases,Tables,Indexes To create new databases and tables
+Create temporary tables Databases To use CREATE TEMPORARY TABLE
+Create view Tables To create new views
+Delete Tables To delete existing rows
+Drop Databases,Tables To drop databases, tables, and views
+File File access on server To read and write files on the server
+Grant option Databases,Tables To give to other users those privileges you possess
+Index Tables To create or drop indexes
+Insert Tables To insert data into tables
+Lock tables Databases To use LOCK TABLES (together with SELECT privilege)
+Process Server Admin To view the plain text of currently executing queries
+References Databases,Tables To have references on tables
+Reload Server Admin To reload or refresh tables, logs and privileges
+Replication client Server Admin To ask where the slave or master servers are
+Replication slave Server Admin To read binary log events from the master
+Select Tables To retrieve rows from table
+Show databases Server Admin To see all databases with SHOW DATABASES
+Show view Tables To see views with SHOW CREATE VIEW
+Shutdown Server Admin To shut down the server
+Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
+Update Tables To update existing rows
+Usage Server Admin No privileges - allow connect only
+Variable_name Value
+Tables_in_test (foo)
+Variable_name Value
+Level Code Message
+call bug4902()|
+Charset Description Default collation Maxlen
+Collation Charset Id Default Compiled Sortlen
+Type Size Min_Value Max_Value Prec Scale Nullable Auto_Increment Unsigned Zerofill Searchable Case_Sensitive Default Comment
+tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer
+tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` char(16) NOT NULL,
+ `data` int(11) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Database Create Database
+test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
+Database (foo)
+Level Code Message
+Field Type Null Key Default Extra
+id char(16)
+data int(11)
+Grants for root@localhost
+GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+Database Table In_use Name_locked
+Privilege Context Comment
+Alter Tables To alter the table
+Create Databases,Tables,Indexes To create new databases and tables
+Create temporary tables Databases To use CREATE TEMPORARY TABLE
+Create view Tables To create new views
+Delete Tables To delete existing rows
+Drop Databases,Tables To drop databases, tables, and views
+File File access on server To read and write files on the server
+Grant option Databases,Tables To give to other users those privileges you possess
+Index Tables To create or drop indexes
+Insert Tables To insert data into tables
+Lock tables Databases To use LOCK TABLES (together with SELECT privilege)
+Process Server Admin To view the plain text of currently executing queries
+References Databases,Tables To have references on tables
+Reload Server Admin To reload or refresh tables, logs and privileges
+Replication client Server Admin To ask where the slave or master servers are
+Replication slave Server Admin To read binary log events from the master
+Select Tables To retrieve rows from table
+Show databases Server Admin To see all databases with SHOW DATABASES
+Show view Tables To see views with SHOW CREATE VIEW
+Shutdown Server Admin To shut down the server
+Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
+Update Tables To update existing rows
+Usage Server Admin No privileges - allow connect only
+Variable_name Value
+Tables_in_test (foo)
+Variable_name Value
+Level Code Message
+drop procedure bug4902|
+create procedure bug4902_2()
+begin
+show processlist;
+end|
+call bug4902_2()|
+Id User Host db Command Time State Info
+# root localhost test Query # NULL show processlist
+call bug4902_2()|
+Id User Host db Command Time State Info
+# root localhost test Query # NULL show processlist
+drop procedure bug4902_2|
+drop table if exists t3|
+create procedure bug4904()
+begin
+declare continue handler for sqlstate 'HY000' begin end;
+create table t2 as select * from t;
+end|
+call bug4904()|
+drop procedure bug4904|
+create procedure bug336(out y int)
+begin
+declare x int;
+set x = (select sum(t.data) from test.t1 t);
+set y = x;
+end|
+insert into t1 values ("a", 2), ("b", 3)|
+call bug336(@y)|
+select @y|
+@y
+5
+delete from t1|
+drop procedure bug336|
+create procedure bug3157()
+begin
+if exists(select * from t1) then
+set @n= @n + 1;
+end if;
+if (select count(*) from t1) then
+set @n= @n + 1;
+end if;
+end|
+set @n = 0|
+insert into t1 values ("a", 1)|
+call bug3157()|
+select @n|
+@n
+2
+delete from t1|
+drop procedure bug3157|
+create procedure bug5251()
+begin
+end|
+select created into @c1 from mysql.proc
+where db='test' and name='bug5251'|
+alter procedure bug5251 comment 'foobar'|
+select count(*) from mysql.proc
+where db='test' and name='bug5251' and created = @c1|
+count(*)
+1
+drop procedure bug5251|
+create procedure bug5251()
+checksum table t1|
+call bug5251()|
+Table Checksum
+test.t1 0
+call bug5251()|
+Table Checksum
+test.t1 0
+drop procedure bug5251|
+create procedure bug5287(param1 int)
+label1:
+begin
+declare c cursor for select 5;
+loop
+if param1 >= 0 then
+leave label1;
+end if;
+end loop;
+end|
+call bug5287(1)|
+drop procedure bug5287|
+create procedure bug5307()
+begin
+end; set @x = 3|
+call bug5307()|
+select @x|
+@x
+3
+drop procedure bug5307|
+create procedure bug5258()
+begin
+end|
+create procedure bug5258_aux()
+begin
+declare c, m char(19);
+select created,modified into c,m from mysql.proc where name = 'bug5258';
+if c = m then
+select 'Ok';
+else
+select c, m;
+end if;
+end|
+call bug5258_aux()|
+Ok
+Ok
+drop procedure bug5258|
+drop procedure bug5258_aux|
+create function bug4487() returns char
+begin
+declare v char;
+return v;
+end|
+select bug4487()|
+bug4487()
+NULL
+drop function bug4487|
+drop procedure if exists bug4941|
+create procedure bug4941(out x int)
+begin
+declare c cursor for select i from t2 limit 1;
+open c;
+fetch c into x;
+close c;
+end|
+insert into t2 values (null, null, null)|
+set @x = 42|
+call bug4941(@x)|
+select @x|
+@x
+NULL
+delete from t1|
+drop procedure bug4941|
+drop procedure if exists bug3583|
+create procedure bug3583()
+begin
+declare c int;
+select * from t1;
+select count(*) into c from t1;
+select c;
+end|
+insert into t1 values ("x", 3), ("y", 5)|
+set @x = @@query_cache_size|
+set global query_cache_size = 10*1024*1024|
+flush status|
+flush query cache|
+show status like 'Qcache_hits'|
+Variable_name Value
+Qcache_hits 0
+call bug3583()|
+id data
+x 3
+y 5
+c
+2
+show status like 'Qcache_hits'|
+Variable_name Value
+Qcache_hits 0
+call bug3583()|
+id data
+x 3
+y 5
+c
+2
+call bug3583()|
+id data
+x 3
+y 5
+c
+2
+show status like 'Qcache_hits'|
+Variable_name Value
+Qcache_hits 2
+set global query_cache_size = @x|
+flush status|
+flush query cache|
+delete from t1|
+drop procedure bug3583|
+drop table if exists t3|
+drop procedure if exists bug4905|
+create table t3 (s1 int,primary key (s1))|
+create procedure bug4905()
+begin
+declare v int;
+declare continue handler for sqlstate '23000' set v = 5;
+insert into t3 values (1);
+end|
+call bug4905()|
+select row_count()|
+row_count()
+1
+call bug4905()|
+select row_count()|
+row_count()
+0
+call bug4905()|
+select row_count()|
+row_count()
+0
+select * from t3|
+s1
+1
+drop procedure bug4905|
+drop table t3|
+drop function if exists bug6022|
+create function bug6022(x int) returns int
+begin
+if x < 0 then
+return 0;
+else
+return bug6022(x-1);
+end if;
+end|
+select bug6022(5)|
+bug6022(5)
+0
+drop function bug6022|
+drop procedure if exists bug6029|
+create procedure bug6029()
+begin
+declare exit handler for 1136 select '1136';
+declare exit handler for sqlstate '23000' select 'sqlstate 23000';
+declare continue handler for sqlexception select 'sqlexception';
+insert into t3 values (1);
+insert into t3 values (1,2);
+end|
+create table t3 (s1 int, primary key (s1))|
+insert into t3 values (1)|
+call bug6029()|
+sqlstate 23000
+sqlstate 23000
+delete from t3|
+call bug6029()|
+1136
+1136
+drop procedure bug6029|
+drop table t3|
+drop table if exists fac|
+create table fac (n int unsigned not null primary key, f bigint unsigned)|
+create procedure ifac(n int unsigned)
+begin
+declare i int unsigned default 1;
+if n > 20 then
+set n = 20; # bigint overflow otherwise
+end if;
+while i <= n do
+begin
+insert into test.fac values (i, fac(i));
+set i = i + 1;
+end;
+end while;
+end|
+call ifac(20)|
+select * from fac|
+n f
+1 1
+2 2
+3 6
+4 24
+5 120
+6 720
+7 5040
+8 40320
+9 362880
+10 3628800
+11 39916800
+12 479001600
+13 6227020800
+14 87178291200
+15 1307674368000
+16 20922789888000
+17 355687428096000
+18 6402373705728000
+19 121645100408832000
+20 2432902008176640000
+drop table fac|
+show function status like '%f%'|
+Db Name Type Definer Modified Created Security_type Comment
+test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+drop procedure ifac|
+drop function fac|
+show function status like '%f%'|
+Db Name Type Definer Modified Created Security_type Comment
+drop table if exists primes|
+create table primes (
+i int unsigned not null primary key,
+p bigint unsigned not null
+)|
+insert into primes values
+( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13),
+( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31),
+(10, 37), (11, 41), (12, 43), (13, 47), (14, 53),
+(15, 59), (16, 61), (17, 67), (18, 71), (19, 73),
+(20, 79), (21, 83), (22, 89), (23, 97), (24, 101),
+(25, 103), (26, 107), (27, 109), (28, 113), (29, 127),
+(30, 131), (31, 137), (32, 139), (33, 149), (34, 151),
+(35, 157), (36, 163), (37, 167), (38, 173), (39, 179),
+(40, 181), (41, 191), (42, 193), (43, 197), (44, 199)|
+create procedure opp(n bigint unsigned, out pp bool)
+begin
+declare r double;
+declare b, s bigint unsigned default 0;
+set r = sqrt(n);
+again:
+loop
+if s = 45 then
+set b = b+200, s = 0;
+else
+begin
+declare p bigint unsigned;
+select t.p into p from test.primes t where t.i = s;
+if b+p > r then
+set pp = 1;
+leave again;
+end if;
+if mod(n, b+p) = 0 then
+set pp = 0;
+leave again;
+end if;
+set s = s+1;
+end;
+end if;
+end loop;
+end|
+create procedure ip(m int unsigned)
+begin
+declare p bigint unsigned;
+declare i int unsigned;
+set i=45, p=201;
+while i < m do
+begin
+declare pp bool default 0;
+call opp(p, pp);
+if pp then
+insert into test.primes values (i, p);
+set i = i+1;
+end if;
+set p = p+2;
+end;
+end while;
+end|
+show create procedure opp|
+Procedure sql_mode Create Procedure
+opp CREATE PROCEDURE `test`.`opp`(n bigint unsigned, out pp bool)
+begin
+declare r double;
+declare b, s bigint unsigned default 0;
+set r = sqrt(n);
+again:
+loop
+if s = 45 then
+set b = b+200, s = 0;
+else
+begin
+declare p bigint unsigned;
+select t.p into p from test.primes t where t.i = s;
+if b+p > r then
+set pp = 1;
+leave again;
+end if;
+if mod(n, b+p) = 0 then
+set pp = 0;
+leave again;
+end if;
+set s = s+1;
+end;
+end if;
+end loop;
+end
+show procedure status like '%p%'|
+Db Name Type Definer Modified Created Security_type Comment
+test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
+call ip(200)|
+select * from primes where i=45 or i=100 or i=199|
+i p
+45 211
+100 557
+199 1229
+drop table primes|
+drop procedure opp|
+drop procedure ip|
+show procedure status like '%p%'|
+Db Name Type Definer Modified Created Security_type Comment
+drop table if exists fib|
+create table fib ( f bigint unsigned not null )|
+insert into fib values (1), (1)|
+create procedure fib(n int unsigned)
+begin
+if n > 0 then
+begin
+declare x, y bigint unsigned;
+declare c cursor for select f from fib order by f desc limit 2;
+open c;
+fetch c into y;
+fetch c into x;
+close c;
+insert into fib values (x+y);
+call fib(n-1);
+end;
+end if;
+end|
+call fib(20)|
+select * from fib order by f asc|
+f
+1
+1
+2
+3
+5
+8
+13
+21
+34
+55
+89
+144
+233
+377
+610
+987
+1597
+2584
+4181
+6765
+10946
+17711
+drop table fib|
+drop procedure fib|
+create procedure bar(x char(16), y int)
+comment "111111111111" sql security invoker
+insert into test.t1 values (x, y)|
+show procedure status like 'bar'|
+Db Name Type Definer Modified Created Security_type Comment
+test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111
+alter procedure bar comment "2222222222" sql security definer|
+alter procedure bar comment "3333333333"|
+alter procedure bar|
+show create procedure bar|
+Procedure sql_mode Create Procedure
+bar CREATE PROCEDURE `test`.`bar`(x char(16), y int)
+ COMMENT '3333333333'
+insert into test.t1 values (x, y)
+show procedure status like 'bar'|
+Db Name Type Definer Modified Created Security_type Comment
+test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333
+drop procedure bar|
+drop table t1;
+drop table t2;
+create procedure p1 () select (select s1 from t1) from t1;
+create table t1 (s1 int);
+call p1();
+(select s1 from t1)
+insert into t1 values (1);
+call p1();
+(select s1 from t1)
+1
+drop procedure p1;
+drop table t1;
+create function `foo` () returns int return 5;
+select `foo` ();
+`foo` ()
+5
+drop function `foo`;
View Lorry Controller Status