drop table if exists t1, t2; delete from mysql.proc; create procedure syntaxerror(t int)| ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 create procedure syntaxerror(t int)| ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 create procedure syntaxerror(t int)| ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 drop table if exists t3| create table t3 ( x int )| insert into t3 values (2), (3)| create procedure bad_into(out param int) select x from t3 into param| call bad_into(@x)| ERROR 42000: Result consisted of more than one row drop procedure bad_into| drop table t3| create procedure proc1() set @x = 42| create function func1() returns int return 42| create procedure foo() create procedure bar() set @x=3| ERROR 2F003: Can't create a PROCEDURE from within another stored routine create procedure foo() create function bar() returns double return 2.3| ERROR 2F003: Can't create a FUNCTION from within another stored routine create procedure proc1() set @x = 42| ERROR 42000: PROCEDURE proc1 already exists create function func1() returns int return 42| ERROR 42000: FUNCTION func1 already exists drop procedure proc1| drop function func1| alter procedure foo| ERROR 42000: PROCEDURE test.foo does not exist alter function foo| ERROR 42000: FUNCTION test.foo does not exist drop procedure foo| ERROR 42000: PROCEDURE test.foo does not exist drop function foo| ERROR 42000: FUNCTION test.foo does not exist call foo()| ERROR 42000: PROCEDURE test.foo does not exist drop procedure if exists foo| Warnings: Note 1305 PROCEDURE foo does not exist show create procedure foo| ERROR 42000: PROCEDURE foo does not exist show create function foo| ERROR 42000: FUNCTION foo does not exist create procedure foo() foo: loop leave bar; end loop| ERROR 42000: LEAVE with no matching label: bar create procedure foo() foo: loop iterate bar; end loop| ERROR 42000: ITERATE with no matching label: bar create procedure foo() foo: begin iterate foo; end| ERROR 42000: ITERATE with no matching label: foo create procedure foo() foo: loop foo: loop set @x=2; end loop foo; end loop foo| ERROR 42000: Redefining label foo create procedure foo() foo: loop set @x=2; end loop bar| ERROR 42000: End-label bar without match create procedure foo() return 42| ERROR 42000: RETURN is only allowed in a FUNCTION create procedure p(x int) set @x = x| create function f(x int) returns int return x+42| call p()| ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 0 call p(1, 2)| ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 2 select f()| ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 0 select f(1, 2)| ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 2 drop procedure p| drop function f| 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 42000: Undefined CONDITION: foo 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 42000: Undefined CONDITION: bar create function f(val int) returns int begin declare x int; set x = val+3; end| ERROR 42000: No RETURN found in FUNCTION f create function f(val int) returns int begin declare x int; set x = val+3; if x < 4 then return x; end if; end| select f(10)| ERROR 2F005: FUNCTION f ended without RETURN drop function f| create procedure p() begin declare c cursor for insert into test.t1 values ("foo", 42); open c; close c; end| ERROR 42000: Cursor statement must be a SELECT 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 42000: Cursor SELECT must not have INTO create procedure p() begin declare c cursor for select * from test.t; open cc; close c; end| ERROR 42000: Undefined CURSOR: cc drop table if exists t1| create table t1 (val int)| create procedure p() begin declare c cursor for select * from test.t1; open c; open c; close c; end| call p()| ERROR 24000: Cursor is already open drop procedure p| create procedure p() begin declare c cursor for select * from test.t1; open c; close c; close c; end| call p()| ERROR 24000: Cursor is not open drop procedure p| alter procedure bar3 sql security invoker| ERROR 42000: PROCEDURE test.bar3 does not exist drop table t1| drop table if exists t1| create table t1 (val int, x float)| insert into t1 values (42, 3.1), (19, 1.2)| create procedure p() begin declare x int; declare c cursor for select * from t1; open c; fetch c into x, y; close c; end| ERROR 42000: Undeclared variable: y create procedure p() begin declare x int; declare c cursor for select * from t1; open c; fetch c into x; close c; end| call p()| ERROR HY000: Incorrect number of FETCH variables 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| call p()| ERROR HY000: Incorrect number of FETCH variables drop procedure p| create procedure p(in x int, x char(10)) begin end| ERROR 42000: Duplicate parameter: x create function p(x int, x char(10)) begin end| ERROR 42000: Duplicate parameter: x create procedure p() begin declare x float; declare x int; end| ERROR 42000: Duplicate variable: x create procedure p() begin declare c condition for 1064; declare c condition for 1065; end| ERROR 42000: Duplicate condition: c create procedure p() begin declare c cursor for select * from t1; declare c cursor for select field from t1; end| ERROR 42000: Duplicate cursor: c create procedure u() use sptmp| ERROR 42000: USE is not allowed in a stored procedure create procedure p() begin declare c cursor for select * from t1; declare x int; end| ERROR 42000: Variable or condition declaration after cursor or handler declaration create procedure p() begin declare x int; declare continue handler for sqlstate '42S99' set x = 1; declare foo condition for sqlstate '42S99'; end| ERROR 42000: Variable or condition declaration after cursor or handler declaration create procedure p() begin declare x int; declare continue handler for sqlstate '42S99' set x = 1; declare c cursor for select * from t1; end| ERROR 42000: Cursor declaration after handler declaration drop procedure if exists p| 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| call p(@tmp_x, @tmp_y, @tmp_z)| select @tmp_x, @tmp_y, @tmp_z| @tmp_x @tmp_y @tmp_z 42 45 87 call p(42, 43, @tmp_z)| ERROR 42000: OUT or INOUT argument 2 for routine test.p is not a variable call p(42, @tmp_y, 43)| ERROR 42000: OUT or INOUT argument 3 for routine test.p is not a variable drop procedure p| create procedure bug1965() begin declare c cursor for select val from t1 order by valname; open c; close c; end| call bug1965()| ERROR 42S22: Unknown column 'valname' in 'order clause' drop procedure bug1965| select 1 into a| ERROR 42000: Undeclared variable: a drop table if exists t3| create table t3 (column_1_0 int)| create procedure bug1653() update t3 set column_1 = 0| call bug1653()| ERROR 42S22: Unknown column 'column_1' in 'field list' drop table t3| create table t3 (column_1 int)| call bug1653()| drop procedure bug1653| drop table t3| create procedure bug2259() begin declare v1 int; declare c1 cursor for select s1 from t1; fetch c1 into v1; end| call bug2259()| ERROR 24000: Cursor is not open drop procedure bug2259| create procedure bug2272() begin declare v int; update t1 set v = 42; end| insert into t1 values (666, 51.3)| call bug2272()| ERROR 42S22: Unknown column 'v' in 'field list' delete from t1| drop procedure bug2272| 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| call bug2329_1()| ERROR 42S22: Unknown column 'v' in 'field list' call bug2329_2()| ERROR 42S22: Unknown column 'v' in 'field list' drop procedure bug2329_1| drop procedure bug2329_2| 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| select bug3287()| ERROR 20000: Case not found for CASE statement 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| call bug3287(2)| ERROR 20000: Case not found for CASE statement drop procedure bug3287| drop table if exists t3| 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| call bug3279(@x)| ERROR 23000: Duplicate entry '6' for key 1 select @x| @x 0 drop procedure bug3279| drop table t3| create procedure nodb.bug3339() begin end| ERROR 42000: Unknown database 'nodb' 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| call bug2653_1(1, @b)| ERROR 42S22: Unknown column 'aa' in 'order clause' call bug2653_2(2, @b)| ERROR 42S22: Unknown column 'aa' in 'order clause' drop procedure bug2653_1| drop procedure bug2653_2| create procedure bug4344() drop procedure bug4344| ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine create procedure bug4344() drop function bug4344| ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine drop procedure if exists bug3294| create procedure bug3294() begin declare continue handler for sqlexception drop table t5; drop table t5; drop table t5; end| create table t5 (x int)| call bug3294()| ERROR 42S02: Unknown table 't5' drop procedure bug3294| drop procedure if exists bug6807| create procedure bug6807() begin declare id int; set id = connection_id(); kill query id; select 'Not reached'; end| call bug6807()| ERROR 70100: Query execution was interrupted call bug6807()| ERROR 70100: Query execution was interrupted drop procedure bug6807| drop procedure if exists bug8776_1| drop procedure if exists bug8776_2| drop procedure if exists bug8776_3| drop procedure if exists bug8776_4| create procedure bug8776_1() begin declare continue handler for sqlstate '42S0200test' begin end; begin end; end| ERROR 42000: Bad SQLSTATE: '42S0200test' create procedure bug8776_2() begin declare continue handler for sqlstate '4200' begin end; begin end; end| ERROR 42000: Bad SQLSTATE: '4200' create procedure bug8776_3() begin declare continue handler for sqlstate '420000' begin end; begin end; end| ERROR 42000: Bad SQLSTATE: '420000' create procedure bug8776_4() begin declare continue handler for sqlstate '42x00' begin end; begin end; end| ERROR 42000: Bad SQLSTATE: '42x00' create procedure bug6600() check table t1| ERROR 0A000: CHECK is not allowed in stored procedures create procedure bug6600() lock table t1 read| ERROR 0A000: LOCK is not allowed in stored procedures create procedure bug6600() unlock table t1| ERROR 0A000: UNLOCK is not allowed in stored procedures drop procedure if exists bug9566| create procedure bug9566() begin select * from t1; end| lock table t1 read| call bug9566()| ERROR HY000: Table 'proc' was not locked with LOCK TABLES unlock tables| drop procedure bug9566| drop procedure if exists bug7299| 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| delete from t1| call bug7299()| ERROR 02000: No data to FETCH drop procedure bug7299| create procedure bug9073() begin declare continue handler for sqlexception select 1; declare continue handler for sqlexception select 2; end| ERROR 42000: Duplicate handler declared in the same block 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 42000: Duplicate handler declared in the same block 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 42000: Duplicate handler declared in the same block 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| ERROR 42000: Duplicate handler declared in the same block drop procedure if exists bug9073| 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| create procedure bug7047() alter procedure bug7047| ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine create function bug7047() returns int begin alter function bug7047; return 0; end| ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine create function bug8408() returns int begin select * from t1; return 0; end| ERROR 0A000: Not allowed to return a result set from a function create function bug8408() returns int begin show warnings; return 0; end| ERROR 0A000: Not allowed to return a result set from a function create function bug8408(a int) returns int begin declare b int; select b; return b; end| ERROR 0A000: Not allowed to return a result set from a function drop function if exists bug8408_f| drop procedure if exists bug8408_p| create function bug8408_f() returns int begin call bug8408_p(); return 0; end| create procedure bug8408_p() select * from t1| call bug8408_p()| val x select bug8408_f()| ERROR 0A000: PROCEDURE test.bug8408_p can't return a result set in the given context drop procedure bug8408_p| drop function bug8408_f| 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| val x bug8408() 2 2.7 3 3 3.14 3 7 7 3 drop function bug8408| delete from t1| drop procedure if exists bug10537| create procedure bug10537() load data local infile '/tmp/somefile' into table t1| ERROR 0A000: LOAD DATA is not allowed in stored procedures drop function if exists bug8409| create function bug8409() returns int begin flush tables; return 5; end| ERROR 0A000: FLUSH is not allowed in stored procedures create procedure bug9529_90123456789012345678901234567890123456789012345678901234567890() begin end| ERROR 42000: Identifier name 'bug9529_90123456789012345678901234567890123456789012345678901234567890' is too long drop procedure if exists bug10969| create procedure bug10969() begin declare s1 int default 0; select default(s1) from t30; end| ERROR 42000: Incorrect column name 's1' create procedure bug10969() begin declare s1 int default 0; select default(t30.s1) from t30; end| drop procedure bug10969| drop table t1| prepare stmt from "select 1"; create procedure p() deallocate prepare stmt; ERROR 0A000: DEALLOCATE is not allowed in stored procedures create function f() returns int begin deallocate prepare stmt; ERROR 0A000: DEALLOCATE is not allowed in stored procedures create procedure p() prepare stmt from "select 1"; ERROR 0A000: PREPARE is not allowed in stored procedures create function f() returns int begin prepare stmt from "select 1"; ERROR 0A000: PREPARE is not allowed in stored procedures create procedure p() execute stmt; ERROR 0A000: EXECUTE is not allowed in stored procedures create function f() returns int begin execute stmt; ERROR 0A000: EXECUTE is not allowed in stored procedures deallocate prepare stmt; create table t1(f1 int); create table t2(f1 int); 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| call SP001(); TEMP_SUM 0 drop procedure SP001; drop table t1, t2;