sp-error.test 2.67 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 22 23 24 25 26 27 28 29 30 31 32 33 34
# Check that we get the right error, i.e. UDF declaration parses correctly,
# but foo.so doesn't exist.
# QQ This generates an error message containing a misleading errno which
#    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"|

create procedure proc1()
  set @x = 42|

create function func1() returns int
  return 42|

# Can't create recursively
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
35
--error 1273
36 37
create procedure foo()
  create procedure bar() set @x=3|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
38
--error 1273
39 40 41 42
create procedure foo()
  create function bar() returns double return 2.3|

# Already exists
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
43
--error 1274
44 45
create procedure proc1()
  set @x = 42|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
46
--error 1274
47 48 49
create function func1() returns int
  return 42|

50 51 52
drop procedure proc1|
drop function func1|

53
# Does not exist
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
54
--error 1275
55
alter procedure foo|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
56
--error 1275
57
alter function foo|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
58
--error 1275
59
drop procedure foo|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
60
--error 1275
61
drop function foo|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
62
--error 1275
63
call foo()|
64
drop procedure if exists foo|
65 66

# LEAVE/ITERATE with no match
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
67
--error 1278
68 69 70 71
create procedure foo()
foo: loop
  leave bar;
end loop|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
72
--error 1278
73 74 75 76 77 78
create procedure foo()
foo: loop
  iterate bar;
end loop|

# Redefining label
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
79
--error 1279
80 81 82 83 84 85 86 87
create procedure foo()
foo: loop
  foo: loop
    set @x=2;
  end loop foo;
end loop foo|

# End label mismatch
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
88
--error 1280
89 90 91 92 93 94
create procedure foo()
foo: loop
  set @x=2;
end loop bar|

# Referring to undef variable
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
95
--error 1281
96 97 98 99 100 101
create procedure foo(out x int)
begin
  declare y int;
  set x = y;
end|

102 103 104 105 106 107 108
# We require INTO in SELECTs for some older clients (as mysql and mysqltest,
# for now).
create procedure foo()
begin
  select name from mysql.proc;
  select type from mysql.proc;
end|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
109
--error 1282
110 111
call foo()|
drop procedure foo|
112 113

# RETURN in FUNCTION only
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
114
--error 1283
115 116 117 118
create procedure foo()
  return 42|

# Doesn't allow queries in FUNCTIONs (for now :-( )
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
119
--error 1284
120 121 122 123 124 125 126
create function foo() returns int
begin
  declare x int;
  select max(c) into x from test.t;
  return x;
end|

127 128 129 130 131 132
# Wrong number of arguments
create procedure p(x int)
  insert into test.t1 values (x)|
create function f(x int) returns int
  return x+42|

pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
133
--error 1288
134
call p()|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
135
--error 1288
136
call p(1, 2)|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
137
--error 1288
138
select f()|
pem@mysql.telia.com's avatar
pem@mysql.telia.com committed
139
--error 1288
140 141 142 143
select f(1, 2)|

drop procedure p|
drop function f|
144 145

delimiter ;|