path: root/mysql-test/t/sp.test

#
# 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),
        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
--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);

  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|


# 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 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|


# The few characteristics we parse
create procedure chistics()
  language sql
  not deterministic
  sql security definer
  comment 'Characteristics procedure test'
 insert into t1 values ("chistics", 1)|

# Call it, just to make sure.
call chistics()|
select * from t1|
delete from t1|
alter procedure chistics sql security invoker name chistics2|
show create procedure chistics2|
drop procedure chistics2|

create function chistics() returns int
  language sql
  deterministic
  sql security invoker
  comment 'Characteristics procedure test'
 return 42|

# Call it, just to make sure.
select chistics()|
alter function chistics name chistics2 comment 'Characteristics function test'|
show create function chistics2|
drop function chistics2|


# 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'|


#
# Test cases for old bugs
#

#
# 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|

#
# BUG#1874
#
create procedure bug1874()
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 bug1874()|
select * from t2|
delete from t1|
delete from t2|
drop procedure bug1874|

#
# BUG#2260
#
create procedure bug2260()
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 bug2260()|
select @x2|
drop procedure bug2260|

#
# 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 bug2227(x int)
begin
  declare y float default 2.6;
  declare z char(16) default "zzz";

  select 1.3, x, y, 42, z;
end|

call bug2227(9)|
drop procedure bug2227|

#
# BUG#2614
#
create procedure bug2614()
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 bug2614()|
--enable_warnings
call bug2614()|
drop table t3|
drop procedure bug2614|

#
# BUG#2674
#
create function bug2674 () returns int
  return @@sort_buffer_size|

set @osbs = @@sort_buffer_size|
set @@sort_buffer_size = 262000|
select bug2674()|
drop function bug2674|
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 bug2772() returns char(10) character set latin2
  return 'a'|

select bug2772()|
drop function bug2772|


#
# 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.
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 name bar2 comment "2222222222" sql security definer|
alter procedure bar2 name 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;