sp.test

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

#
create table t3 (f1 int, f2 int, f3 int)|
insert into t3 values (1,1,1)|

--disable_warnings
drop procedure if exists bug4726|
--enable_warnings
create procedure bug4726()
begin
   declare tmp_o_id INT;
   declare tmp_d_id INT default 1;

   while tmp_d_id <= 2 do
   begin
     select f1 into tmp_o_id from t3 where f2=1 and f3=1;
     set tmp_d_id = tmp_d_id + 1;
   end;
   end while;
end|

call bug4726()|
call bug4726()|
call bug4726()|

drop procedure bug4726|
drop table t3|

#
# BUG#4318
#

--disable_parsing # Don't know if HANDLER commands can work with SPs, or at all..
create table t3 (s1 int)|
insert into t3 values (3), (4)|

--disable_warnings
drop procedure if exists bug4318|
--enable_warnings
create procedure bug4318()
  handler t3 read next|

handler t3 open|
# Expect no results, as tables are closed, but there shouldn't be any errors
call bug4318()|
call bug4318()|
handler t3 close|

drop procedure bug4318|
drop table t3|
--enable_parsing

#
# BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error
#
# Added tests for most other show commands we could find too.
# (Skipping those already tested, and the ones depending on optional handlers.)
#
# Note: This will return a large number of results of different formats,
#       which makes it impossible to filter with --replace_column.
#       It's possible that some of these are not deterministic across
#       platforms. If so, just remove the offending command.
#
--disable_warnings
drop procedure if exists bug4902|
--enable_warnings
create procedure bug4902()
begin
  show charset like 'foo';
  show collation like 'foo';
  show column types;
  show create table t1;
  show create database test;
  show databases like 'foo';
  show errors;
  show columns from t1;
  show keys from t1;
  show open tables like 'foo';
  show privileges;
  show status like 'foo';
  show tables like 'foo';
  show variables like 'foo';
  show warnings;
end|
--disable_parsing
show binlog events;
show storage engines;
show master status;
show slave hosts;
show slave status;
--enable_parsing

call bug4902()|
call bug4902()|

drop procedure bug4902|

#
# BUG#4904
#
--disable_warnings
drop procedure if exists bug4904|
--enable_warnings
create procedure bug4904()
begin
  declare continue handler for sqlstate 'HY000' begin end;

  create table t2 as select * from t3;
end|

-- error 1146
call bug4904()|

drop procedure bug4904|

create table t3 (s1 char character set latin1, s2 char character set latin2)|

--disable_warnings
drop procedure if exists bug4904|
--enable_warnings
create procedure bug4904 ()
begin
  declare continue handler for sqlstate 'HY000' begin end;

  select s1 from t3 union select s2 from t3; 
end|

call bug4904()|

drop procedure bug4904|
drop table t3|

#
# BUG#336
#
--disable_warnings
drop procedure if exists bug336|
--enable_warnings
create procedure bug336(out y int)
begin
  declare x int;
  set x = (select sum(t.data) from test.t1 t);
  set y = x;
end|

insert into t1 values ("a", 2), ("b", 3)|
call bug336(@y)|
select @y|
delete from t1|
drop procedure bug336|

#
# BUG#3157
#
--disable_warnings
drop procedure if exists bug3157|
--enable_warnings
create procedure bug3157()
begin
  if exists(select * from t1) then
    set @n= @n + 1;
  end if;
  if (select count(*) from t1) then
    set @n= @n + 1;
  end if;
end|

set @n = 0|
insert into t1 values ("a", 1)|
call bug3157()|
select @n|
delete from t1|
drop procedure bug3157|

#
# BUG#5251: mysql changes creation time of a procedure/function when altering
#
--disable_warnings
drop procedure if exists bug5251|
--enable_warnings
create procedure bug5251()
begin
end|

select created into @c1 from mysql.proc
  where db='test' and name='bug5251'|
--sleep 2
alter procedure bug5251 comment 'foobar'|
select count(*) from mysql.proc
  where  db='test' and name='bug5251' and created = @c1|

drop procedure bug5251|

#
# BUG#5279: Stored procedure packets out of order if CHECKSUM TABLE
#
--disable_warnings
drop procedure if exists bug5251|
--enable_warnings
create procedure bug5251()
  checksum table t1|

call bug5251()|
call bug5251()|
drop procedure bug5251|

#
# BUG#5287: Stored procedure crash if leave outside loop
#
--disable_warnings
drop procedure if exists bug5287|
--enable_warnings
create procedure bug5287(param1 int)
label1:
  begin
    declare c cursor for select 5;

    loop
      if param1 >= 0 then
        leave label1;
      end if;
    end loop;
