sp-error.result 18.8 KB
Newer Older
1
drop table if exists t1, t2;
2
delete from mysql.proc;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
3
create procedure syntaxerror(t int)|
monty@mysql.com's avatar
monty@mysql.com committed
4
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
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
5
create procedure syntaxerror(t int)|
monty@mysql.com's avatar
monty@mysql.com committed
6
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
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
7
create procedure syntaxerror(t int)|
monty@mysql.com's avatar
monty@mysql.com committed
8
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
9 10 11 12 13 14 15 16 17
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|
18
create procedure proc1()
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
19
set @x = 42|
20
create function func1() returns int
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
21
return 42|
22
create procedure foo()
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
23
create procedure bar() set @x=3|
24
ERROR 2F003: Can't create a PROCEDURE from within another stored routine
25
create procedure foo()
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
26
create function bar() returns double return 2.3|
27
ERROR 2F003: Can't create a FUNCTION from within another stored routine
28
create procedure proc1()
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
29
set @x = 42|
30
ERROR 42000: PROCEDURE proc1 already exists
31
create function func1() returns int
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
32
return 42|
33
ERROR 42000: FUNCTION func1 already exists
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
34 35 36
drop procedure proc1|
drop function func1|
alter procedure foo|
37
ERROR 42000: PROCEDURE test.foo does not exist
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
38
alter function foo|
39
ERROR 42000: FUNCTION test.foo does not exist
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
40
drop procedure foo|
41
ERROR 42000: PROCEDURE test.foo does not exist
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
42
drop function foo|
43
ERROR 42000: FUNCTION test.foo does not exist
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
44
call foo()|
45
ERROR 42000: PROCEDURE test.foo does not exist
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
46
drop procedure if exists foo|
47
Warnings:
48
Note	1305	PROCEDURE foo does not exist
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
49
show create procedure foo|
50
ERROR 42000: PROCEDURE foo does not exist
51 52
show create function foo|
ERROR 42000: FUNCTION foo does not exist
53 54 55
create procedure foo()
foo: loop
leave bar;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
56
end loop|
57
ERROR 42000: LEAVE with no matching label: bar
58 59 60
create procedure foo()
foo: loop
iterate bar;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
61
end loop|
62
ERROR 42000: ITERATE with no matching label: bar
63
create procedure foo()
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
64 65
foo: begin
iterate foo;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
66
end|
67
ERROR 42000: ITERATE with no matching label: foo
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
68
create procedure foo()
69 70 71 72
foo: loop
foo: loop
set @x=2;
end loop foo;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
73
end loop foo|
74
ERROR 42000: Redefining label foo
75 76 77
create procedure foo()
foo: loop
set @x=2;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
78
end loop bar|
79
ERROR 42000: End-label bar without match
80
create procedure foo()
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
81
return 42|
82
ERROR 42000: RETURN is only allowed in a FUNCTION
83
create procedure p(x int)
84
set @x = x|
85
create function f(x int) returns int
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
86 87
return x+42|
call p()|
88
ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 0
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
89
call p(1, 2)|
90
ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 2
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
91
select f()|
92
ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 0
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
93
select f(1, 2)|
94
ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 2
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
95 96
drop procedure p|
drop function f|
97 98 99 100 101 102 103 104 105 106
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;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
107
end|
108
ERROR 42000: Undefined CONDITION: foo
109 110 111 112 113 114 115 116 117 118 119
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;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
120
end|
121
ERROR 42000: Undefined CONDITION: bar
122 123 124 125
create function f(val int) returns int
begin
declare x int;
set x = val+3;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
126
end|
127
ERROR 42000: No RETURN found in FUNCTION f
128 129 130 131 132 133 134
create function f(val int) returns int
begin
declare x int;
set x = val+3;
if x < 4 then
return x;
end if;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
135 136
end|
select f(10)|
137
ERROR 2F005: FUNCTION f ended without RETURN
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
138
drop function f|
139 140 141 142 143
create procedure p()
begin
declare c cursor for insert into test.t1 values ("foo", 42);
open c;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
144
end|
145
ERROR 42000: Cursor statement must be a SELECT
146 147 148 149 150 151
create procedure p()
begin
declare x int;
declare c cursor for select * into x from test.t limit 1;
open c;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
152
end|
153
ERROR 42000: Cursor SELECT must not have INTO
154 155 156 157 158
create procedure p()
begin
declare c cursor for select * from test.t;
open cc;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
159
end|
160
ERROR 42000: Undefined CURSOR: cc
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
161 162
drop table if exists t1|
create table t1 (val int)|
163 164 165 166 167 168
create procedure p()
begin
declare c cursor for select * from test.t1;
open c;
open c;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
169 170
end|
call p()|
171
ERROR 24000: Cursor is already open
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
172
drop procedure p|
173 174 175 176 177 178
create procedure p()
begin
declare c cursor for select * from test.t1;
open c;
close c;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
179 180
end|
call p()|
181
ERROR 24000: Cursor is not open
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
182 183
drop procedure p|
alter procedure bar3 sql security invoker|
184
ERROR 42000: PROCEDURE test.bar3 does not exist
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
185 186 187 188
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)|
189 190 191
create procedure p()
begin
declare x int;
192
declare c cursor for select * from t1;
193 194 195
open c;
fetch c into x, y;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
196
end|
197
ERROR 42000: Undeclared variable: y
198 199 200
create procedure p()
begin
declare x int;
201
declare c cursor for select * from t1;
202 203 204
open c;
fetch c into x;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
205 206
end|
call p()|
207
ERROR HY000: Incorrect number of FETCH variables
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
208
drop procedure p|
209 210 211 212 213
create procedure p()
begin
declare x int;
declare y float;
declare z int;
214
declare c cursor for select * from t1;
215 216 217
open c;
fetch c into x, y, z;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
218 219
end|
call p()|
220
ERROR HY000: Incorrect number of FETCH variables
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
221
drop procedure p|
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
222 223
create procedure p(in x int, x char(10))
begin
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
224
end|
225
ERROR 42000: Duplicate parameter: x
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
226 227
create function p(x int, x char(10))
begin
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
228
end|
229
ERROR 42000: Duplicate parameter: x
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
230 231 232 233
create procedure p()
begin
declare x float;
declare x int;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
234
end|
235
ERROR 42000: Duplicate variable: x
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
236 237 238 239
create procedure p()
begin
declare c condition for 1064;
declare c condition for 1065;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
240
end|
241
ERROR 42000: Duplicate condition: c
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
242 243 244 245
create procedure p()
begin
declare c cursor for select * from t1;
declare c cursor for select field from t1;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
246
end|
247
ERROR 42000: Duplicate cursor: c
248
create procedure u()
249
use sptmp|
250
ERROR 0A000: USE is not allowed in stored procedures
251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270
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
271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288
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|
289 290 291 292 293 294 295 296 297 298 299 300 301
create procedure p() begin end|
lock table t1 read|
call p()|
unlock tables|
drop procedure p|
lock tables t1 read, mysql.proc write|
ERROR HY000: You can't combine write-locking of system 'mysql.proc' table with other tables
lock tables mysql.proc write, mysql.user write|
ERROR HY000: You can't combine write-locking of system 'mysql.proc' table with other tables
lock tables t1 read, mysql.proc read|
unlock tables|
lock tables mysql.proc write|
unlock tables|
302 303 304 305 306
create procedure bug1965()
begin
declare c cursor for select val from t1 order by valname;
open c;
close c;
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
307 308
end|
call bug1965()|
309
ERROR 42S22: Unknown column 'valname' in 'order clause'
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
310 311
drop procedure bug1965|
select 1 into a|
312
ERROR 42000: Undeclared variable: a
313
drop table if exists t3|
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
314
create table t3 (column_1_0 int)|
315
create procedure bug1653()
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
316 317
update t3 set column_1 = 0|
call bug1653()|
318
ERROR 42S22: Unknown column 'column_1' in 'field list'
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
319 320 321 322 323
drop table t3|
create table t3 (column_1 int)|
call bug1653()|
drop procedure bug1653|
drop table t3|
324 325 326
create procedure bug2259()
begin
declare v1 int;
327
declare c1 cursor for select s1 from t1;
328 329 330 331 332
fetch c1 into v1;
end|
call bug2259()|
ERROR 24000: Cursor is not open
drop procedure bug2259|
333 334 335 336 337 338 339 340 341
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|
342
drop procedure bug2272|
343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358
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|
359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379
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|
380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403
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|
404 405
create procedure nodb.bug3339() begin end|
ERROR 42000: Unknown database 'nodb'
406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421
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|
422
create procedure bug4344() drop procedure bug4344|
423
ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine
424
create procedure bug4344() drop function bug4344|
425
ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine
426 427 428 429 430
drop procedure if exists bug3294|
create procedure bug3294()
begin
declare continue handler for sqlexception drop table t5;
drop table t5;
431
drop table t5;
432
end|
433
create table t5 (x int)|
434 435 436
call bug3294()|
ERROR 42S02: Unknown table 't5'
drop procedure bug3294|
437 438 439 440 441 442 443 444 445 446 447 448 449
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|
450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477
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'
478 479 480 481 482 483 484 485 486
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
487 488 489 490 491 492
drop procedure if exists bug9566|
create procedure bug9566()
begin
select * from t1;
end|
lock table t1 read|
493
alter procedure bug9566 comment 'Some comment'|
494 495 496
ERROR HY000: Table 'proc' was not locked with LOCK TABLES
unlock tables|
drop procedure bug9566|
497 498 499 500 501 502 503 504 505 506 507 508 509
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|
510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550
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|
551 552 553 554 555 556 557 558 559
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
560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578
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
579 580 581
drop function if exists bug8408_f|
drop procedure if exists bug8408_p|
create function bug8408_f() returns int
582
begin
583
call bug8408_p();
584 585
return 0;
end|
586
create procedure bug8408_p()
587
select * from t1|
588
call bug8408_p()|
589
val	x
590 591 592 593
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|
594 595 596 597 598 599 600 601 602 603 604 605 606 607
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|
608 609 610 611
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
612 613 614 615 616 617 618
drop function if exists bug8409|
create function bug8409()
returns int
begin
flush tables;
return 5;
end|
619
ERROR 0A000: FLUSH is not allowed in stored function or trigger
620
create procedure bug9529_90123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123()
621 622
begin
end|
623
ERROR 42000: Identifier name 'bug9529_90123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' is too long
624 625 626 627 628 629 630 631 632 633 634 635 636
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|
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
637
drop table t1|
638 639 640 641 642 643 644 645 646 647 648 649 650 651
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;
652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680
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;
681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720
drop function if exists bug11394|
drop function if exists bug11394_1|
drop function if exists bug11394_2|
drop procedure if exists bug11394|
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|
select bug11394(2)|
ERROR HY000: Recursive stored routines are not allowed.
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|
select bug11394_1(2)|
ERROR HY000: Recursive stored routines are not allowed.
drop function bug11394_1|
create function bug11394_2(i int) returns int return i|
select bug11394_2(bug11394_2(10))|
bug11394_2(bug11394_2(10))
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|
call bug11394(2, 1)|
ERROR HY000: Recursive stored routines are not allowed.
drop procedure bug11394|
721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736
drop function if exists bug11834_1;
drop function if exists bug11834_2;
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;
bug11834_2()
10
execute stmt;
bug11834_2()
10
drop function bug11834_1;
execute stmt;
ERROR 42000: FUNCTION test.bug11834_1 does not exist
deallocate prepare stmt;
drop function bug11834_2;