summaryrefslogtreecommitdiff
path: root/mysql-test/t/sp.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/sp.test')
-rw-r--r--mysql-test/t/sp.test2358
1 files changed, 2358 insertions, 0 deletions
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
new file mode 100644
index 00000000000..ec1fb3e7452
--- /dev/null
+++ b/mysql-test/t/sp.test
@@ -0,0 +1,2358 @@
+#
+# Basic stored PROCEDURE tests
+#
+# Please keep this file free of --error cases and other
+# things that will not run in a single debugged mysqld
+# process (e.g. master-slave things).
+
+use test;
+
+--disable_warnings
+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;
+--enable_warnings
+
+create table t1 (
+ id char(16) not null,
+ data int not null
+);
+create table t2 (
+ s char(16),
+ i int,
+ d double
+);
+
+
+# Single statement, no params.
+create procedure foo42()
+ insert into test.t1 values ("foo", 42);
+
+call foo42();
+select * from t1;
+delete from t1;
+drop procedure foo42;
+
+
+# Single statement, two IN params.
+create procedure bar(x char(16), y int)
+ insert into test.t1 values (x, y);
+
+call bar("bar", 666);
+select * from t1;
+delete from t1;
+# Don't drop procedure yet...
+
+
+# Now for multiple statements...
+delimiter |;
+
+# Empty statement
+create procedure empty()
+begin
+end|
+
+call empty()|
+drop procedure empty|
+
+# Scope test. This is legal (warnings might be possible in the future,
+# but for the time being, we just accept it).
+create procedure scope(a int, b float)
+begin
+ declare b int;
+ declare c float;
+
+ begin
+ declare c int;
+ end;
+end|
+
+drop procedure scope|
+
+# Two statements.
+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|
+delete from t1|
+drop procedure two|
+
+
+# Simple test of local variables and SET.
+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|
+delete from t1|
+drop procedure locset|
+
+
+# In some contexts local variables are not recognized
+# (and in some, you have to qualify the identifier).
+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|
+delete from t1|
+drop procedure setcontext|
+
+
+# Set things to null
+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|
+drop table t3|
+drop procedure nullset|
+
+
+# The peculiar (non-standard) mixture of variables types in SET.
+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'|
+select id,data,@z from t1|
+delete from t1|
+drop procedure mixset|
+
+
+# Multiple CALL statements, one with OUT parameter.
+create procedure zip(x char(16), y int)
+begin
+ declare z int;
+ call zap(y, z);
+ call bar(x, z);
+end|
+
+# SET local variables and OUT parameter.
+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|
+delete from t1|
+drop procedure zip|
+drop procedure bar|
+
+# Top-level OUT parameter
+call zap(7, @zap)|
+select @zap|
+
+drop procedure zap|
+
+
+# "Deep" calls...
+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|
+delete from t1|
+drop procedure c1|
+drop procedure c2|
+drop procedure c3|
+drop procedure c4|
+
+# INOUT test
+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|
+delete from t1|
+drop procedure iotest|
+drop procedure inc2|
+
+# Propagating top-level @-vars
+create procedure incr(inout x int)
+ call inc(x)|
+
+# Before
+select @zap|
+call incr(@zap)|
+# After
+select @zap|
+
+drop procedure inc|
+drop procedure incr|
+
+# Call-by-value test
+# The expected result is:
+# ("cbv2", 4)
+# ("cbv1", 4711)
+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|
+delete from t1|
+drop procedure cbv1|
+drop procedure cbv2|
+
+
+# Subselect arguments
+
+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)|
+
+# QQ This doesn't work yet
+#create procedure sub2(id char(16))
+#begin
+# declare x int;
+# set x = (select sum(t.x) from test.t2 t);
+# insert into test.t1 values (id, x);
+#end|
+
+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))|
+#call sub2("sub2");
+select * from t1|
+select sub3((select max(i) from t2))|
+drop procedure sub1|
+#drop procedure sub2|
+drop function sub3|
+delete from t2|
+
+# Basic tests of the flow control constructs
+
+# Just test on 'x'...
+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|
+delete from t1|
+drop procedure a0|
+
+
+# The same, but with a more traditional test.
+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|
+delete from t1|
+drop procedure a|
+
+
+# REPEAT
+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|
+delete from t1|
+drop procedure b|
+
+
+# Check that repeat isn't parsed the wrong way
+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|
+
+# We don't actually want to call it.
+drop procedure b2|
+
+
+# Labelled WHILE with ITERATE (pointless really)
+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|
+delete from t1|
+drop procedure c|
+
+
+# Labelled WHILE with LEAVE
+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|
+delete from t1|
+drop procedure d|
+
+
+# LOOP, with simple IF statement
+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|
+delete from t1|
+drop procedure e|
+
+
+# A full IF statement
+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|
+delete from t1|
+drop procedure f|
+
+
+# This form of CASE is really just syntactic sugar for IF-ELSEIF-...
+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|
+delete from t1|
+drop procedure g|
+
+
+# The "simple CASE"
+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|
+delete from t1|
+drop procedure h|
+
+
+# It's actually possible to LEAVE a BEGIN-END block
+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|
+delete from t1|
+drop procedure i|
+
+
+# The non-standard GOTO, for compatibility
+#
+# QQQ The "label" syntax is temporary, it will (hopefully)
+# change to the more common "L:" syntax soon.
+#
+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()|
+drop procedure goto1|
+
+# With dummy handlers, just to test restore of contexts with jumps
+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)|
+call goto2(1)|
+call goto2(2)|
+
+drop procedure goto2|
+delete from t1|
+
+# Check label visibility for some more cases. We don't call these.
+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|
+
+# SELECT with one of more result set sent back to the clinet
+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()|
+drop procedure sel1|
+
+create procedure sel2()
+begin
+ select * from t1;
+ select * from t2;
+end|
+
+call sel2()|
+drop procedure sel2|
+delete from t1|
+delete from t2|
+
+# SELECT INTO local variables
+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|
+delete from t1|
+drop procedure into_test|
+
+
+# SELECT INTO with a mix of local and global variables
+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|
+delete from t1|
+drop procedure into_test2|
+
+
+# SELECT * INTO ... (bug test)
+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)|
+# Two call needed for bug test
+call into_test3()|
+call into_test3()|
+select * from t2|
+delete from t1|
+delete from t2|
+drop procedure into_test3|
+
+
+# SELECT INTO with no data is a warning ("no data", which we will
+# not see normally). When not caught, execution proceeds.
+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|
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3 ( s char(16), d int)|
+call into_test4()|
+select * from t3|
+insert into t1 values ("i4", 77)|
+call into_test4()|
+select * from t3|
+delete from t1|
+drop table t3|
+drop procedure into_test4|
+
+
+# These two (and the two procedures above) caused an assert() to fail in
+# sql_base.cc:lock_tables() at some point.
+
+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|
+
+system rm -f /tmp/spout|
+call into_outfile("ofile", 1)|
+system rm -f /tmp/spout|
+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|
+
+system rm -f /tmp/spdump|
+call into_dumpfile("dfile", 1)|
+system rm -f /tmp/spdump|
+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|
+
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+call create_select("cs", 90)|
+select * from t1, t3|
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+delete from t1|
+drop procedure create_select|
+
+
+# A minimal, constant FUNCTION.
+create function e() returns double
+ return 2.7182818284590452354|
+
+set @e = e()|
+select e(), @e|
+
+# A minimal function with one argument
+create function inc(i int) returns int
+ return i+1|
+
+select inc(1), inc(99), inc(-71)|
+
+# A minimal function with two arguments
+create function mul(x int, y int) returns int
+ return x*y|
+
+select mul(1,1), mul(3,5), mul(4711, 666)|
+
+# A minimal string function
+create function append(s1 char(8), s2 char(8)) returns char(16)
+ return concat(s1, s2)|
+
+select append("foo", "bar")|
+
+# A function with flow control
+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)|
+
+# Nested calls
+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)|
+
+
+# Various function calls in differen statements
+
+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))|
+
+# These don't work yet.
+select * from t2 where s = append("a", "b")|
+select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)|
+select * from t2 where d = e()|
+select * from t2|
+delete from t2|
+
+drop function e|
+drop function inc|
+drop function mul|
+drop function append|
+drop function fun|
+
+
+#
+# CONDITIONs and HANDLERs
+#
+
+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|
+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|
+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|
+delete from t1|
+drop procedure hndlr3|
+
+
+# Variables might be uninitialized when using handlers
+# (Otherwise the compiler can detect if a variable is not set, but
+# not in this case.)
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+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|
+drop table t3|
+drop procedure hndlr4|
+
+
+#
+# Cursors
+#
+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|
+drop procedure cur1|
+
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+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|
+delete from t1|
+delete from t2|
+drop table t3|
+drop procedure cur2|
+
+
+# The few characteristics we parse
+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|
+# Call it, just to make sure.
+call chistics()|
+select * from t1|
+delete from t1|
+alter procedure chistics sql security invoker|
+show create procedure chistics|
+drop procedure chistics|
+
+create function chistics() returns int
+ language sql
+ deterministic
+ sql security invoker
+ comment 'Characteristics procedure test'
+ return 42|
+
+show create function chistics|
+# Call it, just to make sure.
+select chistics()|
+alter function chistics
+ no sql
+ comment 'Characteristics function test'|
+show create function chistics|
+drop function chistics|
+
+
+# Check mode settings
+insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
+
+set @@sql_mode = 'ANSI'|
+delimiter $|
+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$
+delimiter |$
+set @@sql_mode = ''|
+
+set sql_select_limit = 1|
+call modes(@c1, @c2)|
+set sql_select_limit = default|
+
+select @c1, @c2|
+delete from t1|
+drop procedure modes|
+
+
+# Check that dropping a database without routines works.
+# (Dropping with routines is tested in sp-security.test)
+# First an empty db.
+create database sp_db1|
+drop database sp_db1|
+
+# Again, with a table.
+create database sp_db2|
+use sp_db2|
+# Just put something in here...
+create table t3 ( s char(4), t int )|
+insert into t3 values ("abcd", 42), ("dcba", 666)|
+use test|
+drop database sp_db2|
+
+# And yet again, with just a procedure.
+create database sp_db3|
+use sp_db3|
+create procedure dummy(out x int)
+ set x = 42|
+use test|
+drop database sp_db3|
+# Check that it's gone
+select type,db,name from mysql.proc where db = 'sp_db3'|
+
+
+# ROW_COUNT() function after a CALL
+# We test the other cases here too, although it's not strictly SP specific
+create procedure rc()
+begin
+ delete from t1;
+ insert into t1 values ("a", 1), ("b", 2), ("c", 3);
+end|
+
+call rc()|
+select row_count()|
+update t1 set data=42 where id = "b";
+select row_count()|
+delete from t1|
+select row_count()|
+delete from t1|
+select row_count()|
+select * from t1|
+select row_count()|
+drop procedure rc|
+
+
+#
+# Test cases for old bugs
+#
+
+#
+# BUG#822
+#
+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|
+delete from t1|
+drop procedure bug|
+
+#
+# BUG#1495
+#
+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|
+delete from t1|
+drop procedure bug|
+
+#
+# BUG#1547
+#
+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|
+delete from t1|
+drop procedure bug|
+
+#
+# BUG#1656
+#
+--disable_warnings
+drop table if exists t70|
+--enable_warnings
+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|
+drop table t70|
+drop procedure bug|
+
+#
+# BUG#1862
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3(a int)|
+
+create procedure bug()
+begin
+ insert into t3 values(2);
+ flush tables;
+end|
+
+call bug()|
+# the second call caused a segmentation
+call bug()|
+select * from t3|
+drop table t3|
+drop procedure bug|
+
+#
+# BUG#1874
+#
+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|
+delete from t1|
+delete from t2|
+drop procedure bug|
+
+#
+# BUG#2260
+#
+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|
+drop procedure bug|
+
+#
+# BUG#2267
+#
+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|
+
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+call bug2267_1()|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+call bug2267_2()|
+call bug2267_3()|
+call bug2267_4()|
+
+drop procedure bug2267_1|
+drop procedure bug2267_2|
+drop procedure bug2267_3|
+drop procedure bug2267_4|
+
+#
+# BUG#2227
+#
+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)|
+drop procedure bug|
+
+#
+# BUG#2614
+#
+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|
+
+--disable_warnings
+call bug()|
+--enable_warnings
+call bug()|
+drop table t3|
+drop procedure bug|
+
+#
+# BUG#2674
+#
+create function bug () returns int
+ return @@sort_buffer_size|
+
+set @osbs = @@sort_buffer_size|
+set @@sort_buffer_size = 262000|
+select bug()|
+drop function bug|
+set @@sort_buffer_size = @osbs|
+
+#
+# BUG#3259
+#
+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|
+
+#
+# BUG##2772
+#
+create function bug() returns char(10) character set latin2
+ return 'a'|
+
+select bug()|
+drop function bug|
+
+#
+# BUG#2776
+#
+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|
+call bug2(@x)|
+select @x|
+drop procedure bug|
+drop procedure bug2|
+
+#
+# BUG#2780
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3 (s1 smallint)|
+
+insert into t3 values (123456789012)|
+
+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|
+select * from t3|
+
+drop procedure bug|
+drop table t3|
+
+#
+# BUG#1863
+#
+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)|
+call bug1863(10)|
+select * from t4|
+
+drop procedure bug1863|
+drop table t3, t4|
+
+#
+# BUG#2656
+#
+--disable_warnings
+drop table if exists t3, t4|
+--enable_warnings
+
+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()|
+call bug2656_1()|
+call bug2656_2()|
+call bug2656_2()|
+drop procedure bug2656_1|
+drop procedure bug2656_2|
+drop table t3, t4|
+
+
+#
+# BUG#3426
+#
+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|
+call bug3426(NULL, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+# Clear SP cache
+alter procedure bug3426 sql security invoker|
+call bug3426(NULL, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+call bug3426(1000, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+
+drop procedure bug3426|
+
+#
+# BUG#3448
+#
+--disable_warnings
+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|
+--enable_warnings
+
+insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
+insert into t4 values (1 , 'bCh1' )|
+
+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|
+call bug3448()|
+call bug3448()|
+
+drop procedure bug3448|
+drop table t3, t4|
+
+
+#
+# BUG#3734
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+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')|
+call bug3734('Security')|
+
+drop procedure bug3734|
+drop table t3|
+
+#
+# BUG#3863
+#
+create procedure bug3863()
+begin
+ set @a = 0;
+ while @a < 5 do
+ set @a = @a + 1;
+ end while;
+end|
+
+call bug3863()|
+select @a|
+call bug3863()|
+select @a|
+
+drop procedure bug3863|
+
+#
+# BUG#2460
+#
+
+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)|
+call bug2460_1(2)|
+insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')|
+call bug2460_1(2)|
+call bug2460_1(2)|
+
+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|
+
+drop procedure bug2460_1|
+drop procedure bug2460_2|
+drop table t3|
+
+
+#
+# BUG#2564
+#
+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)|
+
+delimiter $|
+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$
+delimiter |$
+
+set @@sql_mode = ''|
+show create procedure bug2564_1|
+show create procedure bug2564_2|
+show create function bug2564_3|
+show create function bug2564_4|
+
+drop procedure bug2564_1|
+drop procedure bug2564_2|
+drop function bug2564_3|
+drop function bug2564_4|
+
+#
+# BUG#3132
+#
+create function bug3132(s char(20)) returns char(50)
+ return concat('Hello, ', s, '!')|
+
+select bug3132('Bob') union all select bug3132('Judy')|
+drop function bug3132|
+
+#
+# BUG#3843
+#
+create procedure bug3843()
+ analyze table t1|
+
+# Testing for packets out of order
+call bug3843()|
+call bug3843()|
+select 1+2|
+
+drop procedure bug3843|
+
+#
+# BUG#3368
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+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')|
+call bug3368('yz')|
+drop procedure bug3368|
+drop table t3|
+
+#
+# BUG#4579
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+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()|
+call bug4579_1()|
+
+drop procedure bug4579_1|
+drop procedure bug4579_2|
+drop table t3|
+
+
+#
+# BUG#4726
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+
+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|
+
+#
+# BUG#4318
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+
+create table t3 (s1 int)|
+insert into t3 values (3), (4)|
+
+create procedure bug4318()
+ handler t3 read next|
+
+handler t3 open|
+# Expect no results, as tables are closed, but there shouldn't be any errors
+call bug4318()|
+call bug4318()|
+handler t3 close|
+
+drop procedure bug4318|
+drop table t3|
+
+#
+# BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error
+#
+# Added tests for most other show commands we could find too.
+# (Skipping those already tested, and the ones depending on optional handlers.)
+#
+# Note: This will return a large number of results of different formats,
+# which makes it impossible to filter with --replace_column.
+# It's possible that some of these are not deterministic across
+# platforms. If so, just remove the offending command.
+#
+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|
+#show binlog events;
+#show storage engines;
+#show master status;
+#show slave hosts;
+#show slave status;
+
+call bug4902()|
+call bug4902()|
+
+drop procedure bug4902|
+
+# We need separate SP for SHOW PROCESSLIST since we want use replace_column
+create procedure bug4902_2()
+begin
+ show processlist;
+end|
+--replace_column 1 # 6 #
+call bug4902_2()|
+--replace_column 1 # 6 #
+call bug4902_2()|
+drop procedure bug4902_2|
+
+#
+# BUG#4904
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+
+create procedure bug4904()
+begin
+ declare continue handler for sqlstate 'HY000' begin end;
+
+ create table t2 as select * from t3;
+end|
+
+-- error 1146
+call bug4904()|
+
+drop procedure bug4904|
+
+create table t3 (s1 char character set latin1, s2 char character set latin2)|
+
+create procedure bug4904 ()
+begin
+ declare continue handler for sqlstate 'HY000' begin end;
+
+ select s1 from t3 union select s2 from t3;
+end|
+
+call bug4904()|
+
+drop procedure bug4904|
+drop table t3|
+
+#
+# BUG#336
+#
+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|
+delete from t1|
+drop procedure bug336|
+
+#
+# BUG#3157
+#
+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|
+delete from t1|
+drop procedure bug3157|
+
+#
+# BUG#5251: mysql changes creation time of a procedure/function when altering
+#
+create procedure bug5251()
+begin
+end|
+
+select created into @c1 from mysql.proc
+ where db='test' and name='bug5251'|
+--sleep 2
+alter procedure bug5251 comment 'foobar'|
+select count(*) from mysql.proc
+ where db='test' and name='bug5251' and created = @c1|
+
+drop procedure bug5251|
+
+#
+# BUG#5279: Stored procedure packets out of order if CHECKSUM TABLE
+#
+create procedure bug5251()
+ checksum table t1|
+
+call bug5251()|
+call bug5251()|
+drop procedure bug5251|
+
+#
+# BUG#5287: Stored procedure crash if leave outside loop
+#
+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|
+
+
+#
+# BUG#5307: Stored procedure allows statement after BEGIN ... END
+#
+create procedure bug5307()
+begin
+end; set @x = 3|
+
+call bug5307()|
+select @x|
+drop procedure bug5307|
+
+#
+# BUG#5258: Stored procedure modified date is 0000-00-00
+# (This was a design flaw)
+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()|
+
+drop procedure bug5258|
+drop procedure bug5258_aux|
+
+#
+# BUG#4487: Stored procedure connection aborted if uninitialized char
+#
+create function bug4487() returns char
+begin
+ declare v char;
+ return v;
+end|
+
+select bug4487()|
+drop function bug4487|
+
+
+#
+# BUG#4941: Stored procedure crash fetching null value into variable.
+#
+--disable_warnings
+drop procedure if exists bug4941|
+--enable_warnings
+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|
+delete from t1|
+drop procedure bug4941|
+
+
+#
+# BUG#3583: query cache doesn't work for stored procedures
+#
+--disable_warnings
+drop procedure if exists bug3583|
+--enable_warnings
+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'|
+call bug3583()|
+show status like 'Qcache_hits'|
+call bug3583()|
+call bug3583()|
+show status like 'Qcache_hits'|
+
+set global query_cache_size = @x|
+flush status|
+flush query cache|
+delete from t1|
+drop procedure bug3583|
+
+#
+# BUG#4905: Stored procedure doesn't clear for "Rows affected"
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug4905|
+--enable_warnings
+
+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()|
+call bug4905()|
+select row_count()|
+call bug4905()|
+select row_count()|
+select * from t3|
+
+drop procedure bug4905|
+drop table t3|
+
+#
+# BUG#6022: Stored procedure shutdown problem with self-calling function.
+#
+--disable_warnings
+drop function if exists bug6022|
+--enable_warnings
+
+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)|
+drop function bug6022|
+
+#
+# BUG#6029: Stored procedure specific handlers should have priority
+#
+--disable_warnings
+drop procedure if exists bug6029|
+--enable_warnings
+
+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()|
+delete from t3|
+call bug6029()|
+
+drop procedure bug6029|
+drop table t3|
+
+
+#
+# Some "real" examples
+#
+
+# fac
+
+--disable_warnings
+drop table if exists fac|
+--enable_warnings
+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|
+drop table fac|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show function status like '%f%'|
+drop procedure ifac|
+drop function fac|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show function status like '%f%'|
+
+
+# primes
+
+--disable_warnings
+drop table if exists primes|
+--enable_warnings
+
+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|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like '%p%'|
+
+# This isn't the fastest way in the world to compute prime numbers, so
+# don't be too ambitious. ;-)
+call ip(200)|
+# We don't want to select the entire table here, just pick a few
+# examples.
+# The expected result is:
+# i p
+# --- ----
+# 45 211
+# 100 557
+# 199 1229
+select * from primes where i=45 or i=100 or i=199|
+drop table primes|
+drop procedure opp|
+drop procedure ip|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like '%p%'|
+
+
+# Fibonacci, for recursion test. (Yet Another Numerical series :)
+
+--disable_warnings
+drop table if exists fib|
+--enable_warnings
+create table fib ( f bigint unsigned not null )|
+
+insert into fib values (1), (1)|
+
+# We deliberately do it the awkward way, fetching the last two
+# values from the table, in order to exercise various statements
+# and table accesses at each turn.
+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|
+drop table fib|
+drop procedure fib|
+
+
+#
+# Comment & suid
+#
+
+create procedure bar(x char(16), y int)
+ comment "111111111111" sql security invoker
+ insert into test.t1 values (x, y)|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'bar'|
+alter procedure bar comment "2222222222" sql security definer|
+alter procedure bar comment "3333333333"|
+alter procedure bar|
+show create procedure bar|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'bar'|
+drop procedure bar|
+delimiter ;|
+drop table t1;
+drop table t2;
+
+#
+# rexecution
+#
+create procedure p1 () select (select s1 from t1) from t1;
+create table t1 (s1 int);
+call p1();
+insert into t1 values (1);
+call p1();
+drop procedure p1;
+drop table t1;
+
+#
+# backticks
+#
+create function `foo` () returns int return 5;
+select `foo` ();
+drop function `foo`;
View Lorry Controller Status