sp.test

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

# SELECT INTO local variables
--disable_warnings
drop procedure if exists into_test|
--enable_warnings
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
--disable_warnings
drop procedure if exists into_tes2|
--enable_warnings
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)
--disable_warnings
drop procedure if exists into_test3|
--enable_warnings
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.
--disable_warnings
drop procedure if exists into_test4|
--enable_warnings
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|
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.
--disable_warnings
drop procedure if exists into_outfile|
--enable_warnings
--replace_result $MYSQLTEST_VARDIR ..
eval create procedure into_outfile(x char(16), y int)
begin
  insert into test.t1 values (x, y);
  select * into outfile "$MYSQLTEST_VARDIR/tmp/spout" from test.t1;
  insert into test.t1 values (concat(x, "2"), y+2);
end|

--system rm -f $MYSQLTEST_VARDIR/tmp/spout
call into_outfile("ofile", 1)|
--system rm -f $MYSQLTEST_VARDIR/tmp/spout
delete from t1|
drop procedure into_outfile|

--disable_warnings
drop procedure if exists into_dumpfile|
--enable_warnings
--replace_result $MYSQLTEST_VARDIR ..
eval create procedure into_dumpfile(x char(16), y int)
begin
  insert into test.t1 values (x, y);
  select * into dumpfile "$MYSQLTEST_VARDIR/tmp/spdump" from test.t1 limit 1;
  insert into test.t1 values (concat(x, "2"), y+2);
end|

--system rm -f $MYSQLTEST_VARDIR/tmp/spdump
call into_dumpfile("dfile", 1)|
--system rm -f $MYSQLTEST_VARDIR/tmp/spdump
delete from t1|
drop procedure into_dumpfile|

--disable_warnings
drop procedure if exists create_select|
--enable_warnings
create procedure create_select(x char(16), y int)
begin
  insert into test.t1 values (x, y);
  create temporary table test.t3 select * from test.t1;
  insert into test.t3 values (concat(x, "2"), y+2);
end|

call create_select("cs", 90)|
select * from t1, t3|
drop table t3|
delete from t1|
drop procedure create_select|


# A minimal, constant FUNCTION.
--disable_warnings
drop function if exists e|
--enable_warnings
create function e() returns double
  return 2.7182818284590452354|

set @e = e()|
select e(), @e|

# A minimal function with one argument
--disable_warnings
drop function if exists inc|
--enable_warnings
create function inc(i int) returns int
  return i+1|

select inc(1), inc(99), inc(-71)|

# A minimal function with two arguments
--disable_warnings
drop function if exists mul|
--enable_warnings
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
--disable_warnings
drop function if exists append|
--enable_warnings
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
--disable_warnings
drop function if exists fac|
--enable_warnings
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
--disable_warnings
drop function if exists fun|
--enable_warnings
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))|

# Disable PS because double's give a bit different values
--disable_ps_protocol
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|
--enable_ps_protocol
delete from t2|

drop function e|
drop function inc|
drop function mul|
drop function append|
drop function fun|


#
# CONDITIONs and HANDLERs
#

--disable_warnings
drop procedure if exists hndlr1|
--enable_warnings
create procedure hndlr1(val int)
begin
  declare x int default 0;
  declare foo condition for 1136;
  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.t1 values ("hndlr1", val, 2);  # Too many values
  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|

--disable_warnings
drop procedure if exists hndlr2|
--enable_warnings
create procedure hndlr2(val int)
begin
  declare x int default 0;

  begin
    declare exit handler for sqlstate '21S01' set x = 1;

    insert into test.t1 values ("hndlr2", val, 2); # Too many values
  end;

  insert into test.t1 values ("hndlr2", x);
end|

call hndlr2(42)|
select * from t1|
delete from t1|
drop procedure hndlr2|


--disable_warnings
drop procedure if exists hndlr3|
--enable_warnings
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.t1 values ("hndlr3", y, 2);  # Too many values
      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.)
create table t3 ( id   char(16), data int )|

--disable_warnings
drop procedure if exists hndlr4|
--enable_warnings
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
#
--disable_warnings
drop procedure if exists cur1|
--enable_warnings
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|

create table t3 ( s char(16), i int )|

--disable_warnings
drop procedure if exists cur2|
--enable_warnings
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
--disable_warnings
drop procedure if exists chistics|
--enable_warnings
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|

--disable_warnings
drop function if exists chistics|
--enable_warnings
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 $|
--disable_warnings
drop procedure if exists modes$
--enable_warnings
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|
--disable_warnings
drop procedure if exists dummy|
--enable_warnings
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
--disable_warnings
drop procedure if exists rc|
--enable_warnings
create procedure rc()
begin
  delete from t1;
  insert into t1 values ("a", 1), ("b", 2), ("c", 3);
end|

call rc()|
select row_count()|
--disable_ps_protocol
update t1 set data=42 where id = "b";
select row_count()|
--enable_ps_protocol
delete from t1|
select row_count()|
delete from t1|
select row_count()|
select * from t1|
select row_count()|
drop procedure rc|


#
# Let us test how well new locking scheme works.
#

# Let us prepare playground
--disable_warnings
drop function if exists f0|
drop function if exists f1|
drop function if exists f2|
drop function if exists f3|
drop function if exists f4|
drop function if exists f5|
drop function if exists f6|
drop function if exists f7|
drop function if exists f8|
drop function if exists f9|
drop function if exists f10|
drop function if exists f11|
drop function if exists f12_1|
drop function if exists f12_2|
drop view if exists v0|
drop view if exists v1|
drop view if exists v2|
--enable_warnings
delete from t1|
delete from t2|
insert into t1 values ("a", 1), ("b", 2) |
insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |

# Test the simplest function using tables
create function f1() returns int
  return (select sum(data) from t1)|
select f1()|
# This should work too (and give 2 rows as result)
select id, f1() from t1|

# Function which uses two instances of table simultaneously
create function f2() returns int
  return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
select f2()|
select id, f2() from t1|

# Function which uses the same table twice in different queries
create function f3() returns int
begin
  declare n int;
  declare m int;
  set n:= (select min(data) from t1);
  set m:= (select max(data) from t1);
  return n < m;
end|
select f3()|
select id, f3() from t1|

# Calling two functions using same table
select f1(), f3()|
select id, f1(), f3() from t1|

# Function which uses two different tables
create function f4() returns double 
  return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
select f4()|
select s, f4() from t2|

# Recursive functions which due to this recursion require simultaneous
# access to several instance of the same table won't work
create function f5(i int) returns int
begin
  if i <= 0 then
    return 0;
  elseif i = 1  then
    return (select count(*) from t1 where data = i);
  else
    return (select count(*) + f5( i - 1) from t1 where data = i);
  end if;
end|
select f5(1)|
# Since currently recursive functions are disallowed ER_SP_NO_RECURSION
# error will be returned, once we will allow them error about
# insufficient number of locked tables will be returned instead.