sp-error.test 12.3 KB
Newer Older
1 2 3 4 5 6 7 8 9
#
# Stored PROCEDURE error tests
#

# Make sure we don't have any procedures left.
delete from mysql.proc;

delimiter |;

10 11 12 13 14 15 16 17 18 19
# 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)|

20 21
# Check that we get the right error, i.e. UDF declaration parses correctly,
# but foo.so doesn't exist.
monty@mysql.com's avatar
monty@mysql.com committed
22
#    This generates an error message containing a misleading errno which
23 24 25 26 27
#    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"|

28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

--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|


45 46 47 48 49 50 51
create procedure proc1()
  set @x = 42|

create function func1() returns int
  return 42|

# Can't create recursively
52
--error 1303
53 54
create procedure foo()
  create procedure bar() set @x=3|
55
--error 1303
56 57 58 59
create procedure foo()
  create function bar() returns double return 2.3|

# Already exists
60
--error 1304
61 62
create procedure proc1()
  set @x = 42|
63
--error 1304
64 65 66
create function func1() returns int
  return 42|

67 68 69
drop procedure proc1|
drop function func1|

70
# Does not exist
71
--error 1305
72
alter procedure foo|
73
--error 1305
74
alter function foo|
75
--error 1305
76
drop procedure foo|
77
--error 1305
78
drop function foo|
79
--error 1305
80
call foo()|
81
drop procedure if exists foo|
82
--error 1305
83
show create procedure foo|
84 85
--error 1305
show create function foo|
86

87
# LEAVE/ITERATE/GOTO with no match
88
--error 1308
89 90 91 92
create procedure foo()
foo: loop
  leave bar;
end loop|
93
--error 1308
94 95 96 97
create procedure foo()
foo: loop
  iterate bar;
end loop|
98
--error 1308
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
99 100 101 102
create procedure foo()
foo: begin
  iterate foo;
end|
103
--error 1308
104 105 106 107
create procedure foo()
begin
  goto foo;
end|
108
--error 1308
109 110 111 112 113 114 115
create procedure foo()
begin
  begin
    label foo;
  end;
  goto foo;
end|
116
--error 1308
117 118 119 120 121 122 123
create procedure foo()
begin
  goto foo;
  begin
    label foo;
  end;
end|
124
--error 1308
125 126 127 128 129 130 131 132 133
create procedure foo()
begin
  begin
    goto foo;
  end;
  begin
    label foo;
  end;
end|
134
--error 1308
135 136 137 138 139 140 141 142 143
create procedure foo()
begin
  begin
    label foo;
  end;
  begin
    goto foo;
  end;
end|
144 145

# Redefining label
146
--error 1309
147 148 149 150 151 152 153 154
create procedure foo()
foo: loop
  foo: loop
    set @x=2;
  end loop foo;
end loop foo|

# End label mismatch
155
--error 1310
156 157 158 159 160 161
create procedure foo()
foo: loop
  set @x=2;
end loop bar|

# RETURN in FUNCTION only
162
--error 1313
163 164 165
create procedure foo()
  return 42|

166 167
# Wrong number of arguments
create procedure p(x int)
168
  set @x = x|
169 170 171
create function f(x int) returns int
  return x+42|

172
--error 1318
173
call p()|
174
--error 1318
175
call p(1, 2)|
176
--error 1318
177
select f()|
178
--error 1318
179 180 181 182
select f(1, 2)|

drop procedure p|
drop function f|
183

184
--error 1319 
185 186 187 188 189 190 191 192 193 194 195 196 197
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|

198
--error 1319 
199 200 201 202 203 204 205 206 207 208 209 210 211 212
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|

213
--error 1320
214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230
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|

231
--error 1321
232 233 234 235
select f(10)|

drop function f|

236
--error 1322
237 238 239 240 241 242 243 244
create procedure p()
begin
  declare c cursor for insert into test.t1 values ("foo", 42);

  open c;
  close c;
end|

245
--error 1323
246 247 248 249 250 251 252 253 254
create procedure p()
begin
  declare x int;
  declare c cursor for select * into x from test.t limit 1;

  open c;
  close c;
end|

255
--error 1324
256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276
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|
277
--error 1325
278 279 280 281 282 283 284 285 286 287 288
call p()|
drop procedure p|

create procedure p()
begin
  declare c cursor for select * from test.t1;

  open c;
  close c;
  close c;
end|
289
--error 1326
290 291 292
call p()|
drop procedure p|

293
--error 1305
294
alter procedure bar3 sql security invoker|
295

296 297 298 299 300 301 302 303
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)|

304
--error 1327
305 306 307
create procedure p()
begin
  declare x int;
308
  declare c cursor for select * from t1;
309 310 311 312 313 314 315 316 317

  open c;
  fetch c into x, y;
  close c;
end|

create procedure p()
begin
  declare x int;
318
  declare c cursor for select * from t1;
319 320 321 322 323

  open c;
  fetch c into x;
  close c;
