diff options
Diffstat (limited to 'mysql-test/t/sp.test')
| -rw-r--r-- | mysql-test/t/sp.test | 531 |
1 files changed, 455 insertions, 76 deletions
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index ec1fb3e7452..5261267b58e 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8,36 +8,15 @@ use test; --disable_warnings -drop table if exists t1,t2,t3,t4; -drop procedure if exists goto1; -drop procedure if exists goto2; -drop procedure if exists goto3; -drop procedure if exists goto4; -drop procedure if exists goto5; -drop procedure if exists goto6; -drop procedure if exists into_outfile; -drop procedure if exists into_dumpfile; -drop procedure if exists create_select; -drop procedure if exists bar; -drop procedure if exists hndlr1; -drop procedure if exists hndlr2; -drop procedure if exists hndlr3; -drop procedure if exists hndlr4; -drop procedure if exists cur1; -drop procedure if exists cur2; -drop procedure if exists chistics; -drop procedure if exists chistics2; -drop procedure if exists modes; -drop procedure if exists dummy; -drop procedure if exists bug; -drop procedure if exists bug2; -drop function if exists fac; +drop table if exists t1; --enable_warnings - create table t1 ( id char(16) not null, data int not null ); +--disable_warnings +drop table if exists t2; +--enable_warnings create table t2 ( s char(16), i int, @@ -46,6 +25,9 @@ create table t2 ( # Single statement, no params. +--disable_warnings +drop procedure if exists foo42; +--enable_warnings create procedure foo42() insert into test.t1 values ("foo", 42); @@ -56,6 +38,9 @@ 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); @@ -69,6 +54,9 @@ delete from t1; delimiter |; # Empty statement +--disable_warnings +drop procedure if exists empty| +--enable_warnings create procedure empty() begin end| @@ -78,6 +66,9 @@ 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; @@ -91,6 +82,9 @@ 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); @@ -104,6 +98,9 @@ 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; @@ -120,6 +117,9 @@ 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; @@ -136,8 +136,14 @@ 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) )| +--disable_warnings +drop procedure if exists nullset| +--enable_warnings create procedure nullset() begin declare ld date; @@ -163,6 +169,9 @@ 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; @@ -179,6 +188,9 @@ 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; @@ -187,6 +199,9 @@ begin 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; @@ -207,12 +222,24 @@ 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)| @@ -225,18 +252,27 @@ 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| @@ -247,6 +283,9 @@ 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)| @@ -263,6 +302,9 @@ drop procedure incr| # 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; @@ -271,6 +313,9 @@ begin 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; @@ -288,10 +333,16 @@ drop procedure cbv2| 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)| # QQ This doesn't work yet +#--disable_warnings +#drop procedure if exists sub2| +#--enable_warnings #create procedure sub2(id char(16)) #begin # declare x int; @@ -299,6 +350,9 @@ create procedure sub1(id char(16), x int) # 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| @@ -317,6 +371,9 @@ 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; @@ -330,6 +387,9 @@ 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; @@ -343,6 +403,9 @@ 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); @@ -356,6 +419,9 @@ 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); @@ -367,6 +433,9 @@ 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); @@ -382,6 +451,9 @@ 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); @@ -397,6 +469,9 @@ 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 @@ -413,6 +488,9 @@ 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); @@ -431,6 +509,9 @@ 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 @@ -450,6 +531,9 @@ 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 @@ -469,6 +553,9 @@ 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 @@ -490,6 +577,9 @@ drop procedure i| # QQQ The "label" syntax is temporary, it will (hopefully) # change to the more common "L:" syntax soon. # +--disable_warnings +drop procedure if exists goto1| +--enable_warnings create procedure goto1() begin declare y int; @@ -509,6 +599,9 @@ call goto1()| drop procedure goto1| # With dummy handlers, just to test restore of contexts with jumps +--disable_warnings +drop procedure if exists goto2| +--enable_warnings create procedure goto2(a int) begin declare x int default 0; @@ -544,6 +637,9 @@ drop procedure goto2| delete from t1| # Check label visibility for some more cases. We don't call these. +--disable_warnings +drop procedure if exists goto3| +--enable_warnings create procedure goto3() begin label L1; @@ -553,6 +649,9 @@ begin end| drop procedure goto3| +--disable_warnings +drop procedure if exists goto4| +--enable_warnings create procedure goto4() begin begin @@ -564,6 +663,9 @@ begin end| drop procedure goto4| +--disable_warnings +drop procedure if exists goto5| +--enable_warnings create procedure goto5() begin begin @@ -575,6 +677,9 @@ begin end| drop procedure goto5| +--disable_warnings +drop procedure if exists goto6| +--enable_warnings create procedure goto6() begin label L1; @@ -604,6 +709,9 @@ drop procedure goto6| 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; @@ -612,6 +720,9 @@ end| call sel1()| drop procedure sel1| +--disable_warnings +drop procedure if exists sel2| +--enable_warnings create procedure sel2() begin select * from t1; @@ -624,6 +735,9 @@ delete from t1| delete from t2| # 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); @@ -638,6 +752,9 @@ 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); @@ -652,6 +769,9 @@ 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); @@ -673,6 +793,9 @@ 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; @@ -698,7 +821,9 @@ 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 create procedure into_outfile(x char(16), y int) begin insert into test.t1 values (x, y); @@ -712,6 +837,9 @@ system rm -f /tmp/spout| delete from t1| drop procedure into_outfile| +--disable_warnings +drop procedure if exists into_dumpfile| +--enable_warnings create procedure into_dumpfile(x char(16), y int) begin insert into test.t1 values (x, y); @@ -725,7 +853,9 @@ system rm -f /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); @@ -746,6 +876,9 @@ 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| @@ -753,24 +886,36 @@ 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; @@ -785,6 +930,9 @@ 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()| @@ -814,6 +962,9 @@ 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; @@ -833,6 +984,9 @@ 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; @@ -852,6 +1006,9 @@ 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; @@ -890,6 +1047,9 @@ drop table if exists t3| --enable_warnings 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; @@ -910,6 +1070,9 @@ drop procedure hndlr4| # # Cursors # +--disable_warnings +drop procedure if exists cur1| +--enable_warnings create procedure cur1() begin declare a char(16); @@ -939,6 +1102,9 @@ drop table if exists t3| --enable_warnings 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; @@ -977,6 +1143,9 @@ 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 @@ -994,6 +1163,9 @@ 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 @@ -1016,6 +1188,9 @@ 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; @@ -1064,6 +1239,9 @@ 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| @@ -1074,6 +1252,9 @@ 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; @@ -1100,7 +1281,10 @@ drop procedure rc| # # BUG#822 # -create procedure bug(a_id char(16), a_data int) +--disable_warnings +drop procedure if exists bug822| +--enable_warnings +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; @@ -1109,17 +1293,20 @@ begin end if; end| -call bug('foo', 42)| -call bug('foo', 42)| -call bug('bar', 666)| +call bug822('foo', 42)| +call bug822('foo', 42)| +call bug822('bar', 666)| select * from t1| delete from t1| -drop procedure bug| +drop procedure bug822| # # BUG#1495 # -create procedure bug() +--disable_warnings +drop procedure if exists bug1495| +--enable_warnings +create procedure bug1495() begin declare x int; @@ -1132,19 +1319,22 @@ begin end| insert into t1 values ('foo', 12)| -call bug()| +call bug1495()| delete from t1 where id='foo'| insert into t1 values ('bar', 7)| -call bug()| +call bug1495()| delete from t1 where id='bar'| select * from t1| delete from t1| -drop procedure bug| +drop procedure bug1495| # # BUG#1547 # -create procedure bug(s char(16)) +--disable_warnings +drop procedure if exists bug1547| +--enable_warnings +create procedure bug1547(s char(16)) begin declare x int; @@ -1157,11 +1347,11 @@ begin end| insert into t1 values ("foo", 12), ("bar", 7)| -call bug("foo")| -call bug("bar")| +call bug1547("foo")| +call bug1547("bar")| select * from t1| delete from t1| -drop procedure bug| +drop procedure bug1547| # # BUG#1656 @@ -1172,13 +1362,16 @@ drop table if exists t70| create table t70 (s1 int,s2 int)| insert into t70 values (1,2)| -create procedure bug(out p1 int, out p2 int) +--disable_warnings +drop procedure if exists bug1656| +--enable_warnings +create procedure bug1656(out p1 int, out p2 int) select * into p1, p1 from t70| -call bug(@1, @2)| +call bug1656(@1, @2)| select @1, @2| drop table t70| -drop procedure bug| +drop procedure bug1656| # # BUG#1862 @@ -1188,23 +1381,29 @@ drop table if exists t3| --enable_warnings create table t3(a int)| -create procedure bug() +--disable_warnings +drop procedure if exists bug1862| +--enable_warnings +create procedure bug1862() begin insert into t3 values(2); flush tables; end| -call bug()| +call bug1862()| # the second call caused a segmentation -call bug()| +call bug1862()| select * from t3| drop table t3| -drop procedure bug| +drop procedure bug1862| # # BUG#1874 # -create procedure bug() +--disable_warnings +drop procedure if exists bug1874| +--enable_warnings +create procedure bug1874() begin declare x int; declare y double; @@ -1219,16 +1418,19 @@ begin end| insert into t1 (data) values (3), (1), (5), (9), (4)| -call bug()| +call bug1874()| select * from t2| delete from t1| delete from t2| -drop procedure bug| +drop procedure bug1874| # # BUG#2260 # -create procedure bug() +--disable_warnings +drop procedure if exists bug2260| +--enable_warnings +create procedure bug2260() begin declare v1 int; declare c1 cursor for select data from t1; @@ -1240,28 +1442,40 @@ begin close c1; end| -call bug()| +call bug2260()| select @x2| -drop procedure bug| +drop procedure bug2260| # # BUG#2267 # +--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| +--enable_warnings create procedure bug2267_4() begin show create function fac; @@ -1282,7 +1496,10 @@ drop procedure bug2267_4| # # BUG#2227 # -create procedure bug(x int) +--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"; @@ -1290,13 +1507,16 @@ begin select 1.3, x, y, 42, z; end| -call bug(9)| -drop procedure bug| +call bug2227(9)| +drop procedure bug2227| # # BUG#2614 # -create procedure bug() +--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); @@ -1305,29 +1525,41 @@ begin end| --disable_warnings -call bug()| +call bug2614()| --enable_warnings -call bug()| +call bug2614()| drop table t3| -drop procedure bug| +drop procedure bug2614| # # BUG#2674 # -create function bug () returns int +--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 bug()| -drop function bug| +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()| @@ -1342,18 +1574,24 @@ drop procedure BuG3259_2| drop procedure BUG3259_3| # -# BUG##2772 +# BUG#2772 # -create function bug() returns char(10) character set latin2 +--disable_warnings +drop function if exists bug2772| +--enable_warnings +create function bug2772() returns char(10) character set latin2 return 'a'| -select bug()| -drop function bug| +select bug2772()| +drop function bug2772| # # BUG#2776 # -create procedure bug(out x int) +--disable_warnings +drop procedure if exists bug2776_1| +--enable_warnings +create procedure bug2776_1(out x int) begin declare v int; @@ -1361,7 +1599,10 @@ begin set x = v; end| -create procedure bug2(out x int) +--disable_warnings +drop procedure if exists bug2776_2| +--enable_warnings +create procedure bug2776_2(out x int) begin declare v int default 42; @@ -1370,12 +1611,12 @@ begin end| set @x = 1| -call bug(@x)| +call bug2776_1(@x)| select @x| -call bug2(@x)| +call bug2776_2(@x)| select @x| -drop procedure bug| -drop procedure bug2| +drop procedure bug2776_1| +drop procedure bug2776_2| # # BUG#2780 @@ -1387,7 +1628,10 @@ create table t3 (s1 smallint)| insert into t3 values (123456789012)| -create procedure bug() +--disable_warnings +drop procedure if exists bug2780| +--enable_warnings +create procedure bug2780() begin declare exit handler for sqlwarning set @x = 1; @@ -1396,11 +1640,11 @@ begin insert into t3 values (0); end| -call bug()| +call bug2780()| select @x| select * from t3| -drop procedure bug| +drop procedure bug2780| drop table t3| # @@ -1411,6 +1655,9 @@ 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 @@ -1471,6 +1718,9 @@ 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 @@ -1479,6 +1729,9 @@ begin 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 @@ -1502,6 +1755,9 @@ 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 @@ -1546,6 +1802,9 @@ create table t4 ( 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| @@ -1578,6 +1837,9 @@ insert into t3 (title,body) values ('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)| @@ -1590,6 +1852,9 @@ drop table t3| # # BUG#3863 # +--disable_warnings +drop procedure if exists bug3863| +--enable_warnings create procedure bug3863() begin set @a = 0; @@ -1617,6 +1882,9 @@ create table t3 ( 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 ) @@ -1634,6 +1902,9 @@ 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; @@ -1654,20 +1925,32 @@ 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 |$ @@ -1686,6 +1969,9 @@ 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, '!')| @@ -1695,6 +1981,9 @@ drop function bug3132| # # BUG#3843 # +--disable_warnings +drop procedure if exists bug3843| +--enable_warnings create procedure bug3843() analyze table t1| @@ -1714,6 +2003,9 @@ drop table if exists t3| 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; @@ -1733,6 +2025,9 @@ drop table if exists t3| 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; @@ -1742,6 +2037,9 @@ begin call bug4579_2(); end| +--disable_warnings +drop procedure if exists bug4579_2| +--enable_warnings create procedure bug4579_2 () begin end| @@ -1765,6 +2063,9 @@ drop table if exists t3| 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; @@ -1795,6 +2096,9 @@ drop table if exists t3| 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| @@ -1818,6 +2122,9 @@ drop table t3| # 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'; @@ -1849,6 +2156,9 @@ call bug4902()| drop procedure bug4902| # We need separate SP for SHOW PROCESSLIST since we want use replace_column +--disable_warnings +drop procedure if exists bug4902_2| +--enable_warnings create procedure bug4902_2() begin show processlist; @@ -1866,6 +2176,9 @@ drop procedure bug4902_2| drop table if exists t3| --enable_warnings +--disable_warnings +drop procedure if exists bug4904| +--enable_warnings create procedure bug4904() begin declare continue handler for sqlstate 'HY000' begin end; @@ -1880,6 +2193,9 @@ 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; @@ -1895,6 +2211,9 @@ drop table t3| # # BUG#336 # +--disable_warnings +drop procedure if exists bug336| +--enable_warnings create procedure bug336(out y int) begin declare x int; @@ -1911,6 +2230,9 @@ drop procedure bug336| # # BUG#3157 # +--disable_warnings +drop procedure if exists bug3157| +--enable_warnings create procedure bug3157() begin if exists(select * from t1) then @@ -1931,6 +2253,9 @@ 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| @@ -1947,6 +2272,9 @@ 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| @@ -1957,6 +2285,9 @@ 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 @@ -1975,6 +2306,9 @@ 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| @@ -1986,10 +2320,16 @@ 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); @@ -2010,6 +2350,9 @@ 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; @@ -2026,6 +2369,9 @@ drop function bug4487| --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; @@ -2048,6 +2394,9 @@ drop procedure bug4941| --disable_warnings drop procedure if exists bug3583| --enable_warnings +--disable_warnings +drop procedure if exists bug3583| +--enable_warnings create procedure bug3583() begin declare c int; @@ -2086,6 +2435,9 @@ drop procedure if exists bug4905| create table t3 (s1 int,primary key (s1))| +--disable_warnings +drop procedure if exists bug4905| +--enable_warnings create procedure bug4905() begin declare v int; @@ -2112,6 +2464,9 @@ drop table t3| 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 @@ -2131,6 +2486,9 @@ drop function bug6022| 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'; @@ -2162,6 +2520,9 @@ drop table if exists fac| --enable_warnings create table fac (n int unsigned not null primary key, f bigint unsigned)| +--disable_warnings +drop procedure if exists ifac| +--enable_warnings create procedure ifac(n int unsigned) begin declare i int unsigned default 1; @@ -2210,6 +2571,9 @@ insert into primes values (35, 157), (36, 163), (37, 167), (38, 173), (39, 179), (40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| +--disable_warnings +drop procedure if exists opp| +--enable_warnings create procedure opp(n bigint unsigned, out pp bool) begin declare r double; @@ -2240,6 +2604,9 @@ begin end loop; end| +--disable_warnings +drop procedure if exists ip| +--enable_warnings create procedure ip(m int unsigned) begin declare p bigint unsigned; @@ -2295,6 +2662,9 @@ 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. +--disable_warnings +drop procedure if exists fib| +--enable_warnings create procedure fib(n int unsigned) begin if n > 0 then @@ -2323,6 +2693,9 @@ drop procedure fib| # Comment & suid # +--disable_warnings +drop procedure if exists bar| +--enable_warnings create procedure bar(x char(16), y int) comment "111111111111" sql security invoker insert into test.t1 values (x, y)| @@ -2342,6 +2715,9 @@ drop table t2; # # rexecution # +--disable_warnings +drop procedure if exists p1; +--enable_warnings create procedure p1 () select (select s1 from t1) from t1; create table t1 (s1 int); call p1(); @@ -2353,6 +2729,9 @@ drop table t1; # # backticks # +--disable_warnings +drop function if exists foo; +--enable_warnings create function `foo` () returns int return 5; select `foo` (); drop function `foo`; |