diff options
Diffstat (limited to 'mysql-test/t/sp.test')
| -rw-r--r-- | mysql-test/t/sp.test | 1054 |
1 files changed, 1054 insertions, 0 deletions
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test new file mode 100644 index 00000000000..091a3e364ce --- /dev/null +++ b/mysql-test/t/sp.test @@ -0,0 +1,1054 @@ +# +# Basic stored PROCEDURE tests +# +# + +use test; + +--disable_warnings +drop table if exists t1; +drop table if exists t2; +--enable_warnings + +create table t1 ( + id char(16) not null, + data int not null +); +create table t2 ( + s char(16) not null, + i int not null, + d double not null +); + + +# 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; + + +# USE test: Make sure we remain in the same DB. +create procedure u() + use sptmp; + +--disable_warnings +drop database if exists sptmp; +--enable_warnings +create database sptmp; +use test; +call u(); +select database(); +drop database sptmp; +drop procedure u; + + +# 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| + + +# Set things to null +--disable_warnings +drop table if exists t3| +--enable_warnings +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); +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| + + +# 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| + + +# 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)| +delete from t2| +# 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 1306 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 done int default 0; + declare continue handler for 1306 set done = 1; + declare c cursor for select * from test.t2; + declare a char(16); + declare b int; + declare c double; + + 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 continue handler for 1306 set done = 1; + declare c1 cursor for select id,data from test.t1; + declare c2 cursor for select i from test.t2; + + open c1; + open c2; + repeat + begin + declare a char(16); + declare b,c int; + + fetch c1 into a, b; + fetch 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| + +# +# BUG#822 +# +create procedure bug822(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 bug822('foo', 42)| +call bug822('foo', 42)| +call bug822('bar', 666)| +select * from t1| +delete from t1| +drop procedure bug822| + +# +# BUG#1495 +# +create procedure bug1495() +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 bug1495()| +delete from t1 where id='foo'| +insert into t1 values ('bar', 7)| +call bug1495()| +delete from t1 where id='bar'| +select * from t1| +delete from t1| +drop procedure bug1495| + +# +# BUG#1547 +# +create procedure bug1547(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 bug1547("foo")| +call bug1547("bar")| +select * from t1| +delete from t1| +drop procedure bug1547| + +# +# 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 bug1656(out p1 int, out p2 int) + select * into p1, p1 from t70| + +call bug1656(@1, @2)| +select @1, @2| +drop table t70| +drop procedure bug1656| + + +# +# BUG#1862 +# +--disable_warnings +drop table if exists t3| +--enable_warnings +create table t3(a int)| + +create procedure bug1862() +begin + insert into t3 values(2); + flush tables; +end| + +call bug1862()| +# the second call caused a segmentation +call bug1862()| +select * from t3| +drop table t3| +drop procedure bug1862| + + +# +# 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 4 '0000-00-00 00:00:00' 5 '0000-00-00 00:00:00' +show function status like '%f%'| +drop procedure ifac| +drop function fac| + + +# 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 4 '0000-00-00 00:00:00' 5 '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. +select * from primes where i=45 or i=100 or i=199| +drop table primes| +drop procedure opp| +drop procedure ip| + +# Comment & suid +create procedure bar(x char(16), y int) + comment "111111111111" sql security invoker + insert into test.t1 values (x, y)| +--replace_column 4 '0000-00-00 00:00:00' 5 '0000-00-00 00:00:00' +show procedure status like 'bar'| +alter procedure bar name bar2 comment "2222222222" sql security definer| +alter procedure bar2 name bar comment "3333333333"| +alter procedure bar| +show create procedure bar| +--replace_column 4 '0000-00-00 00:00:00' 5 '0000-00-00 00:00:00' +show procedure status like 'bar'| +drop procedure bar| + +delimiter ;| +drop table t1; +drop table t2; |