# # Stored PROCEDURE error tests # --disable_warnings drop table if exists t1, t2; --enable_warnings # 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. # 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"| --disable_warnings drop table if exists t3| --enable_warnings create table t3 ( x int )| insert into t3 values (2), (3)| create procedure bad_into(out param int) select x from t3 into param| --error 1172 call bad_into(@x)| drop procedure bad_into| drop table t3| create procedure proc1() set @x = 42| create function func1() returns int return 42| # Can't create recursively --error 1303 create procedure foo() create procedure bar() set @x=3| --error 1303 create procedure foo() create function bar() returns double return 2.3| # Already exists --error 1304 create procedure proc1() set @x = 42| --error 1304 create function func1() returns int return 42| drop procedure proc1| drop function func1| # Does not exist --error 1305 alter procedure foo| --error 1305 alter function foo| --error 1305 drop procedure foo| --error 1305 drop function foo| --error 1305 call foo()| drop procedure if exists foo| --error 1305 show create procedure foo| --error 1305 show create function foo| # LEAVE/ITERATE with no match --error 1308 create procedure foo() foo: loop leave bar; end loop| --error 1308 create procedure foo() foo: loop iterate bar; end loop| --error 1308 create procedure foo() foo: begin iterate foo; end| # Redefining label --error 1309 create procedure foo() foo: loop foo: loop set @x=2; end loop foo; end loop foo| # End label mismatch --error 1310 create procedure foo() foo: loop set @x=2; end loop bar| # RETURN in FUNCTION only --error 1313 create procedure foo() return 42| # Wrong number of arguments create procedure p(x int) set @x = x| create function f(x int) returns int return x+42| --error 1318 call p()| --error 1318 call p(1, 2)| --error 1318 select f()| --error 1318 select f(1, 2)| drop procedure p| drop function f| --error 1319 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 1319 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 1320 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 1321 select f(10)| drop function f| --error 1322 create procedure p() begin declare c cursor for insert into test.t1 values ("foo", 42); open c; close c; end| --error 1323 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 1324 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 1325 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 1326 call p()| drop procedure p| --error 1305 alter procedure bar3 sql security invoker| 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 1327 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 1328 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 1328 call p()| drop procedure p| --error 1330 create procedure p(in x int, x char(10)) begin end| --error 1330 create function p(x int, x char(10)) begin end| --error 1331 create procedure p() begin declare x float; declare x int; end| --error 1332 create procedure p() begin declare c condition for 1064; declare c condition for 1065; end| --error 1333 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 ER_SP_BADSTATEMENT create procedure u() use sptmp| # Enforced standard order of declarations --error 1337 create procedure p() begin declare c cursor for select * from t1; declare x int; end| --error 1337 create procedure p() begin declare x int; declare continue handler for sqlstate '42S99' set x = 1; declare foo condition for sqlstate '42S99'; end| --error 1338 create procedure p() begin declare x int; declare continue handler for sqlstate '42S99' set x = 1; declare c cursor for select * from t1; end| # Check in and inout arguments. --disable_warnings drop procedure if exists p| --enable_warnings create procedure p(in x int, inout y int, out z int) begin set y = x+y; set z = x+y; end| set @tmp_x = 42| set @tmp_y = 3| set @tmp_z = 0| # For reference: this is ok call p(@tmp_x, @tmp_y, @tmp_z)| select @tmp_x, @tmp_y, @tmp_z| --error ER_SP_NOT_VAR_ARG call p(42, 43, @tmp_z)| --error ER_SP_NOT_VAR_ARG call p(42, @tmp_y, 43)| drop procedure p| # # Let us test that we can access mysql.proc table for routines # definitions lookup without locking it explicitly. # create procedure p() begin end| lock table t1 read| # This should succeed call p()| unlock tables| drop procedure p| # Let us check restrictions which this ability puts on mysql.proc locking. --error ER_WRONG_LOCK_OF_SYSTEM_TABLE lock tables t1 read, mysql.proc write| --error ER_WRONG_LOCK_OF_SYSTEM_TABLE lock tables mysql.proc write, mysql.user write| # Locking for read should be OK lock tables t1 read, mysql.proc read| unlock tables| # You also should be able lock only mysql.proc for write lock tables mysql.proc write| unlock tables| # # Check that in functions we don't allow to update tables which # are used by statements which invoke these functions. # --disable_warnings drop function if exists f1| --enable_warnings create function f1(i int) returns int begin insert into t1 (val) values (i); return 0; end| --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG select val, f1(val) from t1| # Table alias should not matter --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG select val, f1(val) from t1 as tab| select * from t1| --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG update t1 set val= f1(val)| select * from t1| # But this should be OK select f1(17)| select * from t1| # Cleanup delete from t1 where val= 17| drop function f1| # # 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 1327 select 1 into a| # # 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 t1; fetch c1 into v1; end| --error 1326 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()| truncate table 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 1339 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 1339 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| # # BUG#3339 # --error 1049 create procedure nodb.bug3339() begin end| # # BUG#2653 # create procedure bug2653_1(a int, out b int) set b = aa| create procedure bug2653_2(a int, out b int) begin if aa < 0 then set b = - a; else set b = a; end if; end| --error 1054 call bug2653_1(1, @b)| --error 1054 call bug2653_2(2, @b)| drop procedure bug2653_1| drop procedure bug2653_2| # # BUG#4344 # --error 1357 create procedure bug4344() drop procedure bug4344| --error 1357 create procedure bug4344() drop function bug4344| # # BUG#3294: Stored procedure crash if table dropped before use # (Actually, when an error occurs within an error handler.) --disable_warnings drop procedure if exists bug3294| --enable_warnings create procedure bug3294() begin declare continue handler for sqlexception drop table t5; drop table t5; drop table t5; end| create table t5 (x int)| --error 1051 call bug3294()| drop procedure bug3294| # # BUG#876: Stored Procedures: Invalid SQLSTATE is allowed in # a DECLARE ? HANDLER FOR stmt. # --disable_warnings drop procedure if exists bug8776_1| drop procedure if exists bug8776_2| drop procedure if exists bug8776_3| drop procedure if exists bug8776_4| --enable_warnings --error ER_SP_BAD_SQLSTATE create procedure bug8776_1() begin declare continue handler for sqlstate '42S0200test' begin end; begin end; end| --error ER_SP_BAD_SQLSTATE create procedure bug8776_2() begin declare continue handler for sqlstate '4200' begin end; begin end; end| --error ER_SP_BAD_SQLSTATE create procedure bug8776_3() begin declare continue handler for sqlstate '420000' begin end; begin end; end| --error ER_SP_BAD_SQLSTATE create procedure bug8776_4() begin declare continue handler for sqlstate '42x00' begin end; begin end; end| # # BUG#6600: Stored procedure crash after repeated calls with check table # --error ER_SP_BADSTATEMENT create procedure bug6600() check table t1| # Check these two as well, while we're at it. (Although it isn't really # related to the bug report, but to the fix.) --error ER_SP_BADSTATEMENT create procedure bug6600() lock table t1 read| --error ER_SP_BADSTATEMENT create procedure bug6600() unlock table t1| # # BUG#9566: explicit LOCK TABLE and store procedures result in illegal state # # We should not think that mysql.proc table does not exist if we are unable # to open it under LOCK TABLE or in prelocked mode. # --disable_warnings drop procedure if exists bug9566| --enable_warnings create procedure bug9566() begin select * from t1; end| lock table t1 read| # This should fail since we forgot to lock mysql.proc for writing # explicitly, and we can't open mysql.proc for _writing_ if there # are locked tables. --error 1100 alter procedure bug9566 comment 'Some comment'| unlock tables| # This should succeed drop procedure bug9566| # # BUG#7299: Stored procedures: exception handler catches not-found conditions # --disable_warnings drop procedure if exists bug7299| --enable_warnings create procedure bug7299() begin declare v int; declare c cursor for select val from t1; declare exit handler for sqlexception select 'Error!'; open c; fetch c into v; end| truncate table t1| --error ER_SP_FETCH_NO_DATA call bug7299()| drop procedure bug7299| # # BUG#9073: Able to declare two handlers for same condition in same scope # --error ER_SP_DUP_HANDLER create procedure bug9073() begin declare continue handler for sqlexception select 1; declare continue handler for sqlexception select 2; end| --error ER_SP_DUP_HANDLER create procedure bug9073() begin declare condname1 condition for 1234; declare continue handler for condname1 select 1; declare exit handler for condname1 select 2; end| --error ER_SP_DUP_HANDLER create procedure bug9073() begin declare condname1 condition for sqlstate '42000'; declare condname2 condition for sqlstate '42000'; declare exit handler for condname1 select 1; declare continue handler for condname2 select 2; end| --error ER_SP_DUP_HANDLER create procedure bug9073() begin declare condname1 condition for sqlstate '42000'; declare exit handler for condname1 select 1; declare exit handler for sqlstate '42000' select 2; end| # This should still work. --disable_warnings drop procedure if exists bug9073| --enable_warnings create procedure bug9073() begin declare condname1 condition for sqlstate '42000'; declare continue handler for condname1 select 1; begin declare exit handler for sqlstate '42000' select 2; begin declare continue handler for sqlstate '42000' select 3; end; end; end| drop procedure bug9073| # # BUG#7047: Stored procedure crash if alter procedure # --error ER_SP_NO_DROP_SP create procedure bug7047() alter procedure bug7047| --error ER_SP_NO_DROP_SP create function bug7047() returns int begin alter function bug7047; return 0; end| # # BUG#8408: Stored procedure crash if function contains SHOW # BUG#9058: Stored Procedures: Crash if function included SELECT # # Some things are caught when parsing --error ER_SP_NO_RETSET create function bug8408() returns int begin select * from t1; return 0; end| --error ER_SP_NO_RETSET create function bug8408() returns int begin show warnings; return 0; end| --error ER_SP_NO_RETSET create function bug8408(a int) returns int begin declare b int; select b; return b; end| --disable_warnings drop function if exists bug8408_f| drop procedure if exists bug8408_p| --enable_warnings # Some things must be caught at invokation time create function bug8408_f() returns int begin call bug8408_p(); return 0; end| create procedure bug8408_p() select * from t1| call bug8408_p()| --error ER_SP_NO_RETSET select bug8408_f()| drop procedure bug8408_p| drop function bug8408_f| # But this is ok create function bug8408() returns int begin declare n int default 0; select count(*) into n from t1; return n; end| insert into t1 value (2, 2.7), (3, 3.14), (7, 7.0)| select *,bug8408() from t1| drop function bug8408| truncate table t1| # # BUG#10537: Server crashes while loading data file into table through # procedure. # Disable load until it's PS and SP safe --disable_warnings drop procedure if exists bug10537| --enable_warnings --error ER_SP_BADSTATEMENT create procedure bug10537() load data local infile '/tmp/somefile' into table t1| # # BUG#8409: Stored procedure crash if function contains FLUSH # --disable_warnings drop function if exists bug8409| --enable_warnings --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush tables; return 5; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin reset query cache; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin reset master; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin reset slave; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush hosts; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush privileges; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush tables with read lock; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush tables; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush logs; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush status; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush slave; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush master; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush des_key_file; return 1; end| --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG create function bug8409() returns int begin flush user_resources; return 1; end| # # BUG#9529: Stored Procedures: No Warning on truncation of procedure name # during creation. # BUG#17015: Routine name truncation not an error # When we started using utf8 for mysql.proc, this limit appeared # to be higher, but in reality the names were truncated. --error ER_TOO_LONG_IDENT create procedure bug9529_901234567890123456789012345678901234567890123456789012345() begin end| --disable_warnings drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234| --enable_warnings # Check the upper limit, just to make sure. create procedure bug17015_0123456789012345678901234567890123456789012345678901234() begin end| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show procedure status like 'bug17015%'| drop procedure bug17015_0123456789012345678901234567890123456789012345678901234| # # BUG#10969: Stored procedures: crash if default() function # --disable_warnings drop procedure if exists bug10969| --enable_warnings --error ER_WRONG_COLUMN_NAME create procedure bug10969() begin declare s1 int default 0; select default(s1) from t30; end| # This should work create procedure bug10969() begin declare s1 int default 0; select default(t30.s1) from t30; end| drop procedure bug10969| drop table t1| delimiter ;| # BUG#9814: Closing a cursor that is not open create table t1(f1 int); create table t2(f1 int); delimiter |; CREATE PROCEDURE SP001() P1: BEGIN DECLARE ENDTABLE INT DEFAULT 0; DECLARE TEMP_NUM INT; DECLARE TEMP_SUM INT; DECLARE C1 CURSOR FOR SELECT F1 FROM t1; DECLARE C2 CURSOR FOR SELECT F1 FROM t2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET ENDTABLE = 1; SET ENDTABLE=0; SET TEMP_SUM=0; SET TEMP_NUM=0; OPEN C1; FETCH C1 INTO TEMP_NUM; WHILE ENDTABLE = 0 DO SET TEMP_SUM=TEMP_NUM+TEMP_SUM; FETCH C1 INTO TEMP_NUM; END WHILE; SELECT TEMP_SUM; CLOSE C1; CLOSE C1; SELECT 'end of proc'; END P1| delimiter ;| --error 1326 call SP001(); drop procedure SP001; drop table t1, t2; # Bug #11394 "Recursion in SP crash server" and bug #11600 "Stored # procedures: crash with function calling itself". # We have to disable recursion since in many cases LEX and many # Item's can't be used in reentrant way nowdays. delimiter |; --disable_warnings drop function if exists bug11394| drop function if exists bug11394_1| drop function if exists bug11394_2| drop procedure if exists bug11394| --enable_warnings create function bug11394(i int) returns int begin if i <= 0 then return 0; else return (i in (100, 200, bug11394(i-1), 400)); end if; end| # If we allow recursive functions without additional modifications # this will crash server since Item for "IN" is not reenterable. --error 1424 select bug11394(2)| drop function bug11394| create function bug11394_1(i int) returns int begin if i <= 0 then return 0; else return (select bug11394_1(i-1)); end if; end| # The following statement will crash because some LEX members responsible # for selects cannot be used in reentrant fashion. --error 1424 select bug11394_1(2)| drop function bug11394_1| # Note that the following should be allowed since it does not contains # recursion create function bug11394_2(i int) returns int return i| select bug11394_2(bug11394_2(10))| drop function bug11394_2| create procedure bug11394(i int, j int) begin if i > 0 then call bug11394(i - 1,(select 1)); end if; end| --error ER_SP_RECURSION_LIMIT call bug11394(2, 1)| set @@max_sp_recursion_depth=10| call bug11394(2, 1)| set @@max_sp_recursion_depth=default| drop procedure bug11394| delimiter ;| # # BUG 12490 (Packets out of order if calling HELP CONTENTS from Stored Procedure) # --error 1314 CREATE PROCEDURE BUG_12490() HELP CONTENTS; --error 1314 CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS; CREATE TABLE t_bug_12490(a int); --error 1314 CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS; DROP TABLE t_bug_12490; # # Bug#11834 "Re-execution of prepared statement with dropped function # crashes server". Also tests handling of prepared stmts which use # stored functions but does not require prelocking. # --disable_warnings drop function if exists bug11834_1; drop function if exists bug11834_2; --enable_warnings create function bug11834_1() returns int return 10; create function bug11834_2() returns int return bug11834_1(); prepare stmt from "select bug11834_2()"; execute stmt; # Re-execution of statement should not crash server. execute stmt; drop function bug11834_1; # Attempt to execute statement should return proper error and # should not crash server. # NOTE! The error we get from the below query indicates that the sp bug11834_2 # does not exist(this is wrong but can be accepted) # This behaviour has been reported as bug#21294 --error ER_SP_DOES_NOT_EXIST execute stmt; deallocate prepare stmt; drop function bug11834_2; # # Bug#12953 "Stored procedures: crash if OPTIMIZE TABLE in function" # delimiter |; --disable_warnings DROP FUNCTION IF EXISTS bug12953| --enable_warnings --error ER_SP_NO_RETSET CREATE FUNCTION bug12953() RETURNS INT BEGIN OPTIMIZE TABLE t1; RETURN 1; END| delimiter ;| # # Bug##12995 "Inside function "Table 't4' was not locked with LOCK TABLES" # delimiter |; --disable_warnings DROP FUNCTION IF EXISTS bug12995| --enable_warnings --error ER_SP_BADSTATEMENT CREATE FUNCTION bug12995() RETURNS INT BEGIN HANDLER t1 OPEN; RETURN 1; END| --error ER_SP_BADSTATEMENT CREATE FUNCTION bug12995() RETURNS INT BEGIN HANDLER t1 READ FIRST; RETURN 1; END| --error ER_SP_BADSTATEMENT CREATE FUNCTION bug12995() RETURNS INT BEGIN HANDLER t1 CLOSE; RETURN 1; END| --error 1305 SELECT bug12995()| delimiter ;| # # BUG#12712: SET AUTOCOMMIT should fail within SP/functions/triggers # --disable_warnings drop procedure if exists bug12712; drop function if exists bug12712; --enable_warnings # Can... create procedure bug12712() set session autocommit = 0; select @@autocommit; set @au = @@autocommit; call bug12712(); select @@autocommit; set session autocommit = @au; delimiter |; create function bug12712() returns int begin call bug12712(); return 0; end| # Can't... --error ER_SP_CANT_SET_AUTOCOMMIT set @x = bug12712()| drop procedure bug12712| drop function bug12712| --error ER_SP_CANT_SET_AUTOCOMMIT create function bug12712() returns int begin set session autocommit = 0; return 0; end| --error ER_SP_CANT_SET_AUTOCOMMIT create function bug12712() returns int begin set @@autocommit = 0; return 0; end| --error ER_SP_CANT_SET_AUTOCOMMIT create function bug12712() returns int begin set local autocommit = 0; return 0; end| delimiter ;| --error ER_SP_CANT_SET_AUTOCOMMIT create trigger bug12712 before insert on t1 for each row set session autocommit = 0; # # BUG#9367: Stored procedures: client hang after "show warnings" # --disable_parsing --disable_warnings drop procedure if exists bug9367; --enable_warnings create table t1 (s1 int); select s1 from t1; delimiter |; create procedure bug9367() begin declare v int; declare c cursor for select s1 from t1; open c; show warnings; fetch c into v; select v; end| delimiter ;| call bug9367(); drop procedure bug9367; drop table t1; --enable_parsing # # BUG#13510: Setting password local variable changes current password # delimiter |; --disable_warnings drop procedure if exists bug13510_1| drop procedure if exists bug13510_2| drop procedure if exists bug13510_3| drop procedure if exists bug13510_4| --enable_warnings --error ER_SP_BAD_VAR_SHADOW create procedure bug13510_1() begin declare password varchar(10); set password = 'foo1'; select password; end| # Check that an error message is sent --error ER_PARSE_ERROR set names='foo2'| --error ER_SP_BAD_VAR_SHADOW create procedure bug13510_2() begin declare names varchar(10); set names = 'foo2'; select names; end| create procedure bug13510_3() begin declare password varchar(10); set `password` = 'foo3'; select password; end| create procedure bug13510_4() begin declare names varchar(10); set `names` = 'foo4'; select names; end| call bug13510_3()| call bug13510_4()| drop procedure bug13510_3| drop procedure bug13510_4| # # Test that statements which implicitly commit transaction are prohibited # in stored function and triggers. Attempt to create function or trigger # containing such statement should produce error (includes test for # bug #13627). # --disable_warnings drop function if exists bug_13627_f| --enable_warnings CREATE TABLE t1 (a int)| -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END | -- error ER_SP_BADSTATEMENT CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN load table t1 from master; END | -- error ER_SP_BADSTATEMENT CREATE FUNCTION bug_13627_f() returns int BEGIN load table t1 from master; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a); END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column b int; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column b int; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END | -- error ER_SP_BADSTATEMENT CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END | -- error ER_SP_BADSTATEMENT CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END | -- error ER_SP_BADSTATEMENT CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END | -- error ER_SP_BADSTATEMENT CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END | -- error ER_SP_NO_RECURSIVE_CREATE CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create trigger tr2 before insert on t1 for each row do select 1; END | -- error ER_SP_NO_RECURSIVE_CREATE CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1 for each row do select 1; return 1; END | -- error ER_SP_NO_DROP_SP CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END | -- error ER_SP_NO_DROP_SP CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END | -- error ER_SP_NO_RECURSIVE_CREATE CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns int return 1; END | -- error ER_SP_NO_RECURSIVE_CREATE CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return 1; return 1; END | CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN CREATE TEMPORARY TABLE t2 (a int); DROP TEMPORARY TABLE t2; END | CREATE FUNCTION bug_13627_f() returns int BEGIN CREATE TEMPORARY TABLE t2 (a int); DROP TEMPORARY TABLE t2; return 1; END | drop table t1| drop function bug_13627_f| delimiter ;| # BUG#12329: "Bogus error msg when executing PS with stored procedure after # SP was re-created". See also test for related bug#13399 in trigger.test drop function if exists bug12329; --enable_warnings create table t1 as select 1 a; create table t2 as select 1 a; create function bug12329() returns int return (select a from t1); prepare stmt1 from 'select bug12329()'; execute stmt1; drop function bug12329; create function bug12329() returns int return (select a+100 from t2); select bug12329(); # Until we implement proper mechanism for invalidation of PS/SP when table # or SP's are changed the following statement will fail with 'Table ... was # not locked' error (this mechanism should be based on the new TDC). --error 1100 execute stmt1; deallocate prepare stmt1; drop function bug12329; drop table t1, t2; # # Bug#13514 "server crash when create a stored procedure before choose a # database" and # Bug#13587 "Server crash when SP is created without database # selected" # create database mysqltest1; use mysqltest1; drop database mysqltest1; --error ER_NO_DB_ERROR create function f1() returns int return 1; delimiter |; --error ER_NO_DB_ERROR create procedure p1(out param1 int) begin select count(*) into param1 from t3; end| delimiter ;| use test; # # BUG#13037: undefined variable in IF cause erroneous error-message # --disable_warnings DROP PROCEDURE IF EXISTS bug13037_p1; DROP PROCEDURE IF EXISTS bug13037_p2; DROP PROCEDURE IF EXISTS bug13037_p3; --enable_warnings delimiter |; CREATE PROCEDURE bug13037_p1() BEGIN IF bug13037_foo THEN SELECT 1; END IF; END| CREATE PROCEDURE bug13037_p2() BEGIN SET @bug13037_foo = bug13037_bar; END| CREATE PROCEDURE bug13037_p3() BEGIN SELECT bug13037_foo; END| delimiter ;| --echo --error 1054 CALL bug13037_p1(); --error 1054 CALL bug13037_p2(); --error 1054 CALL bug13037_p3(); --error 1054 CALL bug13037_p1(); --error 1054 CALL bug13037_p2(); --error 1054 CALL bug13037_p3(); DROP PROCEDURE bug13037_p1; DROP PROCEDURE bug13037_p2; DROP PROCEDURE bug13037_p3; # # Bug#14569 "editing a stored procedure kills mysqld-nt" # create database mysqltest1; create database mysqltest2; use mysqltest1; drop database mysqltest1; create procedure mysqltest2.p1() select version(); --error ER_NO_DB_ERROR create procedure p2() select version(); use mysqltest2; --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show procedure status; drop database mysqltest2; use test; # # Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server" # delimiter |; --disable_warnings DROP FUNCTION IF EXISTS bug13012| --enable_warnings --error ER_SP_NO_RETSET CREATE FUNCTION bug13012() RETURNS INT BEGIN REPAIR TABLE t1; RETURN 1; END| --error ER_SP_NO_RETSET CREATE FUNCTION bug13012() RETURNS INT BEGIN BACKUP TABLE t1 TO '/tmp'; RETURN 1; END| --error ER_SP_NO_RETSET CREATE FUNCTION bug13012() RETURNS INT BEGIN RESTORE TABLE t1 FROM '/tmp'; RETURN 1; END| create table t1 (a int)| CREATE PROCEDURE bug13012_1() REPAIR TABLE t1| CREATE FUNCTION bug13012_2() RETURNS INT BEGIN CALL bug13012_1(); RETURN 1; END| --error ER_SP_NO_RETSET SELECT bug13012_2()| drop table t1| drop procedure bug13012_1| drop function bug13012_2| delimiter ;| # # BUG#11555 "Stored procedures: current SP tables locking make # impossible view security". We should not expose names of tables # which are implicitly used by view (via stored routines/triggers). # # Note that SQL standard assumes that you simply won't be able drop table # and leave some objects (routines/views/triggers) which were depending on # it. Such objects should be dropped in advance (by default) or will be # dropped simultaneously with table (DROP TABLE with CASCADE clause). # So these tests probably should go away once we will implement standard # behavior. --disable_warnings drop function if exists bug11555_1; drop function if exists bug11555_2; drop view if exists v1, v2, v3, v4; --enable_warnings create function bug11555_1() returns int return (select max(i) from t1); create function bug11555_2() returns int return bug11555_1(); # It is OK to report name of implicitly used table which is missing # when we create view. --error ER_NO_SUCH_TABLE create view v1 as select bug11555_1(); --error ER_NO_SUCH_TABLE create view v2 as select bug11555_2(); # But we should hide name of missing implicitly used table when we use view create table t1 (i int); create view v1 as select bug11555_1(); create view v2 as select bug11555_2(); create view v3 as select * from v1; drop table t1; --error ER_VIEW_INVALID select * from v1; --error ER_VIEW_INVALID select * from v2; --error ER_VIEW_INVALID select * from v3; # Note that creation of view which depends on broken view is yet # another form of view usage. --error ER_VIEW_INVALID create view v4 as select * from v1; drop view v1, v2, v3; # We also should hide details about broken triggers which are # invoked for view. drop function bug11555_1; drop function bug11555_2; create table t1 (i int); create table t2 (i int); create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i); create view v1 as select * from t1; drop table t2; --error ER_VIEW_INVALID insert into v1 values (1); drop trigger t1_ai; create function bug11555_1() returns int return (select max(i) from t2); create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1(); --error ER_VIEW_INVALID insert into v1 values (2); drop function bug11555_1; drop table t1; drop view v1; # # BUG#15658: Server crashes after creating function as empty string # --disable_warnings drop procedure if exists ` bug15658`; --enable_warnings --error ER_SP_WRONG_NAME create procedure ``() select 1; --error ER_SP_WRONG_NAME create procedure ` `() select 1; --error ER_SP_WRONG_NAME create procedure `bug15658 `() select 1; --error ER_WRONG_DB_NAME create procedure ``.bug15658() select 1; --error ER_WRONG_DB_NAME create procedure `x `.bug15658() select 1; # This should work create procedure ` bug15658`() select 1; call ` bug15658`(); --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show procedure status; drop procedure ` bug15658`; # # BUG#14270: Stored procedures: crash if load index # --disable_warnings drop function if exists bug14270; drop table if exists t1; --enable_warnings create table t1 (s1 int primary key); delimiter |; --error ER_SP_NO_RETSET create function bug14270() returns int begin load index into cache t1; return 1; end| --error ER_SP_NO_RETSET create function bug14270() returns int begin cache index t1 key (`primary`) in keycache1; return 1; end| delimiter ;| drop table t1; # # BUG#15091: Sp Returns Unknown error in order clause....and # there is no order by clause # --disable_warnings drop procedure if exists bug15091; --enable_warnings delimiter |; create procedure bug15091() begin declare selectstr varchar(6000) default ' '; declare conditionstr varchar(5000) default ''; set selectstr = concat(selectstr, ' and ', c.operatorid, 'in (',conditionstr, ')'); end| delimiter ;| # The error message used to be: # ERROR 1109 (42S02): Unknown table 'c' in order clause # but is now rephrased to something less misleading: # ERROR 1109 (42S02): Unknown table 'c' in field list --error ER_UNKNOWN_TABLE call bug15091(); drop procedure bug15091; # # BUG#16896: Stored function: unused AGGREGATE-clause in CREATE FUNCTION # --disable_warnings drop function if exists bug16896; --enable_warnings --error ER_SP_NO_AGGREGATE create aggregate function bug16896() returns int return 1; # # # BUG#14702: misleading error message when syntax error in CREATE # PROCEDURE # # Misleading error message was given when IF NOT EXISTS was used in # CREATE PROCEDURE. # --disable_warnings DROP PROCEDURE IF EXISTS bug14702; --enable_warnings --error ER_PARSE_ERROR CREATE IF NOT EXISTS PROCEDURE bug14702() BEGIN END; --error ER_PARSE_ERROR CREATE PROCEDURE IF NOT EXISTS bug14702() BEGIN END; # # BUG#20953: create proc with a create view that uses local # vars/params should fail to create # # See test case for what syntax is forbidden in a view. # --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 (i INT); # We do not have to drop this procedure and view because they won't be # created. --error ER_VIEW_SELECT_CLAUSE CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a; --error ER_VIEW_SELECT_CLAUSE CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file"; --error ER_VIEW_SELECT_CLAUSE CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file"; --error ER_VIEW_SELECT_CLAUSE CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE(); --error ER_VIEW_SELECT_DERIVED CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1; --error ER_VIEW_SELECT_VARIABLE CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i; delimiter |; --error ER_VIEW_SELECT_VARIABLE CREATE PROCEDURE bug20953() BEGIN DECLARE i INT; CREATE VIEW v AS SELECT i; END | delimiter ;| --error ER_VIEW_SELECT_VARIABLE PREPARE stmt FROM "CREATE VIEW v AS SELECT ?"; DROP TABLE t1; # # End of 5.0 tests # --echo End of 5.0 tests # # Bug#16164 "Easter egg": check that SHOW AUTHORS is disabled in # stored functions/triggers # --disable_warnings drop function if exists bug16164; --enable_warnings delimiter |; --error ER_SP_NO_RETSET create function bug16164() returns int begin show authors; return 42; end| delimiter ;| # # BUG#20701: BINARY keyword should be forbidden in stored routines # --disable_warnings drop function if exists bug20701; --enable_warnings # # This was disabled in 5.1.12. See bug #20701 # When collation support in SP is implemented, then this test should # be removed. # --error ER_NOT_SUPPORTED_YET create function bug20701() returns varchar(25) binary return "test"; create function bug20701() returns varchar(25) return "test"; drop function bug20701; --echo End of 5.1 tests # # BUG#NNNN: New bug synopsis # #--disable_warnings #drop procedure if exists bugNNNN; #drop function if exists bugNNNN; #--enable_warnings #create procedure bugNNNN... #create function bugNNNN...