Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp.test

raw
1 --source include/default_charset.inc
2
3 SET sql_mode=ORACLE;
4
5 --echo # Testing routines with no parameters
6 DELIMITER /;
7 CREATE FUNCTION f1 RETURN INT
8 AS
9 BEGIN
10 RETURN 10;
11 END;
12 /
13 DELIMITER ;/
14 --vertical_results
15 SHOW CREATE FUNCTION f1;
16 --horizontal_results
17 SELECT f1();
18 DROP FUNCTION f1;
19
20
21 DELIMITER /;
22 CREATE PROCEDURE p1
23 AS
24 BEGIN
25 SET @a=10;
26 END;
27 /
28 DELIMITER ;/
29 --vertical_results
30 SHOW CREATE PROCEDURE p1;
31 --horizontal_results
32 SET @a=0;
33 CALL p1();
34 SELECT @a;
35 DROP PROCEDURE p1;
36
37 --echo # Testing ":=" to set the default value of a variable
38 DELIMITER /;
39 CREATE FUNCTION f1 () RETURN NUMBER(10) AS
40 a NUMBER(10) := 10;
41 BEGIN
42 DECLARE
43 b NUMBER(10) DEFAULT 3;
44 BEGIN
45 RETURN a+b;
46 END;
47 END;
48 /
49 DELIMITER ;/
50 SELECT f1();
51 DROP FUNCTION f1;
52
53 --echo # Testing labels
54
55 DELIMITER /;
56 CREATE FUNCTION f1 (a INT) RETURN CLOB AS
57 BEGIN
58 <<label1>>
59 BEGIN
60 IF a = 1 THEN
61 LEAVE label1;
62 END IF;
63 RETURN 'IS NOT 1';
64 END label1;
65 RETURN 'IS 1';
66 END;
67 /
68 DELIMITER ;/
69 SELECT f1(1);
70 SELECT f1(2);
71 DROP FUNCTION f1;
72
73
74 DELIMITER /;
75 CREATE FUNCTION f1 (a INT) RETURN INT IS
76 BEGIN
77 <<label1>>
78 LOOP
79 IF a = 2 THEN
80 LEAVE label1;
81 END IF;
82 SET a= a-1;
83 END LOOP;
84 RETURN a;
85 END;
86 /
87 DELIMITER ;/
88 SELECT f1(4);
89 DROP FUNCTION f1;
90
91
92 DELIMITER /;
93 CREATE FUNCTION f1 (a INT) RETURN INT AS
94 BEGIN
95 <<label1>>
96 WHILE a>0 LOOP
97 IF a = 2 THEN
98 LEAVE label1;
99 END IF;
100 SET a= a-1;
101 END LOOP label1;
102 RETURN a;
103 END;
104 /
105 DELIMITER ;/
106 SELECT f1(4);
107 DROP FUNCTION f1;
108
109
110 DELIMITER /;
111 CREATE FUNCTION f1 (a INT) RETURN INT AS
112 BEGIN
113 <<label1>>
114 REPEAT
115 IF a = 2 THEN
116 LEAVE label1;
117 END IF;
118 SET a= a-1;
119 UNTIL a=0 END REPEAT;
120 RETURN a;
121 END;
122 /
123 DELIMITER ;/
124 SELECT f1(4);
125 DROP FUNCTION f1;
126
127 --echo # Testing IN/OUT/INOUT
128
129 DELIMITER /;
130 CREATE PROCEDURE p1 (p1 IN VARCHAR2(10), p2 OUT VARCHAR2(10)) AS
131 BEGIN
132 SET p1='p1new';
133 SET p2='p2new';
134 END;
135 /
136 DELIMITER ;/
137 SET @p1='p1', @p2='p2';
138 CALL p1(@p1, @p2);
139 SELECT @p1, @p2;
140 DROP PROCEDURE p1;
141
142 --echo # Testing Oracle-style assigment
143 DELIMITER /;
144 CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10)) AS
145 BEGIN
146 p1:= 'p1new';
147 END;
148 /
149 DELIMITER ;/
150 SET @p1='p1';
151 CALL p1(@p1);
152 SELECT @p1;
153 DROP PROCEDURE p1;
154
155 --echo # Testing that NULL is a valid statement
156 DELIMITER /;
157 CREATE PROCEDURE p1(a INT) AS
158 BEGIN
159 NULL;
160 END;
161 /
162 DELIMITER ;/
163 DROP PROCEDURE p1;
164
165 DELIMITER /;
166 CREATE PROCEDURE p1(a INT) AS
167 a INT:=10;
168 BEGIN
169 IF a=10 THEN NULL; ELSE NULL; END IF;
170 END;
171 /
172 DELIMITER ;/
173 DROP PROCEDURE p1;
174
175 --echo # Keywords that are OK for table names, but not for SP variables
176 CREATE TABLE function (function int);
177 INSERT INTO function SET function=10;
178 SELECT function.function FROM function;
179 DROP TABLE function;
180
181 --echo # Testing that (some) keyword_sp are allowed in Oracle-style assignments
182 DELIMITER /;
183 CREATE PROCEDURE p1 (action OUT INT) AS BEGIN action:=10; END;/
184 DROP PROCEDURE p1/
185 CREATE PROCEDURE p1 (clob OUT INT) AS BEGIN clob:=10; END;/
186 DROP PROCEDURE p1/
187 CREATE PROCEDURE p1 (enum OUT INT) AS BEGIN enum:=10; END;/
188 DROP PROCEDURE p1/
189 CREATE PROCEDURE p1 (via OUT INT) AS BEGIN via:=10; END;/
190 DROP PROCEDURE p1/
191 DELIMITER ;/
192
193 --echo # Testing keyword_directly_assignable
194 DELIMITER /;
195 CREATE PROCEDURE p1 (ascii OUT INT) AS BEGIN ascii:=10; END;/
196 DROP PROCEDURE p1/
197 CREATE PROCEDURE p1 (backup OUT INT) AS BEGIN backup:=10; END;/
198 DROP PROCEDURE p1/
199 CREATE PROCEDURE p1 (binlog OUT INT) AS BEGIN binlog:=10; END;/
200 DROP PROCEDURE p1/
201 CREATE PROCEDURE p1 (byte OUT INT) AS BEGIN byte:=10; END;/
202 DROP PROCEDURE p1/
203 CREATE PROCEDURE p1 (cache OUT INT) AS BEGIN cache:=10; END;/
204 DROP PROCEDURE p1/
205 CREATE PROCEDURE p1 (checksum OUT INT) AS BEGIN checksum:=10; END;/
206 DROP PROCEDURE p1/
207 CREATE PROCEDURE p1 (checkpoint OUT INT) AS BEGIN checkpoint:=10; END;/
208 DROP PROCEDURE p1/
209 CREATE PROCEDURE p1 (column_add OUT INT) AS BEGIN column_add:=10; END;/
210 DROP PROCEDURE p1/
211 CREATE PROCEDURE p1 (column_check OUT INT) AS BEGIN column_check:=10; END;/
212 DROP PROCEDURE p1/
213 CREATE PROCEDURE p1 (column_create OUT INT) AS BEGIN column_create:=10; END;/
214 DROP PROCEDURE p1/
215 CREATE PROCEDURE p1 (column_delete OUT INT) AS BEGIN column_delete:=10; END;/
216 DROP PROCEDURE p1/
217 CREATE PROCEDURE p1 (column_get OUT INT) AS BEGIN column_get:=10; END;/
218 DROP PROCEDURE p1/
219 CREATE PROCEDURE p1 (deallocate OUT INT) AS BEGIN deallocate:=10; END;/
220 DROP PROCEDURE p1/
221 CREATE PROCEDURE p1 (examined OUT INT) AS BEGIN examined:=10; END;/
222 DROP PROCEDURE p1/
223 CREATE PROCEDURE p1 (execute OUT INT) AS BEGIN execute:=10; END;/
224 DROP PROCEDURE p1/
225 CREATE PROCEDURE p1 (flush OUT INT) AS BEGIN flush:=10; END;/
226 DROP PROCEDURE p1/
227 CREATE PROCEDURE p1 (format OUT INT) AS BEGIN format:=10; END;/
228 DROP PROCEDURE p1/
229 CREATE PROCEDURE p1 (get OUT INT) AS BEGIN get:=10; END;/
230 DROP PROCEDURE p1/
231 CREATE PROCEDURE p1 (help OUT INT) AS BEGIN help:=10; END;/
232 DROP PROCEDURE p1/
233 CREATE PROCEDURE p1 (host OUT INT) AS BEGIN host:=10; END;/
234 DROP PROCEDURE p1/
235 CREATE PROCEDURE p1 (install OUT INT) AS BEGIN install:=10; END;/
236 DROP PROCEDURE p1/
237 CREATE PROCEDURE p1 (option OUT INT) AS BEGIN option:=10; END;/
238 DROP PROCEDURE p1/
239 CREATE PROCEDURE p1 (options OUT INT) AS BEGIN options:=10; END;/
240 DROP PROCEDURE p1/
241 CREATE PROCEDURE p1 (owner OUT INT) AS BEGIN owner:=10; END;/
242 DROP PROCEDURE p1/
243 CREATE PROCEDURE p1 (parser OUT INT) AS BEGIN parser:=10; END;/
244 DROP PROCEDURE p1/
245 CREATE PROCEDURE p1 (port OUT INT) AS BEGIN port:=10; END;/
246 DROP PROCEDURE p1/
247 CREATE PROCEDURE p1 (prepare OUT INT) AS BEGIN prepare:=10; END;/
248 DROP PROCEDURE p1/
249 CREATE PROCEDURE p1 (remove OUT INT) AS BEGIN remove:=10; END;/
250 DROP PROCEDURE p1/
251 CREATE PROCEDURE p1 (reset OUT INT) AS BEGIN reset:=10; END;/
252 DROP PROCEDURE p1/
253 CREATE PROCEDURE p1 (restore OUT INT) AS BEGIN restore:=10; END;/
254 DROP PROCEDURE p1/
255 CREATE PROCEDURE p1 (security OUT INT) AS BEGIN security:=10; END;/
256 DROP PROCEDURE p1/
257 CREATE PROCEDURE p1 (server OUT INT) AS BEGIN server:=10; END;/
258 DROP PROCEDURE p1/
259 CREATE PROCEDURE p1 (signed OUT INT) AS BEGIN signed:=10; END;/
260 DROP PROCEDURE p1/
261 CREATE PROCEDURE p1 (socket OUT INT) AS BEGIN socket:=10; END;/
262 DROP PROCEDURE p1/
263 CREATE PROCEDURE p1 (slave OUT INT) AS BEGIN slave:=10; END;/
264 DROP PROCEDURE p1/
265 CREATE PROCEDURE p1 (slaves OUT INT) AS BEGIN slaves:=10; END;/
266 DROP PROCEDURE p1/
267 CREATE PROCEDURE p1 (soname OUT INT) AS BEGIN soname:=10; END;/
268 DROP PROCEDURE p1/
269 CREATE PROCEDURE p1 (start OUT INT) AS BEGIN start:=10; END;/
270 DROP PROCEDURE p1/
271 CREATE PROCEDURE p1 (stop OUT INT) AS BEGIN stop:=10; END;/
272 DROP PROCEDURE p1/
273 CREATE PROCEDURE p1 (stored OUT INT) AS BEGIN stored:=10; END;/
274 DROP PROCEDURE p1/
275 CREATE PROCEDURE p1 (unicode OUT INT) AS BEGIN unicode:=10; END;/
276 DROP PROCEDURE p1/
277 CREATE PROCEDURE p1 (uninstall OUT INT) AS BEGIN uninstall:=10; END;/
278 DROP PROCEDURE p1/
279 CREATE PROCEDURE p1 (upgrade OUT INT) AS BEGIN upgrade:=10; END;/
280 DROP PROCEDURE p1/
281 CREATE PROCEDURE p1 (wrapper OUT INT) AS BEGIN wrapper:=10; END;/
282 DROP PROCEDURE p1/
283 CREATE PROCEDURE p1 (xa OUT INT) AS BEGIN xa:=10; END;/
284 DROP PROCEDURE p1/
285 DELIMITER ;/
286
287
288 --echo # Testing that keyword_directly_not_assignable does not work in :=
289 DELIMITER /;
290 --error ER_PARSE_ERROR
291 CREATE PROCEDURE p1 (commit OUT INT) AS BEGIN commit:=10; END;/
292 --error ER_PARSE_ERROR
293 CREATE PROCEDURE p1 (rollback OUT INT) AS BEGIN rollback:=10; END;/
294 --error ER_PARSE_ERROR
295 CREATE PROCEDURE p1 (shutdown OUT INT) AS BEGIN shutdown:=10; END;/
296 --error ER_PARSE_ERROR
297 CREATE PROCEDURE p1 (exception OUT INT) AS BEGIN exception:=10; END;/
298 DELIMITER ;/
299
300
301 --echo # Testing that keyword_directly_not_assignable works in SET statements.
302 DELIMITER /;
303 CREATE PROCEDURE p1 (contains OUT INT) AS BEGIN SET contains=10; END;/
304 DROP PROCEDURE p1/
305 CREATE PROCEDURE p1 (language OUT INT) AS BEGIN SET language=10; END;/
306 DROP PROCEDURE p1/
307 CREATE PROCEDURE p1 (no OUT INT) AS BEGIN SET no=10; END;/
308 DROP PROCEDURE p1/
309 CREATE PROCEDURE p1 (charset OUT INT) AS BEGIN SET charset=10; END;/
310 DROP PROCEDURE p1/
311 CREATE PROCEDURE p1 (do OUT INT) AS BEGIN SET do=10; END;/
312 DROP PROCEDURE p1/
313 CREATE PROCEDURE p1 (repair OUT INT) AS BEGIN SET repair=10; END;/
314 DROP PROCEDURE p1/
315 CREATE PROCEDURE p1 (handler OUT INT) AS BEGIN SET handler=10; END;/
316 DROP PROCEDURE p1/
317 CREATE PROCEDURE p1 (open OUT INT) AS BEGIN SET open=10; END;/
318 DROP PROCEDURE p1/
319 CREATE PROCEDURE p1 (close OUT INT) AS BEGIN SET close=10; END;/
320 DROP PROCEDURE p1/
321 CREATE PROCEDURE p1 (savepoint OUT INT) AS BEGIN SET savepoint=10; END;/
322 DROP PROCEDURE p1/
323 CREATE PROCEDURE p1 (truncate OUT INT) AS BEGIN SET truncate=10; END;/
324 DROP PROCEDURE p1/
325 CREATE PROCEDURE p1 (begin OUT INT) AS BEGIN SET begin=10; END;/
326 DROP PROCEDURE p1/
327 CREATE PROCEDURE p1 (end OUT INT) AS BEGIN SET end=10; END;/
328 DROP PROCEDURE p1/
329 CREATE PROCEDURE p1 (exception OUT INT) AS BEGIN SET exception=10; END;/
330 DROP PROCEDURE p1/
331 DELIMITER ;/
332
333 --echo # Testing that keyword_directly_not_assignable works in table/column names
334 CREATE TABLE contains (contains INT);
335 DROP TABLE contains;
336 CREATE TABLE language (language INT);
337 DROP TABLE language;
338 CREATE TABLE no (no INT);
339 DROP TABLE no;
340 CREATE TABLE charset (charset INT);
341 DROP TABLE charset;
342 CREATE TABLE do (do INT);
343 DROP TABLE do;
344 CREATE TABLE repair (repair INT);
345 DROP TABLE repair;
346 CREATE TABLE handler (handler INT);
347 DROP TABLE handler;
348 CREATE TABLE open (open INT);
349 DROP TABLE open;
350 CREATE TABLE close (close INT);
351 DROP TABLE close;
352 CREATE TABLE savepoint (savepoint INT);
353 DROP TABLE savepoint;
354 CREATE TABLE truncate (truncate INT);
355 DROP TABLE truncate;
356 CREATE TABLE begin (begin INT);
357 DROP TABLE begin;
358 CREATE TABLE end (end INT);
359 DROP TABLE end;
360 CREATE TABLE exception (exception INT);
361 DROP TABLE exception;
362
363 --echo # Testing ELSIF
364 DELIMITER /;
365 CREATE FUNCTION f1(a INT) RETURN CLOB
366 AS
367 BEGIN
368 IF a=1 THEN RETURN 'a is 1';
369 ELSIF a=2 THEN RETURN 'a is 2';
370 ELSE RETURN 'a is unknown';
371 END IF;
372 END;
373 /
374 DELIMITER ;/
375 SELECT f1(2) FROM DUAL;
376 DROP FUNCTION f1;
377
378
379
380 --echo # Testing top-level declarations
381 DELIMITER /;
382 CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10))
383 AS
384 p2 VARCHAR(10);
385 BEGIN
386 p2:='p1new';
387 p1:=p2;
388 END;
389 /
390 DELIMITER ;/
391 SET @p1='p1';
392 CALL p1(@p1);
393 SELECT @p1;
394 DROP PROCEDURE p1;
395
396 DELIMITER /;
397 CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20)
398 AS
399 p2 VARCHAR(10);
400 BEGIN
401 p2:='new';
402 RETURN CONCAT(p1, p2);
403 END;
404 /
405 DELIMITER ;/
406 SET @p1='p1';
407 SELECT f1(@p1);
408 DROP FUNCTION f1;
409
410 --echo # Testing non-top declarations
411
412 DELIMITER /;
413 CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10))
414 AS
415 BEGIN
416 DECLARE
417 p2 VARCHAR(10);
418 BEGIN
419 p2:='p1new';
420 p1:=p2;
421 END;
422 DECLARE
423 t1 VARCHAR(10);
424 t2 VARCHAR(10);
425 BEGIN
426 END;
427 END;
428 /
429 DELIMITER ;/
430 SET @p1='p1';
431 CALL p1(@p1);
432 SELECT @p1;
433 DROP PROCEDURE p1;
434
435 DELIMITER /;
436 CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20)
437 AS
438 BEGIN
439 DECLARE
440 p2 VARCHAR(10);
441 BEGIN
442 p2:='new';
443 RETURN CONCAT(p1, p2);
444 END;
445 DECLARE
446 t1 VARCHAR(10);
447 t2 VARCHAR(10);
448 BEGIN
449 END;
450 END;
451 /
452 DELIMITER ;/
453 SET @p1='p1';
454 SELECT f1(@p1);
455 DROP FUNCTION f1;
456
457
458 --echo # Testing exceptions
459
460 CREATE TABLE t1 (c1 INT);
461
462 DELIMITER /;
463
464 CREATE PROCEDURE sp1 (p1 IN VARCHAR2(20), p2 OUT VARCHAR2(30))
465 IS
466 v1 INT;
467 BEGIN
468 SELECT c1 INTO v1 FROM t1;
469 p2 := p1;
470 EXCEPTION
471 WHEN NOT FOUND THEN
472 BEGIN
473 p2 := 'def';
474 END;
475 END;
476 /
477
478 DELIMITER ;/
479
480 CALL sp1('abc', @a);
481 SELECT @a;
482
483 DROP PROCEDURE sp1;
484 DROP TABLE t1;
485
486
487 DELIMITER /;
488 CREATE PROCEDURE sp1 (v IN OUT INT, error IN INT)
489 IS
490 BEGIN
491 SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=error, MESSAGE_TEXT='User defined error!';
492 v:= 223;
493 EXCEPTION
494 WHEN 30001 THEN
495 BEGIN
496 v:= 113;
497 END;
498 END;
499 /
500 DELIMITER ;/
501 SET @v=10;
502 CALL sp1(@v, 30001);
503 --error 30002
504 CALL sp1(@v, 30002);
505 SELECT @v;
506 DROP PROCEDURE sp1;
507
508
509 DELIMITER /;
510 CREATE PROCEDURE sp1 (v IN OUT INT, error IN INT)
511 IS
512 BEGIN
513 BEGIN
514 BEGIN
515 SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=error, MESSAGE_TEXT='User defined error!';
516 v:= 223;
517 EXCEPTION
518 WHEN 30001 THEN
519 BEGIN
520 v:= 113;
521 END;
522 END;
523 END;
524 END;
525 /
526 DELIMITER ;/
527 SET @v=10;
528 CALL sp1(@v, 30001);
529 SELECT @v;
530 SET @v=10;
531 --error 30002
532 CALL sp1(@v, 30002);
533 SELECT @v;
534 DROP PROCEDURE sp1;
535
536
537 --echo #
538 --echo # Testing EXIT statement
539 --echo #
540
541 DELIMITER /;
542 --error ER_SP_LILABEL_MISMATCH
543 CREATE FUNCTION f1 RETURN INT
544 IS
545 i INT := 0;
546 BEGIN
547 EXIT;
548 END;
549 /
550 DELIMITER ;/
551
552
553 DELIMITER /;
554 --error ER_SP_LILABEL_MISMATCH
555 CREATE FUNCTION f1 RETURN INT
556 IS
557 i INT := 0;
558 BEGIN
559 <<lable1>>
560 BEGIN
561 <<label2>>
562 LOOP
563 EXIT label1;
564 END LOOP;
565 END;
566 END;
567 /
568 DELIMITER ;/
569
570
571 DELIMITER /;
572 CREATE FUNCTION f1 RETURN INT
573 IS
574 i INT := 0;
575 BEGIN
576 LOOP
577 LOOP
578 i:= i + 1;
579 IF i >= 5 THEN
580 EXIT;
581 END IF;
582 END LOOP;
583 i:= i + 100;
584 EXIT;
585 END LOOP;
586 RETURN i;
587 END;
588 /
589 DELIMITER ;/
590 SELECT f1() FROM DUAL;
591 DROP FUNCTION f1;
592
593
594 DELIMITER /;
595 CREATE FUNCTION f1 RETURN INT
596 IS
597 i INT := 0;
598 BEGIN
599 <<label1>>
600 LOOP
601 <<label2>>
602 LOOP
603 i:= i + 1;
604 IF i >= 5 THEN
605 EXIT label2;
606 END IF;
607 END LOOP;
608 i:= i + 100;
609 EXIT;
610 END LOOP;
611 RETURN i;
612 END;
613 /
614 DELIMITER ;/
615 SELECT f1() FROM DUAL;
616 DROP FUNCTION f1;
617
618
619 DELIMITER /;
620 CREATE FUNCTION f1 RETURN INT
621 IS
622 i INT := 0;
623 BEGIN
624 <<label1>>
625 LOOP
626 <<label2>>
627 LOOP
628 i:= i + 1;
629 IF i >= 5 THEN
630 EXIT label1;
631 END IF;
632 END LOOP;
633 i:= i + 100;
634 EXIT;
635 END LOOP;
636 RETURN i;
637 END;
638 /
639 DELIMITER ;/
640 SELECT f1() FROM DUAL;
641 DROP FUNCTION f1;
642
643
644 DELIMITER /;
645 CREATE FUNCTION f1 RETURN INT
646 IS
647 i INT := 0;
648 BEGIN
649 LOOP
650 i:= i + 1;
651 EXIT WHEN i >=5;
652 END LOOP;
653 RETURN i;
654 END;
655 /
656 DELIMITER ;/
657 SELECT f1() FROM DUAL;
658 DROP FUNCTION f1;
659
660
661 DELIMITER /;
662 CREATE FUNCTION f1 RETURN INT
663 IS
664 i INT := 0;
665 BEGIN
666 <<label1>>
667 LOOP
668 <<label2>>
669 LOOP
670 i:= i + 1;
671 EXIT label2 WHEN i >= 5;
672 END LOOP;
673 i:= i + 100;
674 EXIT;
675 END LOOP;
676 RETURN i;
677 END;
678 /
679 DELIMITER ;/
680 SELECT f1() FROM DUAL;
681 DROP FUNCTION f1;
682
683
684 DELIMITER /;
685 CREATE FUNCTION f1 RETURN INT
686 IS
687 i INT := 0;
688 BEGIN
689 <<label1>>
690 LOOP
691 <<label2>>
692 LOOP
693 i:= i + 1;
694 EXIT label1 WHEN i >= 5;
695 END LOOP;
696 i:= i + 100;
697 EXIT;
698 END LOOP;
699 RETURN i;
700 END;
701 /
702 DELIMITER ;/
703 SELECT f1() FROM DUAL;
704 DROP FUNCTION f1;
705
706
707 --echo # Testing CURSOR declaration
708
709 CREATE TABLE t1 (a INT);
710 INSERT INTO t1 VALUES (1);
711 DELIMITER /;
712 CREATE FUNCTION f1 RETURN INT
713 AS
714 v_a INT:=10;
715 CURSOR c IS SELECT a FROM t1;
716 BEGIN
717 OPEN c;
718 FETCH c INTO v_a;
719 CLOSE c;
720 RETURN v_a;
721 EXCEPTION
722 WHEN OTHERS THEN RETURN -1;
723 END;
724 /
725 DELIMITER ;/
726 SELECT f1() FROM DUAL;
727 DROP FUNCTION f1;
728 DROP TABLE t1;
729
730
731 --echo # Testing RETURN in procedures
732
733 DELIMITER /;
734 --error ER_SP_BADRETURN
735 CREATE PROCEDURE p1 (a IN OUT INT)
736 AS
737 BEGIN
738 RETURN 10;
739 END;
740 /
741 DELIMITER ;/
742
743 DELIMITER /;
744 --error ER_PARSE_ERROR
745 CREATE FUNCTION f1 (a INT) RETURN INT
746 AS
747 BEGIN
748 RETURN;
749 END;
750 /
751 DELIMITER ;/
752
753 DELIMITER /;
754 CREATE PROCEDURE p1 (a IN OUT INT)
755 AS
756 BEGIN
757 IF a < 10 THEN
758 BEGIN
759 a:= a - 1;
760 RETURN;
761 END;
762 END IF;
763 a:= a + 1;
764 EXCEPTION
765 WHEN OTHERS THEN RETURN;
766 END;
767 /
768 DELIMITER ;/
769 SET @v=10;
770 CALL p1(@v);
771 SELECT @v;
772 SET @v=9;
773 CALL p1(@v);
774 SELECT @v;
775 DROP PROCEDURE p1;
776
777 DELIMITER /;
778 CREATE PROCEDURE p1 (a IN OUT INT)
779 AS
780 BEGIN
781 DROP TABLE t1_non_existent;
782 EXCEPTION
783 WHEN OTHERS THEN
784 BEGIN
785 a:= 100;
786 RETURN;
787 END;
788 END;
789 /
790 DELIMITER ;/
791 SET @v=10;
792 CALL p1(@v);
793 SELECT @v;
794 DROP PROCEDURE p1;
795
796
797 --echo # Testing WHILE loop
798
799 DELIMITER /;
800 CREATE PROCEDURE p1 (a IN OUT INT)
801 AS
802 i INT:= 1;
803 j INT:= 3;
804 BEGIN
805 WHILE i<=j
806 LOOP
807 a:= a + i;
808 i:= i + 1;
809 END LOOP;
810 END;
811 /
812 DELIMITER ;/
813 SET @v=0;
814 CALL p1(@v);
815 SELECT @v;
816 DROP PROCEDURE p1;
817
818 DELIMITER /;
819 CREATE PROCEDURE p1 (a IN OUT INT)
820 AS
821 i INT:= 1;
822 j INT:= 3;
823 BEGIN
824 <<label>>
825 WHILE i<=j
826 LOOP
827 a:= a + i;
828 i:= i + 1;
829 END LOOP label;
830 END;
831 /
832 DELIMITER ;/
833 SET @v=0;
834 CALL p1(@v);
835 SELECT @v;
836 DROP PROCEDURE p1;
837
838
839 --echo # Testing the FOR loop statement
840
841 CREATE TABLE t1 (a INT);
842 DELIMITER /;
843 FOR i IN 1..3
844 LOOP
845 INSERT INTO t1 VALUES (i);
846 END LOOP;
847 /
848 DELIMITER ;/
849 SELECT * FROM t1;
850 DROP TABLE t1;
851
852
853 DELIMITER /;
854 --error ER_PARSE_ERROR
855 CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURN INT
856 AS
857 total INT := 0;
858 BEGIN
859 FOR i IN lower_bound . . upper_bound
860 LOOP
861 NULL
862 END LOOP;
863 RETURN total;
864 END;
865 /
866 DELIMITER ;/
867
868
869 DELIMITER /;
870 CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURN INT
871 AS
872 total INT := 0;
873 BEGIN
874 FOR i IN lower_bound .. upper_bound
875 LOOP
876 total:= total + i;
877 IF i = lim THEN
878 EXIT;
879 END IF;
880 -- Bounds are calculated only once.
881 -- The below assignments have no effect on the loop condition
882 lower_bound:= 900;
883 upper_bound:= 1000;
884 END LOOP;
885 RETURN total;
886 END;
887 /
888 DELIMITER ;/
889 SELECT f1(1, 3, 100) FROM DUAL;
890 SELECT f1(1, 3, 2) FROM DUAL;
891 DROP FUNCTION f1;
892
893
894 DELIMITER /;
895 CREATE FUNCTION f1 RETURN INT
896 AS
897 total INT := 0;
898 BEGIN
899 FOR i IN 1 .. 5
900 LOOP
901 total:= total + 1000;
902 FOR j IN 1 .. 5
903 LOOP
904 total:= total + 1;
905 IF j = 3 THEN
906 EXIT; -- End the internal loop
907 END IF;
908 END LOOP;
909 END LOOP;
910 RETURN total;
911 END;
912 /
913 DELIMITER ;/
914 SELECT f1() FROM DUAL;
915 DROP FUNCTION f1;
916
917
918 DELIMITER /;
919 CREATE FUNCTION f1 (a INT, b INT) RETURN INT
920 AS
921 total INT := 0;
922 BEGIN
923 FOR i IN REVERSE 1..a
924 LOOP
925 total:= total + i;
926 IF i = b THEN
927 EXIT;
928 END IF;
929 END LOOP;
930 RETURN total;
931 END
932 /
933 DELIMITER ;/
934 SELECT f1(3, 100) FROM DUAL;
935 SELECT f1(3, 2) FROM DUAL;
936 DROP FUNCTION f1;
937
938
939 --echo # Testing labeled FOR LOOP statement
940
941 DELIMITER /;
942 CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURN INT
943 AS
944 total INT := 0;
945 BEGIN
946 <<la>>
947 FOR ia IN 1 .. a
948 LOOP
949 total:= total + 1000;
950 <<lb>>
951 FOR ib IN 1 .. b
952 LOOP
953 total:= total + 1;
954 EXIT lb WHEN ib = limitb;
955 EXIT la WHEN ia = limita;
956 END LOOP lb;
957 END LOOP la;
958 RETURN total;
959 END;
960 /
961 DELIMITER ;/
962 SELECT f1(1, 1, 1, 1) FROM DUAL;
963 SELECT f1(1, 2, 1, 2) FROM DUAL;
964 SELECT f1(2, 1, 2, 1) FROM DUAL;
965 SELECT f1(2, 1, 2, 2) FROM DUAL;
966 SELECT f1(2, 2, 2, 2) FROM DUAL;
967 SELECT f1(2, 3, 2, 3) FROM DUAL;
968 DROP FUNCTION f1;
969
970
971 --echo # Testing labeled ITERATE in a labeled FOR LOOP statement
972
973 DELIMITER /;
974 CREATE FUNCTION f1 (a INT, b INT, blim INT) RETURN INT
975 AS
976 total INT := 0;
977 BEGIN
978 <<la>>
979 FOR ia IN 1 .. a
980 LOOP
981 total:= total + 1000;
982 DECLARE
983 ib INT:= 1;
984 BEGIN
985 WHILE ib <= b
986 LOOP
987 IF ib > blim THEN
988 ITERATE la;
989 END IF;
990 ib:= ib + 1;
991 total:= total + 1;
992 END LOOP;
993 END;
994 END LOOP la;
995 RETURN total;
996 END;
997 /
998 DELIMITER ;/
999 SELECT f1(3,3,0), f1(3,3,1), f1(3,3,2), f1(3,3,3), f1(3,3,4) FROM DUAL;
1000 DROP FUNCTION f1;
1001
1002
1003 --echo # Testing CONTINUE statement
1004
1005 DELIMITER /;
1006 CREATE FUNCTION f1(a INT) RETURN INT
1007 AS
1008 total INT:= 0;
1009 BEGIN
1010 FOR i IN 1 .. a
1011 LOOP
1012 IF i=5 THEN
1013 CONTINUE;
1014 END IF;
1015 total:= total + 1;
1016 END LOOP;
1017 RETURN total;
1018 END;
1019 /
1020 DELIMITER ;/
1021 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
1022 DROP FUNCTION f1;
1023
1024
1025 DELIMITER /;
1026 CREATE FUNCTION f1(a INT) RETURN INT
1027 AS
1028 total INT:= 0;
1029 BEGIN
1030 <<lj>>
1031 FOR j IN 1 .. 2
1032 LOOP
1033 FOR i IN 1 .. a
1034 LOOP
1035 IF i=5 THEN
1036 CONTINUE lj;
1037 END IF;
1038 total:= total + 1;
1039 END LOOP;
1040 END LOOP;
1041 RETURN total;
1042 END;
1043 /
1044 DELIMITER ;/
1045 SELECT f1(3), f1(4), f1(5) FROM DUAL;
1046 DROP FUNCTION f1;
1047
1048
1049 DELIMITER /;
1050 CREATE FUNCTION f1(a INT) RETURN INT
1051 AS
1052 total INT:= 0;
1053 BEGIN
1054 <<lj>>
1055 FOR j IN 1 .. 2
1056 LOOP
1057 FOR i IN 1 .. a
1058 LOOP
1059 CONTINUE lj WHEN i=5;
1060 total:= total + 1;
1061 END LOOP;
1062 END LOOP;
1063 RETURN total;
1064 END;
1065 /
1066 DELIMITER ;/
1067 SELECT f1(3), f1(4), f1(5) FROM DUAL;
1068 DROP FUNCTION f1;
1069
1070
1071 DELIMITER /;
1072 CREATE FUNCTION f1(a INT) RETURN INT
1073 AS
1074 total INT:= 0;
1075 i INT:= 1;
1076 BEGIN
1077 WHILE i <= a
1078 LOOP
1079 i:= i + 1;
1080 IF i=6 THEN
1081 CONTINUE;
1082 END IF;
1083 total:= total + 1;
1084 END LOOP;
1085 RETURN total;
1086 END;
1087 /
1088 DELIMITER ;/
1089 SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
1090 DROP FUNCTION f1;
1091
1092 --echo #
1093 --echo # Testing behaviour of unknown identifiers in EXIT and CONTINUE statements
1094 --echo #
1095
1096 DELIMITER $$;
1097 --error ER_SP_UNDECLARED_VAR
1098 CREATE PROCEDURE p1
1099 AS
1100 BEGIN
1101 LOOP
1102 EXIT WHEN unknown_ident IS NULL;
1103 END LOOP;
1104 END$$
1105 DELIMITER ;$$
1106
1107
1108 DELIMITER $$;
1109 --error ER_SP_UNDECLARED_VAR
1110 CREATE PROCEDURE p1
1111 AS
1112 BEGIN
1113 <<label>>
1114 LOOP
1115 EXIT label WHEN unknown_ident IS NULL;
1116 END LOOP;
1117 END$$
1118 DELIMITER ;$$
1119
1120
1121 DELIMITER $$;
1122 --error ER_SP_UNDECLARED_VAR
1123 CREATE PROCEDURE p1
1124 AS
1125 BEGIN
1126 LOOP
1127 CONTINUE WHEN unknown_ident IS NULL;
1128 END LOOP;
1129 END$$
1130 DELIMITER ;$$
1131
1132
1133 DELIMITER $$;
1134 --error ER_SP_UNDECLARED_VAR
1135 CREATE PROCEDURE p1
1136 AS
1137 BEGIN
1138 <<label>>
1139 LOOP
1140 CONTINUE label WHEN unknown_ident IS NULL;
1141 END LOOP;
1142 END$$
1143 DELIMITER ;$$
1144
1145 --echo #
1146 --echo # MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT
1147 --echo #
1148
1149 EXPLAIN EXTENDED SELECT sql%rowcount;
1150 CREATE TABLE t1 AS SELECT SQL%ROWCOUNT;
1151 SHOW CREATE TABLE t1;
1152 DROP TABLE t1;
1153
1154 --echo #
1155 --echo # UPDATE
1156 --echo #
1157
1158 CREATE TABLE t1 (a INT);
1159 DELIMITER $$;
1160 CREATE PROCEDURE p1
1161 AS
1162 BEGIN
1163 UPDATE t1 SET a=30;
1164 SELECT SQL%ROWCOUNT;
1165 END;
1166 $$
1167 DELIMITER ;$$
1168 CALL p1();
1169 DROP PROCEDURE p1;
1170 DROP TABLE t1;
1171
1172
1173 CREATE TABLE t1 (a INT);
1174 INSERT INTO t1 VALUES (10);
1175 INSERT INTO t1 VALUES (20);
1176 DELIMITER $$;
1177 CREATE PROCEDURE p1
1178 AS
1179 BEGIN
1180 UPDATE t1 SET a=30;
1181 SELECT SQL%ROWCOUNT;
1182 END;
1183 $$
1184 DELIMITER ;$$
1185 CALL p1();
1186 DROP PROCEDURE p1;
1187 DROP TABLE t1;
1188
1189
1190 --echo #
1191 --echo # DELETE
1192 --echo #
1193
1194 CREATE TABLE t1 (a INT);
1195 DELIMITER $$;
1196 CREATE PROCEDURE p1
1197 AS
1198 BEGIN
1199 DELETE FROM t1;
1200 SELECT SQL%ROWCOUNT;
1201 END;
1202 $$
1203 DELIMITER ;$$
1204 CALL p1();
1205 DROP PROCEDURE p1;
1206 DROP TABLE t1;
1207
1208
1209 CREATE TABLE t1 (a INT);
1210 INSERT INTO t1 VALUES (10);
1211 INSERT INTO t1 VALUES (20);
1212 DELIMITER $$;
1213 CREATE PROCEDURE p1
1214 AS
1215 BEGIN
1216 DELETE FROM t1;
1217 SELECT SQL%ROWCOUNT;
1218 END;
1219 $$
1220 DELIMITER ;$$
1221 CALL p1();
1222 DROP PROCEDURE p1;
1223 DROP TABLE t1;
1224
1225
1226 --echo #
1227 --echo # SELECT ... INTO var FROM ... - one row found
1228 --echo #
1229
1230 CREATE TABLE t1 (a INT);
1231 INSERT INTO t1 VALUES (10);
1232 INSERT INTO t1 VALUES (20);
1233 DELIMITER $$;
1234 CREATE PROCEDURE p1
1235 AS
1236 va INT;
1237 BEGIN
1238 SELECT a INTO va FROM t1 LIMIT 1;
1239 SELECT SQL%ROWCOUNT;
1240 END;
1241 $$
1242 DELIMITER ;$$
1243 CALL p1();
1244 DROP PROCEDURE p1;
1245 DROP TABLE t1;
1246
1247
1248 --echo #
1249 --echo # SELECT ... INTO var FROM ... - no rows found
1250 --echo #
1251 CREATE TABLE t1 (a INT);
1252 DELIMITER $$;
1253 CREATE PROCEDURE p1
1254 AS
1255 va INT;
1256 BEGIN
1257 SELECT a INTO va FROM t1;
1258 SELECT SQL%ROWCOUNT;
1259 END;
1260 $$
1261 DELIMITER ;$$
1262 CALL p1();
1263 DROP PROCEDURE p1;
1264 DROP TABLE t1;
1265
1266
1267 CREATE TABLE t1 (a INT);
1268 DELIMITER $$;
1269 CREATE PROCEDURE p1
1270 AS
1271 va INT;
1272 BEGIN
1273 SELECT a INTO va FROM t1;
1274 SELECT SQL%ROWCOUNT;
1275 EXCEPTION
1276 WHEN NO_DATA_FOUND THEN SELECT SQL%ROWCOUNT||' (EXCEPTION)';
1277 END;
1278 $$
1279 DELIMITER ;$$
1280 CALL p1();
1281 DROP PROCEDURE p1;
1282 DROP TABLE t1;
1283
1284
1285 --echo #
1286 --echo # SELECT ... INTO var FROM ... - multiple rows found
1287 --echo #
1288
1289 CREATE TABLE t1 (a INT);
1290 INSERT INTO t1 VALUES (10);
1291 INSERT INTO t1 VALUES (20);
1292 DELIMITER $$;
1293 CREATE PROCEDURE p1
1294 AS
1295 va INT:=1;
1296 BEGIN
1297 SELECT a INTO va FROM t1;
1298 SELECT SQL%ROWCOUNT;
1299 EXCEPTION
1300 WHEN TOO_MANY_ROWS THEN SELECT SQL%ROWCOUNT||' (EXCEPTION) va='||va;
1301 END;
1302 $$
1303 DELIMITER ;$$
1304 CALL p1();
1305 DROP PROCEDURE p1;
1306 DROP TABLE t1;
1307
1308
1309
1310 --echo #
1311 --echo # INSERT INTO t2 SELECT ...
1312 --echo #
1313
1314 CREATE TABLE t1 (a INT);
1315 CREATE TABLE t2 (a INT);
1316 INSERT INTO t1 VALUES (10);
1317 INSERT INTO t1 VALUES (20);
1318 DELIMITER $$;
1319 CREATE PROCEDURE p1
1320 AS
1321 BEGIN
1322 INSERT INTO t2 SELECT * FROM t1;
1323 SELECT SQL%ROWCOUNT;
1324 END;
1325 $$
1326 DELIMITER ;$$
1327 CALL p1();
1328 DROP PROCEDURE p1;
1329 DROP TABLE t1, t2;
1330
1331 --echo #
1332 --echo # End of MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT
1333 --echo #
1334
1335 --echo #
1336 --echo # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
1337 --echo #
1338
1339 --echo #
1340 --echo # Missing table
1341 --echo #
1342
1343 DELIMITER $$;
1344 CREATE PROCEDURE p1
1345 AS
1346 a t1.a%TYPE;
1347 BEGIN
1348 NULL;
1349 END;
1350 $$
1351 DELIMITER ;$$
1352 --error ER_NO_SUCH_TABLE
1353 CALL p1();
1354 DROP PROCEDURE p1;
1355
1356
1357 --echo #
1358 --echo # Missing column
1359 --echo #
1360
1361 CREATE TABLE t1 (b INT);
1362 DELIMITER $$;
1363 CREATE PROCEDURE p1
1364 AS
1365 a t1.a%TYPE;
1366 BEGIN
1367 NULL;
1368 END;
1369 $$
1370 DELIMITER ;$$
1371 --error ER_BAD_FIELD_ERROR
1372 CALL p1();
1373 DROP PROCEDURE p1;
1374 DROP TABLE t1;
1375
1376
1377 --echo #
1378 --echo # One %TYPE variable
1379 --echo #
1380
1381 CREATE TABLE t1 (a INT);
1382 DELIMITER $$;
1383 CREATE PROCEDURE p1
1384 AS
1385 a t1.a%TYPE;
1386 BEGIN
1387 a:= 123;
1388 SELECT a;
1389 END;
1390 $$
1391 DELIMITER ;$$
1392 CALL p1();
1393 DROP PROCEDURE p1;
1394 DROP TABLE t1;
1395
1396 --echo #
1397 --echo # Two %TYPE variables, with a truncation warning on assignment
1398 --echo #
1399
1400 CREATE TABLE t1 (a TINYINT, b INT);
1401 DELIMITER $$;
1402 CREATE PROCEDURE p1
1403 AS
1404 a t1.a%TYPE;
1405 b t1.b%TYPE;
1406 BEGIN
1407 a:= 200;
1408 b:= 200;
1409 SELECT a, b;
1410 END;
1411 $$
1412 DELIMITER ;$$
1413 CALL p1();
1414 DROP PROCEDURE p1;
1415 DROP TABLE t1;
1416
1417
1418 --echo #
1419 --echo # %TYPE variables for fields with various attributes
1420 --echo #
1421
1422 CREATE TABLE t1 (
1423 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
1424 a TINYINT NOT NULL,
1425 b INT NOT NULL,
1426 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
1427 UNIQUE(a)
1428 );
1429 DELIMITER $$;
1430 CREATE PROCEDURE p1
1431 AS
1432 id t1.id%TYPE;
1433 a t1.a%TYPE;
1434 b t1.b%TYPE;
1435 ts t1.ts%TYPE;
1436 BEGIN
1437 SELECT id, a, b, ts;
1438 CREATE TABLE t2 AS SELECT id, a, b, ts;
1439 SHOW CREATE TABLE t2;
1440 DROP TABLE t2;
1441 END;
1442 $$
1443 DELIMITER ;$$
1444 CALL p1();
1445 DROP PROCEDURE p1;
1446 DROP TABLE t1;
1447
1448
1449 --echo #
1450 --echo # %TYPE + virtual columns
1451 --echo #
1452
1453 #
1454 # TODO: Changing 'a + 10' to 'a mod 10' make it fail, because
1455 # it's Item::print() returns 'a % 10' which makes grammar conflict
1456 # with cursor attributes
1457
1458 CREATE TABLE t1 (
1459 a INT NOT NULL,
1460 b VARCHAR(32),
1461 c INT AS (a + 10) VIRTUAL,
1462 d VARCHAR(5) AS (left(b,5)) PERSISTENT
1463 );
1464 DELIMITER $$;
1465 CREATE PROCEDURE p1
1466 AS
1467 c t1.c%TYPE;
1468 d t1.d%TYPE;
1469 BEGIN
1470 SELECT c, d;
1471 CREATE TABLE t2 AS SELECT c, d;
1472 SHOW CREATE TABLE t2;
1473 DROP TABLE t2;
1474 END;
1475 $$
1476 DELIMITER ;$$
1477 CALL p1();
1478 DROP PROCEDURE p1;
1479 DROP TABLE t1;
1480
1481
1482 --echo #
1483 --echo # %TYPE + the ZEROFILL attribute
1484 --echo #
1485
1486 CREATE TABLE t1 (
1487 dz DECIMAL(10,3) ZEROFILL
1488 );
1489 DELIMITER $$;
1490 CREATE PROCEDURE p1
1491 AS
1492 dzr t1.dz%TYPE := 10;
1493 dzt DECIMAL(10,3) ZEROFILL := 10;
1494 BEGIN
1495 SELECT dzr, dzt;
1496 CREATE TABLE t2 AS SELECT dzr,dzt;
1497 SHOW CREATE TABLE t2;
1498 DROP TABLE t2;
1499 END;
1500 $$
1501 DELIMITER ;$$
1502 CALL p1();
1503 DROP PROCEDURE p1;
1504 DROP TABLE t1;
1505
1506
1507 --echo #
1508 --echo # Temporary tables shadow real tables for %TYPE purposes
1509 --echo #
1510 CREATE TABLE t1 (a VARCHAR(10));
1511 INSERT INTO t1 VALUES ('t1');
1512 CREATE TEMPORARY TABLE t1 (a INT);
1513 INSERT INTO t1 VALUES (10);
1514 SELECT * FROM t1;
1515
1516 DELIMITER $$;
1517 CREATE PROCEDURE p1
1518 AS
1519 a t1.a%TYPE:=11;
1520 BEGIN
1521 CREATE TABLE t2 AS SELECT a;
1522 END;
1523 $$
1524 DELIMITER ;$$
1525 --echo #
1526 --echo # Should use INT(11) as %TYPE, as in the temporary table
1527 --echo #
1528 CALL p1();
1529 SHOW CREATE TABLE t2;
1530 SELECT * FROM t2;
1531 DROP TABLE t2;
1532 SELECT * FROM t1;
1533 DROP TEMPORARY TABLE t1;
1534 SELECT * FROM t1;
1535 --echo #
1536 --echo # Should use VARCHAR(10) as %TYPE, as in the real table
1537 --echo #
1538 CALL p1();
1539 SHOW CREATE TABLE t2;
1540 SELECT * FROM t2;
1541 DROP TABLE t2;
1542 DROP PROCEDURE p1;
1543 DROP TABLE t1;
1544
1545
1546 --echo #
1547 --echo # t1.a%TYPE searches for "t1" in the current database
1548 --echo #
1549
1550 CREATE TABLE t1 (a VARCHAR(10));
1551 CREATE DATABASE test1;
1552 CREATE TABLE test1.t1 (a INT);
1553 DELIMITER $$;
1554 CREATE PROCEDURE p1
1555 AS
1556 a t1.a%TYPE:=11;
1557 BEGIN
1558 CREATE TABLE test.t2 AS SELECT a;
1559 END;
1560 $$
1561 DELIMITER ;$$
1562
1563 --echo #
1564 --echo # This interprets t1.a%TYPE as VARCHAR(10), as in test.t1.a
1565 --echo #
1566
1567 USE test;
1568 CALL test.p1();
1569 SHOW CREATE TABLE test.t2;
1570 DROP TABLE test.t2;
1571
1572 --echo #
1573 --echo # This interprets t1.a%TYPE as INT, as in test1.t1.a
1574 --echo #
1575
1576 USE test1;
1577 CALL test.p1();
1578 SHOW CREATE TABLE test.t2;
1579 DROP TABLE test.t2;
1580
1581 --echo #
1582 --echo # Error if there is no an active database
1583 --echo #
1584
1585 DROP DATABASE test1;
1586 --error ER_NO_DB_ERROR
1587 CALL test.p1();
1588
1589 USE test;
1590 DROP PROCEDURE p1;
1591 DROP TABLE t1;
1592
1593
1594 --echo #
1595 --echo # A reference to a table in a non-existing database
1596 --echo #
1597 DELIMITER $$;
1598 CREATE PROCEDURE p1
1599 AS
1600 a test1.t1.a%TYPE;
1601 BEGIN
1602 CREATE TABLE t1 AS SELECT a;
1603 END;
1604 $$
1605 DELIMITER ;$$
1606 --error ER_NO_SUCH_TABLE
1607 CALL p1;
1608 DROP PROCEDURE p1;
1609
1610
1611 --echo #
1612 --echo # A reference to a table in a different database
1613 --echo #
1614 CREATE TABLE t1(a INT);
1615 CREATE DATABASE test1;
1616 CREATE TABLE test1.t1 (a VARCHAR(10));
1617 DELIMITER $$;
1618 CREATE PROCEDURE p1
1619 AS
1620 a t1.a%TYPE;
1621 b test1.t1.a%TYPE;
1622 BEGIN
1623 CREATE TABLE t2 AS SELECT a,b;
1624 END;
1625 $$
1626 DELIMITER ;$$
1627 CALL p1;
1628 SHOW CREATE TABLE t2;
1629 DROP PROCEDURE p1;
1630 DROP TABLE t2;
1631 DROP DATABASE test1;
1632 DROP TABLE t1;
1633
1634
1635 --echo #
1636 --echo # Using a table before it appears in a %TYPE declaration + multiple %TYPE declarations
1637 --echo #
1638 CREATE TABLE t1 (a INT, b VARCHAR(10));
1639 INSERT INTO t1 (a,b) VALUES (10,'b10');
1640 DELIMITER $$;
1641 CREATE PROCEDURE p1
1642 AS
1643 BEGIN
1644 INSERT INTO t1 (a,b) VALUES (11, 'b11');
1645 SELECT * FROM t1;
1646 DECLARE
1647 va t1.a%TYPE:= 30;
1648 vb t1.b%TYPE:= 'b30';
1649 BEGIN
1650 INSERT INTO t1 (a,b) VALUES (12,'b12');
1651 SELECT * FROM t1;
1652 INSERT INTO t1 (a,b) VALUES (va, vb);
1653 SELECT * FROM t1;
1654 END;
1655 DECLARE
1656 va t1.a%TYPE:= 40;
1657 vb t1.b%TYPE:= 'b40';
1658 BEGIN
1659 INSERT INTO t1 (a,b) VALUES (va,vb);
1660 SELECT * FROM t1;
1661 END;
1662 END;
1663 $$
1664 DELIMITER ;$$
1665 CALL p1;
1666 DROP TABLE t1;
1667 DROP PROCEDURE p1;
1668
1669
1670 --echo #
1671 --echo # %TYPE variables + TABLE vs VIEW
1672 --echo #
1673
1674 CREATE TABLE t1 (
1675 bit6 BIT(6),
1676 bit7 BIT(7),
1677 bit8 BIT(8),
1678 i1 TINYINT,
1679 i2 SMALLINT,
1680 i3 MEDIUMINT,
1681 i4 INT,
1682 i8 BIGINT,
1683 ff FLOAT,
1684 fd DOUBLE,
1685 cc CHAR(10),
1686 cv VARCHAR(10),
1687 cvu VARCHAR(10) CHARACTER SET utf8,
1688 t1 TINYTEXT,
1689 t2 TEXT,
1690 t3 MEDIUMTEXT,
1691 t4 LONGTEXT,
1692 enum1 ENUM('a','b','c'),
1693 set1 SET('a','b','c'),
1694 blob1 TINYBLOB,
1695 blob2 BLOB,
1696 blob3 MEDIUMBLOB,
1697 blob4 LONGBLOB,
1698 yy YEAR,
1699 dd DATE,
1700 tm0 TIME,
1701 tm3 TIME(3),
1702 tm6 TIME(6),
1703 dt0 DATETIME,
1704 dt3 DATETIME(3),
1705 dt6 DATETIME(6),
1706 ts0 TIMESTAMP,
1707 ts3 TIMESTAMP(3),
1708 ts6 TIMESTAMP(6),
1709 dc100 DECIMAL(10,0),
1710 dc103 DECIMAL(10,3),
1711 dc209 DECIMAL(20,9)
1712 );
1713
1714
1715 DELIMITER $$;
1716 CREATE PROCEDURE p1(command enum('create','select'))
1717 AS
1718 bit6 t1.bit6%TYPE := 0x30;
1719 bit7 t1.bit7%TYPE := 0x41;
1720 bit8 t1.bit8%TYPE := 0x7E;
1721 i1 t1.i1%TYPE := 11;
1722 i2 t1.i2%TYPE := 12;
1723 i3 t1.i3%TYPE := 13;
1724 i4 t1.i4%TYPE := 14;
1725 i8 t1.i8%TYPE := 18;
1726 ff t1.ff%TYPE := 21;
1727 fd t1.fd%TYPE := 22;
1728 cc t1.cc%TYPE := 'char';
1729 cv t1.cv%TYPE := 'varchar';
1730 cvu t1.cvu%TYPE := 'varcharu8';
1731 t1 t1.t1%TYPE := 'text1';
1732 t2 t1.t2%TYPE := 'text2';
1733 t3 t1.t3%TYPE := 'text3';
1734 t4 t1.t4%TYPE := 'text4';
1735 enum1 t1.enum1%TYPE := 'b';
1736 set1 t1.set1%TYPE := 'a,c';
1737 blob1 t1.blob1%TYPE := 'blob1';
1738 blob2 t1.blob2%TYPE := 'blob2';
1739 blob3 t1.blob3%TYPE := 'blob3';
1740 blob4 t1.blob4%TYPE := 'blob4';
1741 yy t1.yy%TYPE := 2001;
1742 dd t1.dd%TYPE := '2001-01-01';
1743 tm0 t1.tm0%TYPE := '00:00:01';
1744 tm3 t1.tm3%TYPE := '00:00:03.333';
1745 tm6 t1.tm6%TYPE := '00:00:06.666666';
1746 dt0 t1.dt0%TYPE := '2001-01-01 00:00:01';
1747 dt3 t1.dt3%TYPE := '2001-01-03 00:00:01.333';
1748 dt6 t1.dt6%TYPE := '2001-01-06 00:00:01.666666';
1749 ts0 t1.ts0%TYPE := '2002-01-01 00:00:01';
1750 ts3 t1.ts3%TYPE := '2002-01-03 00:00:01.333';
1751 ts6 t1.ts6%TYPE := '2002-01-06 00:00:01.666666';
1752 dc100 t1.dc100%TYPE := 10;
1753 dc103 t1.dc103%TYPE := 10.123;
1754 dc209 t1.dc209%TYPE := 10.123456789;
1755 BEGIN
1756 CASE
1757 WHEN command='create' THEN
1758 CREATE TABLE t2 AS SELECT
1759 bit6, bit7, bit8,
1760 i1,i2,i3,i4,i8,
1761 ff,fd, dc100, dc103, dc209,
1762 cc,cv,cvu,
1763 t1,t2,t3,t4,
1764 enum1, set1,
1765 blob1, blob2, blob3, blob4,
1766 dd, yy,
1767 tm0, tm3, tm6,
1768 dt0, dt3, dt6,
1769 ts0, ts3, ts6;
1770 WHEN command='select' THEN
1771 SELECT
1772 bit6, bit7, bit8,
1773 i1,i2,i3,i4,i8,
1774 ff,fd, dc100, dc103, dc209,
1775 cc,cv,cvu,
1776 t1,t2,t3,t4,
1777 enum1, set1,
1778 blob1, blob2, blob3, blob4,
1779 dd, yy,
1780 tm0, tm3, tm6,
1781 dt0, dt3, dt6,
1782 ts0, ts3, ts6;
1783 END CASE;
1784 END;
1785 $$
1786 DELIMITER ;$$
1787
1788 --echo #
1789 --echo # TABLE
1790 --echo #
1791 CALL p1('create');
1792 SHOW CREATE TABLE t2;
1793 --vertical_results
1794 SELECT * FROM t2;
1795 --horizontal_results
1796 DROP TABLE t2;
1797
1798 --disable_ps_protocol
1799 --enable_metadata
1800 --vertical_results
1801 CALL p1('select');
1802 --horizontal_results
1803 --disable_metadata
1804 --enable_ps_protocol
1805
1806 --echo #
1807 --echo # VIEW
1808 --echo #
1809 ALTER TABLE t1 RENAME t0;
1810 CREATE VIEW t1 AS SELECT * FROM t0;
1811
1812 CALL p1('create');
1813 SHOW CREATE TABLE t2;
1814 --vertical_results
1815 SELECT * FROM t2;
1816 --horizontal_results
1817 DROP TABLE t2;
1818
1819 --disable_ps_protocol
1820 --enable_metadata
1821 --vertical_results
1822 CALL p1('select');
1823 --horizontal_results
1824 --disable_metadata
1825 --enable_ps_protocol
1826
1827 DROP VIEW t1;
1828 DROP TABLE t0;
1829
1830 DROP PROCEDURE p1;
1831
1832 --echo #
1833 --echo # VIEW with subqueries
1834 --echo #
1835 CREATE TABLE t1 (a INT,b INT);
1836 INSERT INTO t1 VALUES (10,1),(20,2),(30,3),(40,4);
1837 SELECT AVG(a) FROM t1;
1838 CREATE VIEW v1 AS SELECT a,1 as b FROM t1 WHERE a>(SELECT AVG(a) FROM t1) AND b>(SELECT 1);
1839 SELECT * FROM v1;
1840 DELIMITER $$;
1841 CREATE PROCEDURE p1
1842 AS
1843 a v1.a%TYPE := 10;
1844 b v1.b%TYPE := 1;
1845 BEGIN
1846 SELECT a,b;
1847 END;
1848 $$
1849 DELIMITER ;$$
1850 CALL p1;
1851 DROP PROCEDURE p1;
1852 DELIMITER $$;
1853 CREATE FUNCTION f1 RETURN INT
1854 AS
1855 a v1.a%TYPE := 10;
1856 b v1.b%TYPE := 1;
1857 BEGIN
1858 RETURN a+b;
1859 END;
1860 $$
1861 DELIMITER ;$$
1862 SELECT f1();
1863 DROP FUNCTION f1;
1864 DROP VIEW v1;
1865 DROP TABLE t1;
1866
1867
1868 --echo #
1869 --echo # %TYPE variables + INFORMATION_SCHEMA
1870 --echo #
1871 DELIMITER $$;
1872 CREATE PROCEDURE p1
1873 AS
1874 tables_table_name INFORMATION_SCHEMA.TABLES.TABLE_NAME%TYPE;
1875 tables_table_rows INFORMATION_SCHEMA.TABLES.TABLE_ROWS%TYPE;
1876 processlist_info INFORMATION_SCHEMA.PROCESSLIST.INFO%TYPE;
1877 processlist_info_binary INFORMATION_SCHEMA.PROCESSLIST.INFO_BINARY%TYPE;
1878 BEGIN
1879 CREATE TABLE t1 AS SELECT
1880 tables_table_name,
1881 tables_table_rows,
1882 processlist_info,
1883 processlist_info_binary;
1884 END;
1885 $$
1886 DELIMITER ;$$
1887 CALL p1();
1888 SHOW CREATE TABLE t1;
1889 DROP TABLE t1;
1890 DROP PROCEDURE p1;
1891
1892
1893 --echo #
1894 --echo # %TYPE + Table structure change
1895 --echo # Data type for both a0 and a1 is chosen in the very beginning
1896 --echo #
1897 DELIMITER $$;
1898 CREATE PROCEDURE p1
1899 AS
1900 a0 t1.a%TYPE;
1901 BEGIN
1902 ALTER TABLE t1 MODIFY a VARCHAR(10); -- This does not affect a1
1903 DECLARE
1904 a1 t1.a%TYPE;
1905 BEGIN
1906 CREATE TABLE t2 AS SELECT a0, a1;
1907 SHOW CREATE TABLE t2;
1908 DROP TABLE t2;
1909 END;
1910 END
1911 $$
1912 DELIMITER ;$$
1913 CREATE TABLE t1 (a INT);
1914 CALL p1;
1915 DROP TABLE t1;
1916 DROP PROCEDURE p1;
1917
1918
1919 --echo #
1920 --echo # %TYPE in parameters
1921 --echo #
1922 CREATE TABLE t1 (a VARCHAR(10));
1923 CREATE DATABASE test1;
1924 CREATE TABLE test1.t1 (b SMALLINT);
1925 DELIMITER $$;
1926 CREATE PROCEDURE p1(a t1.a%TYPE, b test1.t1.b%TYPE)
1927 AS
1928 BEGIN
1929 CREATE TABLE t2 AS SELECT a, b;
1930 END;
1931 $$
1932 DELIMITER ;$$
1933 CALL p1('test', 123);
1934 SHOW CREATE TABLE t2;
1935 SELECT * FROM t2;
1936 DROP TABLE t2;
1937 DROP PROCEDURE p1;
1938 DROP TABLE test1.t1;
1939 DROP DATABASE test1;
1940 DROP TABLE t1;
1941
1942 --echo #
1943 --echo # %TYPE in a stored function variables and arguments
1944 --echo #
1945
1946 CREATE TABLE t1 (a INT);
1947 SET sql_mode=ORACLE;
1948 DELIMITER $$;
1949 CREATE FUNCTION f1 (prm t1.a%TYPE) RETURN INT
1950 AS
1951 a t1.a%TYPE:= prm;
1952 BEGIN
1953 RETURN a;
1954 END;
1955 $$
1956 DELIMITER ;$$
1957 SELECT f1(20);
1958 DROP FUNCTION f1;
1959 DROP TABLE t1;
1960
1961
1962 --echo #
1963 --echo # var%TYPE in function RETURN clause is not supported yet
1964 --echo #
1965 DELIMITER $$;
1966 --error ER_PARSE_ERROR
1967 CREATE FUNCTION f1 RETURN a%TYPE
1968 AS
1969 BEGIN
1970 RETURN 0;
1971 END;
1972 $$
1973 DELIMITER ;$$
1974
1975
1976 --echo #
1977 --echo # End of MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
1978 --echo #
1979
1980
1981 --echo #
1982 --echo # MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword
1983 --echo #
1984
1985 DELIMITER $$;
1986 CREATE FUNCTION f1 RETURN INT AS
1987 BEGIN
1988 RETURN 10;
1989 END f1;
1990 $$
1991 DELIMITER ;$$
1992 DROP FUNCTION f1;
1993
1994 DELIMITER $$;
1995 CREATE FUNCTION test.f1 RETURN INT AS
1996 BEGIN
1997 RETURN 10;
1998 END test.f1;
1999 $$
2000 DELIMITER ;$$
2001 DROP FUNCTION f1;
2002
2003 DELIMITER $$;
2004 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
2005 CREATE FUNCTION test.f1 RETURN INT AS
2006 BEGIN
2007 RETURN 10;
2008 END test2.f1;
2009 $$
2010 DELIMITER ;$$
2011
2012 DELIMITER $$;
2013 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
2014 CREATE FUNCTION test.f1 RETURN INT AS
2015 BEGIN
2016 RETURN 10;
2017 END test.f2;
2018 $$
2019 DELIMITER ;$$
2020
2021 DELIMITER $$;
2022 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
2023 CREATE FUNCTION f1 RETURN INT AS
2024 BEGIN
2025 RETURN 10;
2026 END test.f2;
2027 $$
2028 DELIMITER ;$$
2029
2030 DELIMITER $$;
2031 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
2032 CREATE FUNCTION f1 RETURN INT AS
2033 BEGIN
2034 RETURN 10;
2035 END test2.f1;
2036 $$
2037 DELIMITER ;$$
2038
2039
2040 DELIMITER $$;
2041 CREATE PROCEDURE p1 AS
2042 BEGIN
2043 NULL;
2044 END p1;
2045 $$
2046 DELIMITER ;$$
2047 DROP PROCEDURE p1;
2048
2049 DELIMITER $$;
2050 CREATE PROCEDURE test.p1 AS
2051 BEGIN
2052 NULL;
2053 END test.p1;
2054 $$
2055 DELIMITER ;$$
2056 DROP PROCEDURE p1;
2057
2058 DELIMITER $$;
2059 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
2060 CREATE PROCEDURE test.p1 AS
2061 BEGIN
2062 NULL;
2063 END test2.p1;
2064 $$
2065 DELIMITER ;$$
2066
2067 DELIMITER $$;
2068 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
2069 CREATE PROCEDURE test.p1 AS
2070 BEGIN
2071 NULL;
2072 END test.p2;
2073 $$
2074 DELIMITER ;$$
2075
2076 DELIMITER $$;
2077 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
2078 CREATE PROCEDURE p1 AS
2079 BEGIN
2080 NULL;
2081 END test.p2;
2082 $$
2083 DELIMITER ;$$
2084
2085 DELIMITER $$;
2086 --error ER_END_IDENTIFIER_DOES_NOT_MATCH
2087 CREATE PROCEDURE p1 AS
2088 BEGIN
2089 NULL;
2090 END test2.p1;
2091 $$
2092 DELIMITER ;$$
2093
2094 --echo #
2095 --echo # MDEV-12107 sql_mode=ORACLE: Inside routines the CALL keywoard is optional
2096 --echo #
2097 DELIMITER /;
2098 CREATE OR REPLACE PROCEDURE p1(a INT) AS
2099 BEGIN
2100 SELECT 'This is p1' AS "comment";
2101 END;
2102 /
2103 CREATE OR REPLACE PROCEDURE p2 AS
2104 BEGIN
2105 SELECT 'This is p2' AS "comment";
2106 END;
2107 /
2108 BEGIN
2109 p1(10);
2110 p2;
2111 test.p1(10);
2112 test.p2;
2113 END;
2114 /
2115 CREATE PROCEDURE p3 AS
2116 BEGIN
2117 p1(10);
2118 p2;
2119 test.p1(10);
2120 test.p2;
2121 END
2122 /
2123 DELIMITER ;/
2124 CALL p3;
2125 DROP PROCEDURE p3;
2126 DROP PROCEDURE p2;
2127 DROP PROCEDURE p1;
2128
2129
2130 --echo #
2131 --echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
2132 --echo #
2133
2134 --enable_metadata
2135 --disable_ps_protocol
2136 SELECT SQL%ROWCOUNT;
2137 --enable_ps_protocol
2138 --disable_metadata
2139
2140 --echo #
2141 --echo # MDEV-13686 EXCEPTION reserved keyword in SQL_MODE=oracle but not in Oracle itself
2142 --echo #
2143 CREATE TABLE t1 (c1 int);
2144 CREATE VIEW v1 AS SELECT c1 exception FROM t1;
2145 SELECT exception FROM v1;
2146 DROP VIEW v1;
2147 DROP TABLE t1;
2148
2149
2150
2151 --echo #
2152 --echo # MDEV-14139 Anchored data types for variables
2153 --echo #
2154
2155 DELIMITER $$;
2156 --error ER_SP_UNDECLARED_VAR
2157 BEGIN NOT ATOMIC
2158 DECLARE a a%TYPE;
2159 END;
2160 $$
2161 DELIMITER ;$$
2162
2163
2164 DELIMITER $$;
2165 DECLARE
2166 int11 INT;
2167 dec103 DECIMAL(10,3);
2168 flt0 FLOAT;
2169 dbl0 DOUBLE;
2170 enum0 ENUM('a','b');
2171 bit3 BIT(3);
2172
2173 varchar10 VARCHAR(10);
2174 text1 TEXT;
2175 tinytext1 TINYTEXT;
2176 mediumtext1 MEDIUMTEXT;
2177 longtext1 LONGTEXT;
2178
2179 time3 TIME(3);
2180 datetime4 DATETIME(4);
2181 timestamp5 TIMESTAMP(5);
2182 date0 DATE;
2183
2184 a_int11 int11%TYPE;
2185 a_dec103 dec103%TYPE;
2186 a_flt0 flt0%TYPE;
2187 a_dbl0 dbl0%TYPE;
2188 a_bit3 bit3%TYPE;
2189 a_enum0 enum0%TYPE;
2190 a_varchar10 varchar10%TYPE;
2191 a_text1 text1%TYPE;
2192 a_tinytext1 tinytext1%TYPE;
2193 a_mediumtext1 mediumtext1%TYPE;
2194 a_longtext1 longtext1%TYPE;
2195 a_time3 time3%TYPE;
2196 a_datetime4 datetime4%TYPE;
2197 a_timestamp5 timestamp5%TYPE;
2198 a_date0 date0%TYPE;
2199
2200 aa_int11 a_int11%TYPE;
2201 aa_dec103 a_dec103%TYPE;
2202 aa_flt0 a_flt0%TYPE;
2203 aa_dbl0 a_dbl0%TYPE;
2204 aa_bit3 a_bit3%TYPE;
2205 aa_enum0 a_enum0%TYPE;
2206 aa_varchar10 a_varchar10%TYPE;
2207 aa_text1 a_text1%TYPE;
2208 aa_tinytext1 a_tinytext1%TYPE;
2209 aa_mediumtext1 a_mediumtext1%TYPE;
2210 aa_longtext1 a_longtext1%TYPE;
2211 aa_time3 a_time3%TYPE;
2212 aa_datetime4 a_datetime4%TYPE;
2213 aa_timestamp5 a_timestamp5%TYPE;
2214 aa_date0 a_date0%TYPE;
2215 BEGIN
2216 CREATE TABLE t1 AS
2217 SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3,
2218 a_enum0,a_varchar10,
2219 a_text1,a_tinytext1,a_mediumtext1,a_longtext1,
2220 a_time3,a_datetime4,a_timestamp5,a_date0;
2221 SHOW CREATE TABLE t1;
2222 DROP TABLE t1;
2223
2224 CREATE TABLE t1 AS
2225 SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3,
2226 aa_enum0,aa_varchar10,
2227 aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1,
2228 aa_time3,aa_datetime4,aa_timestamp5,aa_date0;
2229 SHOW CREATE TABLE t1;
2230 DROP TABLE t1;
2231
2232 END;
2233 $$
2234 DELIMITER ;$$
2235
2236
2237 --echo #
2238 --echo # MDEV-11160 "Incorrect column name" when "CREATE TABLE t1 AS SELECT spvar"
2239 --echo #
2240
2241
2242 CREATE TABLE t1 (x INT);
2243 INSERT INTO t1 VALUES (10);
2244 CREATE VIEW v1 AS SELECT x+1 AS a,x+1 AS b FROM t1;
2245 DELIMITER $$;
2246 CREATE PROCEDURE p1
2247 AS
2248 a INT := 1;
2249 b INT := 2;
2250 BEGIN
2251 CREATE TABLE t2 AS SELECT a,b FROM v1;
2252 SHOW CREATE TABLE t2;
2253 SELECT * FROM t2;
2254 DROP TABLE t2;
2255 END;
2256 $$
2257 DELIMITER ;$$
2258 CALL p1();
2259 DROP PROCEDURE p1;
2260 DROP VIEW v1;
2261 DROP TABLE t1;
2262
2263
2264 --echo #
2265 --echo # MDEV-14228 MariaDB crashes with function
2266 --echo #
2267
2268 CREATE TABLE t1 (c VARCHAR(16), KEY(c));
2269 INSERT INTO t1 VALUES ('foo');
2270
2271 DELIMITER $$;
2272 CREATE FUNCTION f1() RETURN VARCHAR(16)
2273 IS
2274 v VARCHAR2(16);
2275 BEGIN
2276 FOR v IN (SELECT DISTINCT c FROM t1)
2277 LOOP
2278 IF (v = 'bar') THEN
2279 SELECT 1 INTO @a;
2280 END IF;
2281 END LOOP;
2282 RETURN 'qux';
2283 END $$
2284 DELIMITER ;$$
2285 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
2286 SELECT f1();
2287 DROP FUNCTION f1;
2288
2289 DELIMITER $$;
2290 CREATE FUNCTION f1() RETURN VARCHAR(16)
2291 IS
2292 v t1%ROWTYPE;
2293 BEGIN
2294 IF v = 'bar' THEN
2295 NULL;
2296 END IF;
2297 RETURN 'qux';
2298 END $$
2299 DELIMITER ;$$
2300 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
2301 SELECT f1();
2302 DROP FUNCTION f1;
2303
2304 DELIMITER $$;
2305 CREATE FUNCTION f1() RETURN VARCHAR(16)
2306 IS
2307 v ROW(a INT);
2308 BEGIN
2309 IF v = 'bar' THEN
2310 NULL;
2311 END IF;
2312 RETURN 'qux';
2313 END $$
2314 DELIMITER ;$$
2315 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
2316 SELECT f1();
2317 DROP FUNCTION f1;
2318
2319 DROP TABLE t1;
2320
2321
2322 DELIMITER $$;
2323 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
2324 DECLARE
2325 v ROW(a INT);
2326 BEGIN
2327 SELECT v IN ('a','b');
2328 END $$
2329 DELIMITER ;$$
2330
2331 DELIMITER $$;
2332 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
2333 DECLARE
2334 v ROW(a INT);
2335 BEGIN
2336 SELECT 'a' IN (v,'b');
2337 END $$
2338 DELIMITER ;$$
2339
2340 DELIMITER $$;
2341 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
2342 DECLARE
2343 v ROW(a INT);
2344 BEGIN
2345 SELECT 'a' IN ('b',v);
2346 END $$
2347 DELIMITER ;$$
2348
2349 --echo #
2350 --echo # MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly
2351 --echo #
2352
2353 DELIMITER $$;
2354 DECLARE
2355 totalprice DECIMAL(12,2):=NULL;
2356 loop_start INTEGER := 1;
2357 BEGIN
2358 FOR idx IN REVERSE loop_start..10 LOOP
2359 SELECT idx;
2360 END LOOP;
2361 END;
2362 $$
2363 DELIMITER ;$$
2364
2365
2366 DELIMITER $$;
2367 CREATE PROCEDURE p1 AS
2368 loop_start INTEGER := 1;
2369 BEGIN
2370 FOR idx IN REVERSE 3..loop_start LOOP
2371 SELECT idx;
2372 END LOOP;
2373 END;
2374 $$
2375 DELIMITER ;$$
2376 CALL p1();
2377 DROP PROCEDURE p1;
2378
2379
2380 DELIMITER $$;
2381 CREATE PROCEDURE p1 AS
2382 loop_start INTEGER := 1;
2383 BEGIN
2384 FOR idx IN REVERSE loop_start..3 LOOP
2385 SELECT idx;
2386 END LOOP;
2387 END;
2388 $$
2389 DELIMITER ;$$
2390 CALL p1();
2391 DROP PROCEDURE p1;
2392
2393
2394 --echo #
2395 --echo # MDEV-28588 SIGSEGV in __memmove_avx_unaligned_erms, strmake_root
2396 --echo #
2397
2398 SET sql_mode=ORACLE;
2399 BEGIN END;
2400
2401 CREATE TABLE t1 (a INT);
2402 CREATE TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW BEGIN END;
2403 --vertical_results
2404 SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='tr';
2405 --horizontal_results
2406 DROP TRIGGER tr;
2407 DROP TABLE t1;
2408
2409 --echo #
2410 --echo # End of 10.3 tests
2411 --echo #
2412
2413 --echo #
2414 --echo # MDEV-19637 Crash on an SP variable assignment to a wrong subselect
2415 --echo #
2416
2417 DELIMITER $$;
2418 --error ER_CANT_USE_OPTION_HERE
2419 DECLARE
2420 a INT;
2421 BEGIN
2422 SET a=(SELECT 1 FROM DUAL UNION SELECT HIGH_PRIORITY 2 FROM DUAL);
2423 END;
2424 $$
2425 DELIMITER ;$$
2426
2427
2428 --echo #
2429 --echo # End of 10.4 tests
2430 --echo #