end|
call bug5287(1)|
drop procedure bug5287|


#
# BUG#5307: Stored procedure allows statement after BEGIN ... END
#
--disable_warnings
drop procedure if exists bug5307|
--enable_warnings
create procedure bug5307()
begin
end; set @x = 3|

call bug5307()|
select @x|
drop procedure bug5307|

#
# BUG#5258: Stored procedure modified date is 0000-00-00
# (This was a design flaw)
--disable_warnings
drop procedure if exists bug5258|
--enable_warnings
create procedure bug5258()
begin
end|

--disable_warnings
drop procedure if exists bug5258_aux|
--enable_warnings
create procedure bug5258_aux()
begin
  declare c, m char(19);

  select created,modified into c,m from mysql.proc where name = 'bug5258';
  if c = m then
    select 'Ok';
  else
    select c, m;
  end if;
end|

call bug5258_aux()|

drop procedure bug5258|
drop procedure bug5258_aux|

#
# BUG#4487: Stored procedure connection aborted if uninitialized char
#
--disable_warnings
drop function if exists bug4487|
--enable_warnings
create function bug4487() returns char
begin
  declare v char;
  return v;
end|

select bug4487()|
drop function bug4487|


#
# BUG#4941: Stored procedure crash fetching null value into variable.
#
--disable_warnings
drop procedure if exists bug4941|
--enable_warnings
--disable_warnings
drop procedure if exists bug4941|
--enable_warnings
create procedure bug4941(out x int)
begin
  declare c cursor for select i from t2 limit 1;
  open c;
  fetch c into x;
  close c;
end|

insert into t2 values (null, null, null)|
set @x = 42|
call bug4941(@x)|
select @x|
delete from t1|
drop procedure bug4941|

#
# BUG#4905: Stored procedure doesn't clear for "Rows affected"
#
--disable_warnings
drop procedure if exists bug4905|
--enable_warnings

create table t3 (s1 int,primary key (s1))|

--disable_warnings
drop procedure if exists bug4905|
--enable_warnings
create procedure bug4905()
begin
  declare v int;
  declare continue handler for sqlstate '23000' set v = 5;

  insert into t3 values (1);
end|

call bug4905()|
select row_count()|
call bug4905()|
select row_count()|
call bug4905()|
select row_count()|
select * from t3|

drop procedure bug4905|
drop table t3|

#
# BUG#6022: Stored procedure shutdown problem with self-calling function.
#

--disable_parsing # until we implement support for recursive stored functions.
--disable_warnings
drop function if exists bug6022|
--enable_warnings

--disable_warnings
drop function if exists bug6022|
--enable_warnings
create function bug6022(x int) returns int
begin
  if x < 0 then
    return 0;
  else
    return bug6022(x-1);
  end if;
end|

select bug6022(5)|
drop function bug6022|
--enable_parsing

#
# BUG#6029: Stored procedure specific handlers should have priority
#
--disable_warnings
drop procedure if exists bug6029|
--enable_warnings

--disable_warnings
drop procedure if exists bug6029|
--enable_warnings
create procedure bug6029()
begin
  declare exit handler for 1136  select '1136';
  declare exit handler for sqlstate '23000'  select 'sqlstate 23000';
  declare continue handler for sqlexception  select 'sqlexception';

  insert into t3 values (1);
  insert into t3 values (1,2);
end|
 
create table t3 (s1 int, primary key (s1))|
insert into t3 values (1)|
call bug6029()|
delete from t3|
call bug6029()|

drop procedure bug6029|
drop table t3|

#
# BUG#8540: Local variable overrides an alias
#
--disable_warnings
drop procedure if exists bug8540|
--enable_warnings

create procedure bug8540()
begin
  declare x int default 1;
  select x as y, x+0 as z;
end|

call bug8540()|
drop procedure bug8540|

#
# BUG#6642: Stored procedure crash if expression with set function
#
create table t3 (s1 int)|

--disable_warnings
drop procedure if exists bug6642|
--enable_warnings

create procedure bug6642()
  select abs(count(s1)) from t3|

call bug6642()|
call bug6642()|
drop procedure bug6642|

#
# BUG#7013: Stored procedure crash if group by ... with rollup
#
insert into t3 values (0),(1)|
--disable_warnings
drop procedure if exists bug7013|
--enable_warnings
create procedure bug7013()
  select s1,count(s1) from t3 group by s1 with rollup|
