2 # Stored PROCEDURE error tests
6 drop table if exists t1, t2;
9 # Backup the mysql.proc table
10 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
11 eval SELECT * FROM mysql.proc INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/proc.txt';
13 # Make sure we don't have any procedures left.
14 delete from mysql.proc;
18 # This should give three syntax errors (sometimes crashed; bug #643)
19 # (Unfortunately, this is not a 100% test, on some platforms this
20 # passed despite the bug.)
22 create procedure syntaxerror(t int)|
24 create procedure syntaxerror(t int)|
26 create procedure syntaxerror(t int)|
28 # Check that we get the right error, i.e. UDF declaration parses correctly,
29 # but foo.so doesn't exist.
30 # This generates an error message containing a misleading errno which
31 # might vary between systems (it usually doesn't have anything to do with
32 # the actual failing dlopen()).
34 #create function foo returns real soname "foo.so"|
38 drop table if exists t3|
40 create table t3 ( x int )|
41 insert into t3 values (2), (3)|
43 create procedure bad_into(out param int)
44 select x from t3 into param|
49 drop procedure bad_into|
53 create procedure proc1()
56 create function func1() returns int
59 # Can't create recursively
61 create procedure foo()
62 create procedure bar() set @x=3|
64 create procedure foo()
65 create function bar() returns double return 2.3|
69 create procedure proc1()
72 create function func1() returns int
89 drop procedure if exists foo|
91 show create procedure foo|
93 show create function foo|
95 # LEAVE/ITERATE with no match
97 create procedure foo()
102 create procedure foo()
107 create procedure foo()
114 create procedure foo()
123 create procedure foo()
128 # RETURN in FUNCTION only
130 create procedure foo()
133 # Wrong number of arguments
134 create procedure p(x int)
136 create function f(x int) returns int
152 create procedure p(val int, out res int)
154 declare x int default 0;
155 declare continue handler for foo set x = 1;
157 insert into test.t1 values (val);
166 create procedure p(val int, out res int)
168 declare x int default 0;
169 declare foo condition for 1146;
170 declare continue handler for bar set x = 1;
172 insert into test.t1 values (val);
181 create function f(val int) returns int
188 create function f(val int) returns int
203 --error ER_PARSE_ERROR
206 declare c cursor for insert into test.t1 values ("foo", 42);
216 declare c cursor for select * into x from test.t limit 1;
225 declare c cursor for select * from test.t;
232 drop table if exists t1|
234 create table t1 (val int)|
238 declare c cursor for select * from test.t1;
250 declare c cursor for select * from test.t1;
261 alter procedure bar3 sql security invoker|
266 drop table if exists t1|
268 create table t1 (val int, x float)|
269 insert into t1 values (42, 3.1), (19, 1.2)|
275 declare c cursor for select * from t1;
285 declare c cursor for select * from t1;
300 declare c cursor for select * from t1;
303 fetch c into x, y, z;
311 create procedure p(in x int, x char(10))
315 create function p(x int, x char(10))
329 declare c condition for 1064;
330 declare c condition for 1065;
336 declare c cursor for select * from t1;
337 declare c cursor for select field from t1;
341 --error ER_SP_BADSTATEMENT
345 # Enforced standard order of declarations
349 declare c cursor for select * from t1;
356 declare continue handler for sqlstate '42S99' set x = 1;
357 declare foo condition for sqlstate '42S99';
364 declare continue handler for sqlstate '42S99' set x = 1;
365 declare c cursor for select * from t1;
368 # Check in and inout arguments.
370 drop procedure if exists p|
372 create procedure p(in x int, inout y int, out z int)
381 # For reference: this is ok
382 call p(@tmp_x, @tmp_y, @tmp_z)|
383 select @tmp_x, @tmp_y, @tmp_z|
385 --error ER_SP_NOT_VAR_ARG
386 call p(42, 43, @tmp_z)|
387 --error ER_SP_NOT_VAR_ARG
388 call p(42, @tmp_y, 43)|
394 # Let us test that we can access mysql.proc table for routines
395 # definitions lookup without locking it explicitly.
397 create procedure p() begin end|
399 # This should succeed
403 # Let us check restrictions which this ability puts on mysql.proc locking.
404 --error ER_WRONG_LOCK_OF_SYSTEM_TABLE
405 lock tables t1 read, mysql.proc write|
406 --error ER_WRONG_LOCK_OF_SYSTEM_TABLE
407 lock tables mysql.proc write, mysql.user write|
408 # Locking for read should be OK
409 lock tables t1 read, mysql.proc read|
411 # You also should be able lock only mysql.proc for write
412 lock tables mysql.proc write|
417 # Check that in functions we don't allow to update tables which
418 # are used by statements which invoke these functions.
421 drop function if exists f1|
423 create function f1(i int) returns int
425 insert into t1 (val) values (i);
428 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
429 select val, f1(val) from t1|
430 # Table alias should not matter
431 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
432 select val, f1(val) from t1 as tab|
434 --error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
435 update t1 set val= f1(val)|
437 # But this should be OK
441 delete from t1 where val= 17|
448 create procedure bug1965()
450 declare c cursor for select val from t1 order by valname;
457 drop procedure bug1965|
469 drop table if exists t3|
471 create table t3 (column_1_0 int)|
473 create procedure bug1653()
474 update t3 set column_1 = 0|
479 create table t3 (column_1 int)|
482 drop procedure bug1653|
488 # Note: When this bug existed, it did not necessarily cause a crash
489 # in all builds, but valgrind did give warnings.
490 create procedure bug2259()
493 declare c1 cursor for select s1 from t1;
500 drop procedure bug2259|
505 create procedure bug2272()
509 update t1 set v = 42;
512 insert into t1 values (666, 51.3)|
516 drop procedure bug2272|
521 create procedure bug2329_1()
525 insert into t1 (v) values (5);
528 create procedure bug2329_2()
532 replace t1 set v = 5;
539 drop procedure bug2329_1|
540 drop procedure bug2329_2|
545 create function bug3287() returns int
547 declare v int default null;
550 when v is not null then return 1;
556 drop function bug3287|
558 create procedure bug3287(x int)
561 insert into test.t1 values (x, 0.1);
563 insert into test.t1 values (x, 1.1);
567 drop procedure bug3287|
573 drop table if exists t3|
575 create table t3 (s1 int, primary key (s1))|
576 insert into t3 values (5),(6)|
578 create procedure bug3279(out y int)
580 declare x int default 0;
582 declare exit handler for sqlexception set x = x+1;
583 insert into t3 values (5);
587 insert into t3 values (6);
596 drop procedure bug3279|
603 create procedure nodb.bug3339() begin end|
608 create procedure bug2653_1(a int, out b int)
611 create procedure bug2653_2(a int, out b int)
621 call bug2653_1(1, @b)|
623 call bug2653_2(2, @b)|
625 drop procedure bug2653_1|
626 drop procedure bug2653_2|
632 create procedure bug4344() drop procedure bug4344|
634 create procedure bug4344() drop function bug4344|
637 # BUG#3294: Stored procedure crash if table dropped before use
638 # (Actually, when an error occurs within an error handler.)
640 drop procedure if exists bug3294|
642 create procedure bug3294()
644 declare continue handler for sqlexception drop table t5;
649 create table t5 (x int)|
652 drop procedure bug3294|
655 # BUG#876: Stored Procedures: Invalid SQLSTATE is allowed in
656 # a DECLARE ? HANDLER FOR stmt.
659 drop procedure if exists bug8776_1|
660 drop procedure if exists bug8776_2|
661 drop procedure if exists bug8776_3|
662 drop procedure if exists bug8776_4|
664 --error ER_SP_BAD_SQLSTATE
665 create procedure bug8776_1()
667 declare continue handler for sqlstate '42S0200test' begin end;
671 --error ER_SP_BAD_SQLSTATE
672 create procedure bug8776_2()
674 declare continue handler for sqlstate '4200' begin end;
678 --error ER_SP_BAD_SQLSTATE
679 create procedure bug8776_3()
681 declare continue handler for sqlstate '420000' begin end;
685 --error ER_SP_BAD_SQLSTATE
686 create procedure bug8776_4()
688 declare continue handler for sqlstate '42x00' begin end;
694 # BUG#6600: Stored procedure crash after repeated calls with check table
696 --error ER_SP_BADSTATEMENT
697 create procedure bug6600()
700 # Check these two as well, while we're at it. (Although it isn't really
701 # related to the bug report, but to the fix.)
702 --error ER_SP_BADSTATEMENT
703 create procedure bug6600()
705 --error ER_SP_BADSTATEMENT
706 create procedure bug6600()
710 # BUG#9566: explicit LOCK TABLE and store procedures result in illegal state
712 # We should not think that mysql.proc table does not exist if we are unable
713 # to open it under LOCK TABLE or in prelocked mode.
716 drop procedure if exists bug9566|
718 create procedure bug9566()
723 # This should fail since we forgot to lock mysql.proc for writing
724 # explicitly, and we can't open mysql.proc for _writing_ if there
727 alter procedure bug9566 comment 'Some comment'|
729 # This should succeed
730 drop procedure bug9566|
734 # BUG#7299: Stored procedures: exception handler catches not-found conditions
737 drop procedure if exists bug7299|
739 create procedure bug7299()
742 declare c cursor for select val from t1;
743 declare exit handler for sqlexception select 'Error!';
750 --error ER_SP_FETCH_NO_DATA
752 drop procedure bug7299|
756 # BUG#9073: Able to declare two handlers for same condition in same scope
758 --error ER_SP_DUP_HANDLER
759 create procedure bug9073()
761 declare continue handler for sqlexception select 1;
762 declare continue handler for sqlexception select 2;
764 --error ER_SP_DUP_HANDLER
765 create procedure bug9073()
767 declare condname1 condition for 1234;
768 declare continue handler for condname1 select 1;
769 declare exit handler for condname1 select 2;
771 --error ER_SP_DUP_HANDLER
772 create procedure bug9073()
774 declare condname1 condition for sqlstate '42000';
775 declare condname2 condition for sqlstate '42000';
776 declare exit handler for condname1 select 1;
777 declare continue handler for condname2 select 2;
779 --error ER_SP_DUP_HANDLER
780 create procedure bug9073()
782 declare condname1 condition for sqlstate '42000';
783 declare exit handler for condname1 select 1;
784 declare exit handler for sqlstate '42000' select 2;
787 # This should still work.
789 drop procedure if exists bug9073|
791 create procedure bug9073()
793 declare condname1 condition for sqlstate '42000';
794 declare continue handler for condname1 select 1;
796 declare exit handler for sqlstate '42000' select 2;
798 declare continue handler for sqlstate '42000' select 3;
802 drop procedure bug9073|
806 # BUG#7047: Stored procedure crash if alter procedure
808 --error ER_SP_NO_DROP_SP
809 create procedure bug7047()
810 alter procedure bug7047|
811 --error ER_SP_NO_DROP_SP
812 create function bug7047() returns int
814 alter function bug7047;
820 # BUG#8408: Stored procedure crash if function contains SHOW
821 # BUG#9058: Stored Procedures: Crash if function included SELECT
824 # Some things are caught when parsing
825 --error ER_SP_NO_RETSET
826 create function bug8408() returns int
831 --error ER_SP_NO_RETSET
832 create function bug8408() returns int
837 --error ER_SP_NO_RETSET
838 create function bug8408(a int) returns int
846 drop function if exists bug8408_f|
847 drop procedure if exists bug8408_p|
850 # Some things must be caught at invokation time
851 create function bug8408_f() returns int
856 create procedure bug8408_p()
860 --error ER_SP_NO_RETSET
863 drop procedure bug8408_p|
864 drop function bug8408_f|
867 create function bug8408() returns int
869 declare n int default 0;
870 select count(*) into n from t1;
874 insert into t1 value (2, 2.7), (3, 3.14), (7, 7.0)|
875 select *,bug8408() from t1|
877 drop function bug8408|
882 # BUG#10537: Server crashes while loading data file into table through
884 # Disable load until it's PS and SP safe
886 drop procedure if exists bug10537|
888 --error ER_SP_BADSTATEMENT
889 create procedure bug10537()
890 load data local infile '/tmp/somefile' into table t1|
894 # BUG#8409: Stored procedure crash if function contains FLUSH
897 drop function if exists bug8409|
899 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
900 create function bug8409()
906 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
907 create function bug8409() returns int begin reset query cache;
909 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
910 create function bug8409() returns int begin reset master;
912 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
913 create function bug8409() returns int begin reset slave;
915 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
916 create function bug8409() returns int begin flush hosts;
918 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
919 create function bug8409() returns int begin flush privileges;
921 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
922 create function bug8409() returns int begin flush tables with read lock;
924 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
925 create function bug8409() returns int begin flush tables;
927 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
928 create function bug8409() returns int begin flush logs;
930 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
931 create function bug8409() returns int begin flush status;
933 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
934 create function bug8409() returns int begin flush slave;
936 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
937 create function bug8409() returns int begin flush master;
939 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
940 create function bug8409() returns int begin flush des_key_file;
942 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
943 create function bug8409() returns int begin flush user_resources;
948 # BUG#9529: Stored Procedures: No Warning on truncation of procedure name
950 # BUG#17015: Routine name truncation not an error
951 # When we started using utf8 for mysql.proc, this limit appeared
952 # to be higher, but in reality the names were truncated.
953 --error ER_TOO_LONG_IDENT
954 create procedure bug9529_901234567890123456789012345678901234567890123456789012345()
959 drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234|
961 # Check the upper limit, just to make sure.
962 create procedure bug17015_0123456789012345678901234567890123456789012345678901234()
966 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
967 show procedure status like 'bug17015%'|
968 drop procedure bug17015_0123456789012345678901234567890123456789012345678901234|
972 # BUG#10969: Stored procedures: crash if default() function
975 drop procedure if exists bug10969|
977 --error ER_WRONG_COLUMN_NAME
978 create procedure bug10969()
980 declare s1 int default 0;
981 select default(s1) from t30;
985 create procedure bug10969()
987 declare s1 int default 0;
988 select default(t30.s1) from t30;
991 drop procedure bug10969|
998 # BUG#9814: Closing a cursor that is not open
999 create table t1(f1 int);
1000 create table t2(f1 int);
1003 CREATE PROCEDURE SP001()
1005 DECLARE ENDTABLE INT DEFAULT 0;
1006 DECLARE TEMP_NUM INT;
1007 DECLARE TEMP_SUM INT;
1008 DECLARE C1 CURSOR FOR SELECT F1 FROM t1;
1009 DECLARE C2 CURSOR FOR SELECT F1 FROM t2;
1010 DECLARE CONTINUE HANDLER FOR NOT FOUND SET ENDTABLE = 1;
1018 FETCH C1 INTO TEMP_NUM;
1019 WHILE ENDTABLE = 0 DO
1020 SET TEMP_SUM=TEMP_NUM+TEMP_SUM;
1021 FETCH C1 INTO TEMP_NUM;
1026 SELECT 'end of proc';
1031 drop procedure SP001;
1034 # Bug #11394 "Recursion in SP crash server" and bug #11600 "Stored
1035 # procedures: crash with function calling itself".
1036 # We have to disable recursion since in many cases LEX and many
1037 # Item's can't be used in reentrant way nowdays.
1040 drop function if exists bug11394|
1041 drop function if exists bug11394_1|
1042 drop function if exists bug11394_2|
1043 drop procedure if exists bug11394|
1045 create function bug11394(i int) returns int
1050 return (i in (100, 200, bug11394(i-1), 400));
1053 # If we allow recursive functions without additional modifications
1054 # this will crash server since Item for "IN" is not reenterable.
1057 drop function bug11394|
1058 create function bug11394_1(i int) returns int
1063 return (select bug11394_1(i-1));
1066 # The following statement will crash because some LEX members responsible
1067 # for selects cannot be used in reentrant fashion.
1069 select bug11394_1(2)|
1070 drop function bug11394_1|
1071 # Note that the following should be allowed since it does not contains
1073 create function bug11394_2(i int) returns int return i|
1074 select bug11394_2(bug11394_2(10))|
1075 drop function bug11394_2|
1076 create procedure bug11394(i int, j int)
1079 call bug11394(i - 1,(select 1));
1082 --error ER_SP_RECURSION_LIMIT
1083 call bug11394(2, 1)|
1084 set @@max_sp_recursion_depth=10|
1085 call bug11394(2, 1)|
1086 set @@max_sp_recursion_depth=default|
1087 drop procedure bug11394|
1092 # BUG 12490 (Packets out of order if calling HELP CONTENTS from Stored Procedure)
1094 --error ER_SP_BADSTATEMENT
1095 CREATE PROCEDURE BUG_12490() HELP CONTENTS;
1096 --error ER_SP_BADSTATEMENT
1097 CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS;
1098 CREATE TABLE t_bug_12490(a int);
1099 --error ER_SP_BADSTATEMENT
1100 CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS;
1101 DROP TABLE t_bug_12490;
1104 # Bug#11834 "Re-execution of prepared statement with dropped function
1105 # crashes server". Also tests handling of prepared stmts which use
1106 # stored functions but does not require prelocking.
1109 drop function if exists bug11834_1;
1110 drop function if exists bug11834_2;
1112 create function bug11834_1() returns int return 10;
1113 create function bug11834_2() returns int return bug11834_1();
1114 prepare stmt from "select bug11834_2()";
1116 # Re-execution of statement should not crash server.
1118 drop function bug11834_1;
1119 # Attempt to execute statement should return proper error and
1120 # should not crash server.
1122 # NOTE! The error we get from the below query indicates that the sp bug11834_2
1123 # does not exist(this is wrong but can be accepted)
1124 # This behaviour has been reported as bug#21294
1125 --error ER_SP_DOES_NOT_EXIST
1127 deallocate prepare stmt;
1128 drop function bug11834_2;
1131 # Bug#12953 "Stored procedures: crash if OPTIMIZE TABLE in function"
1135 DROP FUNCTION IF EXISTS bug12953|
1137 --error ER_SP_NO_RETSET
1138 CREATE FUNCTION bug12953() RETURNS INT
1146 # Bug##12995 "Inside function "Table 't4' was not locked with LOCK TABLES"
1150 DROP FUNCTION IF EXISTS bug12995|
1152 --error ER_SP_BADSTATEMENT
1153 CREATE FUNCTION bug12995() RETURNS INT
1158 --error ER_SP_BADSTATEMENT
1159 CREATE FUNCTION bug12995() RETURNS INT
1161 HANDLER t1 READ FIRST;
1164 --error ER_SP_BADSTATEMENT
1165 CREATE FUNCTION bug12995() RETURNS INT
1176 # BUG#12712: SET AUTOCOMMIT should fail within SP/functions/triggers
1179 drop procedure if exists bug12712;
1180 drop function if exists bug12712;
1183 create procedure bug12712()
1184 set session autocommit = 0;
1186 select @@autocommit;
1187 set @au = @@autocommit;
1189 select @@autocommit;
1190 set session autocommit = @au;
1193 create function bug12712()
1201 --error ER_SP_CANT_SET_AUTOCOMMIT
1202 set @x = bug12712()|
1203 drop procedure bug12712|
1204 drop function bug12712|
1205 --error ER_SP_CANT_SET_AUTOCOMMIT
1206 create function bug12712()
1209 set session autocommit = 0;
1212 --error ER_SP_CANT_SET_AUTOCOMMIT
1213 create function bug12712()
1216 set @@autocommit = 0;
1219 --error ER_SP_CANT_SET_AUTOCOMMIT
1220 create function bug12712()
1223 set local autocommit = 0;
1227 --error ER_SP_CANT_SET_AUTOCOMMIT
1228 create trigger bug12712
1229 before insert on t1 for each row set session autocommit = 0;
1232 # BUG#9367: Stored procedures: client hang after "show warnings"
1236 drop procedure if exists bug9367;
1238 create table t1 (s1 int);
1241 create procedure bug9367()
1244 declare c cursor for select s1 from t1;
1252 drop procedure bug9367;
1257 # BUG#13510: Setting password local variable changes current password
1261 drop procedure if exists bug13510_1|
1262 drop procedure if exists bug13510_2|
1263 drop procedure if exists bug13510_3|
1264 drop procedure if exists bug13510_4|
1267 --error ER_SP_BAD_VAR_SHADOW
1268 create procedure bug13510_1()
1270 declare password varchar(10);
1272 set password = 'foo1';
1276 # Check that an error message is sent
1277 --error ER_PARSE_ERROR
1280 --error ER_SP_BAD_VAR_SHADOW
1281 create procedure bug13510_2()
1283 declare names varchar(10);
1289 create procedure bug13510_3()
1291 declare password varchar(10);
1293 set `password` = 'foo3';
1297 create procedure bug13510_4()
1299 declare names varchar(10);
1301 set `names` = 'foo4';
1308 drop procedure bug13510_3|
1309 drop procedure bug13510_4|
1313 # Test that statements which implicitly commit transaction are prohibited
1314 # in stored function and triggers. Attempt to create function or trigger
1315 # containing such statement should produce error (includes test for
1319 drop function if exists bug_13627_f|
1322 CREATE TABLE t1 (a int)|
1323 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1324 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END |
1325 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1326 CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END |
1328 -- error ER_SP_BADSTATEMENT
1329 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN load table t1 from master; END |
1330 -- error ER_SP_BADSTATEMENT
1331 CREATE FUNCTION bug_13627_f() returns int BEGIN load table t1 from master; return 1; END |
1333 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1334 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END |
1335 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1336 CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END |
1338 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1339 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a); END |
1340 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1341 CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1; END |
1343 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1344 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column b int; END |
1345 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1346 CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column b int; return 1; END |
1348 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1349 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END |
1350 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1351 CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END |
1353 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1354 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END |
1355 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1356 CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END |
1358 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1359 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END |
1360 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1361 CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END |
1363 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1364 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END |
1365 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1366 CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END |
1368 -- error ER_SP_BADSTATEMENT
1369 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END |
1370 -- error ER_SP_BADSTATEMENT
1371 CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END |
1373 -- error ER_SP_BADSTATEMENT
1374 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END |
1375 -- error ER_SP_BADSTATEMENT
1376 CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END |
1378 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1379 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END |
1380 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1381 CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END |
1383 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1384 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END |
1385 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1386 CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END |
1388 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1389 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END |
1390 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1391 CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END |
1393 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1394 CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to 'mysqltest_1'; END |
1395 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1396 CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return 1; END |
1398 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1399 CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from 'mysqltest_1'; END |
1400 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1401 CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1'; return 1; END |
1403 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1404 CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on *.* from 'mysqltest_1'; END |
1405 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1406 CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from 'mysqltest_1'; return 1; END |
1408 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1409 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END |
1410 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1411 CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END |
1413 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1414 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; END |
1415 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1416 CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; return 1; END |
1418 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1419 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END |
1420 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1421 CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END |
1423 -- error ER_SP_BADSTATEMENT
1424 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END |
1425 -- error ER_SP_BADSTATEMENT
1426 CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END |
1428 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1429 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END |
1430 -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1431 CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END |
1433 -- error ER_SP_NO_RECURSIVE_CREATE
1434 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 |
1435 -- error ER_SP_NO_RECURSIVE_CREATE
1436 CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1 for each row do select 1; return 1; END |
1438 -- error ER_SP_NO_DROP_SP
1439 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END |
1440 -- error ER_SP_NO_DROP_SP
1441 CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END |
1443 -- error ER_SP_NO_RECURSIVE_CREATE
1444 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns int return 1; END |
1445 -- error ER_SP_NO_RECURSIVE_CREATE
1446 CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return 1; return 1; END |
1448 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW
1450 CREATE TEMPORARY TABLE t2 (a int);
1451 DROP TEMPORARY TABLE t2;
1453 CREATE FUNCTION bug_13627_f() returns int
1455 CREATE TEMPORARY TABLE t2 (a int);
1456 DROP TEMPORARY TABLE t2;
1461 drop function bug_13627_f|
1465 # BUG#12329: "Bogus error msg when executing PS with stored procedure after
1466 # SP was re-created". See also test for related bug#13399 in trigger.test
1467 drop function if exists bug12329;
1469 create table t1 as select 1 a;
1470 create table t2 as select 1 a;
1471 create function bug12329() returns int return (select a from t1);
1472 prepare stmt1 from 'select bug12329()';
1474 drop function bug12329;
1475 create function bug12329() returns int return (select a+100 from t2);
1478 deallocate prepare stmt1;
1479 drop function bug12329;
1483 # Bug#13514 "server crash when create a stored procedure before choose a
1485 # Bug#13587 "Server crash when SP is created without database
1488 create database mysqltest1;
1490 drop database mysqltest1;
1491 --error ER_NO_DB_ERROR
1492 create function f1() returns int return 1;
1494 --error ER_NO_DB_ERROR
1495 create procedure p1(out param1 int)
1497 select count(*) into param1 from t3;
1504 # BUG#13037: undefined variable in IF cause erroneous error-message
1508 DROP PROCEDURE IF EXISTS bug13037_p1;
1509 DROP PROCEDURE IF EXISTS bug13037_p2;
1510 DROP PROCEDURE IF EXISTS bug13037_p3;
1515 CREATE PROCEDURE bug13037_p1()
1517 IF bug13037_foo THEN
1522 CREATE PROCEDURE bug13037_p2()
1524 SET @bug13037_foo = bug13037_bar;
1527 CREATE PROCEDURE bug13037_p3()
1529 SELECT bug13037_foo;
1550 DROP PROCEDURE bug13037_p1;
1551 DROP PROCEDURE bug13037_p2;
1552 DROP PROCEDURE bug13037_p3;
1555 # Bug#14569 "editing a stored procedure kills mysqld-nt"
1557 create database mysqltest1;
1558 create database mysqltest2;
1560 drop database mysqltest1;
1561 create procedure mysqltest2.p1() select version();
1562 --error ER_NO_DB_ERROR
1563 create procedure p2() select version();
1565 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
1566 show procedure status;
1567 drop database mysqltest2;
1571 # Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server"
1575 DROP FUNCTION IF EXISTS bug13012|
1577 --error ER_SP_NO_RETSET
1578 CREATE FUNCTION bug13012() RETURNS INT
1583 --error ER_SP_NO_RETSET
1584 CREATE FUNCTION bug13012() RETURNS INT
1586 BACKUP TABLE t1 TO '/tmp';
1589 --error ER_SP_NO_RETSET
1590 CREATE FUNCTION bug13012() RETURNS INT
1592 RESTORE TABLE t1 FROM '/tmp';
1595 create table t1 (a int)|
1596 CREATE PROCEDURE bug13012_1() REPAIR TABLE t1|
1597 CREATE FUNCTION bug13012_2() RETURNS INT
1602 --error ER_SP_NO_RETSET
1603 SELECT bug13012_2()|
1605 drop procedure bug13012_1|
1606 drop function bug13012_2|
1610 # BUG#11555 "Stored procedures: current SP tables locking make
1611 # impossible view security". We should not expose names of tables
1612 # which are implicitly used by view (via stored routines/triggers).
1614 # Note that SQL standard assumes that you simply won't be able drop table
1615 # and leave some objects (routines/views/triggers) which were depending on
1616 # it. Such objects should be dropped in advance (by default) or will be
1617 # dropped simultaneously with table (DROP TABLE with CASCADE clause).
1618 # So these tests probably should go away once we will implement standard
1621 drop function if exists bug11555_1;
1622 drop function if exists bug11555_2;
1623 drop view if exists v1, v2, v3, v4;
1625 create function bug11555_1() returns int return (select max(i) from t1);
1626 create function bug11555_2() returns int return bug11555_1();
1627 # It is OK to report name of implicitly used table which is missing
1628 # when we create view.
1629 # For stored functions however, because of exceptions handlers, there is
1630 # no easy way to find out if a missing table makes the view invalid.
1631 create view v1 as select bug11555_1();
1633 create view v2 as select bug11555_2();
1635 # But we should hide name of missing implicitly used table when we use view
1636 create table t1 (i int);
1637 create view v1 as select bug11555_1();
1638 create view v2 as select bug11555_2();
1639 create view v3 as select * from v1;
1641 --error ER_VIEW_INVALID
1643 --error ER_VIEW_INVALID
1645 --error ER_VIEW_INVALID
1647 # Note that creation of view which depends on broken view is yet
1648 # another form of view usage.
1649 create view v4 as select * from v1;
1650 drop view v1, v2, v3, v4;
1651 # We also should hide details about broken triggers which are
1653 drop function bug11555_1;
1654 drop function bug11555_2;
1655 create table t1 (i int);
1656 create table t2 (i int);
1657 create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i);
1658 create view v1 as select * from t1;
1660 # Limitation, the desired error is ER_VIEW_INVALID
1661 --error ER_NO_SUCH_TABLE
1662 insert into v1 values (1);
1664 create function bug11555_1() returns int return (select max(i) from t2);
1665 create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1();
1666 # Limitation, the desired error is ER_VIEW_INVALID
1667 --error ER_NO_SUCH_TABLE
1668 insert into v1 values (2);
1669 drop function bug11555_1;
1674 # BUG#15658: Server crashes after creating function as empty string
1677 drop procedure if exists ` bug15658`;
1680 --error ER_SP_WRONG_NAME
1681 create procedure ``() select 1;
1682 --error ER_SP_WRONG_NAME
1683 create procedure ` `() select 1;
1684 --error ER_SP_WRONG_NAME
1685 create procedure `bug15658 `() select 1;
1686 --error ER_WRONG_DB_NAME
1687 create procedure ``.bug15658() select 1;
1688 --error ER_WRONG_DB_NAME
1689 create procedure `x `.bug15658() select 1;
1692 create procedure ` bug15658`() select 1;
1694 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
1695 show procedure status;
1696 drop procedure ` bug15658`;
1700 # BUG#14270: Stored procedures: crash if load index
1703 drop function if exists bug14270;
1704 drop table if exists t1;
1707 create table t1 (s1 int primary key);
1710 --error ER_SP_NO_RETSET
1711 create function bug14270() returns int
1713 load index into cache t1;
1717 --error ER_SP_NO_RETSET
1718 create function bug14270() returns int
1720 cache index t1 key (`primary`) in keycache1;
1729 # BUG#15091: Sp Returns Unknown error in order clause....and
1730 # there is no order by clause
1733 drop procedure if exists bug15091;
1737 create procedure bug15091()
1739 declare selectstr varchar(6000) default ' ';
1740 declare conditionstr varchar(5000) default '';
1742 set selectstr = concat(selectstr,
1745 'in (',conditionstr, ')');
1749 # The error message used to be:
1750 # ERROR 1109 (42S02): Unknown table 'c' in order clause
1751 # but is now rephrased to something less misleading:
1752 # ERROR 1109 (42S02): Unknown table 'c' in field list
1753 --error ER_UNKNOWN_TABLE
1756 drop procedure bug15091;
1760 # BUG#16896: Stored function: unused AGGREGATE-clause in CREATE FUNCTION
1763 drop function if exists bug16896;
1766 --error ER_PARSE_ERROR
1767 create aggregate function bug16896() returns int return 1;
1771 # BUG#14702: misleading error message when syntax error in CREATE
1774 # Misleading error message was given when IF NOT EXISTS was used in
1778 DROP PROCEDURE IF EXISTS bug14702;
1781 --error ER_PARSE_ERROR
1782 CREATE IF NOT EXISTS PROCEDURE bug14702()
1786 --error ER_PARSE_ERROR
1787 CREATE PROCEDURE IF NOT EXISTS bug14702()
1793 # BUG#20953: create proc with a create view that uses local
1794 # vars/params should fail to create
1796 # See test case for what syntax is forbidden in a view.
1799 DROP TABLE IF EXISTS t1;
1802 CREATE TABLE t1 (i INT);
1804 # We do not have to drop this procedure and view because they won't be
1806 --error ER_VIEW_SELECT_CLAUSE
1807 CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a;
1808 --error ER_VIEW_SELECT_CLAUSE
1809 CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file";
1810 --error ER_VIEW_SELECT_CLAUSE
1811 CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file";
1812 --error ER_VIEW_SELECT_CLAUSE
1813 CREATE PROCEDURE bug20953()
1814 CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE();
1815 --error ER_VIEW_SELECT_DERIVED
1816 CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1;
1817 --error ER_VIEW_SELECT_VARIABLE
1818 CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i;
1820 --error ER_VIEW_SELECT_VARIABLE
1821 CREATE PROCEDURE bug20953()
1824 CREATE VIEW v AS SELECT i;
1827 --error ER_VIEW_SELECT_VARIABLE
1828 PREPARE stmt FROM "CREATE VIEW v AS SELECT ?";
1834 # BUG#24491 "using alias from source table in insert ... on duplicate key"
1837 drop tables if exists t1;
1838 drop procedure if exists bug24491;
1840 create table t1 (id int primary key auto_increment, value varchar(10));
1841 insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
1842 # Let us create routine with INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
1843 # statement which in its ON DUPLICATE KEY clause erroneously tries to assign
1844 # value to a column which is mentioned only in SELECT part.
1845 create procedure bug24491()
1846 insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP';
1847 # Both first and second calls to it should fail
1848 --error ER_BAD_FIELD_ERROR
1850 --error ER_BAD_FIELD_ERROR
1852 drop procedure bug24491;
1853 # And now the same test for more complex case which is more close
1854 # to the one that was reported originally.
1855 create procedure bug24491()
1856 insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP';
1857 --error ER_BAD_FIELD_ERROR
1859 --error ER_BAD_FIELD_ERROR
1861 drop procedure bug24491;
1865 # BUG#18914: Calling certain SPs from triggers fail
1867 # Failing to call a procedure that does implicit commit from a trigger
1868 # is a correct behaviour, however the error message was misleading.
1870 # DROP TABLE IF EXISTS is also fixed to give correct error instead of
1871 # "Table doesn't exist".
1874 DROP FUNCTION IF EXISTS bug18914_f1;
1875 DROP FUNCTION IF EXISTS bug18914_f2;
1876 DROP PROCEDURE IF EXISTS bug18914_p1;
1877 DROP PROCEDURE IF EXISTS bug18914_p2;
1878 DROP TABLE IF EXISTS t1, t2;
1881 CREATE TABLE t1 (i INT);
1883 CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT);
1884 CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table;
1887 CREATE FUNCTION bug18914_f1() RETURNS INT
1893 CREATE FUNCTION bug18914_f2() RETURNS INT
1900 CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
1903 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1904 INSERT INTO t1 VALUES (1);
1906 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1907 SELECT bug18914_f1();
1909 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
1910 SELECT bug18914_f2();
1912 --error ER_NO_SUCH_TABLE
1915 DROP FUNCTION bug18914_f1;
1916 DROP FUNCTION bug18914_f2;
1917 DROP PROCEDURE bug18914_p1;
1918 DROP PROCEDURE bug18914_p2;
1922 # Bug#20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
1926 drop table if exists bogus_table_20713;
1927 drop function if exists func_20713_a;
1928 drop function if exists func_20713_b;
1931 create table bogus_table_20713( id int(10) not null primary key);
1932 insert into bogus_table_20713 values (1), (2), (3);
1936 create function func_20713_a() returns int(11)
1940 declare continue handler for sqlexception set id=null;
1943 set id = (select id from bogus_table_20713 where id = 3);
1949 create function func_20713_b() returns int(11)
1953 declare continue handler for sqlstate value '42S02' set id=null;
1956 set id = (select id from bogus_table_20713 where id = 3);
1965 select func_20713_a();
1969 select func_20713_b();
1972 drop table bogus_table_20713;
1975 select func_20713_a();
1979 select func_20713_b();
1982 drop function if exists func_20713_a;
1983 drop function if exists func_20713_b;
1986 # Bug#25345 (Cursors from Functions)
1990 drop table if exists table_25345_a;
1991 drop table if exists table_25345_b;
1992 drop procedure if exists proc_25345;
1993 drop function if exists func_25345;
1994 drop function if exists func_25345_b;
1997 create table table_25345_a (a int);
1998 create table table_25345_b (b int);
2002 create procedure proc_25345()
2004 declare c1 cursor for select a from table_25345_a;
2005 declare c2 cursor for select b from table_25345_b;
2010 create function func_25345() returns int(11)
2016 create function func_25345_b() returns int(11)
2018 declare c1 cursor for select a from table_25345_a;
2019 declare c2 cursor for select b from table_25345_b;
2027 --error ER_SP_NO_RETSET
2028 select func_25345();
2029 select func_25345_b();
2031 drop table table_25345_a;
2034 --error ER_SP_NO_RETSET
2035 select func_25345();
2036 select func_25345_b();
2038 drop table table_25345_b;
2039 drop procedure proc_25345;
2040 drop function func_25345;
2041 drop function func_25345_b;
2046 --echo End of 5.0 tests
2049 # Bug#16164 "Easter egg": check that SHOW AUTHORS is disabled in
2050 # stored functions/triggers
2053 drop function if exists bug16164;
2056 --error ER_SP_NO_RETSET
2057 create function bug16164() returns int
2066 # BUG#20701: BINARY keyword should be forbidden in stored routines
2069 drop function if exists bug20701;
2072 # This was disabled in 5.1.12. See bug #20701
2073 # When collation support in SP is implemented, then this test should
2076 --error ER_NOT_SUPPORTED_YET
2077 create function bug20701() returns varchar(25) binary return "test";
2078 create function bug20701() returns varchar(25) return "test";
2079 drop function bug20701;
2082 # Bug#26503 (Illegal SQL exception handler code causes the server to crash)
2087 --error ER_SP_LILABEL_MISMATCH
2088 create procedure proc_26503_error_1()
2093 declare continue handler for sqlexception
2098 select "do something";
2100 until true end repeat retry;
2103 --error ER_SP_LILABEL_MISMATCH
2104 create procedure proc_26503_error_2()
2109 declare continue handler for sqlexception
2112 select "do something";
2114 until true end repeat retry;
2117 --error ER_SP_LILABEL_MISMATCH
2118 create procedure proc_26503_error_3()
2123 declare continue handler for sqlexception
2128 select "do something";
2130 until true end repeat retry;
2133 --error ER_SP_LILABEL_MISMATCH
2134 create procedure proc_26503_error_4()
2139 declare continue handler for sqlexception
2142 select "do something";
2144 until true end repeat retry;
2151 # Bug#28360 (RENAME DATABASE destroys routines)
2155 drop procedure if exists proc_28360;
2156 drop function if exists func_28360;
2161 --error ER_SP_NO_DROP_SP
2162 CREATE PROCEDURE proc_28360()
2164 ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
2167 --error ER_SP_NO_DROP_SP
2168 CREATE FUNCTION func_28360() RETURNS int
2170 ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
2178 # Bug#29223 declare cursor c for SHOW .....
2182 DROP PROCEDURE IF EXISTS p1;
2185 --error ER_PARSE_ERROR
2186 CREATE PROCEDURE p1()
2188 DECLARE c char(100);
2189 DECLARE cur1 CURSOR FOR SHOW TABLES;
2199 # Bug#29816 Syntactically wrong query fails with misleading error message
2203 DROP DATABASE IF EXISTS mysqltest;
2205 CREATE DATABASE mysqltest;
2207 DROP DATABASE mysqltest;
2208 # Both ER_SP_DOES_NOT_EXIST and ER_PARSE_ERROR are valid here,
2209 # the result is implementation dependent:
2210 # See Bug#29816 for details
2211 --error ER_SP_DOES_NOT_EXIST
2212 SELECT inexistent(), 1 + ,;
2213 --error ER_SP_DOES_NOT_EXIST
2214 SELECT inexistent();
2215 --error ER_PARSE_ERROR
2216 SELECT .inexistent();
2217 --error ER_PARSE_ERROR
2218 SELECT ..inexistent();
2222 # Bug#30904 SET PASSWORD statement is non-transactional
2227 --error ER_SP_CANT_SET_AUTOCOMMIT
2228 create function f1() returns int
2230 set @test = 1, password = password('foo');
2234 --error ER_SP_CANT_SET_AUTOCOMMIT
2236 before insert on t2 for each row set password = password('foo');|
2241 # Bug#30882 Dropping a temporary table inside a stored function may cause a server crash
2245 drop function if exists f1;
2246 drop function if exists f2;
2247 drop table if exists t1, t2;
2251 create function f1() returns int
2253 drop temporary table t1;
2257 --error ER_CANT_REOPEN_TABLE
2258 create temporary table t1 as select f1();
2261 create function f2() returns int
2263 create temporary table t2 as select f1();
2267 --error ER_CANT_REOPEN_TABLE
2268 create temporary table t1 as select f2();
2274 create function f1() returns int
2276 drop temporary table t2,t1;
2279 create function f2() returns int
2281 create temporary table t2 as select f1();
2285 --error ER_CANT_REOPEN_TABLE
2286 create temporary table t1 as select f2();
2291 create temporary table t2(a int);
2294 create function f2() returns int
2296 drop temporary table t2;
2303 --error ER_BAD_TABLE_ERROR
2306 --echo End of 5.1 tests
2309 # Bug#33983 (Stored Procedures: wrong end <label> syntax is accepted)
2313 drop procedure if exists proc_33983_a;
2314 drop procedure if exists proc_33983_b;
2315 drop procedure if exists proc_33983_c;
2316 drop procedure if exists proc_33983_d;
2321 --error ER_SP_LABEL_MISMATCH
2322 create procedure proc_33983_a()
2333 --error ER_SP_LABEL_MISMATCH
2334 create procedure proc_33983_b()
2341 until FALSE end repeat label1;
2342 until FALSE end repeat;
2345 --error ER_SP_LABEL_MISMATCH
2346 create procedure proc_33983_c()
2357 --error ER_SP_LABEL_MISMATCH
2358 create procedure proc_33983_d()
2369 CREATE TABLE t1 (a INT)|
2370 INSERT INTO t1 VALUES (1),(2)|
2371 CREATE PROCEDURE p1(a INT) BEGIN END|
2372 --error ER_SUBQUERY_NO_1_ROW
2373 CALL p1((SELECT * FROM t1))|
2374 DROP PROCEDURE IF EXISTS p1|
2380 # Bug#21801: SQL exception handlers and warnings
2384 drop procedure if exists p1;
2387 create procedure p1()
2389 create table t1 (a int) type=MyISAM;
2398 # Bug#8759 (Stored Procedures: SQLSTATE '00000' should be illegal)
2402 drop procedure if exists proc_8759;
2407 --error ER_SP_BAD_SQLSTATE
2408 create procedure proc_8759()
2410 declare should_be_illegal condition for sqlstate '00000';
2411 declare continue handler for should_be_illegal set @x=0;
2414 --error ER_SP_BAD_SQLSTATE
2415 create procedure proc_8759()
2417 declare continue handler for sqlstate '00000' set @x=0;
2424 # BUG#NNNN: New bug synopsis
2427 #drop procedure if exists bugNNNN;
2428 #drop function if exists bugNNNN;
2430 #create procedure bugNNNN...
2431 #create function bugNNNN...
2434 # CLEANUP and RESTORE
2436 LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc;
2437 remove_file $MYSQLTEST_VARDIR/tmp/proc.txt;
2440 # Bug #38159: Function parsing problem generates misleading error message
2443 CREATE TABLE t1 (a INT, b INT);
2444 INSERT INTO t1 VALUES (1,1), (2,2);
2445 --error ER_FUNC_INEXISTENT_NAME_COLLISION
2446 SELECT MAX (a) FROM t1 WHERE b = 999999;
2447 SELECT AVG (a) FROM t1 WHERE b = 999999;
2448 --error ER_SP_DOES_NOT_EXIST
2449 SELECT non_existent (a) FROM t1 WHERE b = 999999;
2453 --echo # Bug #47788: Crash in TABLE_LIST::hide_view_error on UPDATE + VIEW +
2454 --echo # SP + MERGE + ALTER
2457 CREATE TABLE t1 (pk INT, b INT, KEY (b));
2458 CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1;
2460 CREATE PROCEDURE p1 (a int) UPDATE IGNORE v1 SET b = a;
2462 --error ER_NON_UPDATABLE_TABLE
2465 ALTER TABLE t1 CHANGE COLUMN b b2 INT;
2467 --error ER_VIEW_INVALID
2475 --echo # Bug#12428824 - PARSER STACK OVERFLOW AND CRASH IN SP_ADD_USED_ROUTINE
2476 --echo # WITH OBSCURE QUERY
2479 --error ER_TOO_LONG_IDENT
2480 SELECT very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999();
2482 --error ER_TOO_LONG_IDENT
2483 CALL very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999();
2485 --error ER_WRONG_DB_NAME
2486 SELECT very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_func();
2488 --error ER_WRONG_DB_NAME
2489 CALL very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_proc();
2491 --error ER_TOO_LONG_IDENT
2492 SELECT db_name.very_long_fn_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999();
2494 --error ER_TOO_LONG_IDENT
2495 CALL db_name.very_long_pr_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999();
2496 --echo End of 5.1 tests