# # Stored PROCEDURE error tests # # Make sure we don't have any procedures left. delete from mysql.proc; delimiter |; # This should give three syntax errors (sometimes crashed; bug #643) # (Unfortunately, this is not a 100% test, on some platforms this # passed despite the bug.) --error 1064 create procedure syntaxerror(t int)| --error 1064 create procedure syntaxerror(t int)| --error 1064 create procedure syntaxerror(t int)| # Check that we get the right error, i.e. UDF declaration parses correctly, # but foo.so doesn't exist. # QQ This generates an error message containing a misleading errno which # might vary between systems (it usually doesn't have anything to do with # the actual failing dlopen()). #--error 1126 #create function foo returns real soname "foo.so"| create procedure proc1() set @x = 42| create function func1() returns int return 42| # Can't create recursively --error 1295 create procedure foo() create procedure bar() set @x=3| --error 1295 create procedure foo() create function bar() returns double return 2.3| # Already exists --error 1296 create procedure proc1() set @x = 42| --error 1296 create function func1() returns int return 42| drop procedure proc1| drop function func1| # Does not exist --error 1297 alter procedure foo| --error 1297 alter function foo| --error 1297 drop procedure foo| --error 1297 drop function foo| --error 1297 call foo()| drop procedure if exists foo| --error 1297 show create procedure foo| # LEAVE/ITERATE with no match --error 1300 create procedure foo() foo: loop leave bar; end loop| --error 1300 create procedure foo() foo: loop iterate bar; end loop| --error 1300 create procedure foo() foo: begin iterate foo; end| # Redefining label --error 1301 create procedure foo() foo: loop foo: loop set @x=2; end loop foo; end loop foo| # End label mismatch --error 1302 create procedure foo() foo: loop set @x=2; end loop bar| # Referring to undef variable create procedure foo(out x int) begin declare y int; set x = y; end| drop procedure foo| # RETURN in FUNCTION only --error 1305 create procedure foo() return 42| # Doesn't allow queries in FUNCTIONs (for now :-( ) --error 1306 create function foo() returns int begin declare x int; select max(c) into x from test.t; return x; end| # Wrong number of arguments create procedure p(x int) insert into test.t1 values (x)| create function f(x int) returns int return x+42| --error 1310 call p()| --error 1310 call p(1, 2)| --error 1310 select f()| --error 1310 select f(1, 2)| drop procedure p| drop function f| --error 1311 create procedure p(val int, out res int) begin declare x int default 0; declare continue handler for foo set x = 1; insert into test.t1 values (val); if (x) then set res = 0; else set res = 1; end if; end| --error 1311 create procedure p(val int, out res int) begin declare x int default 0; declare foo condition for 1146; declare continue handler for bar set x = 1; insert into test.t1 values (val); if (x) then set res = 0; else set res = 1; end if; end| --error 1312 create function f(val int) returns int begin declare x int; set x = val+3; end| create function f(val int) returns int begin declare x int; set x = val+3; if x < 4 then return x; end if; end| --error 1313 select f(10)| drop function f| --error 1314 create procedure p() begin declare c cursor for insert into test.t1 values ("foo", 42); open c; close c; end| --error 1315 create procedure p() begin declare x int; declare c cursor for select * into x from test.t limit 1; open c; close c; end| --error 1316 create procedure p() begin declare c cursor for select * from test.t; open cc; close c; end| --disable_warnings drop table if exists t1| --enable_warnings create table t1 (val int)| create procedure p() begin declare c cursor for select * from test.t1; open c; open c; close c; end| --error 1317 call p()| drop procedure p| create procedure p() begin declare c cursor for select * from test.t1; open c; close c; close c; end| --error 1318 call p()| drop procedure p| --error 1297 alter procedure bar3 sql security invoker| --error 1059 alter procedure bar3 name AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA| drop table t1| --disable_warnings drop table if exists t1| --enable_warnings create table t1 (val int, x float)| insert into t1 values (42, 3.1), (19, 1.2)| --error 1319 create procedure p() begin declare x int; declare c cursor for select * from t1; open c; fetch c into x, y; close c; end| create procedure p() begin declare x int; declare c cursor for select * from t1; open c; fetch c into x; close c; end| --error 1320 call p()| drop procedure p| create procedure p() begin declare x int; declare y float; declare z int; declare c cursor for select * from t1; open c; fetch c into x, y, z; close c; end| --error 1320 call p()| drop procedure p| --error 1322 create procedure p(in x int, x char(10)) begin end| --error 1322 create function p(x int, x char(10)) begin end| --error 1323 create procedure p() begin declare x float; declare x int; end| --error 1324 create procedure p() begin declare c condition for 1064; declare c condition for 1065; end| --error 1325 create procedure p() begin declare c cursor for select * from t1; declare c cursor for select field from t1; end| # USE is not allowed --error 1328 create procedure u() use sptmp| # Enforced standard order of declarations --error 1329 create procedure p() begin declare c cursor for select * from t1; declare x int; end| --error 1329 create procedure p() begin declare x int; declare continue handler for sqlstate '42S99' set x = 1; declare foo condition for sqlstate '42S99'; end| --error 1330 create procedure p() begin declare x int; declare continue handler for sqlstate '42S99' set x = 1; declare c cursor for select * from t1; end| # # BUG#1965 # create procedure bug1965() begin declare c cursor for select val from t1 order by valname; open c; close c; end| --error 1054 call bug1965()| drop procedure bug1965| # # BUG#1966 # --error 1319 select 1 into a| # # BUG#336 # --error 1327 create procedure bug336(id char(16)) begin declare x int; set x = (select sum(t.data) from test.t2 t); end| # # BUG#1654 # --error 1306 create function bug1654() returns int return (select sum(t.data) from test.t2 t)| # # BUG#1653 # --disable_warnings drop table if exists t3| --enable_warnings create table t3 (column_1_0 int)| create procedure bug1653() update t3 set column_1 = 0| --error 1054 call bug1653()| drop table t3| create table t3 (column_1 int)| call bug1653()| drop procedure bug1653| drop table t3| # # BUG#2259 # # Note: When this bug existed, it did not necessarily cause a crash # in all builds, but valgrind did give warnings. create procedure bug2259() begin declare v1 int; declare c1 cursor for select s1 from t10; fetch c1 into v1; end| --error 1318 call bug2259()| drop procedure bug2259| # # BUG#2272 # create procedure bug2272() begin declare v int; update t1 set v = 42; end| insert into t1 values (666, 51.3)| --error 1054 call bug2272()| delete from t1| drop procedure bug2272| # # BUG#2329 # create procedure bug2329_1() begin declare v int; insert into t1 (v) values (5); end| create procedure bug2329_2() begin declare v int; replace t1 set v = 5; end| --error 1054 call bug2329_1()| --error 1054 call bug2329_2()| drop procedure bug2329_1| drop procedure bug2329_2| # # BUG#3287 # create function bug3287() returns int begin declare v int default null; case when v is not null then return 1; end case; return 2; end| --error 1331 select bug3287()| drop function bug3287| create procedure bug3287(x int) case x when 0 then insert into test.t1 values (x, 0.1); when 1 then insert into test.t1 values (x, 1.1); end case| --error 1331 call bug3287(2)| drop procedure bug3287| # # BUG#3297 # --disable_warnings drop table if exists t3| --enable_warnings create table t3 (s1 int, primary key (s1))| insert into t3 values (5),(6)| create procedure bug3279(out y int) begin declare x int default 0; begin declare exit handler for sqlexception set x = x+1; insert into t3 values (5); end; if x < 2 then set x = x+1; insert into t3 values (6); end if; set y = x; end| set @x = 0| --error 1062 call bug3279(@x)| select @x| drop procedure bug3279| drop table t3| drop table t1| delimiter ;|