sp-error.result 6.9 KB
Newer Older
1
delete from mysql.proc;
2
create procedure syntaxerror(t int);
3
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
4
create procedure syntaxerror(t int);
5
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
6
create procedure syntaxerror(t int);
7
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
8 9 10 11 12 13
create procedure proc1()
set @x = 42;
create function func1() returns int
return 42;
create procedure foo()
create procedure bar() set @x=3;
14
ERROR 2F003: Can't create a PROCEDURE from within another stored routine
15 16
create procedure foo()
create function bar() returns double return 2.3;
17
ERROR 2F003: Can't create a FUNCTION from within another stored routine
18 19
create procedure proc1()
set @x = 42;
20
ERROR 42000: PROCEDURE proc1 already exists
21 22
create function func1() returns int
return 42;
23
ERROR 42000: FUNCTION func1 already exists
24 25
drop procedure proc1;
drop function func1;
26
alter procedure foo;
27
ERROR 42000: PROCEDURE foo does not exist
28
alter function foo;
29
ERROR 42000: FUNCTION foo does not exist
30
drop procedure foo;
31
ERROR 42000: PROCEDURE foo does not exist
32
drop function foo;
33
ERROR 42000: FUNCTION foo does not exist
34
call foo();
35
ERROR 42000: PROCEDURE foo does not exist
36 37
drop procedure if exists foo;
Warnings:
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
38
Warning	1288	PROCEDURE foo does not exist
39
show create procedure foo;
40
ERROR 42000: PROCEDURE foo does not exist
41 42 43 44
create procedure foo()
foo: loop
leave bar;
end loop;
45
ERROR 42000: LEAVE with no matching label: bar
46 47 48 49
create procedure foo()
foo: loop
iterate bar;
end loop;
50
ERROR 42000: ITERATE with no matching label: bar
51
create procedure foo()
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
52 53 54
foo: begin
iterate foo;
end;
55
ERROR 42000: ITERATE with no matching label: foo
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
56
create procedure foo()
57 58 59 60 61
foo: loop
foo: loop
set @x=2;
end loop foo;
end loop foo;
62
ERROR 42000: Redefining label foo
63 64 65 66
create procedure foo()
foo: loop
set @x=2;
end loop bar;
67
ERROR 42000: End-label bar without match
68 69 70 71 72
create procedure foo(out x int)
begin
declare y int;
set x = y;
end;
73
Warnings:
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
74
Warning	1294	Referring to uninitialized variable y
75
drop procedure foo;
76 77 78 79 80 81
create procedure foo()
begin
select name from mysql.proc;
select type from mysql.proc;
end;
call foo();
82
ERROR 0A000: SELECT in a stored procedure must have INTO
83
drop procedure foo;
84 85
create procedure foo()
return 42;
86
ERROR 42000: RETURN is only allowed in a FUNCTION
87 88 89 90 91 92
create function foo() returns int
begin
declare x int;
select max(c) into x from test.t;
return x;
end;
93
ERROR 0A000: Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION
94 95 96 97 98
create procedure p(x int)
insert into test.t1 values (x);
create function f(x int) returns int
return x+42;
call p();
99
ERROR 42000: Wrong number of arguments for PROCEDURE p, expected 1, got 0
100
call p(1, 2);
101
ERROR 42000: Wrong number of arguments for PROCEDURE p, expected 1, got 2
102
select f();
103
ERROR 42000: Wrong number of arguments for FUNCTION f, expected 1, got 0
104
select f(1, 2);
105
ERROR 42000: Wrong number of arguments for FUNCTION f, expected 1, got 2
106 107
drop procedure p;
drop function f;
108 109 110 111 112 113 114 115 116 117 118
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;
119
ERROR 42000: Undefined CONDITION: foo
120 121 122 123 124 125 126 127 128 129 130 131
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;
132
ERROR 42000: Undefined CONDITION: bar
133 134 135 136 137
create function f(val int) returns int
begin
declare x int;
set x = val+3;
end;
138
ERROR 42000: No RETURN found in FUNCTION f
139 140 141 142 143 144 145 146 147
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);
148
ERROR 2F005: FUNCTION f ended without RETURN
149
drop function f;
150 151 152 153 154 155
create procedure p()
begin
declare c cursor for insert into test.t1 values ("foo", 42);
open c;
close c;
end;
156
ERROR 42000: Cursor statement must be a SELECT
157 158 159 160 161 162 163
create procedure p()
begin
declare x int;
declare c cursor for select * into x from test.t limit 1;
open c;
close c;
end;
164
ERROR 42000: Cursor SELECT must not have INTO
165 166 167 168 169 170
create procedure p()
begin
declare c cursor for select * from test.t;
open cc;
close c;
end;
171
ERROR 42000: Undefined CURSOR: cc
172 173 174 175 176 177 178 179 180 181
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();
182
ERROR 24000: Cursor is already open
183 184 185 186 187 188 189 190 191
drop procedure p;
create procedure p()
begin
declare c cursor for select * from test.t1;
open c;
close c;
close c;
end;
call p();
192
ERROR 24000: Cursor is not open
193
drop procedure p;
194
alter procedure bar3 sql security invoker;
195
ERROR 42000: PROCEDURE bar3 does not exist
196 197 198
alter procedure bar3 name
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;
ERROR 42000: Identifier name 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' is too long
199 200 201 202 203 204 205 206 207 208 209 210
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 c cursor for select * from t1;
declare x int;
open c;
fetch c into x, y;
close c;
end;
211
ERROR 42000: Undeclared variable: y
212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235
create procedure p()
begin
declare c cursor for select * from t1;
declare x int;
open c;
fetch c into x;
close c;
end;
call p();
ERROR HY000: Wrong number of FETCH variables
drop procedure p;
create procedure p()
begin
declare c cursor for select * from t1;
declare x int;
declare y float;
declare z int;
open c;
fetch c into x, y, z;
close c;
end;
call p();
ERROR HY000: Wrong number of FETCH variables
drop procedure p;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
236 237 238
create procedure p(in x int, x char(10))
begin
end;
239
ERROR 42000: Duplicate parameter: x
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
240 241 242
create function p(x int, x char(10))
begin
end;
243
ERROR 42000: Duplicate parameter: x
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
244 245 246 247 248
create procedure p()
begin
declare x float;
declare x int;
end;
249
ERROR 42000: Duplicate variable: x
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
250 251 252 253 254
create procedure p()
begin
declare c condition for 1064;
declare c condition for 1065;
end;
255
ERROR 42000: Duplicate condition: c
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
256 257 258 259 260
create procedure p()
begin
declare c cursor for select * from t1;
declare c cursor for select field from t1;
end;
261
ERROR 42000: Duplicate cursor: c
262 263 264 265 266 267 268 269 270 271 272
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
273 274 275 276 277 278 279 280 281 282
create procedure bug336(id char(16))
begin
declare x int;
set x = (select sum(t.data) from test.t2 t);
end;
ERROR 0A000: Subselect value not supported
create function bug1654()
returns int
return (select sum(t.data) from test.t2 t);
ERROR 0A000: Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION
283
drop table t1;