end|
324
--error 1328
325 326 327 328 329 330 331 332
call p()|
drop procedure p|

create procedure p()
begin
  declare x int;
  declare y float;
  declare z int;
333
  declare c cursor for select * from t1;
334 335 336 337 338

  open c;
  fetch c into x, y, z;
  close c;
end|
339
--error 1328
340 341 342
call p()|
drop procedure p|

343
--error 1330
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
344 345 346
create procedure p(in x int, x char(10))
begin
end|
347
--error 1330
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
348 349 350
create function p(x int, x char(10))
begin
end|
351

352
--error 1331
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
353 354 355 356 357
create procedure p()
begin
  declare x float;
  declare x int;
end|
358

359
--error 1332	
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
360 361 362 363 364
create procedure p()
begin
  declare c condition for 1064;
  declare c condition for 1065;
end|
365

366
--error 1333
pem@mysql.comhem.se's avatar
pem@mysql.comhem.se committed
367 368 369 370 371 372
create procedure p()
begin
  declare c cursor for select * from t1;
  declare c cursor for select field from t1;
end|

373
# USE is not allowed
374
--error 1336
375
create procedure u()
376
  use sptmp|
377

378
# Enforced standard order of declarations
379
--error 1337
380 381 382 383 384
create procedure p()
begin
  declare c cursor for select * from t1;
  declare x int;
end|
385
--error 1337
386 387 388 389 390 391 392
create procedure p()
begin
  declare x int;
  declare continue handler for sqlstate '42S99' set x = 1;
  declare foo condition for sqlstate '42S99';
end|

393
--error 1338
394 395 396 397 398 399
create procedure p()
begin
  declare x int;
  declare continue handler for sqlstate '42S99' set x = 1;
  declare c cursor for select * from t1;
end|
400

401
--error 1358
402 403 404 405 406 407 408 409 410 411 412
create procedure p()
begin
  declare continue handler for sqlexception
    begin
      goto L1;
    end;

  select field from t1;
  label L1;
end|

413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429
#
# 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
#
430
--error 1327
431 432
select 1 into a|

433 434 435 436
#
# BUG#1653
#
--disable_warnings
437
drop table if exists t3|
438 439 440 441 442 443 444 445 446 447 448 449 450 451 452
--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|

453 454 455 456 457 458 459 460
#
# 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;
461
  declare c1 cursor for select s1 from t1;
462 463 464 465

  fetch c1 into v1;
end|

466
--error 1326
467 468 469
call bug2259()|
drop procedure bug2259|

470 471 472 473 474 475 476 477 478 479 480 481 482 483
#
# 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()|
delete from t1|
484
drop procedure bug2272|
485

486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509
#
# 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|

510 511 512 513 514 515 516 517 518 519 520 521
#
# 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|
522
--error 1339
523 524 525 526 527 528 529 530 531 532
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|
533
--error 1339
534 535 536
call bug3287(2)|
drop procedure bug3287|

537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566
#
# 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 1062
call bug3279(@x)|
select @x|
drop procedure bug3279|
drop table t3|

567 568 569 570 571 572
#
# BUG#3339
#
--error 1049
create procedure nodb.bug3339() begin end|

573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594
#
# 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|
595

596 597 598
#
# BUG#4344
#
599
--error 1357
600
create procedure bug4344() drop procedure bug4344|
601
--error 1357
602 603
create procedure bug4344() drop function bug4344|

604 605 606 607 608 609 610 611 612 613
#
# 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;
614
  drop table t5;
615 616
end|

617
create table t5 (x int)|
618 619 620 621
--error 1051
call bug3294()|
drop procedure bug3294|

622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643
#
# BUG#6807: Stored procedure crash if CREATE PROCEDURE ... KILL QUERY
#
--disable_warnings
drop procedure if exists bug6807|
--enable_warnings
create procedure bug6807()
begin
  declare id int;

  set id = connection_id();
  kill query id;
  select 'Not reached';
end|

--error 1317
call bug6807()|
--error 1317
call bug6807()|

drop procedure bug6807|

644 645 646 647 648 649 650 651 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 681
#
# 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|

682

683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698
#
# 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|

699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721
#
# 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. Probably this test
# should be removed when Monty will allow access to mysql.proc without
# locking it.
#
--disable_warnings
drop procedure if exists bug9566|
--enable_warnings
create procedure bug9566()
begin
  select * from t1;
end|
lock table t1 read|
# This should fail because we forgot to lock mysql.proc table explicitly
--error 1100
call bug9566()|
unlock tables|
# This should succeed
drop procedure bug9566|

722

723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744
#
# 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|

delete from t1|
--error ER_SP_FETCH_NO_DATA
call bug7299()|
drop procedure bug7299|


745 746 747 748 749 750 751 752 753
#
# BUG#NNNN: New bug synopsis
#
#--disable_warnings
#drop procedure if exists bugNNNN|
#--enable_warnings
#create procedure bugNNNN...


754 755
drop table t1|

756
delimiter ;|