sp.test

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

#
# BUG#2267 "Lost connect if stored procedure has SHOW FUNCTION STATUS"
#
--disable_warnings
drop procedure if exists bug2267_1|
--enable_warnings
create procedure bug2267_1()
begin
  show procedure status;
end|

--disable_warnings
drop procedure if exists bug2267_2|
--enable_warnings
create procedure bug2267_2()
begin
  show function status;
end|

--disable_warnings
drop procedure if exists bug2267_3|
--enable_warnings
create procedure bug2267_3()
begin
  show create procedure bug2267_1;
end|

--disable_warnings
drop procedure if exists bug2267_4|
drop function if exists bug2267_4|
--enable_warnings
create procedure bug2267_4()
begin
  show create function bug2267_4;
end|
create function bug2267_4() returns int return 100|

--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|
drop function bug2267_4|

#
# BUG#2227
#
--disable_warnings
drop procedure if exists bug2227|
--enable_warnings
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 "Stored procedure with INSERT ... SELECT that does not
#           contain any tables crashes server"
#
--disable_warnings
drop procedure if exists bug2614|
--enable_warnings
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
#
--disable_warnings
drop function if exists bug2674|
--enable_warnings
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
#
--disable_warnings
drop procedure if exists bug3259_1 |
--enable_warnings
create procedure bug3259_1 () begin end|
--disable_warnings
drop procedure if exists BUG3259_2 |
--enable_warnings
create procedure BUG3259_2 () begin end|
--disable_warnings
drop procedure if exists Bug3259_3 |
--enable_warnings
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
#
--disable_warnings
drop function if exists bug2772|
--enable_warnings
create function bug2772() returns char(10) character set latin2
  return 'a'|

select bug2772()|
drop function bug2772|

#
# BUG#2776
#
--disable_warnings
drop procedure if exists bug2776_1|
--enable_warnings
create procedure bug2776_1(out x int)
begin
  declare v int;

  set v = default;
  set x = v;
end|

--disable_warnings
drop procedure if exists bug2776_2|
--enable_warnings
create procedure bug2776_2(out x int)
begin
  declare v int default 42;

  set v = default;
  set x = v;
end|

set @x = 1|
call bug2776_1(@x)|
select @x|
call bug2776_2(@x)|
select @x|
drop procedure bug2776_1|
drop procedure bug2776_2|

#
# BUG#2780
#
create table t3 (s1 smallint)|

insert into t3 values (123456789012)|

--disable_warnings
drop procedure if exists bug2780|
--enable_warnings
create procedure bug2780()
begin
  declare exit handler for sqlwarning set @x = 1; 

  set @x = 0;
  insert into t3 values (123456789012);
  insert into t3 values (0);
end|

call bug2780()|
select @x|
select * from t3|

drop procedure bug2780|
drop table t3|

#
# BUG#1863
#
create table t3 (content varchar(10) )|
insert into t3 values ("test1")|
insert into t3 values ("test2")|
create table t4 (f1 int, rc int, t3 int)|

--disable_warnings
drop procedure if exists bug1863|
--enable_warnings
create procedure bug1863(in1 int)
begin 

  declare ind int default 0;
  declare t1 int;
  declare t2 int;
  declare t3 int;

  declare rc int default 0;
  declare continue handler for 1065 set rc = 1;

  drop temporary table if exists temp_t1;
  create temporary table temp_t1 (
    f1 int auto_increment, f2 varchar(20), primary key (f1)
  );

  insert into temp_t1 (f2) select content from t3;

  select f2 into t3 from temp_t1 where f1 = 10;

  if (rc) then
       insert into t4 values (1, rc, t3);
  end if;

  insert into t4 values (2, rc, t3);

end|

call bug1863(10)|
call bug1863(10)|
select * from t4|

drop procedure bug1863|
drop temporary table temp_t1;
drop table t3, t4|

#
# BUG#2656
#

create table t3 ( 
  OrderID  int not null,
  MarketID int,
  primary key (OrderID)
)|

