sp-error.result 10.4 KB
Newer Older
1
delete from mysql.proc;
unknown's avatar
unknown committed
2
create procedure syntaxerror(t int)|
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
4
create procedure syntaxerror(t int)|
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
6
create procedure syntaxerror(t int)|
unknown's avatar
unknown committed
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 14 15 16
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|
17
create procedure proc1()
unknown's avatar
unknown committed
18
set @x = 42|
19
create function func1() returns int
unknown's avatar
unknown committed
20
return 42|
21
create procedure foo()
unknown's avatar
unknown committed
22
create procedure bar() set @x=3|
23
ERROR 2F003: Can't create a PROCEDURE from within another stored routine
24
create procedure foo()
unknown's avatar
unknown committed
25
create function bar() returns double return 2.3|
26
ERROR 2F003: Can't create a FUNCTION from within another stored routine
27
create procedure proc1()
unknown's avatar
unknown committed
28
set @x = 42|
29
ERROR 42000: PROCEDURE proc1 already exists
30
create function func1() returns int
unknown's avatar
unknown committed
31
return 42|
32
ERROR 42000: FUNCTION func1 already exists
unknown's avatar
unknown committed
33 34 35
drop procedure proc1|
drop function func1|
alter procedure foo|
36
ERROR 42000: PROCEDURE test.foo does not exist
unknown's avatar
unknown committed
37
alter function foo|
38
ERROR 42000: FUNCTION test.foo does not exist
unknown's avatar
unknown committed
39
drop procedure foo|
40
ERROR 42000: PROCEDURE test.foo does not exist
unknown's avatar
unknown committed
41
drop function foo|
42
ERROR 42000: FUNCTION test.foo does not exist
unknown's avatar
unknown committed
43
call foo()|
44
ERROR 42000: PROCEDURE test.foo does not exist
unknown's avatar
unknown committed
45
drop procedure if exists foo|
46
Warnings:
unknown's avatar
unknown committed
47
Warning	1304	PROCEDURE foo does not exist
unknown's avatar
unknown committed
48
show create procedure foo|
49
ERROR 42000: PROCEDURE foo does not exist
50 51 52
create procedure foo()
foo: loop
leave bar;
unknown's avatar
unknown committed
53
end loop|
54
ERROR 42000: LEAVE with no matching label: bar
55 56 57
create procedure foo()
foo: loop
iterate bar;
unknown's avatar
unknown committed
58
end loop|
59
ERROR 42000: ITERATE with no matching label: bar
60
create procedure foo()
unknown's avatar
unknown committed
61 62
foo: begin
iterate foo;
unknown's avatar
unknown committed
63
end|
64
ERROR 42000: ITERATE with no matching label: foo
unknown's avatar
unknown committed
65
create procedure foo()
66 67 68 69 70
begin
goto foo;
end|
ERROR 42000: GOTO with no matching label: foo
create procedure foo()
71 72 73 74
foo: loop
foo: loop
set @x=2;
end loop foo;
unknown's avatar
unknown committed
75
end loop foo|
76
ERROR 42000: Redefining label foo
77 78 79
create procedure foo()
foo: loop
set @x=2;
unknown's avatar
unknown committed
80
end loop bar|
81
ERROR 42000: End-label bar without match
82 83 84 85
create procedure foo(out x int)
begin
declare y int;
set x = y;
unknown's avatar
unknown committed
86
end|
87
Warnings:
unknown's avatar
unknown committed
88
Warning	1310	Referring to uninitialized variable y
unknown's avatar
unknown committed
89
drop procedure foo|
90
create procedure foo()
unknown's avatar
unknown committed
91
return 42|
92
ERROR 42000: RETURN is only allowed in a FUNCTION
93 94 95 96 97
create function foo() returns int
begin
declare x int;
select max(c) into x from test.t;
return x;
unknown's avatar
unknown committed
98
end|
99
ERROR 0A000: Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION
unknown's avatar
unknown committed
100
create procedure p(x int)
unknown's avatar
unknown committed
101
insert into test.t1 values (x)|
unknown's avatar
unknown committed
102
create function f(x int) returns int
unknown's avatar
unknown committed
103 104
return x+42|
call p()|
unknown's avatar
unknown committed
105
ERROR 42000: Incorrect number of arguments for PROCEDURE p; expected 1, got 0
unknown's avatar
unknown committed
106
call p(1, 2)|
unknown's avatar
unknown committed
107
ERROR 42000: Incorrect number of arguments for PROCEDURE p; expected 1, got 2
unknown's avatar
unknown committed
108
select f()|
unknown's avatar
unknown committed
109
ERROR 42000: Incorrect number of arguments for FUNCTION f; expected 1, got 0
unknown's avatar
unknown committed
110
select f(1, 2)|
unknown's avatar
unknown committed
111
ERROR 42000: Incorrect number of arguments for FUNCTION f; expected 1, got 2
unknown's avatar
unknown committed
112 113
drop procedure p|
drop function f|
114 115 116 117 118 119 120 121 122 123
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;
unknown's avatar
unknown committed
124
end|
125
ERROR 42000: Undefined CONDITION: foo
126 127 128 129 130 131 132 133 134 135 136
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;
unknown's avatar
unknown committed
137
end|
138
ERROR 42000: Undefined CONDITION: bar
139 140 141 142
create function f(val int) returns int
begin
declare x int;
set x = val+3;
unknown's avatar
unknown committed
143
end|
144
ERROR 42000: No RETURN found in FUNCTION f
145 146 147 148 149 150 151
create function f(val int) returns int
begin
declare x int;
set x = val+3;
if x < 4 then
return x;
end if;
unknown's avatar
unknown committed
152 153
end|
select f(10)|
154
ERROR 2F005: FUNCTION f ended without RETURN
unknown's avatar
unknown committed
155
drop function f|
156 157 158 159 160
create procedure p()
begin
declare c cursor for insert into test.t1 values ("foo", 42);
open c;
close c;
unknown's avatar
unknown committed
161
end|
162
ERROR 42000: Cursor statement must be a SELECT
163 164 165 166 167 168
create procedure p()
begin
declare x int;
declare c cursor for select * into x from test.t limit 1;
open c;
close c;
unknown's avatar
unknown committed
169
end|
170
ERROR 42000: Cursor SELECT must not have INTO
171 172 173 174 175
create procedure p()
begin
declare c cursor for select * from test.t;
open cc;
close c;
unknown's avatar
unknown committed
176
end|
177
ERROR 42000: Undefined CURSOR: cc
unknown's avatar
unknown committed
178 179
drop table if exists t1|
create table t1 (val int)|
180 181 182 183 184 185
create procedure p()
begin
declare c cursor for select * from test.t1;
open c;
open c;
close c;
unknown's avatar
unknown committed
186 187
end|
call p()|
188
ERROR 24000: Cursor is already open
unknown's avatar
unknown committed
189
drop procedure p|
190 191 192 193 194 195
create procedure p()
begin
declare c cursor for select * from test.t1;
open c;
close c;
close c;
unknown's avatar
unknown committed
196 197
end|
call p()|
198
ERROR 24000: Cursor is not open
unknown's avatar
unknown committed
199 200
drop procedure p|
alter procedure bar3 sql security invoker|
201
ERROR 42000: PROCEDURE test.bar3 does not exist
unknown's avatar
unknown committed
202
alter procedure bar3 name
unknown's avatar
unknown committed
203
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA|
unknown's avatar
unknown committed
204
ERROR 42000: Identifier name 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' is too long
unknown's avatar
unknown committed
205 206 207 208
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)|
209 210 211
create procedure p()
begin
declare x int;
212
declare c cursor for select * from t1;
213 214 215
open c;
fetch c into x, y;
close c;
unknown's avatar
unknown committed
216
end|
217
ERROR 42000: Undeclared variable: y
218 219 220
create procedure p()
begin
declare x int;
221
declare c cursor for select * from t1;
222 223 224
open c;
fetch c into x;
close c;
unknown's avatar
unknown committed
225 226
end|
call p()|
unknown's avatar
unknown committed
227
ERROR HY000: Incorrect number of FETCH variables
unknown's avatar
unknown committed
228
drop procedure p|
229 230 231 232 233
create procedure p()
begin
declare x int;
declare y float;
declare z int;
234
declare c cursor for select * from t1;
235 236 237
open c;
fetch c into x, y, z;
close c;
unknown's avatar
unknown committed
238 239
end|
call p()|
unknown's avatar
unknown committed
240
ERROR HY000: Incorrect number of FETCH variables
unknown's avatar
unknown committed
241
drop procedure p|
unknown's avatar
unknown committed
242 243
create procedure p(in x int, x char(10))
begin
unknown's avatar
unknown committed
244
end|
245
ERROR 42000: Duplicate parameter: x
unknown's avatar
unknown committed
246 247
create function p(x int, x char(10))
begin
unknown's avatar
unknown committed
248
end|
249
ERROR 42000: Duplicate parameter: x
unknown's avatar
unknown committed
250 251 252 253
create procedure p()
begin
declare x float;
declare x int;
unknown's avatar
unknown committed
254
end|
255
ERROR 42000: Duplicate variable: x
unknown's avatar
unknown committed
256 257 258 259
create procedure p()
begin
declare c condition for 1064;
declare c condition for 1065;
unknown's avatar
unknown committed
260
end|
261
ERROR 42000: Duplicate condition: c
unknown's avatar
unknown committed
262 263 264 265
create procedure p()
begin
declare c cursor for select * from t1;
declare c cursor for select field from t1;
unknown's avatar
unknown committed
266
end|
267
ERROR 42000: Duplicate cursor: c
268
create procedure u()
269
use sptmp|
270
ERROR 42000: USE is not allowed in a stored procedure
271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
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
291 292 293 294 295
create procedure bug1965()
begin
declare c cursor for select val from t1 order by valname;
open c;
close c;
unknown's avatar
unknown committed
296 297
end|
call bug1965()|
298
ERROR 42S22: Unknown column 'valname' in 'order clause'
unknown's avatar
unknown committed
299 300
drop procedure bug1965|
select 1 into a|
301
ERROR 42000: Undeclared variable: a
302 303 304 305
create procedure bug336(id char(16))
begin
declare x int;
set x = (select sum(t.data) from test.t2 t);
unknown's avatar
unknown committed
306
end|
307 308 309
ERROR 0A000: Subselect value not supported
create function bug1654()
returns int
unknown's avatar
unknown committed
310
return (select sum(t.data) from test.t2 t)|
311
ERROR 0A000: Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION
312
drop table if exists t3|
unknown's avatar
unknown committed
313
create table t3 (column_1_0 int)|
314
create procedure bug1653()
unknown's avatar
unknown committed
315 316
update t3 set column_1 = 0|
call bug1653()|
317
ERROR 42S22: Unknown column 'column_1' in 'field list'
unknown's avatar
unknown committed
318 319 320 321 322
drop table t3|
create table t3 (column_1 int)|
call bug1653()|
drop procedure bug1653|
drop table t3|
323 324 325 326 327 328 329 330 331
create procedure bug2259()
begin
declare v1 int;
declare c1 cursor for select s1 from t10;
fetch c1 into v1;
end|
call bug2259()|
ERROR 24000: Cursor is not open
drop procedure bug2259|
332 333 334 335 336 337 338 339 340
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|
341
drop procedure bug2272|
342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357
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|
358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378
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|
379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
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|
403 404
create procedure nodb.bug3339() begin end|
ERROR 42000: Unknown database 'nodb'
405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420
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|
421 422 423 424
create procedure bug4344() drop procedure bug4344|
ERROR HY000: Can't drop a PROCEDURE from within another stored routine
create procedure bug4344() drop function bug4344|
ERROR HY000: Can't drop a FUNCTION from within another stored routine
unknown's avatar
unknown committed
425
drop table t1|