Debian Codegraph @

mysql-test/suite/compat/oracle/r/sp-goto.result

raw
1 set sql_mode=oracle;
2 #
3 # MDEV-10697 sql_mode=ORACLE: GOTO statement
4 #
5 # matrice of tests in procedure
6 # |--------------------------------------------------------
7 # | | Same | Outside | to sub | No |
8 # | | block | one block | block | matching |
9 # | | | | | label |
10 # |--------------------------------------------------------
11 # | Forward jump | F1 | F3 | F5 | F7 |
12 # |--------------------------------------------------------
13 # | Backward jump | F2 | F4 | F6 | F8 |
14 # |--------------------------------------------------------
15 # Jump from handler to outside handling code block : F9
16 # Jump from handler to handling code block : F10 (forbidden)
17 # Jump inside handler : F21
18 # Jump between handler : F22 (forbidden)
19 # Jump from cascaded block with handler : F11
20 # Duplicate label in same block : F12 (forbidden)
21 # Duplicate label in different block : F13
22 # Jump outside unlabeled block : F14
23 # Jump inside/outside labeled block : F15
24 # Jump from if / else : F16
25 # Jump with cursors : F17
26 # Jump outside case : F18
27 # Jump inside/outside case block : F19
28 # Jump outside labeled loop : F20
29 # Jump (continue) labeled loop : F23
30 # Two consecutive label : P24
31 # Two consecutive label (backward and forward jump) : P25
32 # Two consecutive label, continue to wrong label : P26
33 # Consecutive goto label and block label : P27
34 # Test in function
35 # backward jump : func1
36 # forward jump : func2
37 # Test in trigger
38 # forward jump : trg1
39 #
40 # Forward jump in same block
41 #
42 CREATE or replace procedure f1(p2 IN OUT VARCHAR)
43 AS
44 BEGIN
45 p2:='a';
46 goto lab1;
47 <<lab1>>
48 goto lab2;
49 p2:='b';
50 <<lab2>>
51 return ;
52 END;
53 $$
54 call f1(@wp1);
55 select 'f1',@wp1;
56 f1 @wp1
57 f1 a
58 DROP PROCEDURE f1;
59 #
60 # Backward jump in same block
61 #
62 CREATE or replace procedure f2(p2 IN OUT VARCHAR)
63 AS
64 BEGIN
65 p2:='a';
66 <<lab1>>
67 if (p2='b') then
68 return ;
69 end if;
70 p2:='b';
71 goto lab1;
72 END;
73 $$
74 call f2(@wp1);
75 select 'f2',@wp1;
76 f2 @wp1
77 f2 b
78 DROP PROCEDURE f2;
79 #
80 # Forward jump outside one block
81 #
82 CREATE or replace procedure f3(p2 IN OUT VARCHAR)
83 AS
84 BEGIN
85 p2:='a';
86 if (p2='a') then
87 goto lab1;
88 end if;
89 p2:='c';
90 <<lab1>>
91 return ;
92 END;
93 $$
94 call f3(@wp1);
95 select 'f3',@wp1;
96 f3 @wp1
97 f3 a
98 DROP PROCEDURE f3;
99 #
100 # Backward jump outside one block
101 #
102 CREATE or replace procedure f4(p2 IN OUT VARCHAR)
103 AS
104 BEGIN
105 p2:='a';
106 <<lab1>>
107 if (p2='a') then
108 p2:=p2||'b';
109 goto lab1;
110 end if;
111 if (p2='ab') then
112 p2:=p2||'c';
113 end if;
114 return ;
115 END;
116 $$
117 call f4(@wp1);
118 select 'f4',@wp1;
119 f4 @wp1
120 f4 abc
121 DROP PROCEDURE f4;
122 #
123 # Forward jump inside sub block
124 CREATE or replace procedure f5(p2 IN OUT VARCHAR)
125 AS
126 BEGIN
127 p2:='a';
128 goto lab5 ;
129 if (p2='a') then
130 <<lab5>>
131 p2:=p2||'b';
132 end if;
133 return ;
134 END;
135 $$
136 ERROR 42000: GOTO with no matching label: lab5
137 #
138 # Backward jump inside sub block
139 CREATE or replace procedure f6(p2 IN OUT VARCHAR)
140 AS
141 BEGIN
142 p2:='a';
143 if (p2='a') then
144 <<lab6>>
145 p2:=p2||'b';
146 return ;
147 end if;
148 goto lab6 ;
149 END;
150 $$
151 ERROR 42000: GOTO with no matching label: lab6
152 #
153 # Backward jump - missing label
154 CREATE or replace procedure f7(p2 IN OUT VARCHAR)
155 AS
156 BEGIN
157 <<lab>>
158 goto lab7 ;
159 return ;
160 END;
161 $$
162 ERROR 42000: GOTO with no matching label: lab7
163 #
164 # Forward jump - missing label
165 CREATE or replace procedure f8(p2 IN OUT VARCHAR)
166 AS
167 BEGIN
168 goto lab8 ;
169 <<lab>>
170 return ;
171 END;
172 $$
173 ERROR 42000: GOTO with no matching label: lab8
174 #
175 # Jump from handler to procedure code
176 #
177 CREATE or replace procedure f9(lim INT, res OUT VARCHAR)
178 AS
179 a INT;
180 BEGIN
181 <<lab9>>
182 if lim=-1 then
183 res:=res||' -- goto end limit -1 --';
184 goto lab9_end;
185 end if;
186 begin
187 SELECT a INTO a FROM information_schema.tables LIMIT lim;
188 EXCEPTION
189 WHEN TOO_MANY_ROWS THEN
190 begin
191 res:=res||'--- too_many_rows cought ---';
192 lim:=0;
193 goto lab9;
194 end;
195 WHEN NO_DATA_FOUND THEN
196 begin
197 res:=res||'--- no_data_found cought ---';
198 lim:=-1;
199 goto lab9;
200 end;
201 end;
202 res:=res||'error';
203 <<lab9_end>>
204 return ;
205 END;
206 $$
207 SET @res='';
208 CALL f9(2, @res);
209 SELECT 'f9',@res;
210 f9 @res
211 f9 --- too_many_rows cought ------ no_data_found cought --- -- goto end limit -1 --
212 CALL f9(0, @res);
213 SELECT 'f9',@res;
214 f9 @res
215 f9 --- no_data_found cought --- -- goto end limit -1 --
216 DROP PROCEDURE f9;
217 #
218 # Jump from handler to handling bloc
219 CREATE or replace procedure f10(lim INT, res OUT VARCHAR)
220 AS
221 a INT;
222 BEGIN
223 begin
224 <<lab10>>
225 SELECT a INTO a FROM information_schema.tables LIMIT lim;
226 EXCEPTION
227 WHEN TOO_MANY_ROWS THEN
228 begin
229 res:='--- too_many_rows cought ---';
230 goto lab10;
231 end;
232 WHEN NO_DATA_FOUND THEN res:='--- no_data_found cought ---';
233 end;
234 return ;
235 END;
236 $$
237 ERROR 42000: GOTO with no matching label: lab10
238 #
239 # Jump from cascaded block with handler
240 #
241 CREATE or replace procedure f11(lim INT, res OUT VARCHAR)
242 AS
243 a INT;
244 BEGIN
245 <<lab11a>>
246 begin
247 SELECT a INTO a FROM information_schema.tables LIMIT lim;
248 EXCEPTION
249 WHEN TOO_MANY_ROWS THEN
250 begin
251 res:=res||'--- too_many_rows cought 1 ---';
252 goto lab11b;
253 end;
254 WHEN NO_DATA_FOUND THEN
255 begin
256 res:=res||'--- no_data_found cought 1 ---';
257 lim:=2;
258 SELECT a INTO a FROM information_schema.tables LIMIT lim;
259 EXCEPTION
260 WHEN TOO_MANY_ROWS THEN
261 begin
262 res:=res||'--- too_many_rows cought 2 ---';
263 goto lab11a;
264 end;
265 WHEN NO_DATA_FOUND THEN res:='--- no_data_found cought 2 ---';
266 end;
267 end;
268 set res:=res||' error ';
269 <<lab11b>>
270 return ;
271 END;
272 $$
273 SET @res='';
274 CALL f11(0, @res);
275 SELECT 'f11',@res;
276 f11 @res
277 f11 --- no_data_found cought 1 ------ too_many_rows cought 2 ------ too_many_rows cought 1 ---
278 DROP PROCEDURE f11;
279 #
280 # Jump inside handler
281 #
282 CREATE or replace procedure f21(lim INT, res OUT VARCHAR)
283 AS
284 a INT;
285 BEGIN
286 begin
287 SELECT a INTO a FROM information_schema.tables LIMIT lim;
288 EXCEPTION
289 WHEN TOO_MANY_ROWS THEN
290 begin
291 <<retry>>
292 lim:=lim-1;
293 loop
294 begin
295 SELECT a INTO a FROM information_schema.tables LIMIT lim;
296 EXCEPTION
297 WHEN TOO_MANY_ROWS THEN
298 begin
299 lim:=lim-1;
300 goto retry;
301 end;
302 end;
303 exit ;
304 end loop;
305 end;
306 end;
307 res:=lim;
308 return ;
309 END;
310 $$
311 SET @res='';
312 CALL f21(10, @res);
313 SELECT 'f21',@res;
314 f21 @res
315 f21 1
316 drop procedure f21;
317 #
318 # Jump beetween handler
319 CREATE or replace procedure f22(lim INT, res OUT VARCHAR)
320 AS
321 a INT;
322 BEGIN
323 res:='ok';
324 begin
325 SELECT a INTO a FROM information_schema.tables LIMIT lim;
326 EXCEPTION
327 WHEN TOO_MANY_ROWS THEN
328 goto nodata ;
329 WHEN NO_DATA_FOUND THEN
330 begin
331 <<nodata>>
332 res:='error';
333 end;
334 end;
335 return ;
336 END;
337 $$
338 ERROR 42000: GOTO with no matching label: nodata
339 #
340 # Duplicate label in same bloc
341 CREATE or replace procedure f12(lim INT, res OUT VARCHAR)
342 AS
343 a INT;
344 BEGIN
345 <<lab12>>
346 res:='error';
347 <<lab12>>
348 return ;
349 END;
350 $$
351 ERROR 42000: Redefining label lab12
352 #
353 # Duplicate label in different block
354 #
355 CREATE or replace procedure f13(lim INT, res OUT VARCHAR)
356 AS
357 a INT;
358 BEGIN
359 a:=0;
360 <<lab13>>
361 a:=a+1;
362 begin
363 <<lab13>>
364 a:=a+1;
365 if (a<10) then
366 goto lab13;
367 end if;
368 end;
369 res:=a;
370 if (a=10) then
371 goto lab13;
372 end if;
373 return ;
374 END;
375 $$
376 SET @res='';
377 CALL f13(0, @res);
378 SELECT 'f13',@res;
379 f13 @res
380 f13 12
381 DROP PROCEDURE f13;
382 #
383 # Jump outside unlabeled block
384 #
385 CREATE or replace procedure f14(lim INT, res OUT VARCHAR)
386 AS
387 a INT;
388 BEGIN
389 a:=0;
390 loop
391 a:=a+1;
392 if (a<10) then
393 continue;
394 end if;
395 if (a>=lim) then
396 goto lab14;
397 end if;
398 if (a>=20) then
399 exit;
400 end if;
401 end loop;
402 <<lab14>>
403 res:=a;
404 return ;
405 END;
406 $$
407 SET @res='';
408 CALL f14(15, @res);
409 SELECT 'f14',@res;
410 f14 @res
411 f14 15
412 CALL f14(8, @res);
413 SELECT 'f14',@res;
414 f14 @res
415 f14 10
416 CALL f14(25, @res);
417 SELECT 'f14',@res;
418 f14 @res
419 f14 20
420 DROP PROCEDURE f14;
421 #
422 # Jump inside/outside labeled block
423 #
424 CREATE or replace procedure f15(lim INT, res OUT VARCHAR)
425 AS
426 a INT;
427 BEGIN
428 a:=0;
429 <<looplabel>> loop
430 <<beginlooplabel>>
431 a:=a+1;
432 if (a<10) then
433 continue looplabel;
434 end if;
435 if (a>=lim) then
436 goto lab15;
437 end if;
438 if (a>=20) then
439 exit looplabel;
440 end if;
441 goto beginlooplabel;
442 end loop;
443 <<lab15>>
444 res:=a;
445 return ;
446 END;
447 $$
448 SET @res='';
449 CALL f15(15, @res);
450 SELECT 'f15',@res;
451 f15 @res
452 f15 15
453 CALL f15(8, @res);
454 SELECT 'f15',@res;
455 f15 @res
456 f15 10
457 CALL f15(25, @res);
458 SELECT 'f15',@res;
459 f15 @res
460 f15 20
461 DROP PROCEDURE f15;
462 #
463 # Jump from if / else
464 #
465 CREATE or replace procedure f16(lim INT, res OUT VARCHAR)
466 AS
467 a INT;
468 BEGIN
469 if (lim<10) then
470 goto lab16_1;
471 else
472 goto lab16_2;
473 end if;
474 <<lab16_1>>
475 res:='if lab16_1';
476 goto lab16_3;
477 <<lab16_2>>
478 res:='else lab16_2';
479 goto lab16_3;
480 res:='error lab16_3';
481 <<lab16_3>>
482 return ;
483 END;
484 $$
485 SET @res='';
486 CALL f16(15, @res);
487 SELECT 'f16',@res;
488 f16 @res
489 f16 else lab16_2
490 CALL f16(8, @res);
491 SELECT 'f16',@res;
492 f16 @res
493 f16 if lab16_1
494 DROP PROCEDURE f16;
495 #
496 # Jump with cursors
497 #
498 CREATE or replace procedure f17(lim INT, res OUT VARCHAR)
499 AS
500 v_a INT;
501 v_b VARCHAR(10);
502 CURSOR cur1 IS SELECT 1 FROM dual where 1=2;
503 BEGIN
504 OPEN cur1;
505 LOOP
506 FETCH cur1 INTO v_a;
507 EXIT WHEN cur1%NOTFOUND;
508 END LOOP;
509 CLOSE cur1;
510 <<lab17>>
511 lim:=lim-1;
512 begin
513 declare
514 CURSOR cur1 IS SELECT 1 FROM dual;
515 CURSOR cur2 IS SELECT 1 FROM dual where 1=2;
516 begin
517 LOOP
518 OPEN cur1;
519 FETCH cur1 INTO v_a;
520 EXIT WHEN cur1%NOTFOUND;
521 res:=res||'-'||lim ;
522 close cur1;
523 if (lim>0) then
524 goto lab17;
525 else
526 goto lab17_end;
527 end if;
528 END LOOP;
529 end;
530 <<lab17_end>>
531 null;
532 end;
533 END;
534 $$
535 SET @res='';
536 CALL f17(5, @res);
537 SELECT 'f17',@res;
538 f17 @res
539 f17 -4-3-2-1-0
540 DROP PROCEDURE f17;
541 #
542 # Jump outside case
543 #
544 CREATE or replace procedure f18(lim INT, res OUT VARCHAR)
545 AS
546 a INT;
547 BEGIN
548 case lim
549 when 1 then
550 res:='case branch 18_1';
551 goto lab18_1;
552 res:='error';
553 when 2 then
554 res:='case branch 18_2';
555 goto lab18_2;
556 res:='error';
557 else
558 res:='default branch 18';
559 end case;
560 <<lab18_1>>
561 null;
562 <<lab18_2>>
563 return ;
564 END;
565 $$
566 SET @res='';
567 CALL f18(0, @res);
568 SELECT 'f18',@res;
569 f18 @res
570 f18 default branch 18
571 CALL f18(1, @res);
572 SELECT 'f18',@res;
573 f18 @res
574 f18 case branch 18_1
575 CALL f18(2, @res);
576 SELECT 'f18',@res;
577 f18 @res
578 f18 case branch 18_2
579 DROP PROCEDURE f18;
580 #
581 # Jump inside/outside case block
582 #
583 CREATE or replace procedure f19(lim INT, res OUT VARCHAR)
584 AS
585 a INT;
586 BEGIN
587 a:=1;
588 case lim
589 when 1 then
590 <<lab19_0>>
591 a:=a+1;
592 if (a<10) then
593 goto lab19_0;
594 else
595 goto lab19_1;
596 end if;
597 res:='case branch 19_1';
598 else
599 res:='default branch 18';
600 end case;
601 goto lab19_end;
602 <<lab19_1>>
603 res:=a;
604 <<lab19_end>>
605 return ;
606 END;
607 $$
608 SET @res='';
609 CALL f19(1, @res);
610 SELECT 'f19',@res;
611 f19 @res
612 f19 10
613 DROP PROCEDURE f19;
614 #
615 # Jump outside labeled loop
616 #
617 CREATE OR REPLACE PROCEDURE f20(res OUT VARCHAR)
618 AS
619 a INT := 1;
620 BEGIN
621 <<lab>>
622 FOR i IN a..10 LOOP
623 IF i = 5 THEN
624 a:= a+1;
625 goto lab;
626 END IF;
627 END LOOP;
628 res:=a;
629 END;
630 $$
631 CALL f20(@res);
632 SELECT 'f20',@res;
633 f20 @res
634 f20 6
635 DROP PROCEDURE f20;
636 #
637 # Jump (continue) labeled loop
638 #
639 CREATE OR REPLACE PROCEDURE f23(res OUT VARCHAR)
640 AS
641 a INT := 1;
642 BEGIN
643 <<lab>>
644 FOR i IN a..10 LOOP
645 IF i = 5 THEN
646 a:= a+1;
647 continue lab;
648 END IF;
649 END LOOP;
650 res:=a;
651 END;
652 $$
653 CALL f23(@res);
654 SELECT 'f23',@res;
655 f23 @res
656 f23 2
657 DROP PROCEDURE f23;
658 #
659 # Two consecutive label (backward jump)
660 #
661 CREATE OR REPLACE PROCEDURE p24(action IN INT, res OUT varchar) AS
662 a integer;
663 BEGIN
664 <<lab1>>
665 <<lab2>>
666 if (action = 1) then
667 res:=res||' '||action;
668 action:=2;
669 goto lab1;
670 end if;
671 if (action = 2) then
672 res:=res||' '||action;
673 action:=3;
674 goto lab2;
675 end if;
676 END;
677 $$
678 call p24(1,@res);
679 select 'p24',@res;
680 p24 @res
681 p24 1 2
682 DROP PROCEDURE p24;
683 #
684 # Two consecutive label (backward and forward jump)
685 #
686 CREATE OR REPLACE PROCEDURE p25(action IN INT, res OUT varchar) AS
687 a integer;
688 BEGIN
689 if (action = 1) then
690 res:=res||' '||action;
691 action:=2;
692 goto lab2;
693 end if;
694 goto lab_end;
695 <<lab1>>
696 <<lab2>>
697 res:=res||' '||action;
698 if (action = 2) then
699 res:=res||' '||action;
700 action:=3;
701 goto lab1;
702 end if;
703 <<lab_end>>
704 null;
705 END;
706 $$
707 call p25(1,@res);
708 select 'p25',@res;
709 p25 @res
710 p25 1 2 2 3
711 DROP PROCEDURE p25;
712 #
713 # Two consecutive label, continue to wrong label
714 CREATE OR REPLACE PROCEDURE p26(action IN INT, res OUT varchar) AS
715 BEGIN
716 <<lab1>>
717 <<lab2>>
718 FOR i IN 1..10 LOOP
719 continue lab1;
720 END LOOP;
721 END;
722 $$
723 ERROR 42000: CONTINUE with no matching label: lab1
724 #
725 # Consecutive goto label and block label
726 #
727 CREATE OR REPLACE PROCEDURE p27(action IN INT, res OUT varchar) AS
728 BEGIN
729 res:='';
730 <<lab1>>
731 <<lab2>>
732 FOR i IN 1..10 LOOP
733 if (action = 1) then
734 res:=res||' '||action||'-'||i;
735 action:=2;
736 continue lab2;
737 end if;
738 if (action = 2) then
739 res:=res||' '||action||'-'||i;
740 action:='3';
741 goto lab2;
742 end if;
743 if (action = 3) then
744 res:=res||' '||action||'-'||i;
745 action:='4';
746 goto lab1;
747 end if;
748 if (action = 4) then
749 res:=res||' '||action||'-'||i;
750 exit lab2;
751 end if;
752 END LOOP;
753 END;
754 $$
755 call p27(1,@res);
756 select 'p27',@res;
757 p27 @res
758 p27 1-1 2-2 3-1 4-1
759 DROP PROCEDURE p27;
760 # ----------------------
761 # -- TEST IN FUNCTION --
762 # ----------------------
763 #
764 # FUNCTION : Backward jump
765 #
766 CREATE or replace function func1()
767 return varchar
768 AS
769 p2 varchar(10);
770 BEGIN
771 p2:='a';
772 <<lab1>>
773 if (p2='a') then
774 p2:=p2||'b';
775 goto lab1;
776 end if;
777 if (p2='ab') then
778 p2:=p2||'c';
779 end if;
780 return p2;
781 END;
782 $$
783 select 'func1',func1();
784 func1 func1()
785 func1 abc
786 DROP function func1;
787 #
788 # FUNCTION : forward jump
789 #
790 CREATE or replace function func2()
791 return varchar
792 AS
793 p2 varchar(10);
794 BEGIN
795 p2:='a';
796 if (p2='a') then
797 goto lab1;
798 end if;
799 p2:='b';
800 <<lab1>>
801 return p2;
802 END;
803 $$
804 select 'func2',func2();
805 func2 func2()
806 func2 a
807 DROP function func2;
808 # ---------------------
809 # -- TEST IN TRIGGER --
810 # ---------------------
811 #
812 # TRIGGER : forward jump
813 #
814 CREATE TABLE t1 (a INT);
815 CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
816 BEGIN
817 IF :NEW.a IS NULL
818 THEN
819 :NEW.a:= 15;
820 goto end_trg;
821 END IF;
822 :NEW.a:= 10;
823 <<end_trg>>
824 null;
825 END;
826 $$
827 insert into t1 values (1);
828 insert into t1 values (null);
829 SELECT * FROM t1;
830 a
831 10
832 15
833 DROP TRIGGER trg1;
834 DROP TABLE t1;
835 #
836 # MDEV-20667 Server crash on pop_cursor
837 #
838 CREATE TABLE t1 (a VARCHAR(6));
839 CREATE PROCEDURE p1() IS
840 BEGIN
841 IF 1=2 THEN
842 BEGIN
843 DECLARE
844 CURSOR cur1 IS SELECT a FROM t1 ;
845 BEGIN
846 GOTO iac_err;
847 END;
848 END;
849 END IF;
850 IF 1=1 THEN
851 GOTO iac_err;
852 END IF;
853 << iac_err >>
854 RETURN;
855 END//
856 CALL p1();
857 DROP PROCEDURE p1;
858 DROP TABLE t1;
859 CREATE PROCEDURE p1() IS
860 BEGIN
861 IF 1=2 THEN
862 BEGIN
863 DECLARE
864 CURSOR cur1 IS SELECT a FROM t1 ;
865 BEGIN
866 GOTO iac_err;
867 END;
868 END;
869 END IF;
870 IF 1=1 THEN
871 GOTO iac_err;
872 END IF;
873 << iac_err >>
874 RETURN ;
875 END//
876 CALL p1;
877 DROP PROCEDURE p1;
878 CREATE PROCEDURE p1() IS
879 BEGIN
880 IF 1=2 THEN
881 BEGIN
882 DECLARE
883 CURSOR cur1 IS SELECT a FROM t1 ;
884 BEGIN
885 GOTO iac_err;
886 END;
887 END;
888 END IF;
889 GOTO iac_err;
890 << iac_err >>
891 RETURN ;
892 END//
893 CALL p1;
894 DROP PROCEDURE p1;
895 CREATE PROCEDURE p1() IS
896 BEGIN
897 IF 1=2 THEN
898 BEGIN
899 DECLARE
900 CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
901 BEGIN
902 GOTO iac_err;
903 END;
904 END;
905 END IF;
906 IF 1=1 THEN
907 GOTO iac_err;
908 END IF;
909 <<iac_err >>
910 RETURN;
911 END//
912 CALL p1;
913 DROP PROCEDURE p1;