create table t4 ( 
  MarketID int not null,
  Market varchar(60),
  Status char(1),
  primary key (MarketID)
)|

insert t3 (OrderID,MarketID) values (1,1)|
insert t3 (OrderID,MarketID) values (2,2)|
insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")|
insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")|

--disable_warnings
drop procedure if exists bug2656_1|
--enable_warnings
create procedure bug2656_1()
begin 
  select
    m.Market
  from  t4 m JOIN t3 o 
        ON o.MarketID != 1 and o.MarketID = m.MarketID;
end |

--disable_warnings
drop procedure if exists bug2656_2|
--enable_warnings
create procedure bug2656_2()
begin 
  select
    m.Market
  from  
    t4 m, t3 o
  where       
    m.MarketID != 1 and m.MarketID = o.MarketID;
        
end |

call bug2656_1()|
call bug2656_1()|
call bug2656_2()|
call bug2656_2()|
drop procedure bug2656_1|
drop procedure bug2656_2|
drop table t3, t4|


#
# BUG#3426
#
--disable_warnings
drop procedure if exists bug3426|
--enable_warnings
create procedure bug3426(in_time int unsigned, out x int)
begin
  if in_time is null then
    set @stamped_time=10;
    set x=1;
  else
    set @stamped_time=in_time;
    set x=2;
  end if;
end|

call bug3426(1000, @i)|
select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
call bug3426(NULL, @i)|
select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
# Clear SP cache
alter procedure bug3426 sql security invoker|
call bug3426(NULL, @i)|
select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
call bug3426(1000, @i)|
select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|

drop procedure bug3426|

#
# BUG#3448
#
--disable_warnings
create table t3 (
  a int primary key, 
  ach char(1)
) engine = innodb|

create table t4 (
  b int  primary key , 
  bch char(1)
) engine = innodb|
--enable_warnings

insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
insert into t4 values (1 , 'bCh1' )|

--disable_warnings
drop procedure if exists bug3448|
--enable_warnings
create procedure bug3448()
  select * from t3 inner join t4 on t3.a = t4.b|

select * from t3 inner join t4 on t3.a = t4.b|
call bug3448()|
call bug3448()|

drop procedure bug3448|
drop table t3, t4|


#
# BUG#3734
#
create table t3 (
  id int unsigned auto_increment not null primary key,
  title VARCHAR(200),
  body text,
  fulltext (title,body)
)|

insert into t3 (title,body) values
  ('MySQL Tutorial','DBMS stands for DataBase ...'),
  ('How To Use MySQL Well','After you went through a ...'),
  ('Optimizing MySQL','In this tutorial we will show ...'),
  ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
  ('MySQL vs. YourSQL','In the following database comparison ...'),
  ('MySQL Security','When configured properly, MySQL ...')|

--disable_warnings
drop procedure if exists bug3734 |
--enable_warnings
create procedure bug3734 (param1 varchar(100))
  select * from t3 where match (title,body) against (param1)|

call bug3734('database')|
call bug3734('Security')|

drop procedure bug3734|
drop table t3|

#
# BUG#3863
#
--disable_warnings
drop procedure if exists bug3863|
--enable_warnings
create procedure bug3863()
begin
  set @a = 0;
  while @a < 5 do
    set @a = @a + 1;
  end while;
end|

call bug3863()|
select @a|
call bug3863()|
select @a|

drop procedure bug3863|

#
# BUG#2460
#

create table t3 (
  id int(10) unsigned not null default 0,
  rid int(10) unsigned not null default 0,
  msg text not null,
  primary key (id),
  unique key rid (rid, id)
)|

--disable_warnings
drop procedure if exists bug2460_1|
--enable_warnings
create procedure bug2460_1(in v int)
begin
    ( select n0.id from t3 as n0 where n0.id = v )
  union
    ( select n0.id from t3 as n0, t3 as n1
        where n0.id = n1.rid and n1.id = v )
  union
    ( select n0.id from t3 as n0, t3 as n1, t3 as n2
        where n0.id = n1.rid and n1.id = n2.rid and n2.id = v );