call bug7013()|
call bug7013()|
drop procedure bug7013|

#
# BUG#7743: 'Lost connection to MySQL server during query' on Stored Procedure
#
--disable_warnings
drop table if exists t4|
--enable_warnings
create table t4 (
  a mediumint(8) unsigned not null auto_increment,
  b smallint(5) unsigned not null,
  c char(32) not null,
  primary key  (a)
) engine=myisam default charset=latin1|
insert into t4 values (1, 2, 'oneword')|
insert into t4 values (2, 2, 'anotherword')|

--disable_warnings
drop procedure if exists bug7743|
--enable_warnings
create procedure bug7743 ( searchstring char(28) )
begin
  declare var mediumint(8) unsigned;
  select a into var from t4 where b = 2 and c = binary searchstring limit 1;
  select var;
end|

call bug7743("oneword")|
call bug7743("OneWord")|
call bug7743("anotherword")|
call bug7743("AnotherWord")|
drop procedure bug7743|
drop table t4|

#
# BUG#7992: SELECT .. INTO variable .. within Stored Procedure crashes
#           the server
#
delete from t3|
insert into t3 values(1)|
drop procedure if exists bug7992_1|
drop procedure if exists bug7992_2|
create procedure bug7992_1()
begin
  declare i int;
  select max(s1)+1 into i from t3;
end|
create procedure bug7992_2()
  insert into t3 (s1) select max(t4.s1)+1 from t3 as t4|

call bug7992_1()|
call bug7992_1()|
call bug7992_2()|
call bug7992_2()|

drop procedure bug7992_1|
drop procedure bug7992_2|
drop table t3|

#
# BUG#8116: calling simple stored procedure twice in a row results
#           in server crash
#
create table t3 (  userid bigint(20) not null default 0 )|

--disable_warnings
drop procedure if exists bug8116|
--enable_warnings
create procedure bug8116(in _userid int)
   select * from t3 where userid = _userid|

call bug8116(42)|
call bug8116(42)|
drop procedure bug8116|
drop table t3|

#
# BUG#6857: current_time() in STORED PROCEDURES
#
--disable_warnings
drop procedure if exists bug6857|
--enable_warnings
create procedure bug6857(counter int)
begin
  declare t0, t1 int;
  declare plus bool default 0;

  set t0 = current_time();
  while counter > 0 do
    set counter = counter - 1;
  end while;
  set t1 = current_time();
  if t1 > t0 then
    set plus = 1;
  end if;
  select plus;
end|

# QQ: This is currently disabled. Not only does it slow down a normal test
#     run, it makes running with valgrind (or similar tools) extremely
#     painful.
# Make sure this takes at least one second on all machines in all builds.
# 30000 makes it about 3 seconds on an old 1.1GHz linux.
#call bug6857(300000)|

drop procedure bug6857|

#
# BUG#8757: Stored Procedures: Scope of Begin and End Statements do not
#           work properly.
--disable_warnings
drop procedure if exists bug8757|
--enable_warnings
create procedure bug8757()
begin
  declare x int;
  declare c1 cursor for select data from t1 limit 1;

  begin
    declare y int;
    declare c2 cursor for select i from t2 limit 1;

    open c2;
    fetch c2 into y;
    close c2;
    select 2,y;
  end;
  open c1;
  fetch c1 into x;
  close c1;
  select 1,x;
end|

delete from t1|
delete from t2|
insert into t1 values ("x", 1)|
insert into t2 values ("y", 2, 0.0)|

call bug8757()|

delete from t1|
delete from t2|
drop procedure bug8757|


#
# BUG#8762: Stored Procedures: Inconsistent behavior
#           of DROP PROCEDURE IF EXISTS statement.
--disable_warnings
drop procedure if exists bug8762|
--enable_warnings
# Doesn't exist
drop procedure if exists bug8762; create procedure bug8762() begin end|
# Does exist
drop procedure if exists bug8762; create procedure bug8762() begin end|
drop procedure bug8762|


#
# BUG#5240: Stored procedure crash if function has cursor declaration
#
--disable_warnings
drop function if exists bug5240|
--enable_warnings
create function bug5240 () returns int
begin
  declare x int;
  declare c cursor for select data from t1 limit 1;

  open c;
  fetch c into x;
  close c;
  return x;
end|

delete from t1|
insert into t1 values ("answer", 42)|
select id, bug5240() from t1|
drop function bug5240|

#
# BUG#7992: rolling back temporary Item tree changes in SP
#
--disable_warnings
drop procedure if exists p1