Debian Codegraph @

mysql-test/suite/engines/funcs/r/rpl_sp_effects.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection master;
4 SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
5 drop procedure if exists p1;
6 drop procedure if exists p2;
7 drop function if exists f1;
8 drop table if exists t1,t2;
9 drop view if exists v1;
10 create table t1 (a int);
11 SET GLOBAL log_bin_trust_function_creators = 1;
12 create procedure p1()
13 begin
14 declare spv int default 0;
15 while spv < 5 do
16 insert into t1 values(spv+1);
17 set spv=spv+1;
18 end while;
19 end//
20 call p1();
21 connection slave;
22 connection slave;
23 SELECT * FROM t1 ORDER BY a;
24 a
25 1
26 2
27 3
28 4
29 5
30 connection master;
31 SELECT * FROM t1 ORDER BY a;
32 a
33 1
34 2
35 3
36 4
37 5
38 create procedure p2()
39 begin
40 declare a int default 4;
41 create table t2 as select a;
42 end//
43 call p2();
44 SELECT * FROM t2 ORDER BY a;
45 a
46 4
47 connection slave;
48 connection slave;
49 SELECT * FROM t2 ORDER BY a;
50 a
51 4
52 connection master;
53 drop procedure p1;
54 drop procedure p2;
55 drop table t2;
56 create function f1(x int) returns int
57 begin
58 insert into t1 values(x);
59 return x+1;
60 end//
61 create procedure p1(a int, b int)
62 begin
63 declare v int default f1(5);
64 if (f1(6)) then
65 select 'yes';
66 end if;
67 set v = f1(7);
68 while f1(8) < 1 do
69 select 'this cant be';
70 end while;
71 end//
72 call p1(f1(1), f1(2));
73 yes
74 yes
75 SELECT * FROM t1 ORDER BY a;
76 a
77 1
78 1
79 2
80 2
81 3
82 4
83 5
84 5
85 6
86 7
87 8
88 create table t2(a int);
89 insert into t2 values (10),(11);
90 SELECT a,f1(a) FROM t2 ORDER BY a;
91 a f1(a)
92 10 11
93 11 12
94 insert into t2 select f1(3);
95 SELECT 'master:',a FROM t1 ORDER BY a;
96 master: a
97 master: 1
98 master: 1
99 master: 2
100 master: 2
101 master: 3
102 master: 3
103 master: 4
104 master: 5
105 master: 5
106 master: 6
107 master: 7
108 master: 8
109 master: 10
110 master: 11
111 connection slave;
112 connection slave;
113 SELECT 'slave:',a FROM t1 ORDER BY a;
114 slave: a
115 slave: 1
116 slave: 1
117 slave: 2
118 slave: 2
119 slave: 3
120 slave: 3
121 slave: 4
122 slave: 5
123 slave: 5
124 slave: 6
125 slave: 7
126 slave: 8
127 slave: 10
128 slave: 11
129 connection master;
130 drop procedure p1;
131 delete from t1;
132 delete from t2;
133 delete from t1;
134 insert into t2 values(1),(2);
135 create view v1 as select f1(a) as f from t2;
136 select * from v1 order by f;
137 f
138 2
139 3
140 SELECT 'master:',a FROM t1 ORDER BY a;
141 master: a
142 master: 1
143 master: 2
144 connection slave;
145 connection slave;
146 SELECT 'slave:',a FROM t1 ORDER BY a;
147 slave: a
148 slave: 1
149 slave: 2
150 connection master;
151 drop view v1;
152 delete from t1;
153 prepare s1 from 'select f1(?)';
154 set @xx=123;
155 execute s1 using @xx;
156 f1(?)
157 124
158 SELECT 'master:',a FROM t1 ORDER BY a;
159 master: a
160 master: 123
161 connection slave;
162 connection slave;
163 SELECT 'slave:',a FROM t1 ORDER BY a;
164 slave: a
165 slave: 123
166 connection master;
167 delete from t1;
168 create procedure p1(spv int)
169 begin
170 declare c cursor for select f1(spv) from t2;
171 while (spv > 2) do
172 open c;
173 fetch c into spv;
174 close c;
175 set spv= spv - 10;
176 end while;
177 end//
178 call p1(15);
179 SELECT 'master:',a FROM t1 ORDER BY a;
180 master: a
181 master: 6
182 master: 6
183 master: 15
184 master: 15
185 connection slave;
186 connection slave;
187 SELECT 'slave:',a FROM t1 ORDER BY a;
188 slave: a
189 slave: 6
190 slave: 6
191 slave: 15
192 slave: 15
193 connection master;
194 drop procedure p1;
195 drop function f1;
196 drop table t1,t2;
197 create table t1 (a int);
198 create procedure p1()
199 begin
200 insert into t1 values(@x);
201 set @x=@x+1;
202 insert into t1 values(@x);
203 if (f2()) then
204 insert into t1 values(1243);
205 end if;
206 end//
207 create function f2() returns int
208 begin
209 insert into t1 values(@z);
210 set @z=@z+1;
211 insert into t1 values(@z);
212 return 0;
213 end//
214 create function f1() returns int
215 begin
216 insert into t1 values(@y);
217 call p1();
218 return 0;
219 end//
220 set @x=10;
221 set @y=20;
222 set @z=100;
223 select f1();
224 f1()
225 0
226 set @x=30;
227 call p1();
228 SELECT 'master', a FROM t1 ORDER BY a;
229 master a
230 master 10
231 master 11
232 master 20
233 master 30
234 master 31
235 master 100
236 master 101
237 master 101
238 master 102
239 connection slave;
240 connection slave;
241 SELECT 'slave', a FROM t1 ORDER BY a;
242 slave a
243 slave 10
244 slave 11
245 slave 20
246 slave 30
247 slave 31
248 slave 100
249 slave 101
250 slave 101
251 slave 102
252 connection master;
253 drop table t1;
254 drop function f1;
255 drop function f2;
256 drop procedure p1;
257 connection slave;
258 connection master;
259 create table t2 (b BIT(7));
260 create procedure sp_bug26199(bitvalue BIT(7))
261 begin
262 insert into t2 set b = bitvalue;
263 end //
264 create function sf_bug26199(b BIT(7)) returns int
265 begin
266 insert into t2 values(b);
267 return 0;
268 end//
269 call sp_bug26199(b'1110');
270 call sp_bug26199('\0');
271 select sf_bug26199(b'1111111');
272 sf_bug26199(b'1111111')
273 0
274 SET STATEMENT sql_mode = '' FOR
275 select sf_bug26199(b'101111111');
276 sf_bug26199(b'101111111')
277 0
278 Warnings:
279 Warning 1264 Out of range value for column 'b' at row 1
280 select sf_bug26199('\'');
281 sf_bug26199('\'')
282 0
283 select hex(b) from t2;
284 hex(b)
285 E
286 0
287 7F
288 7F
289 27
290 connection slave;
291 select hex(b) from t2;
292 hex(b)
293 E
294 0
295 7F
296 7F
297 27
298 connection master;
299 drop table t2;
300 drop procedure sp_bug26199;
301 drop function sf_bug26199;
302 SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
303 connection slave;
304 set names utf8;
305 CREATE FUNCTION f() RETURNS timestamp DETERMINISTIC
306 BEGIN RETURN '2012-12-21 12:12:12'; END |
307 CREATE PROCEDURE p(t timestamp)
308 BEGIN
309 SET @t = t;
310 PREPARE stmt FROM "
311 UPDATE t1 SET a = @t WHERE '2012-12-31 08:00:00' < f() ";
312 EXECUTE stmt;
313 DEALLOCATE PREPARE stmt;
314 END |
315 create table t1 (a timestamp);
316 call p('2012-12-31 08:00:00');
317 drop table t1;
318 drop procedure p;
319 drop function f;
320 end of the tests
321 include/rpl_end.inc