/Library/mysql/mysql-test/t/sp-error.test
Unknown | 2496 lines | 2123 code | 373 blank | 0 comment | 0 complexity | 863d7759d51fd92564411110c216fc56 MD5 | raw file
Possible License(s): LGPL-2.1, Apache-2.0, LGPL-2.0, LGPL-3.0, MPL-2.0-no-copyleft-exception, GPL-2.0, BSD-3-Clause, GPL-3.0, BSD-2-Clause
- #
- # Stored PROCEDURE error tests
- #
- --disable_warnings
- drop table if exists t1, t2;
- --enable_warnings
- # Backup the mysql.proc table
- --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
- eval SELECT * FROM mysql.proc INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/proc.txt';
- # 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 ER_PARSE_ERROR
- 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 ER_DUP_ENTRY
- 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 ER_SP_BADSTATEMENT
- CREATE PROCEDURE BUG_12490() HELP CONTENTS;
- --error ER_SP_BADSTATEMENT
- CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS;
- CREATE TABLE t_bug_12490(a int);
- --error ER_SP_BADSTATEMENT
- 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 bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to 'mysqltest_1'; END |
- -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
- CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return 1; END |
- -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
- CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from 'mysqltest_1'; END |
- -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
- CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1'; return 1; END |
- -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
- CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on *.* from 'mysqltest_1'; END |
- -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
- CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from '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_SP_BADSTATEMENT
- CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END |
- -- error ER_SP_BADSTATEMENT
- 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();
- 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.
- # For stored functions however, because of exceptions handlers, there is
- # no easy way to find out if a missing table makes the view invalid.
- create view v1 as select bug11555_1();
- drop view v1;
- create view v2 as select bug11555_2();
- drop view v2;
- # 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.
- create view v4 as select * from v1;
- drop view v1, v2, v3, v4;
- # 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;
- # Limitation, the desired error is ER_VIEW_INVALID
- --error ER_NO_SUCH_TABLE
- 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();
- # Limitation, the desired error is ER_VIEW_INVALID
- --error ER_NO_SUCH_TABLE
- 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_PARSE_ERROR
- 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;
- #
- # BUG#24491 "using alias from source table in insert ... on duplicate key"
- #
- --disable_warnings
- drop tables if exists t1;
- drop procedure if exists bug24491;
- --enable_warnings
- create table t1 (id int primary key auto_increment, value varchar(10));
- insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
- # Let us create routine with INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
- # statement which in its ON DUPLICATE KEY clause erroneously tries to assign
- # value to a column which is mentioned only in SELECT part.
- create procedure bug24491()
- insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP';
- # Both first and second calls to it should fail
- --error ER_BAD_FIELD_ERROR
- call bug24491();
- --error ER_BAD_FIELD_ERROR
- call bug24491();
- drop procedure bug24491;
- # And now the same test for more complex case which is more close
- # to the one that was reported originally.
- create procedure bug24491()
- insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP';
- --error ER_BAD_FIELD_ERROR
- call bug24491();
- --error ER_BAD_FIELD_ERROR
- call bug24491();
- drop procedure bug24491;
- drop tables t1;
- #
- # BUG#18914: Calling certain SPs from triggers fail
- #
- # Failing to call a procedure that does implicit commit from a trigger
- # is a correct behaviour, however the error message was misleading.
- #
- # DROP TABLE IF EXISTS is also fixed to give correct error instead of
- # "Table doesn't exist".
- #
- --disable_warnings
- DROP FUNCTION IF EXISTS bug18914_f1;
- DROP FUNCTION IF EXISTS bug18914_f2;
- DROP PROCEDURE IF EXISTS bug18914_p1;
- DROP PROCEDURE IF EXISTS bug18914_p2;
- DROP TABLE IF EXISTS t1, t2;
- --enable_warnings
- CREATE TABLE t1 (i INT);
- CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT);
- CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table;
- delimiter |;
- CREATE FUNCTION bug18914_f1() RETURNS INT
- BEGIN
- CALL bug18914_p1();
- RETURN 1;
- END |
- CREATE FUNCTION bug18914_f2() RETURNS INT
- BEGIN
- CALL bug18914_p2();
- RETURN 1;
- END |
- delimiter ;|
- CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
- CALL bug18914_p1();
- --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
- INSERT INTO t1 VALUES (1);
- --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
- SELECT bug18914_f1();
- --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
- SELECT bug18914_f2();
- --error ER_NO_SUCH_TABLE
- SELECT * FROM t2;
- DROP FUNCTION bug18914_f1;
- DROP FUNCTION bug18914_f2;
- DROP PROCEDURE bug18914_p1;
- DROP PROCEDURE bug18914_p2;
- DROP TABLE t1;
- #
- # Bug#20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
- #
- --disable_warnings
- drop table if exists bogus_table_20713;
- drop function if exists func_20713_a;
- drop function if exists func_20713_b;
- --enable_warnings
- create table bogus_table_20713( id int(10) not null primary key);
- insert into bogus_table_20713 values (1), (2), (3);
- delimiter //;
- create function func_20713_a() returns int(11)
- begin
- declare id int;
- declare continue handler for sqlexception set id=null;
- set @in_func := 1;
- set id = (select id from bogus_table_20713 where id = 3);
- set @in_func := 2;
- return id;
- end//
- create function func_20713_b() returns int(11)
- begin
- declare id int;
- declare continue handler for sqlstate value '42S02' set id=null;
- set @in_func := 1;
- set id = (select id from bogus_table_20713 where id = 3);
- set @in_func := 2;
- return id;
- end//
- delimiter ;//
- set @in_func := 0;
- select func_20713_a();
- select @in_func;
- set @in_func := 0;
- select func_20713_b();
- select @in_func;
- drop table bogus_table_20713;
- set @in_func := 0;
- select func_20713_a();
- select @in_func;
- set @in_func := 0;
- select func_20713_b();
- select @in_func;
- drop function if exists func_20713_a;
- drop function if exists func_20713_b;
- #
- # Bug#25345 (Cursors from Functions)
- #
- --disable_warnings
- drop table if exists table_25345_a;
- drop table if exists table_25345_b;
- drop procedure if exists proc_25345;
- drop function if exists func_25345;
- drop function if exists func_25345_b;
- --enable_warnings
- create table table_25345_a (a int);
- create table table_25345_b (b int);
- delimiter ||;
- create procedure proc_25345()
- begin
- declare c1 cursor for select a from table_25345_a;
- declare c2 cursor for select b from table_25345_b;
- select 1 as result;
- end ||
- create function func_25345() returns int(11)
- begin
- call proc_25345();
- return 1;
- end ||
- create function func_25345_b() returns int(11)
- begin
- declare c1 cursor for select a from table_25345_a;
- declare c2 cursor for select b from table_25345_b;
- return 1;
- end ||
- delimiter ;||
- call proc_25345();
- --error ER_SP_NO_RETSET
- select func_25345();
- select func_25345_b();
- drop table table_25345_a;
- call proc_25345();
- --error ER_SP_NO_RETSET
- select func_25345();
- select func_25345_b();
- drop table table_25345_b;
- drop procedure proc_25345;
- drop function func_25345;
- drop function func_25345_b;
- #
- # 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;
- #
- # Bug#26503 (Illegal SQL exception handler code causes the server to crash)
- #
- delimiter //;
- --error ER_SP_LILABEL_MISMATCH
- create procedure proc_26503_error_1()
- begin
- retry:
- repeat
- begin
- declare continue handler for sqlexception
- begin
- iterate retry;
- end
- select "do something";
- end
- until true end repeat retry;
- end//
- --error ER_SP_LILABEL_MISMATCH
- create procedure proc_26503_error_2()
- begin
- retry:
- repeat
- begin
- declare continue handler for sqlexception
- iterate retry;
- select "do something";
- end
- until true end repeat retry;
- end//
- --error ER_SP_LILABEL_MISMATCH
- create procedure proc_26503_error_3()
- begin
- retry:
- repeat
- begin
- declare continue handler for sqlexception
- begin
- leave retry;
- end
- select "do something";
- end
- until true end repeat retry;
- end//
- --error ER_SP_LILABEL_MISMATCH
- create procedure proc_26503_error_4()
- begin
- retry:
- repeat
- begin
- declare continue handler for sqlexception
- leave retry;
- select "do something";
- end
- until true end repeat retry;
- end//
- delimiter ;//
- #
- # Bug#28360 (RENAME DATABASE destroys routines)
- #
- --disable_warnings
- drop procedure if exists proc_28360;
- drop function if exists func_28360;
- --enable_warnings
- delimiter //;
- --error ER_SP_NO_DROP_SP
- CREATE PROCEDURE proc_28360()
- BEGIN
- ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
- END//
- --error ER_SP_NO_DROP_SP
- CREATE FUNCTION func_28360() RETURNS int
- BEGIN
- ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
- RETURN 0;
- END//
- delimiter ;//
- #
- # Bug#29223 declare cursor c for SHOW .....
- #
- --disable_warnings
- DROP PROCEDURE IF EXISTS p1;
- --enable_warnings
- --delimiter |
- --error ER_PARSE_ERROR
- CREATE PROCEDURE p1()
- BEGIN
- DECLARE c char(100);
- DECLARE cur1 CURSOR FOR SHOW TABLES;
- OPEN cur1;
- FETCH cur1 INTO c;
- select c;
- CLOSE cur1;
- END|
- --delimiter ;
- #
- # Bug#29816 Syntactically wrong query fails with misleading error message
- #
- --disable_warnings
- DROP DATABASE IF EXISTS mysqltest;
- --enable_warnings
- CREATE DATABASE mysqltest;
- USE mysqltest;
- DROP DATABASE mysqltest;
- # Both ER_SP_DOES_NOT_EXIST and ER_PARSE_ERROR are valid here,
- # the result is implementation dependent:
- # See Bug#29816 for details
- --error ER_SP_DOES_NOT_EXIST
- SELECT inexistent(), 1 + ,;
- --error ER_SP_DOES_NOT_EXIST
- SELECT inexistent();
- --error ER_PARSE_ERROR
- SELECT .inexistent();
- --error ER_PARSE_ERROR
- SELECT ..inexistent();
- USE test;
- #
- # Bug#30904 SET PASSWORD statement is non-transactional
- #
- delimiter |;
- --error ER_SP_CANT_SET_AUTOCOMMIT
- create function f1() returns int
- begin
- set @test = 1, password = password('foo');
- return 1;
- end|
- --error ER_SP_CANT_SET_AUTOCOMMIT
- create trigger t1
- before insert on t2 for each row set password = password('foo');|
- delimiter ;|
- #
- # Bug#30882 Dropping a temporary table inside a stored function may cause a server crash
- #
- --disable_warnings
- drop function if exists f1;
- drop function if exists f2;
- drop table if exists t1, t2;
- --enable_warnings
- delimiter |;
- create function f1() returns int
- begin
- drop temporary table t1;
- return 1;
- end|
- delimiter ;|
- --error ER_CANT_REOPEN_TABLE
- create temporary table t1 as select f1();
- delimiter |;
- create function f2() returns int
- begin
- create temporary table t2 as select f1();
- return 1;
- end|
- delimiter ;|
- --error ER_CANT_REOPEN_TABLE
- create temporary table t1 as select f2();
- drop function f1;
- drop function f2;
- delimiter |;
- create function f1() returns int
- begin
- drop temporary table t2,t1;
- return 1;
- end|
- create function f2() returns int
- begin
- create temporary table t2 as select f1();
- return 1;
- end|
- delimiter ;|
- --error ER_CANT_REOPEN_TABLE
- create temporary table t1 as select f2();
- drop function f1;
- drop function f2;
- create temporary table t2(a int);
- select * from t2;
- delimiter |;
- create function f2() returns int
- begin
- drop temporary table t2;
- return 1;
- end|
- delimiter ;|
- select f2();
- drop function f2;
- --error ER_BAD_TABLE_ERROR
- drop table t2;
- --echo End of 5.1 tests
- #
- # Bug#33983 (Stored Procedures: wrong end <label> syntax is accepted)
- #
- --disable_warnings
- drop procedure if exists proc_33983_a;
- drop procedure if exists proc_33983_b;
- drop procedure if exists proc_33983_c;
- drop procedure if exists proc_33983_d;
- --enable_warnings
- delimiter |;
- --error ER_SP_LABEL_MISMATCH
- create procedure proc_33983_a()
- begin
- label1:
- begin
- label2:
- begin
- select 1;
- end label1;
- end;
- end|
- --error ER_SP_LABEL_MISMATCH
- create procedure proc_33983_b()
- begin
- label1:
- repeat
- label2:
- repeat
- select 1;
- until FALSE end repeat label1;
- until FALSE end repeat;
- end|
- --error ER_SP_LABEL_MISMATCH
- create procedure proc_33983_c()
- begin
- label1:
- while TRUE do
- label2:
- while TRUE do
- select 1;
- end while label1;
- end while;
- end|
- --error ER_SP_LABEL_MISMATCH
- create procedure proc_33983_d()
- begin
- label1:
- loop
- label2:
- loop
- select 1;
- end loop label1;
- end loop;
- end|
- CREATE TABLE t1 (a INT)|
- INSERT INTO t1 VALUES (1),(2)|
- CREATE PROCEDURE p1(a INT) BEGIN END|
- --error ER_SUBQUERY_NO_1_ROW
- CALL p1((SELECT * FROM t1))|
- DROP PROCEDURE IF EXISTS p1|
- DROP TABLE t1|
- delimiter ;|
- #
- # Bug#21801: SQL exception handlers and warnings
- #
- --disable_warnings
- drop procedure if exists p1;
- --enable_warnings
- delimiter |;
- create procedure p1()
- begin
- create table t1 (a int) type=MyISAM;
- drop table t1;
- end|
- delimiter ;|
- call p1();
- call p1();
- drop procedure p1;
- #
- # Bug#8759 (Stored Procedures: SQLSTATE '00000' should be illegal)
- #
- --disable_warnings
- drop procedure if exists proc_8759;
- --enable_warnings
- delimiter $$;
- --error ER_SP_BAD_SQLSTATE
- create procedure proc_8759()
- begin
- declare should_be_illegal condition for sqlstate '00000';
- declare continue handler for should_be_illegal set @x=0;
- end$$
- --error ER_SP_BAD_SQLSTATE
- create procedure proc_8759()
- begin
- declare continue handler for sqlstate '00000' set @x=0;
- end$$
- delimiter ;$$
- #
- # 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...
- #
- # CLEANUP and RESTORE
- #
- LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc;
- remove_file $MYSQLTEST_VARDIR/tmp/proc.txt;
- #
- # Bug #38159: Function parsing problem generates misleading error message
- #
- CREATE TABLE t1 (a INT, b INT);
- INSERT INTO t1 VALUES (1,1), (2,2);
- --error ER_FUNC_INEXISTENT_NAME_COLLISION
- SELECT MAX (a) FROM t1 WHERE b = 999999;
- SELECT AVG (a) FROM t1 WHERE b = 999999;
- --error ER_SP_DOES_NOT_EXIST
- SELECT non_existent (a) FROM t1 WHERE b = 999999;
- DROP TABLE t1;
- --echo #
- --echo # Bug #47788: Crash in TABLE_LIST::hide_view_error on UPDATE + VIEW +
- --echo # SP + MERGE + ALTER
- --echo #
- CREATE TABLE t1 (pk INT, b INT, KEY (b));
- CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1;
- CREATE PROCEDURE p1 (a int) UPDATE IGNORE v1 SET b = a;
- --error ER_NON_UPDATABLE_TABLE
- CALL p1(5);
- ALTER TABLE t1 CHANGE COLUMN b b2 INT;
- --error ER_VIEW_INVALID
- CALL p1(7);
- DROP PROCEDURE p1;
- DROP VIEW v1;
- DROP TABLE t1;
- --echo #
- --echo # Bug#12428824 - PARSER STACK OVERFLOW AND CRASH IN SP_ADD_USED_ROUTINE
- --echo # WITH OBSCURE QUERY
- --echo #
- --error ER_TOO_LONG_IDENT
- SELECT very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999();
- --error ER_TOO_LONG_IDENT
- CALL very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999();
- --error ER_WRONG_DB_NAME
- SELECT very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_func();
- --error ER_WRONG_DB_NAME
- CALL very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_proc();
- --error ER_TOO_LONG_IDENT
- SELECT db_name.very_long_fn_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999();
- --error ER_TOO_LONG_IDENT
- CALL db_name.very_long_pr_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999();
- --echo End of 5.1 tests