Debian Codegraph @

mysql-test/suite/rpl/r/rpl_with.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 # Goals: test CTEs in prepared statements, in stored routines,
7 # and if those are replicated correctly.
8 #
9 # In-memory tmp tables
10 set big_tables=0;
11 # CTE in Prepared statement with parameters
12 create table t1(c1 int,c2 int);
13 insert into t1 values(1,2),(3,4);
14 create table t2(a int, b int, c int, d int, e int);
15 # Parameters in CTE which is referenced twice
16 prepare s from
17 'with qn as (select * from t1 where c1=?)
18 select * from qn, qn as qn1';
19 set @p_qn=1;
20 execute s using @p_qn;
21 c1 c2 c1 c2
22 1 2 1 2
23 # Parameters in two CTEs which are referenced in FROM
24 # in the opposite order of their WITH definitions.
25 prepare s from
26 'with qn as (select * from t1 where c1=?),
27 qn2 as (select * from t1 where c1=?)
28 select ? , qn.*, qn2.* from qn2 left join qn on 1';
29 # Test binlogging, with INSERT SELECT, to see if the
30 # parameter-substituted query is correct.
31 prepare si from
32 'insert into t2
33 with qn as (select * from t1 where c1=?),
34 qn2 as (select * from t1 where c1=?)
35 select ? , qn.*, qn2.* from qn2 left join qn on 1';
36 set @p_qn=1,@p_qn2=2,@p_select=10;
37 execute s using @p_qn,@p_qn2,@p_select;
38 ? c1 c2 c1 c2
39 # We execute twice, as 2nd-exec bugs are common.
40 execute s using @p_qn,@p_qn2,@p_select;
41 ? c1 c2 c1 c2
42 execute si using @p_qn,@p_qn2,@p_select;
43 execute si using @p_qn,@p_qn2,@p_select;
44 select * from t2;
45 a b c d e
46 # On slave:
47 select * from t2;
48 a b c d e
49 # Back to Master.
50 set @p_qn=2,@p_qn2=1,@p_select=20;
51 execute s using @p_qn,@p_qn2,@p_select;
52 ? c1 c2 c1 c2
53 20 NULL NULL 1 2
54 execute s using @p_qn,@p_qn2,@p_select;
55 ? c1 c2 c1 c2
56 20 NULL NULL 1 2
57 execute si using @p_qn,@p_qn2,@p_select;
58 execute si using @p_qn,@p_qn2,@p_select;
59 select * from t2;
60 a b c d e
61 20 NULL NULL 1 2
62 20 NULL NULL 1 2
63 # On slave:
64 select * from t2;
65 a b c d e
66 20 NULL NULL 1 2
67 20 NULL NULL 1 2
68 # Back to Master.
69 drop table t1;
70 # CTE in SP
71 create table t1(a int)|
72 insert into t1 values(1),(2)|
73 create table t3(a char(16), b int)|
74 # Materialized CTE
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 open c;
85 repeat
86 fetch c into a, b;
87 if not done then
88 insert into test.t3 values (a,b);
89 end if;
90 until done end repeat;
91 close c;
92 end|
93 call cur1()|
94 select * from t3|
95 a b
96 1 1
97 1 2
98 2 1
99 2 2
100 # On slave:
101 select * from t3|
102 a b
103 1 1
104 1 2
105 2 1
106 2 2
107 # Back to Master.
108 delete from t3|
109 call cur1()|
110 select * from t3|
111 a b
112 1 1
113 1 2
114 2 1
115 2 2
116 # On slave:
117 select * from t3|
118 a b
119 1 1
120 1 2
121 2 1
122 2 2
123 # Back to Master.
124 delete from t3|
125 drop procedure cur1|
126 # Merged CTE
127 create procedure cur1()
128 begin
129 declare a char(16);
130 declare b int;
131 declare done int default 0;
132 declare c cursor for
133 with qn(x,y) as (select * from t1, t1 t2)
134 select x,y from qn where y<3 order by x;
135 declare continue handler for sqlstate '02000' set done = 1;
136 open c;
137 repeat
138 fetch c into a, b;
139 if not done then
140 insert into test.t3 values (a,b);
141 end if;
142 until done end repeat;
143 close c;
144 end|
145 call cur1()|
146 select * from t3|
147 a b
148 1 1
149 1 2
150 2 1
151 2 2
152 # On slave:
153 select * from t3|
154 a b
155 1 1
156 1 2
157 2 1
158 2 2
159 # Back to Master.
160 delete from t3|
161 call cur1()|
162 select * from t3|
163 a b
164 1 1
165 1 2
166 2 1
167 2 2
168 # On slave:
169 select * from t3|
170 a b
171 1 1
172 1 2
173 2 1
174 2 2
175 # Back to Master.
176 delete from t3|
177 drop procedure cur1|
178 # Recursive CTE
179 create procedure cur1()
180 begin
181 declare a char(16);
182 declare b int;
183 declare done int default 0;
184 declare c cursor for
185 with recursive qn(x) as (
186 select t1.a from t1 union all select x*3 from qn where x<100)
187 select "got ",x from qn;
188 declare continue handler for sqlstate '02000' set done = 1;
189 open c;
190 repeat
191 fetch c into a, b;
192 if not done then
193 insert into test.t3 values (a,b);
194 end if;
195 until done end repeat;
196 close c;
197 end|
198 call cur1()|
199 select * from t3|
200 a b
201 got 1
202 got 2
203 got 3
204 got 6
205 got 9
206 got 18
207 got 27
208 got 54
209 got 81
210 got 162
211 got 243
212 # On slave:
213 select * from t3|
214 a b
215 got 1
216 got 2
217 got 3
218 got 6
219 got 9
220 got 18
221 got 27
222 got 54
223 got 81
224 got 162
225 got 243
226 # Back to Master.
227 delete from t3|
228 call cur1()|
229 select * from t3|
230 a b
231 got 1
232 got 2
233 got 3
234 got 6
235 got 9
236 got 18
237 got 27
238 got 54
239 got 81
240 got 162
241 got 243
242 # On slave:
243 select * from t3|
244 a b
245 got 1
246 got 2
247 got 3
248 got 6
249 got 9
250 got 18
251 got 27
252 got 54
253 got 81
254 got 162
255 got 243
256 # Back to Master.
257 delete from t3|
258 drop procedure cur1|
259 # A stored function, returning the highest generated number
260 # under a limit provided in input.
261 create function func1(M int) returns int
262 return (
263 with recursive qn (n,x) as (
264 select 1, t1.a from t1 union all
265 select n+1, x*3 from qn where (x*3)<M)
266 select max(x) from qn )|
267 select func1(100),func1(200)|
268 func1(100) func1(200)
269 81 162
270 drop function func1|
271 # A trigger, which when inserting "b" in t3,
272 # sets "a" to the highest generated number under "b"; indeed a
273 # WITH cannot be used in a generated column's expression (which
274 # cannot contain a subquery), so a workaround is a trigger.
275 create trigger t3_bi before insert on t3 for each row
276 begin
277 declare tmp int;
278 set tmp = (
279 with recursive qn (n,x) as (
280 select 1, t1.a from t1 union all
281 select n+1, x*3 from qn where (x*3)<new.b)
282 select max(x) from qn );
283 set new.a= tmp;
284 end|
285 delete from t3|
286 insert into t3 (b) values(300),(1000)|
287 select * from t3|
288 a b
289 243 300
290 729 1000
291 # On slave:
292 select * from t3|
293 a b
294 243 300
295 729 1000
296 # Back to Master.
297 delete from t3|
298 drop trigger t3_bi|
299 # A local variable used in CTE which is referenced twice
300 insert into t1 values(3),(4)|
301 create procedure cur1()
302 begin
303 declare a char(16);
304 declare b int default 0;
305 declare done int default 0;
306 declare c cursor for
307 with qn(x) as (select * from t1 where t1.a>b)
308 select x*10 from qn union all
309 select x*100 from qn;
310 declare continue handler for sqlstate '02000' set done = 1;
311 set b=2;
312 open c;
313 repeat
314 fetch c into a;
315 if not done then
316 insert into test.t3 values (a,1);
317 end if;
318 until done end repeat;
319 close c;
320 set b=3;
321 set done=0;
322 open c;
323 repeat
324 fetch c into a;
325 if not done then
326 insert into test.t3 values (a,2);
327 end if;
328 until done end repeat;
329 close c;
330 end|
331 call cur1()|
332 select * from t3|
333 a b
334 30 1
335 40 1
336 300 1
337 400 1
338 40 2
339 400 2
340 # On slave:
341 select * from t3|
342 a b
343 30 1
344 40 1
345 300 1
346 400 1
347 40 2
348 400 2
349 # Back to Master.
350 delete from t3|
351 drop procedure cur1|
352 # Test binlogging, with INSERT SELECT, to see if the
353 # parameter-substituted query is correct.
354 create procedure cur1()
355 begin
356 declare a char(16);
357 declare b int default 0;
358 set b=2;
359 insert into t3
360 with qn(x) as (select * from t1 where t1.a>b)
361 select x*10,1 from qn union all
362 select x*100,2 from qn;
363 end|
364 call cur1()|
365 select * from t3|
366 a b
367 30 1
368 40 1
369 300 2
370 400 2
371 # On slave:
372 select * from t3|
373 a b
374 30 1
375 40 1
376 300 2
377 400 2
378 # Back to Master.
379 delete from t3|
380 drop procedure cur1|
381 # Two local variables in two CTEs which are referenced in FROM
382 # in the opposite order of their WITH definitions.
383 create procedure cur1()
384 begin
385 declare a char(16);
386 declare b int default 0;
387 declare done int default 0;
388 declare c cursor for
389 with qn(x) as (select t1.a from t1 where t1.a=b),
390 qn1(x) as (select t1.a*10 from t1 where t1.a=a)
391 select qn1.x-qn.x from qn1, qn;
392 declare continue handler for sqlstate '02000' set done = 1;
393 set a=2,b=3;
394 open c;
395 repeat
396 fetch c into a;
397 if not done then
398 insert into test.t3 values (a,1);
399 end if;
400 until done end repeat;
401 close c;
402 end|
403 call cur1()|
404 select * from t3|
405 a b
406 17 1
407 # On slave:
408 select * from t3|
409 a b
410 17 1
411 # Back to Master.
412 delete from t3|
413 call cur1()|
414 select * from t3|
415 a b
416 17 1
417 # On slave:
418 select * from t3|
419 a b
420 17 1
421 # Back to Master.
422 delete from t3|
423 drop procedure cur1|
424 # Similar, with INSERT SELECT, to test binlogging
425 create procedure cur1()
426 begin
427 declare a char(16);
428 declare b int default 0;
429 set b=3,a=2;
430 insert into t3(a)
431 with qn(x) as (select t1.a from t1 where t1.a=b),
432 qn1(x) as (select t1.a*10 from t1 where t1.a=a)
433 select qn1.x-qn.x from qn1, qn;
434 end|
435 call cur1()|
436 select * from t3|
437 a b
438 17 NULL
439 # On slave:
440 select * from t3|
441 a b
442 17 NULL
443 # Back to Master.
444 delete from t3|
445 call cur1()|
446 select * from t3|
447 a b
448 17 NULL
449 # On slave:
450 select * from t3|
451 a b
452 17 NULL
453 # Back to Master.
454 delete from t3|
455 drop procedure cur1|
456 drop table t1,t2,t3|
457 # On-disk tmp tables
458 set big_tables=1;
459 # CTE in Prepared statement with parameters
460 create table t1(c1 int,c2 int);
461 insert into t1 values(1,2),(3,4);
462 create table t2(a int, b int, c int, d int, e int);
463 # Parameters in CTE which is referenced twice
464 prepare s from
465 'with qn as (select * from t1 where c1=?)
466 select * from qn, qn as qn1';
467 set @p_qn=1;
468 execute s using @p_qn;
469 c1 c2 c1 c2
470 1 2 1 2
471 # Parameters in two CTEs which are referenced in FROM
472 # in the opposite order of their WITH definitions.
473 prepare s from
474 'with qn as (select * from t1 where c1=?),
475 qn2 as (select * from t1 where c1=?)
476 select ? , qn.*, qn2.* from qn2 left join qn on 1';
477 # Test binlogging, with INSERT SELECT, to see if the
478 # parameter-substituted query is correct.
479 prepare si from
480 'insert into t2
481 with qn as (select * from t1 where c1=?),
482 qn2 as (select * from t1 where c1=?)
483 select ? , qn.*, qn2.* from qn2 left join qn on 1';
484 set @p_qn=1,@p_qn2=2,@p_select=10;
485 execute s using @p_qn,@p_qn2,@p_select;
486 ? c1 c2 c1 c2
487 # We execute twice, as 2nd-exec bugs are common.
488 execute s using @p_qn,@p_qn2,@p_select;
489 ? c1 c2 c1 c2
490 execute si using @p_qn,@p_qn2,@p_select;
491 execute si using @p_qn,@p_qn2,@p_select;
492 select * from t2;
493 a b c d e
494 # On slave:
495 select * from t2;
496 a b c d e
497 # Back to Master.
498 set @p_qn=2,@p_qn2=1,@p_select=20;
499 execute s using @p_qn,@p_qn2,@p_select;
500 ? c1 c2 c1 c2
501 20 NULL NULL 1 2
502 execute s using @p_qn,@p_qn2,@p_select;
503 ? c1 c2 c1 c2
504 20 NULL NULL 1 2
505 execute si using @p_qn,@p_qn2,@p_select;
506 execute si using @p_qn,@p_qn2,@p_select;
507 select * from t2;
508 a b c d e
509 20 NULL NULL 1 2
510 20 NULL NULL 1 2
511 # On slave:
512 select * from t2;
513 a b c d e
514 20 NULL NULL 1 2
515 20 NULL NULL 1 2
516 # Back to Master.
517 drop table t1;
518 # CTE in SP
519 create table t1(a int)|
520 insert into t1 values(1),(2)|
521 create table t3(a char(16), b int)|
522 # Materialized CTE
523 create procedure cur1()
524 begin
525 declare a char(16);
526 declare b int;
527 declare done int default 0;
528 declare c cursor for
529 with qn(x,y) as (select * from t1, t1 t2 limit 4)
530 select x,y from qn where y<3 order by x;
531 declare continue handler for sqlstate '02000' set done = 1;
532 open c;
533 repeat
534 fetch c into a, b;
535 if not done then
536 insert into test.t3 values (a,b);
537 end if;
538 until done end repeat;
539 close c;
540 end|
541 call cur1()|
542 select * from t3|
543 a b
544 1 1
545 1 2
546 2 1
547 2 2
548 # On slave:
549 select * from t3|
550 a b
551 1 1
552 1 2
553 2 1
554 2 2
555 # Back to Master.
556 delete from t3|
557 call cur1()|
558 select * from t3|
559 a b
560 1 1
561 1 2
562 2 1
563 2 2
564 # On slave:
565 select * from t3|
566 a b
567 1 1
568 1 2
569 2 1
570 2 2
571 # Back to Master.
572 delete from t3|
573 drop procedure cur1|
574 # Merged CTE
575 create procedure cur1()
576 begin
577 declare a char(16);
578 declare b int;
579 declare done int default 0;
580 declare c cursor for
581 with qn(x,y) as (select * from t1, t1 t2)
582 select x,y from qn where y<3 order by x;
583 declare continue handler for sqlstate '02000' set done = 1;
584 open c;
585 repeat
586 fetch c into a, b;
587 if not done then
588 insert into test.t3 values (a,b);
589 end if;
590 until done end repeat;
591 close c;
592 end|
593 call cur1()|
594 select * from t3|
595 a b
596 1 1
597 1 2
598 2 1
599 2 2
600 # On slave:
601 select * from t3|
602 a b
603 1 1
604 1 2
605 2 1
606 2 2
607 # Back to Master.
608 delete from t3|
609 call cur1()|
610 select * from t3|
611 a b
612 1 1
613 1 2
614 2 1
615 2 2
616 # On slave:
617 select * from t3|
618 a b
619 1 1
620 1 2
621 2 1
622 2 2
623 # Back to Master.
624 delete from t3|
625 drop procedure cur1|
626 # Recursive CTE
627 create procedure cur1()
628 begin
629 declare a char(16);
630 declare b int;
631 declare done int default 0;
632 declare c cursor for
633 with recursive qn(x) as (
634 select t1.a from t1 union all select x*3 from qn where x<100)
635 select "got ",x from qn;
636 declare continue handler for sqlstate '02000' set done = 1;
637 open c;
638 repeat
639 fetch c into a, b;
640 if not done then
641 insert into test.t3 values (a,b);
642 end if;
643 until done end repeat;
644 close c;
645 end|
646 call cur1()|
647 select * from t3|
648 a b
649 got 1
650 got 2
651 got 3
652 got 6
653 got 9
654 got 18
655 got 27
656 got 54
657 got 81
658 got 162
659 got 243
660 # On slave:
661 select * from t3|
662 a b
663 got 1
664 got 2
665 got 3
666 got 6
667 got 9
668 got 18
669 got 27
670 got 54
671 got 81
672 got 162
673 got 243
674 # Back to Master.
675 delete from t3|
676 call cur1()|
677 select * from t3|
678 a b
679 got 1
680 got 2
681 got 3
682 got 6
683 got 9
684 got 18
685 got 27
686 got 54
687 got 81
688 got 162
689 got 243
690 # On slave:
691 select * from t3|
692 a b
693 got 1
694 got 2
695 got 3
696 got 6
697 got 9
698 got 18
699 got 27
700 got 54
701 got 81
702 got 162
703 got 243
704 # Back to Master.
705 delete from t3|
706 drop procedure cur1|
707 # A stored function, returning the highest generated number
708 # under a limit provided in input.
709 create function func1(M int) returns int
710 return (
711 with recursive qn (n,x) as (
712 select 1, t1.a from t1 union all
713 select n+1, x*3 from qn where (x*3)<M)
714 select max(x) from qn )|
715 select func1(100),func1(200)|
716 func1(100) func1(200)
717 81 162
718 drop function func1|
719 # A trigger, which when inserting "b" in t3,
720 # sets "a" to the highest generated number under "b"; indeed a
721 # WITH cannot be used in a generated column's expression (which
722 # cannot contain a subquery), so a workaround is a trigger.
723 create trigger t3_bi before insert on t3 for each row
724 begin
725 declare tmp int;
726 set tmp = (
727 with recursive qn (n,x) as (
728 select 1, t1.a from t1 union all
729 select n+1, x*3 from qn where (x*3)<new.b)
730 select max(x) from qn );
731 set new.a= tmp;
732 end|
733 delete from t3|
734 insert into t3 (b) values(300),(1000)|
735 select * from t3|
736 a b
737 243 300
738 729 1000
739 # On slave:
740 select * from t3|
741 a b
742 243 300
743 729 1000
744 # Back to Master.
745 delete from t3|
746 drop trigger t3_bi|
747 # A local variable used in CTE which is referenced twice
748 insert into t1 values(3),(4)|
749 create procedure cur1()
750 begin
751 declare a char(16);
752 declare b int default 0;
753 declare done int default 0;
754 declare c cursor for
755 with qn(x) as (select * from t1 where t1.a>b)
756 select x*10 from qn union all
757 select x*100 from qn;
758 declare continue handler for sqlstate '02000' set done = 1;
759 set b=2;
760 open c;
761 repeat
762 fetch c into a;
763 if not done then
764 insert into test.t3 values (a,1);
765 end if;
766 until done end repeat;
767 close c;
768 set b=3;
769 set done=0;
770 open c;
771 repeat
772 fetch c into a;
773 if not done then
774 insert into test.t3 values (a,2);
775 end if;
776 until done end repeat;
777 close c;
778 end|
779 call cur1()|
780 select * from t3|
781 a b
782 30 1
783 40 1
784 300 1
785 400 1
786 40 2
787 400 2
788 # On slave:
789 select * from t3|
790 a b
791 30 1
792 40 1
793 300 1
794 400 1
795 40 2
796 400 2
797 # Back to Master.
798 delete from t3|
799 drop procedure cur1|
800 # Test binlogging, with INSERT SELECT, to see if the
801 # parameter-substituted query is correct.
802 create procedure cur1()
803 begin
804 declare a char(16);
805 declare b int default 0;
806 set b=2;
807 insert into t3
808 with qn(x) as (select * from t1 where t1.a>b)
809 select x*10,1 from qn union all
810 select x*100,2 from qn;
811 end|
812 call cur1()|
813 select * from t3|
814 a b
815 30 1
816 40 1
817 300 2
818 400 2
819 # On slave:
820 select * from t3|
821 a b
822 30 1
823 40 1
824 300 2
825 400 2
826 # Back to Master.
827 delete from t3|
828 drop procedure cur1|
829 # Two local variables in two CTEs which are referenced in FROM
830 # in the opposite order of their WITH definitions.
831 create procedure cur1()
832 begin
833 declare a char(16);
834 declare b int default 0;
835 declare done int default 0;
836 declare c cursor for
837 with qn(x) as (select t1.a from t1 where t1.a=b),
838 qn1(x) as (select t1.a*10 from t1 where t1.a=a)
839 select qn1.x-qn.x from qn1, qn;
840 declare continue handler for sqlstate '02000' set done = 1;
841 set a=2,b=3;
842 open c;
843 repeat
844 fetch c into a;
845 if not done then
846 insert into test.t3 values (a,1);
847 end if;
848 until done end repeat;
849 close c;
850 end|
851 call cur1()|
852 select * from t3|
853 a b
854 17 1
855 # On slave:
856 select * from t3|
857 a b
858 17 1
859 # Back to Master.
860 delete from t3|
861 call cur1()|
862 select * from t3|
863 a b
864 17 1
865 # On slave:
866 select * from t3|
867 a b
868 17 1
869 # Back to Master.
870 delete from t3|
871 drop procedure cur1|
872 # Similar, with INSERT SELECT, to test binlogging
873 create procedure cur1()
874 begin
875 declare a char(16);
876 declare b int default 0;
877 set b=3,a=2;
878 insert into t3(a)
879 with qn(x) as (select t1.a from t1 where t1.a=b),
880 qn1(x) as (select t1.a*10 from t1 where t1.a=a)
881 select qn1.x-qn.x from qn1, qn;
882 end|
883 call cur1()|
884 select * from t3|
885 a b
886 17 NULL
887 # On slave:
888 select * from t3|
889 a b
890 17 NULL
891 # Back to Master.
892 delete from t3|
893 call cur1()|
894 select * from t3|
895 a b
896 17 NULL
897 # On slave:
898 select * from t3|
899 a b
900 17 NULL
901 # Back to Master.
902 delete from t3|
903 drop procedure cur1|
904 drop table t1,t2,t3|
905 include/rpl_end.inc