Debian Codegraph @

mysql-test/suite/rpl/include/rpl_with.inc

raw
1 connection master;
2
3 --echo # CTE in Prepared statement with parameters
4
5 create table t1(c1 int,c2 int);
6 insert into t1 values(1,2),(3,4);
7 create table t2(a int, b int, c int, d int, e int);
8
9 --echo # Parameters in CTE which is referenced twice
10
11 prepare s from
12 'with qn as (select * from t1 where c1=?)
13 select * from qn, qn as qn1';
14
15 set @p_qn=1;
16 execute s using @p_qn;
17
18 --echo # Parameters in two CTEs which are referenced in FROM
19 --echo # in the opposite order of their WITH definitions.
20
21 prepare s from
22 'with qn as (select * from t1 where c1=?),
23 qn2 as (select * from t1 where c1=?)
24 select ? , qn.*, qn2.* from qn2 left join qn on 1';
25
26 --echo # Test binlogging, with INSERT SELECT, to see if the
27 --echo # parameter-substituted query is correct.
28
29 prepare si from
30 'insert into t2
31 with qn as (select * from t1 where c1=?),
32 qn2 as (select * from t1 where c1=?)
33 select ? , qn.*, qn2.* from qn2 left join qn on 1';
34
35 set @p_qn=1,@p_qn2=2,@p_select=10;
36 execute s using @p_qn,@p_qn2,@p_select;
37 --echo # We execute twice, as 2nd-exec bugs are common.
38 execute s using @p_qn,@p_qn2,@p_select;
39 execute si using @p_qn,@p_qn2,@p_select;
40 execute si using @p_qn,@p_qn2,@p_select;
41
42 select * from t2;
43 sync_slave_with_master;
44 connection slave;
45 --echo # On slave:
46 select * from t2;
47 --echo # Back to Master.
48 connection master;
49
50 set @p_qn=2,@p_qn2=1,@p_select=20;
51 execute s using @p_qn,@p_qn2,@p_select;
52 execute s using @p_qn,@p_qn2,@p_select;
53 execute si using @p_qn,@p_qn2,@p_select;
54 execute si using @p_qn,@p_qn2,@p_select;
55
56 select * from t2;
57 sync_slave_with_master;
58 --echo # On slave:
59 select * from t2;
60 --echo # Back to Master.
61 connection master;
62
63 drop table t1;
64
65 --echo # CTE in SP
66
67 delimiter |;
68
69 create table t1(a int)|
70 insert into t1 values(1),(2)|
71 create table t3(a char(16), b int)|
72
73 --echo # Materialized CTE
74
75 create procedure cur1()
76 begin
77 declare a char(16);
78 declare b int;
79 declare done int default 0;
80 declare c cursor for
81 with qn(x,y) as (select * from t1, t1 t2 limit 4)
82 select x,y from qn where y<3 order by x;
83 declare continue handler for sqlstate '02000' set done = 1;
84
85 open c;
86 repeat
87 fetch c into a, b;
88 if not done then
89 insert into test.t3 values (a,b);
90 end if;
91 until done end repeat;
92 close c;
93 end|
94
95 call cur1()|
96 --sorted_result
97 select * from t3|
98 sync_slave_with_master|
99 connection slave|
100 --echo # On slave:
101 --sorted_result
102 select * from t3|
103 --echo # Back to Master.
104 connection master|
105
106 delete from t3|
107 # Test 2nd execution
108 call cur1()|
109 --sorted_result
110 select * from t3|
111 sync_slave_with_master|
112 connection slave|
113 --echo # On slave:
114 --sorted_result
115 select * from t3|
116 --echo # Back to Master.
117 connection master|
118 delete from t3|
119
120 drop procedure cur1|
121
122 --echo # Merged CTE
123
124 create procedure cur1()
125 begin
126 declare a char(16);
127 declare b int;
128 declare done int default 0;
129 declare c cursor for
130 with qn(x,y) as (select * from t1, t1 t2)
131 select x,y from qn where y<3 order by x;
132 declare continue handler for sqlstate '02000' set done = 1;
133
134 open c;
135 repeat
136 fetch c into a, b;
137 if not done then
138 insert into test.t3 values (a,b);
139 end if;
140 until done end repeat;
141 close c;
142 end|
143
144 call cur1()|
145 --sorted_result
146 select * from t3|
147 sync_slave_with_master|
148 connection slave|
149 --echo # On slave:
150 --sorted_result
151 select * from t3|
152 --echo # Back to Master.
153 connection master|
154 delete from t3|
155
156 call cur1()|
157 --sorted_result
158 select * from t3|
159 sync_slave_with_master|
160 connection slave|
161 --echo # On slave:
162 --sorted_result
163 select * from t3|
164 --echo # Back to Master.
165 connection master|
166 delete from t3|
167 drop procedure cur1|
168
169 --echo # Recursive CTE
170 # Note the "select **t1.a**"; a mere "select a" would select the local
171 # variable!
172
173 create procedure cur1()
174 begin
175 declare a char(16);
176 declare b int;
177 declare done int default 0;
178 declare c cursor for
179 with recursive qn(x) as (
180 select t1.a from t1 union all select x*3 from qn where x<100)
181 select "got ",x from qn;
182 declare continue handler for sqlstate '02000' set done = 1;
183
184 open c;
185 repeat
186 fetch c into a, b;
187 if not done then
188 insert into test.t3 values (a,b);
189 end if;
190 until done end repeat;
191 close c;
192 end|
193
194 call cur1()|
195 select * from t3|
196 sync_slave_with_master|
197 connection slave|
198 --echo # On slave:
199 select * from t3|
200 --echo # Back to Master.
201 connection master|
202 delete from t3|
203
204 call cur1()|
205 select * from t3|
206 sync_slave_with_master|
207 connection slave|
208 --echo # On slave:
209 select * from t3|
210 --echo # Back to Master.
211 connection master|
212 delete from t3|
213 drop procedure cur1|
214
215 --echo # A stored function, returning the highest generated number
216 --echo # under a limit provided in input.
217 create function func1(M int) returns int
218 return (
219 with recursive qn (n,x) as (
220 select 1, t1.a from t1 union all
221 select n+1, x*3 from qn where (x*3)<M)
222 select max(x) from qn )|
223
224 select func1(100),func1(200)|
225 drop function func1|
226
227 --echo # A trigger, which when inserting "b" in t3,
228 --echo # sets "a" to the highest generated number under "b"; indeed a
229 --echo # WITH cannot be used in a generated column's expression (which
230 --echo # cannot contain a subquery), so a workaround is a trigger.
231
232 create trigger t3_bi before insert on t3 for each row
233 begin
234 declare tmp int;
235 set tmp = (
236 with recursive qn (n,x) as (
237 select 1, t1.a from t1 union all
238 select n+1, x*3 from qn where (x*3)<new.b)
239 select max(x) from qn );
240 set new.a= tmp;
241 end|
242
243 delete from t3|
244 insert into t3 (b) values(300),(1000)|
245 select * from t3|
246 sync_slave_with_master|
247 connection slave|
248 --echo # On slave:
249 select * from t3|
250 --echo # Back to Master.
251 connection master|
252 delete from t3|
253
254 drop trigger t3_bi|
255
256 --echo # A local variable used in CTE which is referenced twice
257
258 insert into t1 values(3),(4)|
259
260 create procedure cur1()
261 begin
262 declare a char(16);
263 declare b int default 0;
264 declare done int default 0;
265 declare c cursor for
266 with qn(x) as (select * from t1 where t1.a>b)
267 select x*10 from qn union all
268 select x*100 from qn;
269 declare continue handler for sqlstate '02000' set done = 1;
270
271 set b=2;
272 open c;
273 repeat
274 fetch c into a;
275 if not done then
276 insert into test.t3 values (a,1);
277 end if;
278 until done end repeat;
279 close c;
280 set b=3;
281 set done=0;
282 open c;
283 repeat
284 fetch c into a;
285 if not done then
286 insert into test.t3 values (a,2);
287 end if;
288 until done end repeat;
289 close c;
290 end|
291
292 call cur1()|
293 select * from t3|
294 sync_slave_with_master|
295 connection slave|
296 --echo # On slave:
297 select * from t3|
298 --echo # Back to Master.
299 connection master|
300 delete from t3|
301 drop procedure cur1|
302
303 --echo # Test binlogging, with INSERT SELECT, to see if the
304 --echo # parameter-substituted query is correct.
305
306 create procedure cur1()
307 begin
308 declare a char(16);
309 declare b int default 0;
310 set b=2;
311 insert into t3
312 with qn(x) as (select * from t1 where t1.a>b)
313 select x*10,1 from qn union all
314 select x*100,2 from qn;
315 end|
316
317 call cur1()|
318 select * from t3|
319 sync_slave_with_master|
320 connection slave|
321 --echo # On slave:
322 select * from t3|
323 --echo # Back to Master.
324 connection master|
325 delete from t3|
326 drop procedure cur1|
327
328 --echo # Two local variables in two CTEs which are referenced in FROM
329 --echo # in the opposite order of their WITH definitions.
330
331 create procedure cur1()
332 begin
333 declare a char(16);
334 declare b int default 0;
335 declare done int default 0;
336 declare c cursor for
337 with qn(x) as (select t1.a from t1 where t1.a=b),
338 qn1(x) as (select t1.a*10 from t1 where t1.a=a)
339 select qn1.x-qn.x from qn1, qn;
340 declare continue handler for sqlstate '02000' set done = 1;
341
342 set a=2,b=3;
343 open c;
344 repeat
345 fetch c into a;
346 if not done then
347 insert into test.t3 values (a,1);
348 end if;
349 until done end repeat;
350 close c;
351 end|
352
353 call cur1()|
354 select * from t3|
355 sync_slave_with_master|
356 connection slave|
357 --echo # On slave:
358 select * from t3|
359 --echo # Back to Master.
360 connection master|
361 delete from t3|
362
363 call cur1()|
364 select * from t3|
365 sync_slave_with_master|
366 connection slave|
367 --echo # On slave:
368 select * from t3|
369 --echo # Back to Master.
370 connection master|
371 delete from t3|
372 drop procedure cur1|
373
374 --echo # Similar, with INSERT SELECT, to test binlogging
375
376 create procedure cur1()
377 begin
378 declare a char(16);
379 declare b int default 0;
380 set b=3,a=2;
381 insert into t3(a)
382 with qn(x) as (select t1.a from t1 where t1.a=b),
383 qn1(x) as (select t1.a*10 from t1 where t1.a=a)
384 select qn1.x-qn.x from qn1, qn;
385 end|
386
387 call cur1()|
388 select * from t3|
389 sync_slave_with_master|
390 connection slave|
391 --echo # On slave:
392 select * from t3|
393 --echo # Back to Master.
394 connection master|
395 delete from t3|
396
397 call cur1()|
398 select * from t3|
399 sync_slave_with_master|
400 connection slave|
401 --echo # On slave:
402 select * from t3|
403 --echo # Back to Master.
404 connection master|
405 delete from t3|
406 drop procedure cur1|
407
408 drop table t1,t2,t3|
409
410 delimiter ;|