## Basic stored PROCEDURE tests## Test cases for bugs are added at the end. See template there.## Some tests that require --error go into sp-error.test# Tests that require inndb go into sp_trans.test# Tests that check privilege and security issues go to sp-security.test.# Tests that require multiple connections, except security/privilege tests,#   go to sp-thread.# Tests that uses 'goto' to into sp-goto.test (currently disabled)# Tests that destroys system tables (e.g. mysql.proc) for error testing#   go to sp-destruct.# Tests that require multibyte character sets, which are not always available,#   go into separate files (e.g. sp-ucs2.test)use test;# Test tables## t1 and t2 are reused throughout the file, and dropped at the end.# t3 and up are created and dropped when needed.#--disable_warningsdrop table if exists t1,t2,t3,t4;drop view if exists v1;drop procedure if exists p1;drop procedure if exists p2;drop function if exists f1;drop function if exists f2;--enable_warningscreate table t1 (	id   char(16) not null default '',        data int not null);create table t2 (	s   char(16),        i   int,	d   double);# Single statement, no params.--disable_warningsdrop procedure if exists foo42;--enable_warningscreate 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.--disable_warningsdrop procedure if exists bar;--enable_warningscreate 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--disable_warningsdrop procedure if exists empty|--enable_warningscreate procedure empty()beginend|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).--disable_warningsdrop procedure if exists scope|--enable_warningscreate procedure scope(a int, b float)begin  declare b int;  declare c float;  begin    declare c int;  end;end|drop procedure scope|# Two statements.--disable_warningsdrop procedure if exists two|--enable_warningscreate 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.--disable_warningsdrop procedure if exists locset|--enable_warningscreate 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).--disable_warningsdrop procedure if exists setcontext|--enable_warningscreate 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 nullcreate table t3 ( d date, i int, f double, s varchar(32) )|--disable_warningsdrop procedure if exists nullset|--enable_warningscreate 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.--disable_warningsdrop procedure if exists mixset|--enable_warningscreate 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.--disable_warningsdrop procedure if exists zip|--enable_warningscreate 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.--disable_warningsdrop procedure if exists zap|--enable_warningscreate 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 parametercall zap(7, @zap)|select @zap|drop procedure zap|# "Deep" calls...--disable_warningsdrop procedure if exists c1|--enable_warningscreate procedure c1(x int)  call c2("c", x)|--disable_warningsdrop procedure if exists c2|--enable_warningscreate procedure c2(s char(16), x int)  call c3(x, s)|--disable_warningsdrop procedure if exists c3|--enable_warningscreate procedure c3(x int, s char(16))  call c4("level", x, s)|--disable_warningsdrop procedure if exists c4|--enable_warningscreate 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--disable_warningsdrop procedure if exists iotest|--enable_warningscreate procedure iotest(x1 char(16), x2 char(16), y int)begin  call inc2(x2, y);  insert into test.t1 values (x1, y);end|--disable_warningsdrop procedure if exists inc2|--enable_warningscreate procedure inc2(x char(16), y int)begin  call inc(y);  insert into test.t1 values (x, y);end|--disable_warningsdrop procedure if exists inc|--enable_warningscreate 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--disable_warningsdrop procedure if exists incr|--enable_warningscreate procedure incr(inout x int)  call inc(x)|# Beforeselect @zap|call incr(@zap)|# Afterselect @zap|drop procedure inc|drop procedure incr|# Call-by-value test#  The expected result is:#    ("cbv2", 4)#    ("cbv1", 4711)--disable_warningsdrop procedure if exists cbv1|--enable_warningscreate procedure cbv1()begin  declare y int default 3;  call cbv2(y+1, y);  insert into test.t1 values ("cbv1", y);end|--disable_warningsdrop procedure if exists cbv2|--enable_warningscreate 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 argumentsinsert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|--disable_warningsdrop procedure if exists sub1|--enable_warningscreate procedure sub1(id char(16), x int)  insert into test.t1 values (id, x)|--disable_warningsdrop procedure if exists sub2|--enable_warningscreate procedure sub2(id char(16))begin  declare x int;  set x = (select sum(t.i) from test.t2 t);  insert into test.t1 values (id, x);end|--disable_warningsdrop procedure if exists sub3|--enable_warningscreate function sub3(i int) returns int  return i+1|call sub1("sub1a", (select 7))|call sub1("sub1b", (select max(i) from t2))|--error ER_OPERAND_COLUMNScall 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 t1|delete from t2|# Basic tests of the flow control constructs# Just test on 'x'...--disable_warningsdrop procedure if exists a0|--enable_warningscreate 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.--disable_warningsdrop procedure if exists a|--enable_warningscreate 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--disable_warningsdrop procedure if exists b|--enable_warningscreate 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--disable_warningsdrop procedure if exists b2|--enable_warningscreate 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)--disable_warningsdrop procedure if exists c|--enable_warningscreate 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--disable_warningsdrop procedure if exists d|--enable_warningscreate 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--disable_warningsdrop procedure if exists e|--enable_warningscreate 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--disable_warningsdrop procedure if exists f|--enable_warningscreate 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-...--disable_warningsdrop procedure if exists g|--enable_warningscreate procedure g(x int)casewhen 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"--disable_warningsdrop procedure if exists h|--enable_warningscreate procedure h(x int)case xwhen 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--disable_warningsdrop procedure if exists i|--enable_warningscreate 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 with one of more result set sent back to the clinetinsert into t1 values ("foo", 3), ("bar", 19)|insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)|--disable_warningsdrop procedure if exists sel1|--enable_warningscreate procedure sel1()begin  select * from t1;end|call sel1()|drop procedure sel1|--disable_warningsdrop procedure if exists sel2|--enable_warningscreate procedure sel2()begin  select * from t1;  select * from t2;end|call sel2()|