end|

call bug2460_1(2)|
call bug2460_1(2)|
insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')|
call bug2460_1(2)|
call bug2460_1(2)|

--disable_warnings
drop procedure if exists bug2460_2|
--enable_warnings
create procedure bug2460_2()
begin
  drop table if exists t3;
  create temporary table t3 (s1 int);
  insert into t3 select 1 union select 1;
end|

call bug2460_2()|
call bug2460_2()|
select * from t3|

drop procedure bug2460_1|
drop procedure bug2460_2|
drop table t3|


#
# BUG#2564
#
set @@sql_mode = ''|
--disable_warnings
drop procedure if exists bug2564_1|
--enable_warnings
create procedure bug2564_1()
    comment 'Joe''s procedure'
  insert into `t1` values ("foo", 1)|

set @@sql_mode = 'ANSI_QUOTES'|
--disable_warnings
drop procedure if exists bug2564_2|
--enable_warnings
create procedure bug2564_2()
  insert into "t1" values ('foo', 1)|

delimiter $|
set @@sql_mode = ''$
--disable_warnings
drop function if exists bug2564_3$
--enable_warnings
create function bug2564_3(x int, y int) returns int
  return x || y$

set @@sql_mode = 'ANSI'$
--disable_warnings
drop function if exists bug2564_4$
--enable_warnings
create function bug2564_4(x int, y int) returns int
  return x || y$
delimiter |$

set @@sql_mode = ''|
show create procedure bug2564_1|
show create procedure bug2564_2|
show create function bug2564_3|
show create function bug2564_4|

drop procedure bug2564_1|
drop procedure bug2564_2|
drop function bug2564_3|
drop function bug2564_4|

#
# BUG#3132
#
--disable_warnings
drop function if exists bug3132|
--enable_warnings
create function bug3132(s char(20)) returns char(50)
  return concat('Hello, ', s, '!')|

select bug3132('Bob') union all select bug3132('Judy')|
drop function bug3132|

#
# BUG#3843
#
--disable_warnings
drop procedure if exists bug3843|
--enable_warnings
create procedure bug3843()
  analyze table t1|

# Testing for packets out of order
call bug3843()|
call bug3843()|
select 1+2|

drop procedure bug3843|

#
# BUG#3368
#
create table t3 ( s1 char(10) )|
insert into t3 values ('a'), ('b')|

--disable_warnings
drop procedure if exists bug3368|
--enable_warnings
create procedure bug3368(v char(10))
begin
  select group_concat(v) from t3;
end|

call bug3368('x')|
call bug3368('yz')|
drop procedure bug3368|
drop table t3|

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

--disable_warnings
drop procedure if exists bug4579_1|
--enable_warnings
create procedure bug4579_1 ()
begin
  declare sf1 int;

  select f1 into sf1 from t3 where f1=1 and f2=1;
  update t3 set f2 = f2 + 1 where f1=1 and f2=1;
  call bug4579_2();
end|

--disable_warnings
drop procedure if exists bug4579_2|
--enable_warnings
create procedure bug4579_2 ()
begin
end|

call bug4579_1()|
call bug4579_1()|
call bug4579_1()|

drop procedure bug4579_1|
drop procedure bug4579_2|
drop table t3|

#
# BUG#2773: Function's data type ignored in stored procedures
#
--disable_warnings
drop procedure if exists bug2773|
--enable_warnings

create function bug2773() returns int return null|
create table t3 as select bug2773()|
show create table t3|
drop table t3|
drop function bug2773|

#
# BUG#3788: Stored procedure packet error
#
--disable_warnings
drop procedure if exists bug3788|
--enable_warnings

create function bug3788() returns date return cast("2005-03-04" as date)|
select bug3788()|
drop function bug3788|

create function bug3788() returns binary(1) return 5|
select bug3788()|
drop function bug3788|
 

#
# BUG#4726