sp.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

#
# 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).
#
# Test cases for bugs are added at the end. See template there.
#
# 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.

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_warnings
drop table if exists t1,t2,t3,t4;
--enable_warnings
create 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_warnings
drop procedure if exists foo42;
--enable_warnings
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.
--disable_warnings
drop procedure if exists bar;
--enable_warnings
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
--disable_warnings
drop procedure if exists empty|
--enable_warnings
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).
--disable_warnings
drop procedure if exists scope|
--enable_warnings
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.
--disable_warnings
drop procedure if exists two|
--enable_warnings
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.
--disable_warnings
drop procedure if exists locset|
--enable_warnings
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).
--disable_warnings
drop procedure if exists setcontext|
--enable_warnings
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) )|

--disable_warnings
drop procedure if exists nullset|
--enable_warnings
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.
--disable_warnings
drop procedure if exists mixset|
--enable_warnings
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.
--disable_warnings
drop procedure if exists zip|
--enable_warnings
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.
--disable_warnings
drop procedure if exists zap|
--enable_warnings
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...
--disable_warnings
drop procedure if exists c1|
--enable_warnings
create procedure c1(x int)
  call c2("c", x)|
--disable_warnings
drop procedure if exists c2|
--enable_warnings
create procedure c2(s char(16), x int)
  call c3(x, s)|
--disable_warnings
drop procedure if exists c3|
--enable_warnings
create procedure c3(x int, s char(16))
  call c4("level", x, s)|
--disable_warnings
drop procedure if exists c4|
--enable_warnings
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
--disable_warnings
drop procedure if exists iotest|
--enable_warnings
create procedure iotest(x1 char(16), x2 char(16), y int)
begin
  call inc2(x2, y);
  insert into test.t1 values (x1, y);
end|

--disable_warnings
drop procedure if exists inc2|
--enable_warnings
create procedure inc2(x char(16), y int)
begin
  call inc(y);
  insert into test.t1 values (x, y);
end|

--disable_warnings
drop procedure if exists inc|
--enable_warnings
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
--disable_warnings
drop procedure if exists incr|
--enable_warnings
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)
--disable_warnings
drop procedure if exists cbv1|
--enable_warnings
create procedure cbv1()
begin
  declare y int default 3;

  call cbv2(y+1, y);
  insert into test.t1 values ("cbv1", y);
end|

--disable_warnings
drop procedure if exists cbv2|
--enable_warnings
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)|

--disable_warnings
drop procedure if exists sub1|
--enable_warnings
create procedure sub1(id char(16), x int)
  insert into test.t1 values (id, x)|

--disable_warnings
drop procedure if exists sub2|
--enable_warnings
create 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_warnings
drop procedure if exists sub3|
--enable_warnings
create 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_COLUMNS
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 t1|
delete from t2|

# Basic tests of the flow control constructs

# Just test on 'x'...
--disable_warnings
drop procedure if exists a0|
--enable_warnings
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.
--disable_warnings
drop procedure if exists a|
--enable_warnings
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
--disable_warnings
drop procedure if exists b|
--enable_warnings
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
--disable_warnings
drop procedure if exists b2|
--enable_warnings
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)
--disable_warnings
drop procedure if exists c|
--enable_warnings
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
--disable_warnings
drop procedure if exists d|
--enable_warnings
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
--disable_warnings
drop procedure if exists e|
--enable_warnings
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
--disable_warnings
drop procedure if exists f|
--enable_warnings
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-...
--disable_warnings
drop procedure if exists g|
--enable_warnings
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"
--disable_warnings
drop procedure if exists h|
--enable_warnings
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
--disable_warnings
drop procedure if exists i|
--enable_warnings
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)|

--disable_warnings
drop procedure if exists sel1|
--enable_warnings
create procedure sel1()
begin
  select * from t1;
end|

call sel1()|
drop procedure sel1|

--disable_warnings
drop procedure if exists sel2|
--enable_warnings
create procedure sel2()
begin
  select * from t1;
  select * from t2;
end|

call sel2()|
drop procedure sel2|
delete from t1|
delete from t2|