Debian Codegraph @

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

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