1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
drop table if exists t1, t2;
drop procedure if exists bug8850|
create table t1 (a int) engine=innodb|
create procedure bug8850()
begin
truncate table t1; insert t1 values (1); rollback;
end|
set autocommit=0|
insert t1 values (2)|
call bug8850()|
commit|
select * from t1|
a
call bug8850()|
set autocommit=1|
select * from t1|
a
drop table t1|
drop procedure bug8850|
drop function if exists bug10015_1|
drop function if exists bug10015_2|
drop function if exists bug10015_3|
drop function if exists bug10015_4|
drop function if exists bug10015_5|
drop function if exists bug10015_6|
drop function if exists bug10015_7|
drop procedure if exists bug10015_8|
create table t1 (id int) engine=innodb|
create table t2 (id int primary key, j int) engine=innodb|
insert into t1 values (1),(2),(3)|
create function bug10015_1() returns int return (select count(*) from t1)|
select *, bug10015_1() from t1|
id bug10015_1()
1 3
2 3
3 3
drop function bug10015_1|
create function bug10015_2() returns int
begin
declare i, s int;
set i:= (select min(id) from t1);
set s:= (select max(id) from t1);
return (s - i);
end|
select *, bug10015_2() from t1|
id bug10015_2()
1 2
2 2
3 2
drop function bug10015_2|
create function bug10015_3() returns int
return (select max(a.id - b.id) from t1 as a, t1 as b where a.id >= b.id)|
select *, bug10015_3() from t1|
id bug10015_3()
1 2
2 2
3 2
drop function bug10015_3|
create function bug10015_4(i int) returns int
begin
declare m int;
set m:= (select max(id) from t2);
insert into t2 values (i, m);
return m;
end|
select *, bug10015_4(id) from t1|
id bug10015_4(id)
1 NULL
2 1
3 2
select * from t2|
id j
1 NULL
2 1
3 2
drop function bug10015_4|
create function bug10015_5(i int) returns int
begin
if (i = 5) then
insert into t2 values (1, 0);
end if;
return i;
end|
insert into t1 values (bug10015_5(4)), (bug10015_5(5))|
ERROR 23000: Duplicate entry '1' for key 1
select * from t1|
id
1
2
3
drop function bug10015_5|
create function bug10015_6(i int) returns int
begin
declare continue handler for sqlexception set @error_in_func:= 1;
if (i = 5) then
insert into t2 values (4, 0), (1, 0);
end if;
return i;
end|
set @error_in_func:= 0|
insert into t1 values (bug10015_6(5)), (bug10015_6(6))|
select @error_in_func|
@error_in_func
1
select * from t1|
id
1
2
3
5
6
select * from t2|
id j
1 NULL
2 1
3 2
4 0
drop function bug10015_6|
create function bug10015_7() returns int
begin
alter table t1 add k int;
return 1;
end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
create function bug10015_7() returns int
begin
start transaction;
return 1;
end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
create function bug10015_7() returns int
begin
drop table t1;
return 1;
end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
create function bug10015_7() returns int
begin
drop temporary table t1;
return 1;
end|
drop function bug10015_7|
create function bug10015_7() returns int
begin
commit;
return 1;
end|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
create function bug10015_7() returns int
begin
call bug10015_8();
return 1;
end|
create procedure bug10015_8() alter table t1 add k int|
select *, bug10015_7() from t1|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
drop procedure bug10015_8|
create procedure bug10015_8() start transaction|
select *, bug10015_7() from t1|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
drop procedure bug10015_8|
create procedure bug10015_8() drop temporary table if exists t1_temp|
select *, bug10015_7() from t1|
id bug10015_7()
1 1
2 1
3 1
5 1
6 1
drop procedure bug10015_8|
create procedure bug10015_8() commit|
select *, bug10015_7() from t1|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
drop procedure bug10015_8|
drop function bug10015_7|
drop table t1, t2|
|
