sp-error.test 6.66 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
unknown's avatar
unknown committed
35
--error 1287
36 37
create procedure foo()
  create procedure bar() set @x=3|
unknown's avatar
unknown committed
38
--error 1287
39 40 41 42
create procedure foo()
  create function bar() returns double return 2.3|

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

unknown's avatar
unknown committed
50 51 52
drop procedure proc1|
drop function func1|

53
# Does not exist
unknown's avatar
unknown committed
54
--error 1289
55
alter procedure foo|
unknown's avatar
unknown committed
56
--error 1289
57
alter function foo|
unknown's avatar
unknown committed
58
--error 1289
59
drop procedure foo|
unknown's avatar
unknown committed
60
--error 1289
61
drop function foo|
unknown's avatar
unknown committed
62
--error 1289
63
call foo()|
64
drop procedure if exists foo|
unknown's avatar
unknown committed
65
--error 1289
66
show create procedure foo|
67 68

# LEAVE/ITERATE with no match
unknown's avatar
unknown committed
69
--error 1292
70 71 72 73
create procedure foo()
foo: loop
  leave bar;
end loop|
unknown's avatar
unknown committed
74
--error 1292
75 76 77 78
create procedure foo()
foo: loop
  iterate bar;
end loop|
unknown's avatar
unknown committed
79
--error 1292
unknown's avatar
unknown committed
80 81 82 83
create procedure foo()
foo: begin
  iterate foo;
end|
84 85

# Redefining label
unknown's avatar
unknown committed
86
--error 1293 
87 88 89 90 91 92 93 94
create procedure foo()
foo: loop
  foo: loop
    set @x=2;
  end loop foo;
end loop foo|

# End label mismatch
unknown's avatar
unknown committed
95
--error 1294
96 97 98 99 100 101 102 103 104 105 106
create procedure foo()
foo: loop
  set @x=2;
end loop bar|

# Referring to undef variable
create procedure foo(out x int)
begin
  declare y int;
  set x = y;
end|
107
drop procedure foo|
108 109

# RETURN in FUNCTION only
unknown's avatar
unknown committed
110
--error 1297
111 112 113 114
create procedure foo()
  return 42|

# Doesn't allow queries in FUNCTIONs (for now :-( )
unknown's avatar
unknown committed
115
--error 1298
116 117 118 119 120 121 122
create function foo() returns int
begin
  declare x int;
  select max(c) into x from test.t;
  return x;
end|

unknown's avatar
unknown committed
123 124 125 126 127 128
# 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|

unknown's avatar
unknown committed
129
--error 1302
unknown's avatar
unknown committed
130
call p()|
unknown's avatar
unknown committed
131
--error 1302
unknown's avatar
unknown committed
132
call p(1, 2)|
unknown's avatar
unknown committed
133
--error 1302
unknown's avatar
unknown committed
134
select f()|
unknown's avatar
unknown committed
135
--error 1302
unknown's avatar
unknown committed
136 137 138 139
select f(1, 2)|

drop procedure p|
drop function f|
140

unknown's avatar
unknown committed
141
--error 1303
142 143 144 145 146 147 148 149 150 151 152 153 154
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|

unknown's avatar
unknown committed
155
--error 1303
156 157 158 159 160 161 162 163 164 165 166 167 168 169
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|

unknown's avatar
unknown committed
170
--error 1304
171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187
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|

unknown's avatar
unknown committed
188
--error 1305
189 190 191 192
select f(10)|

drop function f|

unknown's avatar
unknown committed
193
--error 1306
194 195 196 197 198 199 200 201
create procedure p()
begin
  declare c cursor for insert into test.t1 values ("foo", 42);

  open c;
  close c;
end|

unknown's avatar
unknown committed
202
--error 1307
203 204 205 206 207 208 209 210 211
create procedure p()
begin
  declare x int;
  declare c cursor for select * into x from test.t limit 1;

  open c;
  close c;
end|

unknown's avatar
unknown committed
212
--error 1308
213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233
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|
unknown's avatar
unknown committed
234
--error 1309
235 236 237 238 239 240 241 242 243 244 245
call p()|
drop procedure p|

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

  open c;
  close c;
  close c;
end|
unknown's avatar
unknown committed
246
--error 1310
247 248 249
call p()|
drop procedure p|

unknown's avatar
unknown committed
250
--error 1289
251
alter procedure bar3 sql security invoker|
unknown's avatar
unknown committed
252 253 254 255
--error 1059
alter procedure bar3 name
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA|

256 257 258 259 260 261 262 263
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)|

unknown's avatar
unknown committed
264
--error 1311
265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283
create procedure p()
begin
  declare c cursor for select * from t1;
  declare x int;

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

create procedure p()
begin
  declare c cursor for select * from t1;
  declare x int;

  open c;
  fetch c into x;
  close c;
end|
unknown's avatar
unknown committed
284
--error 1312
285 286 287 288 289 290 291 292 293 294 295 296 297 298
call p()|
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|
unknown's avatar
unknown committed
299
--error 1312
300 301 302
call p()|
drop procedure p|

unknown's avatar
unknown committed
303
--error 1314
unknown's avatar
unknown committed
304 305 306
create procedure p(in x int, x char(10))
begin
end|
unknown's avatar
unknown committed
307
--error 1314
unknown's avatar
unknown committed
308 309 310
create function p(x int, x char(10))
begin
end|
311

unknown's avatar
unknown committed
312
--error 1315
unknown's avatar
unknown committed
313 314 315 316 317
create procedure p()
begin
  declare x float;
  declare x int;
end|
318

unknown's avatar
unknown committed
319
--error 1316
unknown's avatar
unknown committed
320 321 322 323 324
create procedure p()
begin
  declare c condition for 1064;
  declare c condition for 1065;
end|
325

unknown's avatar
unknown committed
326
--error 1317
unknown's avatar
unknown committed
327 328 329 330 331 332
create procedure p()
begin
  declare c cursor for select * from t1;
  declare c cursor for select field from t1;
end|

333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349
#
# 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
#
unknown's avatar
unknown committed
350
--error 1311
351 352
select 1 into a|

353 354 355
#
# BUG#336
#
unknown's avatar
unknown committed
356
--error 1319
357 358 359 360 361 362 363 364 365
create procedure bug336(id char(16))
begin
  declare x int;
  set x = (select sum(t.data) from test.t2 t);
end|

#
# BUG#1654
#
unknown's avatar
unknown committed
366
--error 1298
367 368 369
create function bug1654()
  returns int
return (select sum(t.data) from test.t2 t)|
370

371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390
#
# BUG#1653
#
--disable_warnings
drop table if exists table_1|
--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|

391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407
#
# 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;
  declare c1 cursor for select s1 from t10;

  fetch c1 into v1;
end|

--error 1310
call bug2259()|
drop procedure bug2259|

408 409 410 411 412 413 414 415 416 417 418 419 420 421
#
# 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|
422
drop procedure bug2272|
423

424 425
drop table t1|

426
delimiter ;|