2
# tests that require InnoDB...
5
-- source include/have_innodb.inc
8
drop table if exists t1, t2, t3;
14
# BUG#8850: Truncate table in a stored procedure locks the tables
17
drop procedure if exists bug8850|
19
create table t1 (a int) engine=innodb|
20
create procedure bug8850()
22
truncate table t1; insert t1 values (1); rollback;
35
drop procedure bug8850|
39
# BUG#10015: Crash in InnoDB if stored routines are used
40
# (crash happens in auto-commit mode)
43
drop function if exists bug10015_1|
44
drop function if exists bug10015_2|
45
drop function if exists bug10015_3|
46
drop function if exists bug10015_4|
47
drop function if exists bug10015_5|
48
drop function if exists bug10015_6|
49
drop function if exists bug10015_7|
50
drop procedure if exists bug10015_8|
52
create table t1 (id int) engine=innodb|
53
create table t2 (id int primary key, j int) engine=innodb|
54
insert into t1 values (1),(2),(3)|
55
create function bug10015_1() returns int return (select count(*) from t1)|
56
select *, bug10015_1() from t1|
57
drop function bug10015_1|
58
# Test couple of a bit more complex cases
59
create function bug10015_2() returns int
62
set i:= (select min(id) from t1);
63
set s:= (select max(id) from t1);
66
select *, bug10015_2() from t1|
67
drop function bug10015_2|
68
create function bug10015_3() returns int
69
return (select max(a.id - b.id) from t1 as a, t1 as b where a.id >= b.id)|
70
select *, bug10015_3() from t1|
71
drop function bug10015_3|
72
create function bug10015_4(i int) returns int
75
set m:= (select max(id) from t2);
76
insert into t2 values (i, m);
79
select *, bug10015_4(id) from t1|
81
drop function bug10015_4|
82
# Now let us test how statement rollback works
83
# This function will cause the whole stmt to be rolled back,
84
# there should not be any traces left.
85
create function bug10015_5(i int) returns int
88
insert into t2 values (1, 0);
93
insert into t1 values (bug10015_5(4)), (bug10015_5(5))|
95
drop function bug10015_5|
96
# Thanks to error-handler this function should not cause rollback
97
# of statement calling it. But insert statement in it should be
98
# rolled back completely and don't leave any traces in t2.
99
# Unfortunately we can't implement such behavior in 5.0, so it
100
# is something to be fixed in later 5.* releases (TODO).
101
create function bug10015_6(i int) returns int
103
declare continue handler for sqlexception set @error_in_func:= 1;
105
insert into t2 values (4, 0), (1, 0);
109
set @error_in_func:= 0|
110
insert into t1 values (bug10015_6(5)), (bug10015_6(6))|
111
select @error_in_func|
114
drop function bug10015_6|
115
# Let us test that we don't allow any statements causing transaction
116
# commit in stored functions (we test only most interesting cases here).
117
# Cases which can be caught at creation time:
119
create function bug10015_7() returns int
121
alter table t1 add k int;
125
create function bug10015_7() returns int
131
create function bug10015_7() returns int
136
# It should be OK to drop temporary table.
137
create function bug10015_7() returns int
139
drop temporary table t1;
142
drop function bug10015_7|
144
create function bug10015_7() returns int
149
# Now let us test cases which we can catch only at run-time:
150
create function bug10015_7() returns int
155
create procedure bug10015_8() alter table t1 add k int|
157
select *, bug10015_7() from t1|
158
drop procedure bug10015_8|
159
create procedure bug10015_8() start transaction|
161
select *, bug10015_7() from t1|
162
drop procedure bug10015_8|
163
# Again it is OK to drop temporary table
164
# We are surpressing warnings since they are not essential
165
create procedure bug10015_8() drop temporary table if exists t1_temp|
167
select *, bug10015_7() from t1|
169
drop procedure bug10015_8|
170
create procedure bug10015_8() commit|
172
select *, bug10015_7() from t1|
173
drop procedure bug10015_8|
174
drop function bug10015_7|
179
# BUG#13825 "Triggers: crash if release savepoint".
180
# Also general test for handling of savepoints in stored routines.
182
# According to SQL standard we should establish new savepoint
183
# level before executing stored function/trigger and destroy
184
# this savepoint level after execution. Stored procedures by
185
# default should be executed using the same savepoint level
186
# as their caller (to execute stored procedure using new
187
# savepoint level one should explicitly specify NEW SAVEPOINT
188
# LEVEL clause in procedure creation statement which MySQL
189
# does not support yet).
191
drop function if exists bug13825_0|
192
drop function if exists bug13825_1|
193
drop function if exists bug13825_2|
194
drop function if exists bug13825_3|
195
drop function if exists bug13825_4|
196
drop function if exists bug13825_5|
197
drop procedure if exists bug13825_0|
198
drop procedure if exists bug13825_1|
199
drop procedure if exists bug13825_2|
200
drop table if exists t1|
202
create table t1 (i int) engine=innodb|
203
create table t2 (i int) engine=innodb|
204
create function bug13825_0() returns int
206
rollback to savepoint x;
209
create function bug13825_1() returns int
214
create function bug13825_2() returns int
216
insert into t1 values (2);
218
insert into t1 values (3);
219
rollback to savepoint x;
220
insert into t1 values (4);
223
create procedure bug13825_0()
225
rollback to savepoint x;
227
create procedure bug13825_1()
231
create procedure bug13825_2()
235
insert into t2 values (1)|
236
create trigger t2_bi before insert on t2 for each row
237
rollback to savepoint x|
238
create trigger t2_bu before update on t2 for each row
240
create trigger t2_bd before delete on t2 for each row
242
insert into t1 values (2);
244
insert into t1 values (3);
245
rollback to savepoint x;
246
insert into t1 values (4);
248
create function bug13825_3(rb int) returns int
250
insert into t1 values(1);
252
insert into t1 values(2);
254
rollback to savepoint x;
256
insert into t1 values(3);
259
create function bug13825_4() returns int
262
insert into t1 values(2);
263
rollback to savepoint x;
266
create function bug13825_5(p int) returns int
269
insert into t2 values(p);
270
rollback to savepoint x;
271
insert into t2 values(p+1);
275
# Test of savepoint level handling for stored functions and triggers
277
insert into t1 values (1)|
279
--error ER_SP_DOES_NOT_EXIST
280
set @a:= bug13825_0()|
281
--error ER_SP_DOES_NOT_EXIST
282
insert into t2 values (2)|
283
--error ER_SP_DOES_NOT_EXIST
284
set @a:= bug13825_1()|
285
--error ER_SP_DOES_NOT_EXIST
287
set @a:= bug13825_2()|
289
rollback to savepoint x|
293
rollback to savepoint x|
295
# Of course savepoints set in function should not be visible from its caller
297
set @a:= bug13825_2()|
299
--error ER_SP_DOES_NOT_EXIST
300
rollback to savepoint x|
303
# Test of savepoint level handling for stored procedures
305
insert into t1 values (5)|
307
insert into t1 values (6)|
311
--error ER_SP_DOES_NOT_EXIST
312
rollback to savepoint x|
314
insert into t1 values (7)|
316
rollback to savepoint x|
321
# Let us test that savepoints work inside of functions
322
# even in auto-commit mode
323
select bug13825_3(0)|
326
select bug13825_3(1)|
329
# Curious case: rolling back to savepoint which is set by first
330
# statement in function should not rollback whole transaction.
333
insert into t1 values (1)|
334
set @a:= bug13825_4()|
339
# Other curious case: savepoint in the middle of statement
341
create table t2 (i int) engine=innodb|
342
insert into t1 values (1), (bug13825_5(2)), (3)|
346
drop function bug13825_0|
347
drop function bug13825_1|
348
drop function bug13825_2|
349
drop function bug13825_3|
350
drop function bug13825_4|
351
drop function bug13825_5|
352
drop procedure bug13825_0|
353
drop procedure bug13825_1|
354
drop procedure bug13825_2|
359
# BUG#14840: CONTINUE handler problem
362
drop table if exists t3|
363
drop procedure if exists bug14840_1|
364
drop procedure if exists bug14840_2|
374
# This used to hang the client since the insert returned with an
375
# error status (left over from the update) even though it succeeded,
376
# which caused the execution to end at that point.
377
create procedure bug14840_1()
379
declare err int default 0;
380
declare continue handler for sqlexception
384
update t3 set x = 1, y = 42 where x = 2;
385
insert into t3 values (3, 4711);
394
# A simpler (non-transactional) case: insert at select should be done
395
create procedure bug14840_2()
397
declare err int default 0;
398
declare continue handler for sqlexception
401
select err as 'Ping';
404
update t3 set x = 1, y = 42 where x = 2;
405
update t3 set x = 1, y = 42 where x = 2;
406
insert into t3 values (3, 4711);
410
insert into t3 values (1, 3), (2, 5)|
414
insert into t3 values (1, 3), (2, 5)|
417
drop procedure bug14840_1|
418
drop procedure bug14840_2|
423
# BUG#10656: Stored Procedure - Create index and Truncate table command error
426
drop procedure if exists bug10656_create_index|
427
drop procedure if exists bug10656_myjoin|
428
drop procedure if exists bug10656_truncate_table|
432
`ID` int(11) default NULL,
433
`txt` char(5) default NULL
434
) ENGINE=InnoDB DEFAULT CHARSET=latin1|
436
INSERT INTO t3 (`ID`,`txt`) VALUES
437
(1,'a'), (2,'b'), (3,'c'), (4,'d')|
440
`ID` int(11) default NULL,
441
`txt` char(5) default NULL
442
) ENGINE=InnoDB DEFAULT CHARSET=latin1|
444
INSERT INTO t4 (`ID`,`txt`) VALUES
445
(1,'a'), (2,'b'), (3,'c'), (4,'d')|
447
create procedure bug10656_create_index()
449
create index bug10656_my_index on t3 (ID);
451
call bug10656_create_index()|
453
create procedure bug10656_myjoin()
455
update t3, t4 set t3.txt = t4.txt where t3.id = t4.id;
457
call bug10656_myjoin()|
459
create procedure bug10656_truncate_table()
463
call bug10656_truncate_table()|
466
drop procedure bug10656_create_index|
467
drop procedure bug10656_myjoin|
468
drop procedure bug10656_truncate_table|
486
insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
487
insert into t4 values (1 , 'bCh1' )|
490
drop procedure if exists bug3448|
492
create procedure bug3448()
493
select * from t3 inner join t4 on t3.a = t4.b|
495
select * from t3 inner join t4 on t3.a = t4.b|
499
drop procedure bug3448|
503
# BUG#14210: "Simple query with > operator on large table gives server
505
# Check that cursors work in case when HEAP tables are converted to
509
drop procedure if exists bug14210|
511
set @@session.max_heap_table_size=16384|
512
select @@session.max_heap_table_size|
513
# To trigger the memory corruption the original table must be InnoDB.
514
# No harm if it's not, so don't warn if the suite is run with --skip-innodb
516
create table t3 (a char(255)) engine=InnoDB|
518
create procedure bug14210_fill_table()
520
declare table_size, max_table_size int default 0;
521
select @@session.max_heap_table_size into max_table_size;
523
insert into t3 (a) values (repeat('a', 255));
525
insert into t3 select a from t3;
526
select count(*)*255 from t3 into table_size;
527
until table_size > max_table_size*2 end repeat;
529
call bug14210_fill_table()|
530
drop procedure bug14210_fill_table|
531
create table t4 like t3|
533
create procedure bug14210()
536
declare done int default 0;
537
declare c cursor for select * from t3;
538
declare continue handler for sqlstate '02000' set done = 1;
543
insert into t4 values (upper(a));
545
until done end repeat;
549
select count(*) from t4|
552
drop procedure bug14210|
553
set @@session.max_heap_table_size=default|
556
# BUG#7787: Stored procedures: improper warning for "grant execute" statement
561
CREATE DATABASE db_bug7787|
566
CREATE PROCEDURE p1()
567
SHOW INNODB STATUS; |
569
GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost|
573
DROP DATABASE db_bug7787|
574
drop user user_bug7787@localhost|
578
# Bug#13575 SP funcs in select with distinct/group and order by can
581
create table t3 (f1 int, f2 varchar(3), primary key(f1)) engine=innodb|
582
insert into t3 values (1,'aaa'),(2,'bbb'),(3,'ccc')|
583
CREATE FUNCTION bug13575 ( p1 integer )
586
DECLARE v1 VARCHAR(10) DEFAULT null;
587
SELECT f2 INTO v1 FROM t3 WHERE f1 = p1;
590
select distinct f1, bug13575(f1) from t3 order by f1|
591
drop function bug13575|
596
# BUG#NNNN: New bug synopsis
599
#drop procedure if exists bugNNNN|
601
#create procedure bugNNNN...