Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2 #
3 # MDEV-10914 ROW data type for stored routine variables
4 #
5 #
6 # ROW of ROWs is not supported yet
7 #
8 CREATE PROCEDURE p1()
9 AS
10 a ROW(a ROW(a INT));
11 BEGIN
12 END;
13 $$
14 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 'ROW(a INT));
15 BEGIN
16 END' at line 3
17 #
18 # Returning the entire ROW parameter from a function
19 #
20 CREATE FUNCTION f1(a ROW(a INT, b INT)) RETURN INT
21 AS
22 BEGIN
23 RETURN a;
24 END;
25 $$
26 SELECT f1(ROW(10,20));
27 ERROR HY000: Cannot cast 'row' as 'int' in assignment of `f1(ROW(10,20))`
28 DROP FUNCTION f1;
29 #
30 # ROW as an SP parameter
31 #
32 CREATE FUNCTION f1(a ROW(a INT,b INT)) RETURN INT
33 AS
34 BEGIN
35 RETURN a.b;
36 END;
37 $$
38 CREATE PROCEDURE p1()
39 AS
40 a ROW(a INT,b INT):=(11,21);
41 BEGIN
42 SELECT f1(a);
43 END;
44 $$
45 SELECT f1(ROW(10,20));
46 f1(ROW(10,20))
47 20
48 SELECT f1(10);
49 ERROR 21000: Operand should contain 2 column(s)
50 SELECT f1(ROW(10,20,30));
51 ERROR 21000: Operand should contain 2 column(s)
52 CALL p1();
53 f1(a)
54 21
55 DROP PROCEDURE p1;
56 DROP FUNCTION f1;
57 CREATE PROCEDURE p1(a ROW(a INT,b INT))
58 AS
59 BEGIN
60 SELECT a.a, a.b;
61 END;
62 $$
63 CALL p1(ROW(10,20));
64 a.a a.b
65 10 20
66 CALL p1(10);
67 ERROR 21000: Operand should contain 2 column(s)
68 CALL p1(ROW(10,20,30));
69 ERROR 21000: Operand should contain 2 column(s)
70 DROP PROCEDURE p1;
71 #
72 # ROW as an SP OUT parameter
73 #
74 CREATE PROCEDURE p1(a OUT ROW(a INT,b INT))
75 AS
76 BEGIN
77 a.a:=10;
78 a.b:=20;
79 END;
80 $$
81 CREATE PROCEDURE p2
82 AS
83 a ROW(a INT,b INT):=(11,21);
84 BEGIN
85 CALL p1(a);
86 SELECT a.a,a.b;
87 END;
88 $$
89 CALL p2();
90 a.a a.b
91 10 20
92 DROP PROCEDURE p2;
93 DROP PROCEDURE p1;
94 #
95 # Diplicate row field
96 #
97 CREATE PROCEDURE p1()
98 AS
99 a ROW (a INT, a DOUBLE);
100 BEGIN
101 SELECT a.a;
102 END;
103 $$
104 ERROR 42S21: Duplicate column name 'a'
105 #
106 # Bad scalar default value
107 #
108 CREATE PROCEDURE p1()
109 AS
110 a ROW (a INT, b DOUBLE):= 1;
111 BEGIN
112 SELECT a.a;
113 END;
114 $$
115 CALL p1();
116 ERROR 21000: Operand should contain 2 column(s)
117 DROP PROCEDURE p1;
118 #
119 # Bad ROW default value with a wrong number of fields
120 #
121 CREATE PROCEDURE p1()
122 AS
123 a ROW (a INT, b DOUBLE):= ROW(1,2,3);
124 BEGIN
125 SELECT a.a;
126 END;
127 $$
128 CALL p1();
129 ERROR 21000: Operand should contain 2 column(s)
130 DROP PROCEDURE p1;
131 #
132 # Scalar variable vs table alias cause no ambiguity
133 #
134 CREATE PROCEDURE p1()
135 AS
136 a INT;
137 BEGIN
138 -- a.x is a table column here (not a row variable field)
139 SELECT a.x FROM a;
140 SELECT a.x FROM t1 a;
141 END;
142 $$
143 DROP PROCEDURE p1;
144 #
145 # Using the entire ROW variable in select list
146 #
147 CREATE PROCEDURE p1()
148 AS
149 a ROW (a INT);
150 BEGIN
151 SELECT a;
152 END;
153 $$
154 CALL p1();
155 ERROR 21000: Operand should contain 1 column(s)
156 DROP PROCEDURE p1;
157 CREATE PROCEDURE p1()
158 AS
159 a ROW (a INT,b INT);
160 BEGIN
161 SELECT a;
162 END;
163 $$
164 CALL p1();
165 ERROR 21000: Operand should contain 1 column(s)
166 DROP PROCEDURE p1;
167 #
168 # Using the entire ROW variable in functions
169 #
170 CREATE PROCEDURE p1()
171 AS
172 a ROW (a INT);
173 BEGIN
174 SELECT COALESCE(a);
175 END;
176 $$
177 CALL p1();
178 ERROR 21000: Operand should contain 1 column(s)
179 DROP PROCEDURE p1;
180 CREATE PROCEDURE p1()
181 AS
182 a ROW (a INT,b INT);
183 BEGIN
184 SELECT COALESCE(a);
185 END;
186 $$
187 CALL p1();
188 ERROR 21000: Operand should contain 1 column(s)
189 DROP PROCEDURE p1;
190 CREATE PROCEDURE p1()
191 AS
192 a ROW (a INT);
193 BEGIN
194 SELECT a+1;
195 END;
196 $$
197 CALL p1();
198 ERROR HY000: Illegal parameter data types row and int for operation '+'
199 DROP PROCEDURE p1;
200 CREATE PROCEDURE p1()
201 AS
202 a ROW (a INT,b INT);
203 BEGIN
204 SELECT a+1;
205 END;
206 $$
207 CALL p1();
208 ERROR HY000: Illegal parameter data types row and int for operation '+'
209 DROP PROCEDURE p1;
210 #
211 # Comparing the entire ROW to a scalar value
212 #
213 CREATE PROCEDURE p1()
214 AS
215 a ROW (a INT,b INT);
216 BEGIN
217 SELECT a=1;
218 END;
219 $$
220 CALL p1();
221 ERROR HY000: Illegal parameter data types row and int for operation '='
222 DROP PROCEDURE p1;
223 CREATE PROCEDURE p1()
224 AS
225 a ROW (a INT,b INT);
226 BEGIN
227 SELECT 1=a;
228 END;
229 $$
230 CALL p1();
231 ERROR HY000: Illegal parameter data types int and row for operation '='
232 DROP PROCEDURE p1;
233 #
234 # Passing the entire ROW to a stored function
235 #
236 CREATE FUNCTION f1(a INT) RETURN INT
237 AS
238 BEGIN
239 RETURN a;
240 END;
241 $$
242 CREATE PROCEDURE p1()
243 AS
244 a ROW (a INT,b INT);
245 BEGIN
246 SELECT f1(a);
247 END;
248 $$
249 CALL p1();
250 ERROR HY000: Cannot cast 'row' as 'int' in assignment of `a`
251 DROP PROCEDURE p1;
252 DROP FUNCTION f1;
253 CREATE FUNCTION f1(a INT) RETURN INT
254 AS
255 BEGIN
256 RETURN a;
257 END;
258 $$
259 CREATE PROCEDURE p1()
260 AS
261 a ROW (a INT);
262 BEGIN
263 SELECT f1(a);
264 END;
265 $$
266 CALL p1();
267 ERROR HY000: Cannot cast 'row' as 'int' in assignment of `a`
268 DROP PROCEDURE p1;
269 DROP FUNCTION f1;
270 #
271 # Assigning a scalar value to a ROW variable with 1 column
272 #
273 CREATE OR REPLACE PROCEDURE p1
274 AS
275 rec ROW(a INT);
276 BEGIN
277 rec:=1;
278 END;
279 $$
280 CALL p1();
281 ERROR 21000: Operand should contain 1 column(s)
282 DROP PROCEDURE p1;
283 #
284 # Assigning a scalar value to a ROW variable with 2 columns
285 #
286 CREATE OR REPLACE PROCEDURE p1
287 AS
288 rec ROW(a INT,b INT);
289 BEGIN
290 rec:=1;
291 END;
292 $$
293 CALL p1();
294 ERROR 21000: Operand should contain 2 column(s)
295 DROP PROCEDURE p1;
296 #
297 # Assigning a ROW value to a ROW variable with different number of columns
298 #
299 CREATE OR REPLACE PROCEDURE p1
300 AS
301 rec ROW(a INT,b INT);
302 BEGIN
303 rec:=ROW(1,2,3);
304 END;
305 $$
306 CALL p1();
307 ERROR 21000: Operand should contain 2 column(s)
308 DROP PROCEDURE p1;
309 #
310 # Returning the entire ROW from a function is not supported yet
311 # This syntax would be needed: SELECT f1().x FROM DUAL;
312 #
313 CREATE FUNCTION f1(a INT) RETURN INT
314 AS
315 rec ROW(a INT);
316 BEGIN
317 RETURN rec;
318 END;
319 $$
320 SELECT f1(10);
321 ERROR HY000: Cannot cast 'row' as 'int' in assignment of `f1(10)`
322 DROP FUNCTION f1;
323 #
324 # Using the entire ROW in SELECT..CREATE
325 #
326 CREATE PROCEDURE p1
327 AS
328 rec ROW(a INT,b INT);
329 BEGIN
330 CREATE TABLE t1 AS SELECT rec;
331 END;
332 $$
333 CALL p1();
334 ERROR 21000: Operand should contain 1 column(s)
335 DROP PROCEDURE p1;
336 #
337 # Using the entire ROW in LIMIT
338 #
339 CREATE PROCEDURE p1()
340 AS
341 rec ROW(a INT);
342 BEGIN
343 rec.a:= '10';
344 SELECT * FROM t1 LIMIT rec;
345 END;
346 $$
347 ERROR HY000: A variable of a non-integer based type in LIMIT clause
348 #
349 # Setting ROW fields using a SET command
350 #
351 CREATE OR REPLACE PROCEDURE p1
352 AS
353 rec ROW(a INT,b DOUBLE,c VARCHAR(10));
354 a INT;
355 BEGIN
356 SET @a= 10, rec.a=10, rec.b=20, rec.c= 'test', a= 5;
357 SELECT rec.a, rec.b, rec.c, a;
358 END;
359 $$
360 CALL p1();
361 rec.a rec.b rec.c a
362 10 20 test 5
363 DROP PROCEDURE p1;
364 #
365 # Assigning a ROW variable from a ROW value
366 #
367 CREATE PROCEDURE p1
368 AS
369 rec ROW(a INT,b INT);
370 BEGIN
371 rec:=ROW(1,2);
372 SELECT rec.a, rec.b;
373 END;
374 $$
375 CALL p1();
376 rec.a rec.b
377 1 2
378 DROP PROCEDURE p1;
379 #
380 # Assigning a ROW variable from another ROW value
381 #
382 CREATE PROCEDURE p1
383 AS
384 rec1 ROW(a INT,b INT);
385 rec2 ROW(a INT,b INT);
386 BEGIN
387 rec1:=ROW(1,2);
388 rec2:=rec1;
389 SELECT rec2.a, rec2.b;
390 END;
391 $$
392 CALL p1();
393 rec2.a rec2.b
394 1 2
395 DROP PROCEDURE p1;
396 #
397 # Comparing a ROW variable to a ROW() function
398 #
399 CREATE OR REPLACE PROCEDURE p1
400 AS
401 rec ROW(a INT,b INT);
402 BEGIN
403 rec.a:= 1;
404 rec.b:= 2;
405 SELECT rec=(0,0), rec=ROW(0,0), (0,0)=rec, ROW(0,0)=rec;
406 SELECT rec=(1,2), rec=ROW(1,2), (1,2)=rec, ROW(1,2)=rec;
407 SELECT rec=(NULL,0), rec=ROW(NULL,0);
408 SELECT rec=(NULL,2), rec=ROW(NULL,2);
409 SELECT rec<>(0,0), rec<>ROW(0,0);
410 SELECT rec<>(1,2), rec<>ROW(1,2);
411 SELECT rec<>(NULL,0), rec<>ROW(NULL,0);
412 SELECT rec<>(NULL,2), rec<>ROW(NULL,2);
413 SELECT rec IN ((0,0)), rec IN (ROW(0,0));
414 SELECT rec IN ((1,2)), rec IN (ROW(1,2));
415 SELECT rec IN ((0,NULL),(1,2));
416 SELECT rec NOT IN ((0,NULL),(1,1));
417 SELECT rec NOT IN ((1,NULL),(1,1));
418 END;
419 $$
420 CALL p1();
421 rec=(0,0) rec=ROW(0,0) (0,0)=rec ROW(0,0)=rec
422 0 0 0 0
423 rec=(1,2) rec=ROW(1,2) (1,2)=rec ROW(1,2)=rec
424 1 1 1 1
425 rec=(NULL,0) rec=ROW(NULL,0)
426 0 0
427 rec=(NULL,2) rec=ROW(NULL,2)
428 NULL NULL
429 rec<>(0,0) rec<>ROW(0,0)
430 1 1
431 rec<>(1,2) rec<>ROW(1,2)
432 0 0
433 rec<>(NULL,0) rec<>ROW(NULL,0)
434 1 1
435 rec<>(NULL,2) rec<>ROW(NULL,2)
436 NULL NULL
437 rec IN ((0,0)) rec IN (ROW(0,0))
438 0 0
439 rec IN ((1,2)) rec IN (ROW(1,2))
440 1 1
441 rec IN ((0,NULL),(1,2))
442 1
443 rec NOT IN ((0,NULL),(1,1))
444 1
445 rec NOT IN ((1,NULL),(1,1))
446 NULL
447 DROP PROCEDURE p1;
448 #
449 # Comparing a ROW variable to another ROW variable
450 #
451 CREATE OR REPLACE PROCEDURE p1
452 AS
453 rec1,rec2,rec3 ROW(a INT,b INT);
454 BEGIN
455 rec1.a:= 1;
456 rec1.b:= 2;
457 rec2.a:= 11;
458 rec2.b:= 12;
459 rec3.a:= 11;
460 rec3.b:= 12;
461 SELECT rec1=rec2, rec2=rec1, rec2=rec3, rec3=rec2;
462 END;
463 $$
464 CALL p1();
465 rec1=rec2 rec2=rec1 rec2=rec3 rec3=rec2
466 0 0 1 1
467 DROP PROCEDURE p1;
468 #
469 # Referencing a non-existing row variable
470 #
471 CREATE PROCEDURE p1()
472 AS
473 BEGIN
474 SET a.b=1;
475 END;
476 $$
477 ERROR HY000: Unknown structured system variable or ROW routine variable 'a'
478 CREATE PROCEDURE p1()
479 AS
480 BEGIN
481 a.b:=1;
482 END;
483 $$
484 ERROR HY000: Unknown structured system variable or ROW routine variable 'a'
485 #
486 # Referencing a non-existing row field
487 #
488 CREATE PROCEDURE p1()
489 AS
490 a ROW(a INT,b INT);
491 BEGIN
492 SELECT a.c FROM t1;
493 END;
494 $$
495 ERROR HY000: Row variable 'a' does not have a field 'c'
496 #
497 # ROW and scalar variables with the same name shadowing each other
498 #
499 CREATE PROCEDURE p1()
500 AS
501 a ROW(a INT);
502 BEGIN
503 a.a:=100;
504 DECLARE
505 a INT:= 200;
506 BEGIN
507 SELECT a;
508 DECLARE
509 a ROW(a INT);
510 BEGIN
511 a.a:=300;
512 SELECT a.a;
513 END;
514 SELECT a;
515 END;
516 SELECT a.a;
517 END;
518 $$
519 CALL p1();
520 a
521 200
522 a.a
523 300
524 a
525 200
526 a.a
527 100
528 DROP PROCEDURE p1;
529 #
530 # ROW with good default values
531 #
532 CREATE PROCEDURE p1()
533 AS
534 a ROW(a INT,b INT):= (10,20);
535 b ROW(a INT,b INT):= (11,21);
536 c ROW(a INT,b INT):= a;
537 BEGIN
538 SELECT a.a, a.b, b.a, b.b, c.a, c.b FROM DUAL;
539 END;
540 $$
541 CALL p1;
542 a.a a.b b.a b.b c.a c.b
543 10 20 11 21 10 20
544 DROP PROCEDURE p1;
545 #
546 # ROW in WHERE clause
547 #
548 CREATE TABLE t1 (a INT,b INT);
549 INSERT INTO t1 VALUES (10,20);
550 CREATE PROCEDURE p1()
551 AS
552 rec ROW(a INT,b INT):=ROW(10,20);
553 BEGIN
554 SELECT * FROM t1 WHERE rec=ROW(a,b);
555 SELECT * FROM t1 WHERE ROW(a,b)=rec;
556 SELECT * FROM t1 WHERE rec=ROW(10,20);
557 SELECT * FROM t1 WHERE ROW(10,20)=rec;
558 END;
559 $$
560 CALL p1();
561 a b
562 10 20
563 a b
564 10 20
565 a b
566 10 20
567 a b
568 10 20
569 DROP TABLE t1;
570 DROP PROCEDURE p1;
571 #
572 # ROW fields in WHERE clause
573 #
574 CREATE TABLE t1 (a INT);
575 INSERT INTO t1 VALUES (10),(20);
576 CREATE PROCEDURE p1()
577 AS
578 rec ROW(a INT);
579 BEGIN
580 rec.a:= 10;
581 SELECT * FROM t1 WHERE a=rec.a;
582 END;
583 $$
584 CALL p1();
585 a
586 10
587 DROP TABLE t1;
588 DROP PROCEDURE p1;
589 #
590 # ROW fields in HAVING clause
591 #
592 CREATE TABLE t1 (a INT);
593 INSERT INTO t1 VALUES (10),(20);
594 CREATE PROCEDURE p1()
595 AS
596 rec ROW(a INT);
597 BEGIN
598 rec.a:= 10;
599 SELECT * FROM t1 HAVING a=rec.a;
600 SELECT * FROM t1 HAVING MIN(a)=rec.a;
601 END;
602 $$
603 CALL p1();
604 a
605 10
606 a
607 10
608 DROP TABLE t1;
609 DROP PROCEDURE p1;
610 #
611 # ROW fields in LIMIT clause
612 #
613 CREATE TABLE t1 (a INT);
614 SELECT 1 FROM t1 LIMIT t1.a;
615 ERROR 42000: Undeclared variable: t1
616 DROP TABLE t1;
617 CREATE TABLE t1 (a INT);
618 INSERT INTO t1 VALUES (10),(20);
619 CREATE PROCEDURE p1()
620 AS
621 rec ROW(a INT);
622 BEGIN
623 rec.a:= 10;
624 SELECT * FROM t1 LIMIT rec.a;
625 END;
626 $$
627 CALL p1();
628 a
629 10
630 20
631 DROP TABLE t1;
632 DROP PROCEDURE p1;
633 CREATE PROCEDURE p1()
634 AS
635 rec ROW(a VARCHAR(10));
636 BEGIN
637 rec.a:= '10';
638 SELECT * FROM t1 LIMIT rec.a;
639 END;
640 $$
641 ERROR HY000: A variable of a non-integer based type in LIMIT clause
642 #
643 # ROW fields in select list
644 #
645 CREATE TABLE t1 (a INT);
646 INSERT INTO t1 VALUES (10),(20);
647 CREATE PROCEDURE p1()
648 AS
649 t1 ROW(a INT);
650 BEGIN
651 t1.a:= 10;
652 SELECT t1.a, 'This is the variable t1.a value, rather than the column t1.a' AS comm FROM t1;
653 SELECT t1.a, t2.a, t1.a+t2.a FROM t1 t2;
654 END;
655 $$
656 CALL p1();
657 t1.a comm
658 10 This is the variable t1.a value, rather than the column t1.a
659 10 This is the variable t1.a value, rather than the column t1.a
660 t1.a a t1.a+t2.a
661 10 10 20
662 10 20 30
663 DROP TABLE t1;
664 DROP PROCEDURE p1;
665 #
666 # ROW fields as insert values
667 #
668 CREATE TABLE t1 (a INT, b VARCHAR(10));
669 CREATE PROCEDURE p1()
670 AS
671 rec ROW(a INT, b VARCHAR(10));
672 BEGIN
673 rec.a:= 10;
674 rec.b:= 'test';
675 INSERT INTO t1 VALUES (rec.a, rec.b);
676 END;
677 $$
678 CALL p1();
679 SELECT * FROM t1;
680 a b
681 10 test
682 DROP TABLE t1;
683 DROP PROCEDURE p1;
684 #
685 # ROW fields as SP out parameters
686 #
687 CREATE PROCEDURE p1(a OUT INT, b OUT VARCHAR)
688 AS
689 BEGIN
690 a:= 10;
691 b:= 'test';
692 END;
693 $$
694 CREATE PROCEDURE p2
695 AS
696 rec ROW(a INT, b VARCHAR(10));
697 BEGIN
698 CALL p1(rec.a, rec.b);
699 SELECT rec.a, rec.b;
700 END;
701 $$
702 CALL p2;
703 rec.a rec.b
704 10 test
705 DROP PROCEDURE p1;
706 DROP PROCEDURE p2;
707 #
708 # ROW fields as dynamic SQL out parameters
709 #
710 CREATE PROCEDURE p1(a OUT INT, b OUT VARCHAR)
711 AS
712 BEGIN
713 a:= 20;
714 b:= 'test-dynamic-sql';
715 END;
716 $$
717 CREATE PROCEDURE p2
718 AS
719 rec ROW(a INT, b VARCHAR(30));
720 BEGIN
721 EXECUTE IMMEDIATE 'CALL p1(?,?)' USING rec.a, rec.b;
722 SELECT rec.a, rec.b;
723 END;
724 $$
725 CALL p2;
726 rec.a rec.b
727 20 test-dynamic-sql
728 DROP PROCEDURE p1;
729 DROP PROCEDURE p2;
730 #
731 # ROW fields as SELECT..INTO targets
732 #
733 CREATE PROCEDURE p1
734 AS
735 rec ROW(a INT, b VARCHAR(10));
736 BEGIN
737 SELECT 10,'test' INTO rec.a,rec.b;
738 SELECT rec.a, rec.b;
739 END;
740 $$
741 CALL p1;
742 rec.a rec.b
743 10 test
744 DROP PROCEDURE p1;
745 #
746 # Implicit default NULL handling
747 #
748 CREATE PROCEDURE p1
749 AS
750 rec ROW(a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,0), e TIME, f DATETIME);
751 BEGIN
752 SELECT rec.a, rec.b, rec.c, rec.d, rec.e, rec.f FROM DUAL;
753 END;
754 $$
755 CALL p1();
756 rec.a rec.b rec.c rec.d rec.e rec.f
757 NULL NULL NULL NULL NULL NULL
758 DROP PROCEDURE p1;
759 #
760 # NULL handling
761 #
762 CREATE PROCEDURE p1
763 AS
764 rec1 ROW(a INT, b VARCHAR(10)):=(NULL,NULL);
765 rec2 ROW(a INT, b VARCHAR(10)):=rec1;
766 BEGIN
767 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
768 rec1:= (10,20);
769 rec2:= rec1;
770 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
771 rec1:= (NULL,20);
772 rec2:= rec1;
773 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
774 rec1:= (10,NULL);
775 rec2:= rec1;
776 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
777 rec1:= (NULL,NULL);
778 rec2:= rec1;
779 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
780 END;
781 $$
782 CALL p1;
783 rec1.a rec1.b rec2.a rec2.b
784 NULL NULL NULL NULL
785 rec1.a rec1.b rec2.a rec2.b
786 10 20 10 20
787 rec1.a rec1.b rec2.a rec2.b
788 NULL 20 NULL 20
789 rec1.a rec1.b rec2.a rec2.b
790 10 NULL 10 NULL
791 rec1.a rec1.b rec2.a rec2.b
792 NULL NULL NULL NULL
793 DROP PROCEDURE p1;
794 #
795 # Testing multiple ROW variable declarations
796 # This makes sure that fill_field_definitions() is called only once
797 # per a ROW field, so create length is not converted to internal length
798 # multiple times.
799 #
800 CREATE PROCEDURE p1
801 AS
802 rec1, rec2, rec3 ROW(a VARCHAR(10) CHARACTER SET utf8);
803 BEGIN
804 CREATE TABLE t1 AS SELECT rec1.a, rec2.a, rec3.a;
805 END;
806 $$
807 CALL p1();
808 SHOW CREATE TABLE t1;
809 Table Create Table
810 t1 CREATE TABLE "t1" (
811 "rec1.a" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL,
812 "rec2.a" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL,
813 "rec3.a" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL
814 )
815 DROP TABLE t1;
816 DROP PROCEDURE p1;
817 #
818 # INT
819 #
820 CREATE PROCEDURE p1() AS var INT; rec ROW(var INT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
821 CALL p1();
822 SHOW CREATE TABLE t1;
823 Table Create Table
824 t1 CREATE TABLE "t1" (
825 "var" int(11) DEFAULT NULL,
826 "rec.var" int(11) DEFAULT NULL
827 )
828 DROP TABLE t1;
829 DROP PROCEDURE p1;
830 CREATE PROCEDURE p1() AS var INT(1); rec ROW(var INT(1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
831 CALL p1();
832 SHOW CREATE TABLE t1;
833 Table Create Table
834 t1 CREATE TABLE "t1" (
835 "var" int(11) DEFAULT NULL,
836 "rec.var" int(11) DEFAULT NULL
837 )
838 DROP TABLE t1;
839 DROP PROCEDURE p1;
840 CREATE PROCEDURE p1() AS var INT(2); rec ROW(var INT(2)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
841 CALL p1();
842 SHOW CREATE TABLE t1;
843 Table Create Table
844 t1 CREATE TABLE "t1" (
845 "var" int(11) DEFAULT NULL,
846 "rec.var" int(11) DEFAULT NULL
847 )
848 DROP TABLE t1;
849 DROP PROCEDURE p1;
850 CREATE PROCEDURE p1() AS var INT(3); rec ROW(var INT(3)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
851 CALL p1();
852 SHOW CREATE TABLE t1;
853 Table Create Table
854 t1 CREATE TABLE "t1" (
855 "var" int(11) DEFAULT NULL,
856 "rec.var" int(11) DEFAULT NULL
857 )
858 DROP TABLE t1;
859 DROP PROCEDURE p1;
860 CREATE PROCEDURE p1() AS var INT(4); rec ROW(var INT(4)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
861 CALL p1();
862 SHOW CREATE TABLE t1;
863 Table Create Table
864 t1 CREATE TABLE "t1" (
865 "var" int(11) DEFAULT NULL,
866 "rec.var" int(11) DEFAULT NULL
867 )
868 DROP TABLE t1;
869 DROP PROCEDURE p1;
870 CREATE PROCEDURE p1() AS var INT(5); rec ROW(var INT(5)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
871 CALL p1();
872 SHOW CREATE TABLE t1;
873 Table Create Table
874 t1 CREATE TABLE "t1" (
875 "var" int(11) DEFAULT NULL,
876 "rec.var" int(11) DEFAULT NULL
877 )
878 DROP TABLE t1;
879 DROP PROCEDURE p1;
880 CREATE PROCEDURE p1() AS var INT(6); rec ROW(var INT(6)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
881 CALL p1();
882 SHOW CREATE TABLE t1;
883 Table Create Table
884 t1 CREATE TABLE "t1" (
885 "var" int(11) DEFAULT NULL,
886 "rec.var" int(11) DEFAULT NULL
887 )
888 DROP TABLE t1;
889 DROP PROCEDURE p1;
890 CREATE PROCEDURE p1() AS var INT(7); rec ROW(var INT(7)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
891 CALL p1();
892 SHOW CREATE TABLE t1;
893 Table Create Table
894 t1 CREATE TABLE "t1" (
895 "var" int(11) DEFAULT NULL,
896 "rec.var" int(11) DEFAULT NULL
897 )
898 DROP TABLE t1;
899 DROP PROCEDURE p1;
900 CREATE PROCEDURE p1() AS var INT(8); rec ROW(var INT(8)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
901 CALL p1();
902 SHOW CREATE TABLE t1;
903 Table Create Table
904 t1 CREATE TABLE "t1" (
905 "var" int(11) DEFAULT NULL,
906 "rec.var" int(11) DEFAULT NULL
907 )
908 DROP TABLE t1;
909 DROP PROCEDURE p1;
910 CREATE PROCEDURE p1() AS var INT(9); rec ROW(var INT(9)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
911 CALL p1();
912 SHOW CREATE TABLE t1;
913 Table Create Table
914 t1 CREATE TABLE "t1" (
915 "var" int(11) DEFAULT NULL,
916 "rec.var" int(11) DEFAULT NULL
917 )
918 DROP TABLE t1;
919 DROP PROCEDURE p1;
920 CREATE PROCEDURE p1() AS var INT(10); rec ROW(var INT(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
921 CALL p1();
922 SHOW CREATE TABLE t1;
923 Table Create Table
924 t1 CREATE TABLE "t1" (
925 "var" int(11) DEFAULT NULL,
926 "rec.var" int(11) DEFAULT NULL
927 )
928 DROP TABLE t1;
929 DROP PROCEDURE p1;
930 CREATE PROCEDURE p1() AS var INT(11); rec ROW(var INT(11)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
931 CALL p1();
932 SHOW CREATE TABLE t1;
933 Table Create Table
934 t1 CREATE TABLE "t1" (
935 "var" int(11) DEFAULT NULL,
936 "rec.var" int(11) DEFAULT NULL
937 )
938 DROP TABLE t1;
939 DROP PROCEDURE p1;
940 CREATE PROCEDURE p1() AS var INT(12); rec ROW(var INT(12)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
941 CALL p1();
942 SHOW CREATE TABLE t1;
943 Table Create Table
944 t1 CREATE TABLE "t1" (
945 "var" int(12) DEFAULT NULL,
946 "rec.var" int(12) DEFAULT NULL
947 )
948 DROP TABLE t1;
949 DROP PROCEDURE p1;
950 CREATE PROCEDURE p1() AS var INT(13); rec ROW(var INT(13)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
951 CALL p1();
952 SHOW CREATE TABLE t1;
953 Table Create Table
954 t1 CREATE TABLE "t1" (
955 "var" int(13) DEFAULT NULL,
956 "rec.var" int(13) DEFAULT NULL
957 )
958 DROP TABLE t1;
959 DROP PROCEDURE p1;
960 CREATE PROCEDURE p1() AS var INT(14); rec ROW(var INT(14)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
961 CALL p1();
962 SHOW CREATE TABLE t1;
963 Table Create Table
964 t1 CREATE TABLE "t1" (
965 "var" int(14) DEFAULT NULL,
966 "rec.var" int(14) DEFAULT NULL
967 )
968 DROP TABLE t1;
969 DROP PROCEDURE p1;
970 CREATE PROCEDURE p1() AS var INT(20); rec ROW(var INT(20)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
971 CALL p1();
972 SHOW CREATE TABLE t1;
973 Table Create Table
974 t1 CREATE TABLE "t1" (
975 "var" int(20) DEFAULT NULL,
976 "rec.var" int(20) DEFAULT NULL
977 )
978 DROP TABLE t1;
979 DROP PROCEDURE p1;
980 CREATE PROCEDURE p1() AS var INT(21); rec ROW(var INT(21)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
981 CALL p1();
982 SHOW CREATE TABLE t1;
983 Table Create Table
984 t1 CREATE TABLE "t1" (
985 "var" int(21) DEFAULT NULL,
986 "rec.var" int(21) DEFAULT NULL
987 )
988 DROP TABLE t1;
989 DROP PROCEDURE p1;
990 #
991 # TINYINT
992 #
993 CREATE PROCEDURE p1() AS var TINYINT; rec ROW(var TINYINT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
994 CALL p1();
995 SHOW CREATE TABLE t1;
996 Table Create Table
997 t1 CREATE TABLE "t1" (
998 "var" tinyint(4) DEFAULT NULL,
999 "rec.var" tinyint(4) DEFAULT NULL
1000 )
1001 DROP TABLE t1;
1002 DROP PROCEDURE p1;
1003 CREATE PROCEDURE p1() AS var TINYINT(1); rec ROW(var TINYINT(1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1004 CALL p1();
1005 SHOW CREATE TABLE t1;
1006 Table Create Table
1007 t1 CREATE TABLE "t1" (
1008 "var" tinyint(4) DEFAULT NULL,
1009 "rec.var" tinyint(4) DEFAULT NULL
1010 )
1011 DROP TABLE t1;
1012 DROP PROCEDURE p1;
1013 CREATE PROCEDURE p1() AS var TINYINT(2); rec ROW(var TINYINT(2)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1014 CALL p1();
1015 SHOW CREATE TABLE t1;
1016 Table Create Table
1017 t1 CREATE TABLE "t1" (
1018 "var" tinyint(4) DEFAULT NULL,
1019 "rec.var" tinyint(4) DEFAULT NULL
1020 )
1021 DROP TABLE t1;
1022 DROP PROCEDURE p1;
1023 CREATE PROCEDURE p1() AS var TINYINT(3); rec ROW(var TINYINT(3)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1024 CALL p1();
1025 SHOW CREATE TABLE t1;
1026 Table Create Table
1027 t1 CREATE TABLE "t1" (
1028 "var" tinyint(4) DEFAULT NULL,
1029 "rec.var" tinyint(4) DEFAULT NULL
1030 )
1031 DROP TABLE t1;
1032 DROP PROCEDURE p1;
1033 CREATE PROCEDURE p1() AS var TINYINT(4); rec ROW(var TINYINT(4)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1034 CALL p1();
1035 SHOW CREATE TABLE t1;
1036 Table Create Table
1037 t1 CREATE TABLE "t1" (
1038 "var" tinyint(4) DEFAULT NULL,
1039 "rec.var" tinyint(4) DEFAULT NULL
1040 )
1041 DROP TABLE t1;
1042 DROP PROCEDURE p1;
1043 CREATE PROCEDURE p1() AS var TINYINT(5); rec ROW(var TINYINT(5)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1044 CALL p1();
1045 SHOW CREATE TABLE t1;
1046 Table Create Table
1047 t1 CREATE TABLE "t1" (
1048 "var" tinyint(5) DEFAULT NULL,
1049 "rec.var" tinyint(5) DEFAULT NULL
1050 )
1051 DROP TABLE t1;
1052 DROP PROCEDURE p1;
1053 CREATE PROCEDURE p1() AS var TINYINT(6); rec ROW(var TINYINT(6)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1054 CALL p1();
1055 SHOW CREATE TABLE t1;
1056 Table Create Table
1057 t1 CREATE TABLE "t1" (
1058 "var" tinyint(6) DEFAULT NULL,
1059 "rec.var" tinyint(6) DEFAULT NULL
1060 )
1061 DROP TABLE t1;
1062 DROP PROCEDURE p1;
1063 CREATE PROCEDURE p1() AS var TINYINT(7); rec ROW(var TINYINT(7)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1064 CALL p1();
1065 SHOW CREATE TABLE t1;
1066 Table Create Table
1067 t1 CREATE TABLE "t1" (
1068 "var" tinyint(7) DEFAULT NULL,
1069 "rec.var" tinyint(7) DEFAULT NULL
1070 )
1071 DROP TABLE t1;
1072 DROP PROCEDURE p1;
1073 CREATE PROCEDURE p1() AS var TINYINT(8); rec ROW(var TINYINT(8)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1074 CALL p1();
1075 SHOW CREATE TABLE t1;
1076 Table Create Table
1077 t1 CREATE TABLE "t1" (
1078 "var" tinyint(8) DEFAULT NULL,
1079 "rec.var" tinyint(8) DEFAULT NULL
1080 )
1081 DROP TABLE t1;
1082 DROP PROCEDURE p1;
1083 CREATE PROCEDURE p1() AS var TINYINT(9); rec ROW(var TINYINT(9)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1084 CALL p1();
1085 SHOW CREATE TABLE t1;
1086 Table Create Table
1087 t1 CREATE TABLE "t1" (
1088 "var" tinyint(9) DEFAULT NULL,
1089 "rec.var" tinyint(9) DEFAULT NULL
1090 )
1091 DROP TABLE t1;
1092 DROP PROCEDURE p1;
1093 CREATE PROCEDURE p1() AS var TINYINT(10); rec ROW(var TINYINT(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1094 CALL p1();
1095 SHOW CREATE TABLE t1;
1096 Table Create Table
1097 t1 CREATE TABLE "t1" (
1098 "var" tinyint(10) DEFAULT NULL,
1099 "rec.var" tinyint(10) DEFAULT NULL
1100 )
1101 DROP TABLE t1;
1102 DROP PROCEDURE p1;
1103 CREATE PROCEDURE p1() AS var TINYINT(11); rec ROW(var TINYINT(11)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1104 CALL p1();
1105 SHOW CREATE TABLE t1;
1106 Table Create Table
1107 t1 CREATE TABLE "t1" (
1108 "var" tinyint(11) DEFAULT NULL,
1109 "rec.var" tinyint(11) DEFAULT NULL
1110 )
1111 DROP TABLE t1;
1112 DROP PROCEDURE p1;
1113 CREATE PROCEDURE p1() AS var TINYINT(12); rec ROW(var TINYINT(12)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1114 CALL p1();
1115 SHOW CREATE TABLE t1;
1116 Table Create Table
1117 t1 CREATE TABLE "t1" (
1118 "var" tinyint(12) DEFAULT NULL,
1119 "rec.var" tinyint(12) DEFAULT NULL
1120 )
1121 DROP TABLE t1;
1122 DROP PROCEDURE p1;
1123 CREATE PROCEDURE p1() AS var TINYINT(13); rec ROW(var TINYINT(13)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1124 CALL p1();
1125 SHOW CREATE TABLE t1;
1126 Table Create Table
1127 t1 CREATE TABLE "t1" (
1128 "var" tinyint(13) DEFAULT NULL,
1129 "rec.var" tinyint(13) DEFAULT NULL
1130 )
1131 DROP TABLE t1;
1132 DROP PROCEDURE p1;
1133 CREATE PROCEDURE p1() AS var TINYINT(14); rec ROW(var TINYINT(14)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1134 CALL p1();
1135 SHOW CREATE TABLE t1;
1136 Table Create Table
1137 t1 CREATE TABLE "t1" (
1138 "var" tinyint(14) DEFAULT NULL,
1139 "rec.var" tinyint(14) DEFAULT NULL
1140 )
1141 DROP TABLE t1;
1142 DROP PROCEDURE p1;
1143 CREATE PROCEDURE p1() AS var TINYINT(20); rec ROW(var TINYINT(20)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1144 CALL p1();
1145 SHOW CREATE TABLE t1;
1146 Table Create Table
1147 t1 CREATE TABLE "t1" (
1148 "var" tinyint(20) DEFAULT NULL,
1149 "rec.var" tinyint(20) DEFAULT NULL
1150 )
1151 DROP TABLE t1;
1152 DROP PROCEDURE p1;
1153 CREATE PROCEDURE p1() AS var TINYINT(21); rec ROW(var TINYINT(21)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1154 CALL p1();
1155 SHOW CREATE TABLE t1;
1156 Table Create Table
1157 t1 CREATE TABLE "t1" (
1158 "var" tinyint(21) DEFAULT NULL,
1159 "rec.var" tinyint(21) DEFAULT NULL
1160 )
1161 DROP TABLE t1;
1162 DROP PROCEDURE p1;
1163 #
1164 # SMALLINT
1165 #
1166 CREATE PROCEDURE p1() AS var SMALLINT; rec ROW(var SMALLINT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1167 CALL p1();
1168 SHOW CREATE TABLE t1;
1169 Table Create Table
1170 t1 CREATE TABLE "t1" (
1171 "var" smallint(6) DEFAULT NULL,
1172 "rec.var" smallint(6) DEFAULT NULL
1173 )
1174 DROP TABLE t1;
1175 DROP PROCEDURE p1;
1176 CREATE PROCEDURE p1() AS var SMALLINT(1); rec ROW(var SMALLINT(1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1177 CALL p1();
1178 SHOW CREATE TABLE t1;
1179 Table Create Table
1180 t1 CREATE TABLE "t1" (
1181 "var" smallint(6) DEFAULT NULL,
1182 "rec.var" smallint(6) DEFAULT NULL
1183 )
1184 DROP TABLE t1;
1185 DROP PROCEDURE p1;
1186 CREATE PROCEDURE p1() AS var SMALLINT(2); rec ROW(var SMALLINT(2)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1187 CALL p1();
1188 SHOW CREATE TABLE t1;
1189 Table Create Table
1190 t1 CREATE TABLE "t1" (
1191 "var" smallint(6) DEFAULT NULL,
1192 "rec.var" smallint(6) DEFAULT NULL
1193 )
1194 DROP TABLE t1;
1195 DROP PROCEDURE p1;
1196 CREATE PROCEDURE p1() AS var SMALLINT(3); rec ROW(var SMALLINT(3)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1197 CALL p1();
1198 SHOW CREATE TABLE t1;
1199 Table Create Table
1200 t1 CREATE TABLE "t1" (
1201 "var" smallint(6) DEFAULT NULL,
1202 "rec.var" smallint(6) DEFAULT NULL
1203 )
1204 DROP TABLE t1;
1205 DROP PROCEDURE p1;
1206 CREATE PROCEDURE p1() AS var SMALLINT(4); rec ROW(var SMALLINT(4)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1207 CALL p1();
1208 SHOW CREATE TABLE t1;
1209 Table Create Table
1210 t1 CREATE TABLE "t1" (
1211 "var" smallint(6) DEFAULT NULL,
1212 "rec.var" smallint(6) DEFAULT NULL
1213 )
1214 DROP TABLE t1;
1215 DROP PROCEDURE p1;
1216 CREATE PROCEDURE p1() AS var SMALLINT(5); rec ROW(var SMALLINT(5)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1217 CALL p1();
1218 SHOW CREATE TABLE t1;
1219 Table Create Table
1220 t1 CREATE TABLE "t1" (
1221 "var" smallint(6) DEFAULT NULL,
1222 "rec.var" smallint(6) DEFAULT NULL
1223 )
1224 DROP TABLE t1;
1225 DROP PROCEDURE p1;
1226 CREATE PROCEDURE p1() AS var SMALLINT(6); rec ROW(var SMALLINT(6)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1227 CALL p1();
1228 SHOW CREATE TABLE t1;
1229 Table Create Table
1230 t1 CREATE TABLE "t1" (
1231 "var" smallint(6) DEFAULT NULL,
1232 "rec.var" smallint(6) DEFAULT NULL
1233 )
1234 DROP TABLE t1;
1235 DROP PROCEDURE p1;
1236 CREATE PROCEDURE p1() AS var SMALLINT(7); rec ROW(var SMALLINT(7)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1237 CALL p1();
1238 SHOW CREATE TABLE t1;
1239 Table Create Table
1240 t1 CREATE TABLE "t1" (
1241 "var" smallint(7) DEFAULT NULL,
1242 "rec.var" smallint(7) DEFAULT NULL
1243 )
1244 DROP TABLE t1;
1245 DROP PROCEDURE p1;
1246 CREATE PROCEDURE p1() AS var SMALLINT(8); rec ROW(var SMALLINT(8)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1247 CALL p1();
1248 SHOW CREATE TABLE t1;
1249 Table Create Table
1250 t1 CREATE TABLE "t1" (
1251 "var" smallint(8) DEFAULT NULL,
1252 "rec.var" smallint(8) DEFAULT NULL
1253 )
1254 DROP TABLE t1;
1255 DROP PROCEDURE p1;
1256 CREATE PROCEDURE p1() AS var SMALLINT(9); rec ROW(var SMALLINT(9)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1257 CALL p1();
1258 SHOW CREATE TABLE t1;
1259 Table Create Table
1260 t1 CREATE TABLE "t1" (
1261 "var" smallint(9) DEFAULT NULL,
1262 "rec.var" smallint(9) DEFAULT NULL
1263 )
1264 DROP TABLE t1;
1265 DROP PROCEDURE p1;
1266 CREATE PROCEDURE p1() AS var SMALLINT(10); rec ROW(var SMALLINT(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1267 CALL p1();
1268 SHOW CREATE TABLE t1;
1269 Table Create Table
1270 t1 CREATE TABLE "t1" (
1271 "var" smallint(10) DEFAULT NULL,
1272 "rec.var" smallint(10) DEFAULT NULL
1273 )
1274 DROP TABLE t1;
1275 DROP PROCEDURE p1;
1276 CREATE PROCEDURE p1() AS var SMALLINT(11); rec ROW(var SMALLINT(11)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1277 CALL p1();
1278 SHOW CREATE TABLE t1;
1279 Table Create Table
1280 t1 CREATE TABLE "t1" (
1281 "var" smallint(11) DEFAULT NULL,
1282 "rec.var" smallint(11) DEFAULT NULL
1283 )
1284 DROP TABLE t1;
1285 DROP PROCEDURE p1;
1286 CREATE PROCEDURE p1() AS var SMALLINT(12); rec ROW(var SMALLINT(12)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1287 CALL p1();
1288 SHOW CREATE TABLE t1;
1289 Table Create Table
1290 t1 CREATE TABLE "t1" (
1291 "var" smallint(12) DEFAULT NULL,
1292 "rec.var" smallint(12) DEFAULT NULL
1293 )
1294 DROP TABLE t1;
1295 DROP PROCEDURE p1;
1296 CREATE PROCEDURE p1() AS var SMALLINT(13); rec ROW(var SMALLINT(13)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1297 CALL p1();
1298 SHOW CREATE TABLE t1;
1299 Table Create Table
1300 t1 CREATE TABLE "t1" (
1301 "var" smallint(13) DEFAULT NULL,
1302 "rec.var" smallint(13) DEFAULT NULL
1303 )
1304 DROP TABLE t1;
1305 DROP PROCEDURE p1;
1306 CREATE PROCEDURE p1() AS var SMALLINT(14); rec ROW(var SMALLINT(14)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1307 CALL p1();
1308 SHOW CREATE TABLE t1;
1309 Table Create Table
1310 t1 CREATE TABLE "t1" (
1311 "var" smallint(14) DEFAULT NULL,
1312 "rec.var" smallint(14) DEFAULT NULL
1313 )
1314 DROP TABLE t1;
1315 DROP PROCEDURE p1;
1316 CREATE PROCEDURE p1() AS var SMALLINT(20); rec ROW(var SMALLINT(20)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1317 CALL p1();
1318 SHOW CREATE TABLE t1;
1319 Table Create Table
1320 t1 CREATE TABLE "t1" (
1321 "var" smallint(20) DEFAULT NULL,
1322 "rec.var" smallint(20) DEFAULT NULL
1323 )
1324 DROP TABLE t1;
1325 DROP PROCEDURE p1;
1326 CREATE PROCEDURE p1() AS var SMALLINT(21); rec ROW(var SMALLINT(21)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1327 CALL p1();
1328 SHOW CREATE TABLE t1;
1329 Table Create Table
1330 t1 CREATE TABLE "t1" (
1331 "var" smallint(21) DEFAULT NULL,
1332 "rec.var" smallint(21) DEFAULT NULL
1333 )
1334 DROP TABLE t1;
1335 DROP PROCEDURE p1;
1336 #
1337 # MEDIUMINT
1338 #
1339 CREATE PROCEDURE p1() AS var MEDIUMINT; rec ROW(var MEDIUMINT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1340 CALL p1();
1341 SHOW CREATE TABLE t1;
1342 Table Create Table
1343 t1 CREATE TABLE "t1" (
1344 "var" mediumint(9) DEFAULT NULL,
1345 "rec.var" mediumint(9) DEFAULT NULL
1346 )
1347 DROP TABLE t1;
1348 DROP PROCEDURE p1;
1349 CREATE PROCEDURE p1() AS var MEDIUMINT(1); rec ROW(var MEDIUMINT(1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1350 CALL p1();
1351 SHOW CREATE TABLE t1;
1352 Table Create Table
1353 t1 CREATE TABLE "t1" (
1354 "var" mediumint(8) DEFAULT NULL,
1355 "rec.var" mediumint(8) DEFAULT NULL
1356 )
1357 DROP TABLE t1;
1358 DROP PROCEDURE p1;
1359 CREATE PROCEDURE p1() AS var MEDIUMINT(2); rec ROW(var MEDIUMINT(2)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1360 CALL p1();
1361 SHOW CREATE TABLE t1;
1362 Table Create Table
1363 t1 CREATE TABLE "t1" (
1364 "var" mediumint(8) DEFAULT NULL,
1365 "rec.var" mediumint(8) DEFAULT NULL
1366 )
1367 DROP TABLE t1;
1368 DROP PROCEDURE p1;
1369 CREATE PROCEDURE p1() AS var MEDIUMINT(3); rec ROW(var MEDIUMINT(3)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1370 CALL p1();
1371 SHOW CREATE TABLE t1;
1372 Table Create Table
1373 t1 CREATE TABLE "t1" (
1374 "var" mediumint(8) DEFAULT NULL,
1375 "rec.var" mediumint(8) DEFAULT NULL
1376 )
1377 DROP TABLE t1;
1378 DROP PROCEDURE p1;
1379 CREATE PROCEDURE p1() AS var MEDIUMINT(4); rec ROW(var MEDIUMINT(4)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1380 CALL p1();
1381 SHOW CREATE TABLE t1;
1382 Table Create Table
1383 t1 CREATE TABLE "t1" (
1384 "var" mediumint(8) DEFAULT NULL,
1385 "rec.var" mediumint(8) DEFAULT NULL
1386 )
1387 DROP TABLE t1;
1388 DROP PROCEDURE p1;
1389 CREATE PROCEDURE p1() AS var MEDIUMINT(5); rec ROW(var MEDIUMINT(5)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1390 CALL p1();
1391 SHOW CREATE TABLE t1;
1392 Table Create Table
1393 t1 CREATE TABLE "t1" (
1394 "var" mediumint(8) DEFAULT NULL,
1395 "rec.var" mediumint(8) DEFAULT NULL
1396 )
1397 DROP TABLE t1;
1398 DROP PROCEDURE p1;
1399 CREATE PROCEDURE p1() AS var MEDIUMINT(6); rec ROW(var MEDIUMINT(6)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1400 CALL p1();
1401 SHOW CREATE TABLE t1;
1402 Table Create Table
1403 t1 CREATE TABLE "t1" (
1404 "var" mediumint(8) DEFAULT NULL,
1405 "rec.var" mediumint(8) DEFAULT NULL
1406 )
1407 DROP TABLE t1;
1408 DROP PROCEDURE p1;
1409 CREATE PROCEDURE p1() AS var MEDIUMINT(7); rec ROW(var MEDIUMINT(7)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1410 CALL p1();
1411 SHOW CREATE TABLE t1;
1412 Table Create Table
1413 t1 CREATE TABLE "t1" (
1414 "var" mediumint(8) DEFAULT NULL,
1415 "rec.var" mediumint(8) DEFAULT NULL
1416 )
1417 DROP TABLE t1;
1418 DROP PROCEDURE p1;
1419 CREATE PROCEDURE p1() AS var MEDIUMINT(8); rec ROW(var MEDIUMINT(8)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1420 CALL p1();
1421 SHOW CREATE TABLE t1;
1422 Table Create Table
1423 t1 CREATE TABLE "t1" (
1424 "var" mediumint(8) DEFAULT NULL,
1425 "rec.var" mediumint(8) DEFAULT NULL
1426 )
1427 DROP TABLE t1;
1428 DROP PROCEDURE p1;
1429 CREATE PROCEDURE p1() AS var MEDIUMINT(9); rec ROW(var MEDIUMINT(9)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1430 CALL p1();
1431 SHOW CREATE TABLE t1;
1432 Table Create Table
1433 t1 CREATE TABLE "t1" (
1434 "var" mediumint(9) DEFAULT NULL,
1435 "rec.var" mediumint(9) DEFAULT NULL
1436 )
1437 DROP TABLE t1;
1438 DROP PROCEDURE p1;
1439 CREATE PROCEDURE p1() AS var MEDIUMINT(10); rec ROW(var MEDIUMINT(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1440 CALL p1();
1441 SHOW CREATE TABLE t1;
1442 Table Create Table
1443 t1 CREATE TABLE "t1" (
1444 "var" mediumint(10) DEFAULT NULL,
1445 "rec.var" mediumint(10) DEFAULT NULL
1446 )
1447 DROP TABLE t1;
1448 DROP PROCEDURE p1;
1449 CREATE PROCEDURE p1() AS var MEDIUMINT(11); rec ROW(var MEDIUMINT(11)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1450 CALL p1();
1451 SHOW CREATE TABLE t1;
1452 Table Create Table
1453 t1 CREATE TABLE "t1" (
1454 "var" mediumint(11) DEFAULT NULL,
1455 "rec.var" mediumint(11) DEFAULT NULL
1456 )
1457 DROP TABLE t1;
1458 DROP PROCEDURE p1;
1459 CREATE PROCEDURE p1() AS var MEDIUMINT(12); rec ROW(var MEDIUMINT(12)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1460 CALL p1();
1461 SHOW CREATE TABLE t1;
1462 Table Create Table
1463 t1 CREATE TABLE "t1" (
1464 "var" mediumint(12) DEFAULT NULL,
1465 "rec.var" mediumint(12) DEFAULT NULL
1466 )
1467 DROP TABLE t1;
1468 DROP PROCEDURE p1;
1469 CREATE PROCEDURE p1() AS var MEDIUMINT(13); rec ROW(var MEDIUMINT(13)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1470 CALL p1();
1471 SHOW CREATE TABLE t1;
1472 Table Create Table
1473 t1 CREATE TABLE "t1" (
1474 "var" mediumint(13) DEFAULT NULL,
1475 "rec.var" mediumint(13) DEFAULT NULL
1476 )
1477 DROP TABLE t1;
1478 DROP PROCEDURE p1;
1479 CREATE PROCEDURE p1() AS var MEDIUMINT(14); rec ROW(var MEDIUMINT(14)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1480 CALL p1();
1481 SHOW CREATE TABLE t1;
1482 Table Create Table
1483 t1 CREATE TABLE "t1" (
1484 "var" mediumint(14) DEFAULT NULL,
1485 "rec.var" mediumint(14) DEFAULT NULL
1486 )
1487 DROP TABLE t1;
1488 DROP PROCEDURE p1;
1489 CREATE PROCEDURE p1() AS var MEDIUMINT(20); rec ROW(var MEDIUMINT(20)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1490 CALL p1();
1491 SHOW CREATE TABLE t1;
1492 Table Create Table
1493 t1 CREATE TABLE "t1" (
1494 "var" mediumint(20) DEFAULT NULL,
1495 "rec.var" mediumint(20) DEFAULT NULL
1496 )
1497 DROP TABLE t1;
1498 DROP PROCEDURE p1;
1499 CREATE PROCEDURE p1() AS var MEDIUMINT(21); rec ROW(var MEDIUMINT(21)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1500 CALL p1();
1501 SHOW CREATE TABLE t1;
1502 Table Create Table
1503 t1 CREATE TABLE "t1" (
1504 "var" mediumint(21) DEFAULT NULL,
1505 "rec.var" mediumint(21) DEFAULT NULL
1506 )
1507 DROP TABLE t1;
1508 DROP PROCEDURE p1;
1509 #
1510 # BIGINT
1511 #
1512 CREATE PROCEDURE p1() AS var BIGINT; rec ROW(var BIGINT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1513 CALL p1();
1514 SHOW CREATE TABLE t1;
1515 Table Create Table
1516 t1 CREATE TABLE "t1" (
1517 "var" bigint(20) DEFAULT NULL,
1518 "rec.var" bigint(20) DEFAULT NULL
1519 )
1520 DROP TABLE t1;
1521 DROP PROCEDURE p1;
1522 CREATE PROCEDURE p1() AS var BIGINT(1); rec ROW(var BIGINT(1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1523 CALL p1();
1524 SHOW CREATE TABLE t1;
1525 Table Create Table
1526 t1 CREATE TABLE "t1" (
1527 "var" bigint(20) DEFAULT NULL,
1528 "rec.var" bigint(20) DEFAULT NULL
1529 )
1530 DROP TABLE t1;
1531 DROP PROCEDURE p1;
1532 CREATE PROCEDURE p1() AS var BIGINT(2); rec ROW(var BIGINT(2)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1533 CALL p1();
1534 SHOW CREATE TABLE t1;
1535 Table Create Table
1536 t1 CREATE TABLE "t1" (
1537 "var" bigint(20) DEFAULT NULL,
1538 "rec.var" bigint(20) DEFAULT NULL
1539 )
1540 DROP TABLE t1;
1541 DROP PROCEDURE p1;
1542 CREATE PROCEDURE p1() AS var BIGINT(3); rec ROW(var BIGINT(3)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1543 CALL p1();
1544 SHOW CREATE TABLE t1;
1545 Table Create Table
1546 t1 CREATE TABLE "t1" (
1547 "var" bigint(20) DEFAULT NULL,
1548 "rec.var" bigint(20) DEFAULT NULL
1549 )
1550 DROP TABLE t1;
1551 DROP PROCEDURE p1;
1552 CREATE PROCEDURE p1() AS var BIGINT(4); rec ROW(var BIGINT(4)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1553 CALL p1();
1554 SHOW CREATE TABLE t1;
1555 Table Create Table
1556 t1 CREATE TABLE "t1" (
1557 "var" bigint(20) DEFAULT NULL,
1558 "rec.var" bigint(20) DEFAULT NULL
1559 )
1560 DROP TABLE t1;
1561 DROP PROCEDURE p1;
1562 CREATE PROCEDURE p1() AS var BIGINT(5); rec ROW(var BIGINT(5)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1563 CALL p1();
1564 SHOW CREATE TABLE t1;
1565 Table Create Table
1566 t1 CREATE TABLE "t1" (
1567 "var" bigint(20) DEFAULT NULL,
1568 "rec.var" bigint(20) DEFAULT NULL
1569 )
1570 DROP TABLE t1;
1571 DROP PROCEDURE p1;
1572 CREATE PROCEDURE p1() AS var BIGINT(6); rec ROW(var BIGINT(6)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1573 CALL p1();
1574 SHOW CREATE TABLE t1;
1575 Table Create Table
1576 t1 CREATE TABLE "t1" (
1577 "var" bigint(20) DEFAULT NULL,
1578 "rec.var" bigint(20) DEFAULT NULL
1579 )
1580 DROP TABLE t1;
1581 DROP PROCEDURE p1;
1582 CREATE PROCEDURE p1() AS var BIGINT(7); rec ROW(var BIGINT(7)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1583 CALL p1();
1584 SHOW CREATE TABLE t1;
1585 Table Create Table
1586 t1 CREATE TABLE "t1" (
1587 "var" bigint(20) DEFAULT NULL,
1588 "rec.var" bigint(20) DEFAULT NULL
1589 )
1590 DROP TABLE t1;
1591 DROP PROCEDURE p1;
1592 CREATE PROCEDURE p1() AS var BIGINT(8); rec ROW(var BIGINT(8)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1593 CALL p1();
1594 SHOW CREATE TABLE t1;
1595 Table Create Table
1596 t1 CREATE TABLE "t1" (
1597 "var" bigint(20) DEFAULT NULL,
1598 "rec.var" bigint(20) DEFAULT NULL
1599 )
1600 DROP TABLE t1;
1601 DROP PROCEDURE p1;
1602 CREATE PROCEDURE p1() AS var BIGINT(9); rec ROW(var BIGINT(9)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1603 CALL p1();
1604 SHOW CREATE TABLE t1;
1605 Table Create Table
1606 t1 CREATE TABLE "t1" (
1607 "var" bigint(20) DEFAULT NULL,
1608 "rec.var" bigint(20) DEFAULT NULL
1609 )
1610 DROP TABLE t1;
1611 DROP PROCEDURE p1;
1612 CREATE PROCEDURE p1() AS var BIGINT(10); rec ROW(var BIGINT(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1613 CALL p1();
1614 SHOW CREATE TABLE t1;
1615 Table Create Table
1616 t1 CREATE TABLE "t1" (
1617 "var" bigint(20) DEFAULT NULL,
1618 "rec.var" bigint(20) DEFAULT NULL
1619 )
1620 DROP TABLE t1;
1621 DROP PROCEDURE p1;
1622 CREATE PROCEDURE p1() AS var BIGINT(11); rec ROW(var BIGINT(11)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1623 CALL p1();
1624 SHOW CREATE TABLE t1;
1625 Table Create Table
1626 t1 CREATE TABLE "t1" (
1627 "var" bigint(20) DEFAULT NULL,
1628 "rec.var" bigint(20) DEFAULT NULL
1629 )
1630 DROP TABLE t1;
1631 DROP PROCEDURE p1;
1632 CREATE PROCEDURE p1() AS var BIGINT(12); rec ROW(var BIGINT(12)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1633 CALL p1();
1634 SHOW CREATE TABLE t1;
1635 Table Create Table
1636 t1 CREATE TABLE "t1" (
1637 "var" bigint(20) DEFAULT NULL,
1638 "rec.var" bigint(20) DEFAULT NULL
1639 )
1640 DROP TABLE t1;
1641 DROP PROCEDURE p1;
1642 CREATE PROCEDURE p1() AS var BIGINT(13); rec ROW(var BIGINT(13)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1643 CALL p1();
1644 SHOW CREATE TABLE t1;
1645 Table Create Table
1646 t1 CREATE TABLE "t1" (
1647 "var" bigint(20) DEFAULT NULL,
1648 "rec.var" bigint(20) DEFAULT NULL
1649 )
1650 DROP TABLE t1;
1651 DROP PROCEDURE p1;
1652 CREATE PROCEDURE p1() AS var BIGINT(14); rec ROW(var BIGINT(14)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1653 CALL p1();
1654 SHOW CREATE TABLE t1;
1655 Table Create Table
1656 t1 CREATE TABLE "t1" (
1657 "var" bigint(20) DEFAULT NULL,
1658 "rec.var" bigint(20) DEFAULT NULL
1659 )
1660 DROP TABLE t1;
1661 DROP PROCEDURE p1;
1662 CREATE PROCEDURE p1() AS var BIGINT(20); rec ROW(var BIGINT(20)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1663 CALL p1();
1664 SHOW CREATE TABLE t1;
1665 Table Create Table
1666 t1 CREATE TABLE "t1" (
1667 "var" bigint(20) DEFAULT NULL,
1668 "rec.var" bigint(20) DEFAULT NULL
1669 )
1670 DROP TABLE t1;
1671 DROP PROCEDURE p1;
1672 CREATE PROCEDURE p1() AS var BIGINT(21); rec ROW(var BIGINT(21)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1673 CALL p1();
1674 SHOW CREATE TABLE t1;
1675 Table Create Table
1676 t1 CREATE TABLE "t1" (
1677 "var" bigint(21) DEFAULT NULL,
1678 "rec.var" bigint(21) DEFAULT NULL
1679 )
1680 DROP TABLE t1;
1681 DROP PROCEDURE p1;
1682 #
1683 # DOUBLE
1684 #
1685 CREATE PROCEDURE p1() AS var DOUBLE; rec ROW(var DOUBLE); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1686 CALL p1();
1687 SHOW CREATE TABLE t1;
1688 Table Create Table
1689 t1 CREATE TABLE "t1" (
1690 "var" double DEFAULT NULL,
1691 "rec.var" double DEFAULT NULL
1692 )
1693 DROP TABLE t1;
1694 DROP PROCEDURE p1;
1695 CREATE PROCEDURE p1() AS var DOUBLE(30,1); rec ROW(var DOUBLE(30,1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1696 CALL p1();
1697 SHOW CREATE TABLE t1;
1698 Table Create Table
1699 t1 CREATE TABLE "t1" (
1700 "var" double(30,1) DEFAULT NULL,
1701 "rec.var" double(30,1) DEFAULT NULL
1702 )
1703 DROP TABLE t1;
1704 DROP PROCEDURE p1;
1705 CREATE PROCEDURE p1() AS var DOUBLE(30,2); rec ROW(var DOUBLE(30,2)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1706 CALL p1();
1707 SHOW CREATE TABLE t1;
1708 Table Create Table
1709 t1 CREATE TABLE "t1" (
1710 "var" double(30,2) DEFAULT NULL,
1711 "rec.var" double(30,2) DEFAULT NULL
1712 )
1713 DROP TABLE t1;
1714 DROP PROCEDURE p1;
1715 CREATE PROCEDURE p1() AS var DOUBLE(30,3); rec ROW(var DOUBLE(30,3)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1716 CALL p1();
1717 SHOW CREATE TABLE t1;
1718 Table Create Table
1719 t1 CREATE TABLE "t1" (
1720 "var" double(30,3) DEFAULT NULL,
1721 "rec.var" double(30,3) DEFAULT NULL
1722 )
1723 DROP TABLE t1;
1724 DROP PROCEDURE p1;
1725 CREATE PROCEDURE p1() AS var DOUBLE(30,4); rec ROW(var DOUBLE(30,4)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1726 CALL p1();
1727 SHOW CREATE TABLE t1;
1728 Table Create Table
1729 t1 CREATE TABLE "t1" (
1730 "var" double(30,4) DEFAULT NULL,
1731 "rec.var" double(30,4) DEFAULT NULL
1732 )
1733 DROP TABLE t1;
1734 DROP PROCEDURE p1;
1735 CREATE PROCEDURE p1() AS var DOUBLE(30,5); rec ROW(var DOUBLE(30,5)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1736 CALL p1();
1737 SHOW CREATE TABLE t1;
1738 Table Create Table
1739 t1 CREATE TABLE "t1" (
1740 "var" double(30,5) DEFAULT NULL,
1741 "rec.var" double(30,5) DEFAULT NULL
1742 )
1743 DROP TABLE t1;
1744 DROP PROCEDURE p1;
1745 CREATE PROCEDURE p1() AS var DOUBLE(30,6); rec ROW(var DOUBLE(30,6)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1746 CALL p1();
1747 SHOW CREATE TABLE t1;
1748 Table Create Table
1749 t1 CREATE TABLE "t1" (
1750 "var" double(30,6) DEFAULT NULL,
1751 "rec.var" double(30,6) DEFAULT NULL
1752 )
1753 DROP TABLE t1;
1754 DROP PROCEDURE p1;
1755 CREATE PROCEDURE p1() AS var DOUBLE(30,7); rec ROW(var DOUBLE(30,7)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1756 CALL p1();
1757 SHOW CREATE TABLE t1;
1758 Table Create Table
1759 t1 CREATE TABLE "t1" (
1760 "var" double(30,7) DEFAULT NULL,
1761 "rec.var" double(30,7) DEFAULT NULL
1762 )
1763 DROP TABLE t1;
1764 DROP PROCEDURE p1;
1765 CREATE PROCEDURE p1() AS var DOUBLE(30,8); rec ROW(var DOUBLE(30,8)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1766 CALL p1();
1767 SHOW CREATE TABLE t1;
1768 Table Create Table
1769 t1 CREATE TABLE "t1" (
1770 "var" double(30,8) DEFAULT NULL,
1771 "rec.var" double(30,8) DEFAULT NULL
1772 )
1773 DROP TABLE t1;
1774 DROP PROCEDURE p1;
1775 CREATE PROCEDURE p1() AS var DOUBLE(30,9); rec ROW(var DOUBLE(30,9)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1776 CALL p1();
1777 SHOW CREATE TABLE t1;
1778 Table Create Table
1779 t1 CREATE TABLE "t1" (
1780 "var" double(30,9) DEFAULT NULL,
1781 "rec.var" double(30,9) DEFAULT NULL
1782 )
1783 DROP TABLE t1;
1784 DROP PROCEDURE p1;
1785 CREATE PROCEDURE p1() AS var DOUBLE(30,10); rec ROW(var DOUBLE(30,10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1786 CALL p1();
1787 SHOW CREATE TABLE t1;
1788 Table Create Table
1789 t1 CREATE TABLE "t1" (
1790 "var" double(30,10) DEFAULT NULL,
1791 "rec.var" double(30,10) DEFAULT NULL
1792 )
1793 DROP TABLE t1;
1794 DROP PROCEDURE p1;
1795 CREATE PROCEDURE p1() AS var DOUBLE(30,11); rec ROW(var DOUBLE(30,11)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1796 CALL p1();
1797 SHOW CREATE TABLE t1;
1798 Table Create Table
1799 t1 CREATE TABLE "t1" (
1800 "var" double(30,11) DEFAULT NULL,
1801 "rec.var" double(30,11) DEFAULT NULL
1802 )
1803 DROP TABLE t1;
1804 DROP PROCEDURE p1;
1805 CREATE PROCEDURE p1() AS var DOUBLE(30,12); rec ROW(var DOUBLE(30,12)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1806 CALL p1();
1807 SHOW CREATE TABLE t1;
1808 Table Create Table
1809 t1 CREATE TABLE "t1" (
1810 "var" double(30,12) DEFAULT NULL,
1811 "rec.var" double(30,12) DEFAULT NULL
1812 )
1813 DROP TABLE t1;
1814 DROP PROCEDURE p1;
1815 CREATE PROCEDURE p1() AS var DOUBLE(30,13); rec ROW(var DOUBLE(30,13)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1816 CALL p1();
1817 SHOW CREATE TABLE t1;
1818 Table Create Table
1819 t1 CREATE TABLE "t1" (
1820 "var" double(30,13) DEFAULT NULL,
1821 "rec.var" double(30,13) DEFAULT NULL
1822 )
1823 DROP TABLE t1;
1824 DROP PROCEDURE p1;
1825 CREATE PROCEDURE p1() AS var DOUBLE(30,14); rec ROW(var DOUBLE(30,14)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1826 CALL p1();
1827 SHOW CREATE TABLE t1;
1828 Table Create Table
1829 t1 CREATE TABLE "t1" (
1830 "var" double(30,14) DEFAULT NULL,
1831 "rec.var" double(30,14) DEFAULT NULL
1832 )
1833 DROP TABLE t1;
1834 DROP PROCEDURE p1;
1835 CREATE PROCEDURE p1() AS var DOUBLE(30,20); rec ROW(var DOUBLE(30,20)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1836 CALL p1();
1837 SHOW CREATE TABLE t1;
1838 Table Create Table
1839 t1 CREATE TABLE "t1" (
1840 "var" double(30,20) DEFAULT NULL,
1841 "rec.var" double(30,20) DEFAULT NULL
1842 )
1843 DROP TABLE t1;
1844 DROP PROCEDURE p1;
1845 CREATE PROCEDURE p1() AS var DOUBLE(30,21); rec ROW(var DOUBLE(30,21)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1846 CALL p1();
1847 SHOW CREATE TABLE t1;
1848 Table Create Table
1849 t1 CREATE TABLE "t1" (
1850 "var" double(30,21) DEFAULT NULL,
1851 "rec.var" double(30,21) DEFAULT NULL
1852 )
1853 DROP TABLE t1;
1854 DROP PROCEDURE p1;
1855 #
1856 # VARCHAR
1857 #
1858 CREATE PROCEDURE p1() AS var CHAR; rec ROW(var CHAR); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1859 CALL p1();
1860 SHOW CREATE TABLE t1;
1861 Table Create Table
1862 t1 CREATE TABLE "t1" (
1863 "var" char(1) DEFAULT NULL,
1864 "rec.var" char(1) DEFAULT NULL
1865 )
1866 DROP TABLE t1;
1867 DROP PROCEDURE p1;
1868 CREATE PROCEDURE p1() AS var BINARY; rec ROW(var BINARY); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1869 CALL p1();
1870 SHOW CREATE TABLE t1;
1871 Table Create Table
1872 t1 CREATE TABLE "t1" (
1873 "var" binary(1) DEFAULT NULL,
1874 "rec.var" binary(1) DEFAULT NULL
1875 )
1876 DROP TABLE t1;
1877 DROP PROCEDURE p1;
1878 CREATE PROCEDURE p1() AS var CHAR(1); rec ROW(var CHAR(1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1879 CALL p1();
1880 SHOW CREATE TABLE t1;
1881 Table Create Table
1882 t1 CREATE TABLE "t1" (
1883 "var" char(1) DEFAULT NULL,
1884 "rec.var" char(1) DEFAULT NULL
1885 )
1886 DROP TABLE t1;
1887 DROP PROCEDURE p1;
1888 CREATE PROCEDURE p1() AS var CHAR(10); rec ROW(var CHAR(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1889 CALL p1();
1890 SHOW CREATE TABLE t1;
1891 Table Create Table
1892 t1 CREATE TABLE "t1" (
1893 "var" char(10) DEFAULT NULL,
1894 "rec.var" char(10) DEFAULT NULL
1895 )
1896 DROP TABLE t1;
1897 DROP PROCEDURE p1;
1898 CREATE PROCEDURE p1() AS var NCHAR(10); rec ROW(var NCHAR(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1899 CALL p1();
1900 SHOW CREATE TABLE t1;
1901 Table Create Table
1902 t1 CREATE TABLE "t1" (
1903 "var" char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL,
1904 "rec.var" char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL
1905 )
1906 DROP TABLE t1;
1907 DROP PROCEDURE p1;
1908 CREATE PROCEDURE p1() AS var BINARY(10); rec ROW(var BINARY(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1909 CALL p1();
1910 SHOW CREATE TABLE t1;
1911 Table Create Table
1912 t1 CREATE TABLE "t1" (
1913 "var" binary(10) DEFAULT NULL,
1914 "rec.var" binary(10) DEFAULT NULL
1915 )
1916 DROP TABLE t1;
1917 DROP PROCEDURE p1;
1918 CREATE PROCEDURE p1() AS var VARBINARY(10); rec ROW(var VARBINARY(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1919 CALL p1();
1920 SHOW CREATE TABLE t1;
1921 Table Create Table
1922 t1 CREATE TABLE "t1" (
1923 "var" varbinary(10) DEFAULT NULL,
1924 "rec.var" varbinary(10) DEFAULT NULL
1925 )
1926 DROP TABLE t1;
1927 DROP PROCEDURE p1;
1928 CREATE PROCEDURE p1() AS var VARCHAR(10); rec ROW(var VARCHAR(10)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1929 CALL p1();
1930 SHOW CREATE TABLE t1;
1931 Table Create Table
1932 t1 CREATE TABLE "t1" (
1933 "var" varchar(10) DEFAULT NULL,
1934 "rec.var" varchar(10) DEFAULT NULL
1935 )
1936 DROP TABLE t1;
1937 DROP PROCEDURE p1;
1938 CREATE PROCEDURE p1() AS var VARCHAR(10) CHARACTER SET utf8; rec ROW(var VARCHAR(10) CHARACTER SET utf8); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1939 CALL p1();
1940 SHOW CREATE TABLE t1;
1941 Table Create Table
1942 t1 CREATE TABLE "t1" (
1943 "var" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL,
1944 "rec.var" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL
1945 )
1946 DROP TABLE t1;
1947 DROP PROCEDURE p1;
1948 CREATE PROCEDURE p1() AS var VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin; rec ROW(var VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1949 CALL p1();
1950 SHOW CREATE TABLE t1;
1951 Table Create Table
1952 t1 CREATE TABLE "t1" (
1953 "var" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
1954 "rec.var" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL
1955 )
1956 DROP TABLE t1;
1957 DROP PROCEDURE p1;
1958 #
1959 # TIME
1960 #
1961 CREATE PROCEDURE p1() AS var TIME; rec ROW(var TIME); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1962 CALL p1();
1963 SHOW CREATE TABLE t1;
1964 Table Create Table
1965 t1 CREATE TABLE "t1" (
1966 "var" time DEFAULT NULL,
1967 "rec.var" time DEFAULT NULL
1968 )
1969 DROP TABLE t1;
1970 DROP PROCEDURE p1;
1971 CREATE PROCEDURE p1() AS var TIME(1); rec ROW(var TIME(1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1972 CALL p1();
1973 SHOW CREATE TABLE t1;
1974 Table Create Table
1975 t1 CREATE TABLE "t1" (
1976 "var" time(1) DEFAULT NULL,
1977 "rec.var" time(1) DEFAULT NULL
1978 )
1979 DROP TABLE t1;
1980 DROP PROCEDURE p1;
1981 CREATE PROCEDURE p1() AS var TIME(2); rec ROW(var TIME(2)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1982 CALL p1();
1983 SHOW CREATE TABLE t1;
1984 Table Create Table
1985 t1 CREATE TABLE "t1" (
1986 "var" time(2) DEFAULT NULL,
1987 "rec.var" time(2) DEFAULT NULL
1988 )
1989 DROP TABLE t1;
1990 DROP PROCEDURE p1;
1991 CREATE PROCEDURE p1() AS var TIME(3); rec ROW(var TIME(3)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
1992 CALL p1();
1993 SHOW CREATE TABLE t1;
1994 Table Create Table
1995 t1 CREATE TABLE "t1" (
1996 "var" time(3) DEFAULT NULL,
1997 "rec.var" time(3) DEFAULT NULL
1998 )
1999 DROP TABLE t1;
2000 DROP PROCEDURE p1;
2001 CREATE PROCEDURE p1() AS var TIME(4); rec ROW(var TIME(4)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2002 CALL p1();
2003 SHOW CREATE TABLE t1;
2004 Table Create Table
2005 t1 CREATE TABLE "t1" (
2006 "var" time(4) DEFAULT NULL,
2007 "rec.var" time(4) DEFAULT NULL
2008 )
2009 DROP TABLE t1;
2010 DROP PROCEDURE p1;
2011 CREATE PROCEDURE p1() AS var TIME(5); rec ROW(var TIME(5)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2012 CALL p1();
2013 SHOW CREATE TABLE t1;
2014 Table Create Table
2015 t1 CREATE TABLE "t1" (
2016 "var" time(5) DEFAULT NULL,
2017 "rec.var" time(5) DEFAULT NULL
2018 )
2019 DROP TABLE t1;
2020 DROP PROCEDURE p1;
2021 CREATE PROCEDURE p1() AS var TIME(6); rec ROW(var TIME(6)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2022 CALL p1();
2023 SHOW CREATE TABLE t1;
2024 Table Create Table
2025 t1 CREATE TABLE "t1" (
2026 "var" time(6) DEFAULT NULL,
2027 "rec.var" time(6) DEFAULT NULL
2028 )
2029 DROP TABLE t1;
2030 DROP PROCEDURE p1;
2031 #
2032 # DATETIME
2033 #
2034 CREATE PROCEDURE p1() AS var DATETIME; rec ROW(var DATETIME); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2035 CALL p1();
2036 SHOW CREATE TABLE t1;
2037 Table Create Table
2038 t1 CREATE TABLE "t1" (
2039 "var" datetime DEFAULT NULL,
2040 "rec.var" datetime DEFAULT NULL
2041 )
2042 DROP TABLE t1;
2043 DROP PROCEDURE p1;
2044 CREATE PROCEDURE p1() AS var DATETIME(1); rec ROW(var DATETIME(1)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2045 CALL p1();
2046 SHOW CREATE TABLE t1;
2047 Table Create Table
2048 t1 CREATE TABLE "t1" (
2049 "var" datetime(1) DEFAULT NULL,
2050 "rec.var" datetime(1) DEFAULT NULL
2051 )
2052 DROP TABLE t1;
2053 DROP PROCEDURE p1;
2054 CREATE PROCEDURE p1() AS var DATETIME(2); rec ROW(var DATETIME(2)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2055 CALL p1();
2056 SHOW CREATE TABLE t1;
2057 Table Create Table
2058 t1 CREATE TABLE "t1" (
2059 "var" datetime(2) DEFAULT NULL,
2060 "rec.var" datetime(2) DEFAULT NULL
2061 )
2062 DROP TABLE t1;
2063 DROP PROCEDURE p1;
2064 CREATE PROCEDURE p1() AS var DATETIME(3); rec ROW(var DATETIME(3)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2065 CALL p1();
2066 SHOW CREATE TABLE t1;
2067 Table Create Table
2068 t1 CREATE TABLE "t1" (
2069 "var" datetime(3) DEFAULT NULL,
2070 "rec.var" datetime(3) DEFAULT NULL
2071 )
2072 DROP TABLE t1;
2073 DROP PROCEDURE p1;
2074 CREATE PROCEDURE p1() AS var DATETIME(4); rec ROW(var DATETIME(4)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2075 CALL p1();
2076 SHOW CREATE TABLE t1;
2077 Table Create Table
2078 t1 CREATE TABLE "t1" (
2079 "var" datetime(4) DEFAULT NULL,
2080 "rec.var" datetime(4) DEFAULT NULL
2081 )
2082 DROP TABLE t1;
2083 DROP PROCEDURE p1;
2084 CREATE PROCEDURE p1() AS var DATETIME(5); rec ROW(var DATETIME(5)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2085 CALL p1();
2086 SHOW CREATE TABLE t1;
2087 Table Create Table
2088 t1 CREATE TABLE "t1" (
2089 "var" datetime(5) DEFAULT NULL,
2090 "rec.var" datetime(5) DEFAULT NULL
2091 )
2092 DROP TABLE t1;
2093 DROP PROCEDURE p1;
2094 CREATE PROCEDURE p1() AS var DATETIME(6); rec ROW(var DATETIME(6)); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2095 CALL p1();
2096 SHOW CREATE TABLE t1;
2097 Table Create Table
2098 t1 CREATE TABLE "t1" (
2099 "var" datetime(6) DEFAULT NULL,
2100 "rec.var" datetime(6) DEFAULT NULL
2101 )
2102 DROP TABLE t1;
2103 DROP PROCEDURE p1;
2104 #
2105 # LOB
2106 #
2107 CREATE PROCEDURE p1() AS var TEXT; rec ROW(var TEXT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2108 CALL p1();
2109 SHOW CREATE TABLE t1;
2110 Table Create Table
2111 t1 CREATE TABLE "t1" (
2112 "var" text DEFAULT NULL,
2113 "rec.var" text DEFAULT NULL
2114 )
2115 DROP TABLE t1;
2116 DROP PROCEDURE p1;
2117 CREATE PROCEDURE p1() AS var TINYTEXT; rec ROW(var TINYTEXT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2118 CALL p1();
2119 SHOW CREATE TABLE t1;
2120 Table Create Table
2121 t1 CREATE TABLE "t1" (
2122 "var" tinytext DEFAULT NULL,
2123 "rec.var" tinytext DEFAULT NULL
2124 )
2125 DROP TABLE t1;
2126 DROP PROCEDURE p1;
2127 CREATE PROCEDURE p1() AS var MEDIUMTEXT; rec ROW(var MEDIUMTEXT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2128 CALL p1();
2129 SHOW CREATE TABLE t1;
2130 Table Create Table
2131 t1 CREATE TABLE "t1" (
2132 "var" mediumtext DEFAULT NULL,
2133 "rec.var" mediumtext DEFAULT NULL
2134 )
2135 DROP TABLE t1;
2136 DROP PROCEDURE p1;
2137 CREATE PROCEDURE p1() AS var LONGTEXT; rec ROW(var LONGTEXT); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2138 CALL p1();
2139 SHOW CREATE TABLE t1;
2140 Table Create Table
2141 t1 CREATE TABLE "t1" (
2142 "var" longtext DEFAULT NULL,
2143 "rec.var" longtext DEFAULT NULL
2144 )
2145 DROP TABLE t1;
2146 DROP PROCEDURE p1;
2147 CREATE PROCEDURE p1() AS var TEXT CHARACTER SET utf8; rec ROW(var TEXT CHARACTER SET utf8); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2148 CALL p1();
2149 SHOW CREATE TABLE t1;
2150 Table Create Table
2151 t1 CREATE TABLE "t1" (
2152 "var" text CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL,
2153 "rec.var" text CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL
2154 )
2155 DROP TABLE t1;
2156 DROP PROCEDURE p1;
2157 CREATE PROCEDURE p1() AS var TINYTEXT CHARACTER SET utf8; rec ROW(var TINYTEXT CHARACTER SET utf8); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2158 CALL p1();
2159 SHOW CREATE TABLE t1;
2160 Table Create Table
2161 t1 CREATE TABLE "t1" (
2162 "var" tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL,
2163 "rec.var" tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL
2164 )
2165 DROP TABLE t1;
2166 DROP PROCEDURE p1;
2167 CREATE PROCEDURE p1() AS var MEDIUMTEXT CHARACTER SET utf8; rec ROW(var MEDIUMTEXT CHARACTER SET utf8); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2168 CALL p1();
2169 SHOW CREATE TABLE t1;
2170 Table Create Table
2171 t1 CREATE TABLE "t1" (
2172 "var" mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL,
2173 "rec.var" mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL
2174 )
2175 DROP TABLE t1;
2176 DROP PROCEDURE p1;
2177 CREATE PROCEDURE p1() AS var LONGTEXT CHARACTER SET utf8; rec ROW(var LONGTEXT CHARACTER SET utf8); BEGIN CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
2178 CALL p1();
2179 SHOW CREATE TABLE t1;
2180 Table Create Table
2181 t1 CREATE TABLE "t1" (
2182 "var" longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL,
2183 "rec.var" longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci DEFAULT NULL
2184 )
2185 DROP TABLE t1;
2186 DROP PROCEDURE p1;
2187 #
2188 # End of MDEV-10914 ROW data type for stored routine variables
2189 #
2190 #
2191 # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
2192 #
2193 #
2194 # Referring to a table in a non-existing database
2195 #
2196 CREATE PROCEDURE p1()
2197 AS
2198 rec test2.t1%ROWTYPE;
2199 BEGIN
2200 NULL;
2201 END;
2202 $$
2203 CALL p1();
2204 ERROR 42S02: Table 'test2.t1' doesn't exist
2205 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
2206 CALL p1();
2207 ERROR 42S02: Table 'test2.t1' doesn't exist
2208 DROP TABLE t1;
2209 DROP PROCEDURE p1;
2210 #
2211 # Referring to a table in the current database
2212 #
2213 CREATE PROCEDURE p1()
2214 AS
2215 rec t1%ROWTYPE;
2216 BEGIN
2217 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
2218 SHOW CREATE TABLE t2;
2219 DROP TABLE t2;
2220 END;
2221 $$
2222 CALL p1();
2223 ERROR 42S02: Table 'test.t1' doesn't exist
2224 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
2225 CALL p1();
2226 Table Create Table
2227 t2 CREATE TABLE "t2" (
2228 "rec.a" int(11) DEFAULT NULL,
2229 "rec.b" varchar(10) DEFAULT NULL,
2230 "rec.c" double DEFAULT NULL,
2231 "rec.d" decimal(10,0) DEFAULT NULL
2232 )
2233 DROP TABLE t1;
2234 DROP PROCEDURE p1;
2235 #
2236 # Referring to a table in an explicitly specified database
2237 #
2238 CREATE PROCEDURE p1()
2239 AS
2240 rec test.t1%ROWTYPE;
2241 BEGIN
2242 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
2243 SHOW CREATE TABLE t2;
2244 DROP TABLE t2;
2245 END;
2246 $$
2247 CALL p1();
2248 ERROR 42S02: Table 'test.t1' doesn't exist
2249 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
2250 CALL p1();
2251 Table Create Table
2252 t2 CREATE TABLE "t2" (
2253 "rec.a" int(11) DEFAULT NULL,
2254 "rec.b" varchar(10) DEFAULT NULL,
2255 "rec.c" double DEFAULT NULL,
2256 "rec.d" decimal(10,0) DEFAULT NULL
2257 )
2258 DROP TABLE t1;
2259 DROP PROCEDURE p1;
2260 #
2261 # Referring to a view in the current database
2262 #
2263 CREATE PROCEDURE p1()
2264 AS
2265 rec v1%ROWTYPE;
2266 BEGIN
2267 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
2268 SHOW CREATE TABLE t2;
2269 DROP TABLE t2;
2270 END;
2271 $$
2272 CALL p1();
2273 ERROR 42S02: Table 'test.v1' doesn't exist
2274 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
2275 CREATE VIEW v1 AS SELECT * FROM t1;
2276 CALL p1();
2277 Table Create Table
2278 t2 CREATE TABLE "t2" (
2279 "rec.a" int(11) DEFAULT NULL,
2280 "rec.b" varchar(10) DEFAULT NULL,
2281 "rec.c" double DEFAULT NULL,
2282 "rec.d" decimal(10,0) DEFAULT NULL
2283 )
2284 DROP VIEW v1;
2285 DROP TABLE t1;
2286 DROP PROCEDURE p1;
2287 #
2288 # Referring to a view in an explicitly specified database
2289 #
2290 CREATE PROCEDURE p1()
2291 AS
2292 rec test.v1%ROWTYPE;
2293 BEGIN
2294 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
2295 SHOW CREATE TABLE t2;
2296 DROP TABLE t2;
2297 END;
2298 $$
2299 CALL p1();
2300 ERROR 42S02: Table 'test.v1' doesn't exist
2301 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
2302 CREATE VIEW v1 AS SELECT * FROM t1;
2303 CALL p1();
2304 Table Create Table
2305 t2 CREATE TABLE "t2" (
2306 "rec.a" int(11) DEFAULT NULL,
2307 "rec.b" varchar(10) DEFAULT NULL,
2308 "rec.c" double DEFAULT NULL,
2309 "rec.d" decimal(10,0) DEFAULT NULL
2310 )
2311 DROP VIEW v1;
2312 DROP TABLE t1;
2313 DROP PROCEDURE p1;
2314 #
2315 # Checking that all table%ROWTYPE fields are NULL by default
2316 #
2317 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
2318 CREATE PROCEDURE p1()
2319 AS
2320 rec1 t1%ROWTYPE;
2321 BEGIN
2322 SELECT rec1.a, rec1.b, rec1.c, rec1.d;
2323 END;
2324 $$
2325 CALL p1();
2326 rec1.a rec1.b rec1.c rec1.d
2327 NULL NULL NULL NULL
2328 DROP TABLE t1;
2329 DROP PROCEDURE p1;
2330 #
2331 # A table%ROWTYPE variable with a ROW expression as a default
2332 #
2333 CREATE TABLE t1 (a INT, b VARCHAR(10));
2334 CREATE PROCEDURE p1()
2335 AS
2336 rec1 t1%ROWTYPE DEFAULT ROW(10,'bbb');
2337 BEGIN
2338 SELECT rec1.a, rec1.b;
2339 END;
2340 $$
2341 CALL p1();
2342 rec1.a rec1.b
2343 10 bbb
2344 DROP TABLE t1;
2345 DROP PROCEDURE p1;
2346 #
2347 # A table%ROWTYPE variable with an incompatible ROW expression as a default
2348 #
2349 CREATE TABLE t1 (a INT, b VARCHAR(10));
2350 CREATE PROCEDURE p1()
2351 AS
2352 rec1 t1%ROWTYPE DEFAULT ROW(10,'bbb','ccc');
2353 BEGIN
2354 SELECT rec1.a, rec1.b;
2355 END;
2356 $$
2357 CALL p1();
2358 ERROR 21000: Operand should contain 2 column(s)
2359 DROP TABLE t1;
2360 DROP PROCEDURE p1;
2361 #
2362 # A table%ROWTYPE variable with a ROW variable as a default
2363 #
2364 CREATE TABLE t1 (a INT, b VARCHAR(10));
2365 CREATE PROCEDURE p1()
2366 AS
2367 rec1 ROW(a INT, b VARCHAR(10)):= ROW(10,'bbb');
2368 rec2 t1%ROWTYPE DEFAULT rec1;
2369 BEGIN
2370 SELECT rec2.a, rec2.b;
2371 END;
2372 $$
2373 CALL p1();
2374 rec2.a rec2.b
2375 10 bbb
2376 DROP TABLE t1;
2377 DROP PROCEDURE p1;
2378 #
2379 # A ROW variable using a table%ROWTYPE variable as a default
2380 #
2381 CREATE TABLE t1 (a INT, b VARCHAR(10));
2382 CREATE PROCEDURE p1()
2383 AS
2384 rec1 t1%ROWTYPE := ROW(10,'bbb');
2385 rec2 ROW(a INT, b VARCHAR(10)):= rec1;
2386 BEGIN
2387 SELECT rec2.a, rec2.b;
2388 END;
2389 $$
2390 CALL p1();
2391 rec2.a rec2.b
2392 10 bbb
2393 DROP TABLE t1;
2394 DROP PROCEDURE p1;
2395 #
2396 # Assigning table%ROWTYPE variables with a different column count
2397 #
2398 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE);
2399 CREATE TABLE t2 (a INT, b VARCHAR(10));
2400 CREATE PROCEDURE p1()
2401 AS
2402 rec1 t1%ROWTYPE;
2403 rec2 t2%ROWTYPE;
2404 BEGIN
2405 rec2:=rec1;
2406 END;
2407 $$
2408 CALL p1();
2409 ERROR 21000: Operand should contain 2 column(s)
2410 DROP PROCEDURE p1;
2411 CREATE PROCEDURE p1()
2412 AS
2413 rec1 t1%ROWTYPE;
2414 rec2 t2%ROWTYPE;
2415 BEGIN
2416 rec1:=rec2;
2417 END;
2418 $$
2419 CALL p1();
2420 ERROR 21000: Operand should contain 3 column(s)
2421 DROP TABLE t2;
2422 DROP TABLE t1;
2423 DROP PROCEDURE p1;
2424 #
2425 # Assigning compatible table%ROWTYPE variables (equal number of fields)
2426 #
2427 CREATE TABLE t1 (a INT, b VARCHAR(10));
2428 CREATE TABLE t2 (x INT, y VARCHAR(10));
2429 CREATE PROCEDURE p1()
2430 AS
2431 rec1 t1%ROWTYPE;
2432 rec2 t2%ROWTYPE;
2433 BEGIN
2434 rec1.a:= 10;
2435 rec1.b:= 'bbb';
2436 rec2:=rec1;
2437 SELECT rec2.x, rec2.y;
2438 END;
2439 $$
2440 CALL p1();
2441 rec2.x rec2.y
2442 10 bbb
2443 DROP TABLE t2;
2444 DROP TABLE t1;
2445 DROP PROCEDURE p1;
2446 #
2447 # Assigning between incompatible table%ROWTYPE and explicit ROW variables
2448 #
2449 CREATE TABLE t1 (a INT, b VARCHAR(10));
2450 CREATE PROCEDURE p1()
2451 AS
2452 rec1 t1%ROWTYPE;
2453 rec2 ROW(x INT,y INT,z INT);
2454 BEGIN
2455 rec2.x:= 10;
2456 rec2.y:= 20;
2457 rec2.z:= 30;
2458 rec1:= rec2;
2459 END;
2460 $$
2461 CALL p1();
2462 ERROR 21000: Operand should contain 2 column(s)
2463 DROP TABLE t1;
2464 DROP PROCEDURE p1;
2465 #
2466 # Assigning between compatible table%ROWTYPE and explicit ROW variables
2467 #
2468 CREATE TABLE t1 (a INT, b VARCHAR(10));
2469 CREATE PROCEDURE p1()
2470 AS
2471 rec1 t1%ROWTYPE;
2472 rec2 ROW(x INT,y INT);
2473 BEGIN
2474 rec2.x:= 10;
2475 rec2.y:= 20;
2476 rec1:= rec2;
2477 SELECT rec1.a, rec1.b;
2478 rec1.a:= 11;
2479 rec1.b:= 21;
2480 rec2:= rec1;
2481 SELECT rec2.x, rec2.y;
2482 END;
2483 $$
2484 CALL p1();
2485 rec1.a rec1.b
2486 10 20
2487 rec2.x rec2.y
2488 11 21
2489 DROP TABLE t1;
2490 DROP PROCEDURE p1;
2491 #
2492 # Assigning table%ROWTYPE from a ROW expression
2493 #
2494 CREATE TABLE t1 (a INT, b VARCHAR(10));
2495 CREATE PROCEDURE p1()
2496 AS
2497 rec1 t1%ROWTYPE;
2498 BEGIN
2499 rec1:= ROW(10,20);
2500 SELECT rec1.a, rec1.b;
2501 END;
2502 $$
2503 CALL p1();
2504 rec1.a rec1.b
2505 10 20
2506 DROP TABLE t1;
2507 DROP PROCEDURE p1;
2508 #
2509 # Fetching a cursor into a table%ROWTYPE variable with a wrong field count
2510 #
2511 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
2512 CREATE TABLE t2 (a INT, b VARCHAR(10));
2513 INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
2514 CREATE PROCEDURE p1()
2515 AS
2516 rec2 t2%ROWTYPE;
2517 CURSOR cur1 IS SELECT * FROM t1;
2518 BEGIN
2519 OPEN cur1;
2520 FETCH cur1 INTO rec2;
2521 CLOSE cur1;
2522 END;
2523 $$
2524 CALL p1();
2525 ERROR HY000: Incorrect number of FETCH variables
2526 DROP TABLE t2;
2527 DROP TABLE t1;
2528 DROP PROCEDURE p1;
2529 #
2530 # Fetching a cursor into a table%ROWTYPE variable
2531 #
2532 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
2533 CREATE TABLE t2 LIKE t1;
2534 INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
2535 INSERT INTO t1 VALUES (20,'bb2',222.222e2, 12.32);
2536 INSERT INTO t1 VALUES (30,'bb3',333.333e2, 12.33);
2537 CREATE PROCEDURE p1()
2538 AS
2539 rec t1%ROWTYPE;
2540 CURSOR cur IS SELECT * FROM t1;
2541 BEGIN
2542 OPEN cur;
2543 LOOP
2544 FETCH cur INTO rec;
2545 EXIT WHEN cur%NOTFOUND;
2546 SELECT rec.a, rec.b, rec.c, rec.d;
2547 INSERT INTO t2 VALUES (rec.a, rec.b, rec.c, rec.d);
2548 END LOOP;
2549 CLOSE cur;
2550 END;
2551 $$
2552 CALL p1();
2553 rec.a rec.b rec.c rec.d
2554 10 bb1 11111.1 12.31
2555 rec.a rec.b rec.c rec.d
2556 20 bb2 22222.2 12.32
2557 rec.a rec.b rec.c rec.d
2558 30 bb3 33333.3 12.33
2559 SELECT * FROM t2;
2560 a b c d
2561 10 bb1 11111.1 12.31
2562 20 bb2 22222.2 12.32
2563 30 bb3 33333.3 12.33
2564 DROP TABLE t2;
2565 DROP TABLE t1;
2566 DROP PROCEDURE p1;
2567 #
2568 # Fetching a cursor into a table%ROWTYPE variable with different column names
2569 #
2570 CREATE TABLE t1 (a INT, b VARCHAR(10));
2571 CREATE TABLE t2 (x INT, y VARCHAR(10));
2572 INSERT INTO t1 VALUES (10,'bbb');
2573 CREATE PROCEDURE p1()
2574 AS
2575 rec2 t2%ROWTYPE;
2576 CURSOR cur1 IS SELECT * FROM t1;
2577 BEGIN
2578 OPEN cur1;
2579 FETCH cur1 INTO rec2;
2580 SELECT rec2.x, rec2.y;
2581 CLOSE cur1;
2582 END;
2583 $$
2584 CALL p1();
2585 rec2.x rec2.y
2586 10 bbb
2587 DROP TABLE t2;
2588 DROP TABLE t1;
2589 DROP PROCEDURE p1;
2590 #
2591 # Fetching a cursor into a table%ROWTYPE variable, with truncation
2592 #
2593 CREATE TABLE t1 (a INT, b VARCHAR(10));
2594 CREATE TABLE t2 (a INT, b INT);
2595 INSERT INTO t1 VALUES (10,'11x');
2596 CREATE PROCEDURE p1()
2597 AS
2598 rec2 t2%ROWTYPE;
2599 CURSOR cur1 IS SELECT * FROM t1;
2600 BEGIN
2601 OPEN cur1;
2602 FETCH cur1 INTO rec2;
2603 SELECT rec2.a, rec2.b;
2604 CLOSE cur1;
2605 END;
2606 $$
2607 CALL p1();
2608 rec2.a rec2.b
2609 10 11
2610 Warnings:
2611 Warning 1265 Data truncated for column 'b' at row 1
2612 DROP TABLE t2;
2613 DROP TABLE t1;
2614 DROP PROCEDURE p1;
2615 #
2616 # table%ROWTYPE variables are not allowed in LIMIT
2617 #
2618 CREATE TABLE t1 (a INT, b INT);
2619 INSERT INTO t1 VALUES (1,2);
2620 CREATE PROCEDURE p1()
2621 AS
2622 rec1 t1%ROWTYPE:=(1,2);
2623 BEGIN
2624 SELECT * FROM t1 LIMIT rec1.a;
2625 END;
2626 $$
2627 ERROR HY000: A variable of a non-integer based type in LIMIT clause
2628 DROP TABLE t1;
2629 #
2630 # table%ROWTYPE variable fields as OUT parameters
2631 #
2632 CREATE TABLE t1 (a INT, b VARCHAR(10));
2633 CREATE PROCEDURE p1(a OUT INT,b OUT VARCHAR(10))
2634 AS
2635 BEGIN
2636 a:=10;
2637 b:='bb';
2638 END;
2639 $$
2640 CREATE PROCEDURE p2()
2641 AS
2642 rec1 t1%ROWTYPE;
2643 BEGIN
2644 CALL p1(rec1.a, rec1.b);
2645 SELECT rec1.a, rec1.b;
2646 END;
2647 $$
2648 CALL p2();
2649 rec1.a rec1.b
2650 10 bb
2651 DROP PROCEDURE p2;
2652 DROP PROCEDURE p1;
2653 DROP TABLE t1;
2654 #
2655 # Passing the entire table%ROWTYPE variable
2656 #
2657 CREATE TABLE t1 (a INT, b VARCHAR(10));
2658 CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10)))
2659 AS
2660 BEGIN
2661 SELECT a.a, a.b;
2662 END;
2663 $$
2664 CREATE PROCEDURE p2()
2665 AS
2666 rec1 t1%ROWTYPE:= ROW(10,'bb');
2667 BEGIN
2668 CALL p1(rec1);
2669 END;
2670 $$
2671 CALL p2();
2672 a.a a.b
2673 10 bb
2674 DROP PROCEDURE p2;
2675 DROP PROCEDURE p1;
2676 DROP TABLE t1;
2677 #
2678 # Passing the entire table%ROWTYPE variable as an OUT parameter
2679 #
2680 CREATE TABLE t1 (a INT, b VARCHAR(10));
2681 CREATE PROCEDURE p1(a OUT ROW(a INT, b VARCHAR(10)))
2682 AS
2683 BEGIN
2684 a:= ROW(10,'bb');
2685 END;
2686 $$
2687 CREATE PROCEDURE p2()
2688 AS
2689 rec1 t1%ROWTYPE;
2690 BEGIN
2691 CALL p1(rec1);
2692 SELECT rec1.a, rec1.b;
2693 END;
2694 $$
2695 CALL p2();
2696 rec1.a rec1.b
2697 10 bb
2698 DROP PROCEDURE p2;
2699 DROP PROCEDURE p1;
2700 DROP TABLE t1;
2701 #
2702 # Assigning a table%ROWTYPE field to an OUT parameter
2703 #
2704 CREATE TABLE t1 (a INT, b VARCHAR(10));
2705 CREATE PROCEDURE p1 (res IN OUT INTEGER)
2706 AS
2707 rec1 t1%ROWTYPE:=ROW(10,'b0');
2708 BEGIN
2709 res:=rec1.a;
2710 END;
2711 $$
2712 CALL p1(@res);
2713 SELECT @res;
2714 @res
2715 10
2716 SET @res=NULL;
2717 DROP PROCEDURE p1;
2718 DROP TABLE t1;
2719 #
2720 # Testing Item_splocal_row_field_by_name::print
2721 #
2722 CREATE TABLE t1 (a INT, b VARCHAR(10));
2723 CREATE PROCEDURE p1
2724 AS
2725 rec t1%ROWTYPE:=ROW(10,'bb');
2726 BEGIN
2727 EXPLAIN EXTENDED SELECT rec.a, rec.b;
2728 END;
2729 $$
2730 CALL p1();
2731 id select_type table type possible_keys key key_len ref rows filtered Extra
2732 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2733 Warnings:
2734 Note 1003 select rec.a@0["a"] AS "rec.a",rec.b@0["b"] AS "rec.b"
2735 DROP PROCEDURE p1;
2736 DROP TABLE t1;
2737 #
2738 # Non-existing field
2739 #
2740 CREATE TABLE t1 (a INT, b VARCHAR(10));
2741 CREATE PROCEDURE p1
2742 AS
2743 rec t1%ROWTYPE;
2744 BEGIN
2745 SELECT rec.c;
2746 END;
2747 $$
2748 CALL p1();
2749 ERROR HY000: Row variable 'rec' does not have a field 'c'
2750 ALTER TABLE t1 ADD c INT;
2751 CALL p1();
2752 rec.c
2753 NULL
2754 DROP PROCEDURE p1;
2755 DROP TABLE t1;
2756 #
2757 # Testing that field names are case insensitive
2758 #
2759 CREATE TABLE t1 (a INT, b VARCHAR(10));
2760 CREATE PROCEDURE p1
2761 AS
2762 rec t1%ROWTYPE:=ROW(10,'bb');
2763 BEGIN
2764 SELECT rec.A, rec.B;
2765 END;
2766 $$
2767 CALL p1();
2768 rec.A rec.B
2769 10 bb
2770 DROP PROCEDURE p1;
2771 DROP TABLE t1;
2772 #
2773 # Testing that table%ROWTYPE uses temporary tables vs shadowed real tables
2774 #
2775 CREATE TABLE t1 (a INT, b VARCHAR(10));
2776 CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
2777 CREATE PROCEDURE p1
2778 AS
2779 rec t1%ROWTYPE:=ROW(10,'bb');
2780 BEGIN
2781 SELECT rec.A, rec.B;
2782 END;
2783 $$
2784 CALL p1();
2785 ERROR HY000: Row variable 'rec' does not have a field 'A'
2786 DROP TEMPORARY TABLE t1;
2787 CALL p1();
2788 rec.A rec.B
2789 10 bb
2790 DROP PROCEDURE p1;
2791 DROP TABLE t1;
2792 #
2793 # Testing that the structure of table%ROWTYPE variables is determined at the very beginning and is not changed after ALTER
2794 #
2795 CREATE TABLE t1 (a INT, b VARCHAR(32));
2796 INSERT INTO t1 VALUES (10,'b10');
2797 CREATE PROCEDURE p1 AS
2798 BEGIN
2799 ALTER TABLE t1 ADD c INT;
2800 DECLARE
2801 rec t1%ROWTYPE; -- this will not have column "c"
2802 BEGIN
2803 rec.c:=10;
2804 END;
2805 END;
2806 $$
2807 CALL p1();
2808 ERROR HY000: Row variable 'rec' does not have a field 'c'
2809 DROP TABLE t1;
2810 DROP PROCEDURE p1;
2811 #
2812 # MDEV-12291 Allow ROW variables as SELECT INTO targets
2813 #
2814 # ROW variable with a wrong column count
2815 CREATE TABLE t1 (a INT, b VARCHAR(32));
2816 INSERT INTO t1 VALUES (10,'b10');
2817 CREATE PROCEDURE p1 AS
2818 rec1 ROW(a INT, b VARCHAR(32), c DOUBLE);
2819 BEGIN
2820 SELECT * FROM t1 INTO rec1;
2821 SELECT rec1.a, rec1.b;
2822 END;
2823 $$
2824 CALL p1();
2825 ERROR 21000: The used SELECT statements have a different number of columns
2826 DROP TABLE t1;
2827 DROP PROCEDURE p1;
2828 # Multiple ROW variables
2829 CREATE TABLE t1 (a INT, b VARCHAR(32));
2830 INSERT INTO t1 VALUES (10,'b10');
2831 CREATE PROCEDURE p1 AS
2832 rec1 ROW(a INT, b VARCHAR(32));
2833 BEGIN
2834 SELECT * FROM t1 INTO rec1, rec1;
2835 SELECT rec1.a, rec1.b;
2836 END;
2837 $$
2838 CALL p1();
2839 ERROR 21000: Operand should contain 2 column(s)
2840 DROP TABLE t1;
2841 DROP PROCEDURE p1;
2842 # ROW variables working example
2843 CREATE TABLE t1 (a INT, b VARCHAR(32));
2844 INSERT INTO t1 VALUES (10,'b10');
2845 CREATE PROCEDURE p1 AS
2846 rec1 ROW(a INT, b VARCHAR(32));
2847 BEGIN
2848 SELECT * FROM t1 INTO rec1;
2849 SELECT rec1.a, rec1.b;
2850 END;
2851 $$
2852 CALL p1();
2853 rec1.a rec1.b
2854 10 b10
2855 DROP TABLE t1;
2856 DROP PROCEDURE p1;
2857 # table%ROWTYPE variable with a wrong column count
2858 CREATE TABLE t1 (a INT, b VARCHAR(32));
2859 INSERT INTO t1 VALUES (10,'b10');
2860 CREATE PROCEDURE p1 AS
2861 rec1 t1%ROWTYPE;
2862 BEGIN
2863 SELECT 10,'a','b' FROM t1 INTO rec1;
2864 SELECT rec1.a, rec1.b;
2865 END;
2866 $$
2867 CALL p1();
2868 ERROR 21000: The used SELECT statements have a different number of columns
2869 DROP TABLE t1;
2870 DROP PROCEDURE p1;
2871 # Multiple table%ROWTYPE variables
2872 CREATE TABLE t1 (a INT, b VARCHAR(32));
2873 INSERT INTO t1 VALUES (10,'b10');
2874 CREATE PROCEDURE p1 AS
2875 rec1 t1%ROWTYPE;
2876 BEGIN
2877 SELECT 10,'a' FROM t1 INTO rec1, rec1;
2878 SELECT rec1.a, rec1.b;
2879 END;
2880 $$
2881 CALL p1();
2882 ERROR 21000: Operand should contain 2 column(s)
2883 DROP TABLE t1;
2884 DROP PROCEDURE p1;
2885 # table%ROWTYPE working example
2886 CREATE TABLE t1 (a INT, b VARCHAR(32));
2887 INSERT INTO t1 VALUES (10,'b10');
2888 CREATE PROCEDURE p1 AS
2889 rec1 t1%ROWTYPE;
2890 BEGIN
2891 SELECT * FROM t1 INTO rec1;
2892 SELECT rec1.a, rec1.b;
2893 END;
2894 $$
2895 CALL p1();
2896 rec1.a rec1.b
2897 10 b10
2898 DROP TABLE t1;
2899 DROP PROCEDURE p1;
2900 # cursor%ROWTYPE variable with a wrong column count
2901 CREATE TABLE t1 (a INT, b VARCHAR(32));
2902 INSERT INTO t1 VALUES (10,'b10');
2903 CREATE PROCEDURE p1 AS
2904 CURSOR cur1 IS SELECT 10, 'b0', 'c0';
2905 rec1 cur1%ROWTYPE;
2906 BEGIN
2907 SELECT * FROM t1 INTO rec1;
2908 SELECT rec1.a, rec1.b;
2909 END;
2910 $$
2911 CALL p1();
2912 ERROR 21000: The used SELECT statements have a different number of columns
2913 DROP TABLE t1;
2914 DROP PROCEDURE p1;
2915 # Multiple cursor%ROWTYPE variables
2916 CREATE TABLE t1 (a INT, b VARCHAR(32));
2917 INSERT INTO t1 VALUES (10,'b10');
2918 CREATE PROCEDURE p1 AS
2919 CURSOR cur1 IS SELECT * FROM t1;
2920 rec1 cur1%ROWTYPE;
2921 BEGIN
2922 SELECT * FROM t1 INTO rec1, rec1;
2923 SELECT rec1.a, rec1.b;
2924 END;
2925 $$
2926 CALL p1();
2927 ERROR 21000: Operand should contain 2 column(s)
2928 DROP TABLE t1;
2929 DROP PROCEDURE p1;
2930 # cursor%ROWTYPE working example
2931 CREATE TABLE t1 (a INT, b VARCHAR(32));
2932 INSERT INTO t1 VALUES (10,'b10');
2933 CREATE PROCEDURE p1 AS
2934 CURSOR cur1 IS SELECT * FROM t1;
2935 rec1 cur1%ROWTYPE;
2936 BEGIN
2937 SELECT * FROM t1 INTO rec1;
2938 SELECT rec1.a, rec1.b;
2939 END;
2940 $$
2941 CALL p1();
2942 rec1.a rec1.b
2943 10 b10
2944 DROP TABLE t1;
2945 DROP PROCEDURE p1;
2946 #
2947 # MDEV-12347 Valgrind reports invalid read errors in Item_field_row::element_index_by_name
2948 #
2949 CREATE TABLE t1 (a INT, b ENUM('b0','b1','b12','b3'));
2950 CREATE PROCEDURE p1 AS
2951 BEGIN
2952 DECLARE
2953 rec t1%ROWTYPE;
2954 BEGIN
2955 rec.b:='b0';
2956 SELECT rec.b;
2957 END;
2958 END;
2959 $$
2960 CALL p1();
2961 rec.b
2962 b0
2963 DROP TABLE t1;
2964 DROP PROCEDURE p1;
2965 CREATE TABLE t1 (a INT, b SET('b0','b1','b12','b3'));
2966 CREATE PROCEDURE p1 AS
2967 BEGIN
2968 DECLARE
2969 rec t1%ROWTYPE;
2970 BEGIN
2971 rec.b:='b0';
2972 SELECT rec.b;
2973 END;
2974 END;
2975 $$
2976 CALL p1();
2977 rec.b
2978 b0
2979 DROP TABLE t1;
2980 DROP PROCEDURE p1;
2981 #
2982 # MDEV-13273 Confusion between table alias and ROW type variable
2983 #
2984 CREATE TABLE t1 (c1 INT, c2 INT);
2985 INSERT INTO t1 VALUES (0,0);
2986 CREATE PROCEDURE p1
2987 AS
2988 a INT;
2989 b INT;
2990 BEGIN
2991 -- a.c1 is a table column
2992 SELECT a.c1 INTO b
2993 FROM t1 a
2994 WHERE a.c2 = 0;
2995 SELECT b;
2996 END;
2997 $$
2998 CALL p1;
2999 b
3000 0
3001 DROP PROCEDURE p1;
3002 DROP TABLE t1;
3003 CREATE TABLE t1 (c1 INT, c2 INT);
3004 INSERT INTO t1 VALUES (0,0);
3005 CREATE PROCEDURE p1
3006 AS
3007 a ROW (c1 INT, c2 INT) := ROW(101,102);
3008 b INT;
3009 BEGIN
3010 -- a.c1 is a ROW variable field
3011 SELECT a.c1 INTO b
3012 FROM t1 a
3013 WHERE a.c2 = 102;
3014 SELECT b;
3015 END;
3016 $$
3017 CALL p1;
3018 b
3019 101
3020 DROP PROCEDURE p1;
3021 DROP TABLE t1;
3022 CREATE TABLE t1 (c1 INT, c2 INT);
3023 INSERT INTO t1 VALUES (0,0);
3024 CREATE PROCEDURE p1
3025 AS
3026 a t1%ROWTYPE := ROW (10,20);
3027 b INT;
3028 BEGIN
3029 -- a.c1 is a ROW variable field
3030 SELECT a.c1 INTO b
3031 FROM t1 a
3032 WHERE a.c2 = 20;
3033 SELECT b;
3034 END;
3035 $$
3036 CALL p1;
3037 b
3038 10
3039 DROP PROCEDURE p1;
3040 DROP TABLE t1;
3041 CREATE TABLE t1 (c1 INT, c2 INT);
3042 INSERT INTO t1 VALUES (0,0);
3043 CREATE PROCEDURE p1
3044 AS
3045 CURSOR cur1 IS SELECT * FROM t1;
3046 a cur1%ROWTYPE := ROW (10,20);
3047 b INT;
3048 BEGIN
3049 -- a.c1 is a ROW variable field
3050 SELECT a.c1 INTO b
3051 FROM t1 a
3052 WHERE a.c2 = 20;
3053 SELECT b;
3054 END;
3055 $$
3056 CALL p1;
3057 b
3058 10
3059 DROP PROCEDURE p1;
3060 DROP TABLE t1;
3061 #
3062 # MDEV-13527 Crash when EXPLAIN SELECT .. INTO row_sp_variable.field
3063 #
3064 DECLARE
3065 a ROW(a INT);
3066 BEGIN
3067 EXPLAIN SELECT 1 INTO a.a;
3068 END;
3069 $$
3070 id select_type table type possible_keys key key_len ref rows Extra
3071 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
3072 #
3073 # MDEV-14139 Anchored data types for variables
3074 #
3075 DECLARE
3076 row1 ROW(int11 INT,text1 TEXT);
3077 a_row1 row1%TYPE;
3078 aa_row1 a_row1%TYPE;
3079 BEGIN
3080 CREATE TABLE t1 AS SELECT a_row1.int11 AS int11, a_row1.text1 AS text1;
3081 SHOW CREATE TABLE t1;
3082 DROP TABLE t1;
3083 CREATE TABLE t1 AS SELECT aa_row1.int11 AS int11, aa_row1.text1 AS text1;
3084 SHOW CREATE TABLE t1;
3085 DROP TABLE t1;
3086 END;
3087 $$
3088 Table Create Table
3089 t1 CREATE TABLE "t1" (
3090 "int11" int(11) DEFAULT NULL,
3091 "text1" text DEFAULT NULL
3092 )
3093 Table Create Table
3094 t1 CREATE TABLE "t1" (
3095 "int11" int(11) DEFAULT NULL,
3096 "text1" text DEFAULT NULL
3097 )
3098 #
3099 # Start of 11.7 tests
3100 #
3101 #
3102 # MDEV-12252 ROW data type for stored function return values
3103 #
3104 CREATE FUNCTION f1() RETURN ROW(a INT)
3105 AS
3106 BEGIN
3107 RETURN NULL;
3108 END;
3109 $$
3110 SHOW CREATE FUNCTION f1;
3111 Function sql_mode Create Function character_set_client collation_connection Database Collation
3112 f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"() RETURN row(a int(11))
3113 AS
3114 BEGIN
3115 RETURN NULL;
3116 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
3117 DROP FUNCTION f1;
3118 CREATE FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32))
3119 AS
3120 BEGIN
3121 RETURN ROW(1,'b1');
3122 END;
3123 $$
3124 CREATE PROCEDURE p1col2
3125 AS
3126 r ROW(a INT, b VARCHAR(32)) := f1();
3127 BEGIN
3128 SELECT r.a, r.b;
3129 END;
3130 $$
3131 CREATE PROCEDURE p1col3
3132 AS
3133 r ROW(a INT, b VARCHAR(32),c DOUBLE) := f1();
3134 BEGIN
3135 SELECT r.a, r.b, r.c;
3136 END;
3137 $$
3138 SHOW CREATE FUNCTION f1;
3139 Function sql_mode Create Function character_set_client collation_connection Database Collation
3140 f1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" FUNCTION "f1"() RETURN row(a int(11),b varchar(32) CHARSET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci)
3141 AS
3142 BEGIN
3143 RETURN ROW(1,'b1');
3144 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
3145 CALL p1col2();
3146 r.a r.b
3147 1 b1
3148 CALL p1col3();
3149 ERROR 21000: Operand should contain 3 column(s)
3150 SELECT f1();
3151 ERROR 21000: Operand should contain 1 column(s)
3152 SELECT f1()=ROW(1,'b1') AS c;
3153 c
3154 1
3155 DROP FUNCTION f1;
3156 DROP PROCEDURE p1col2;
3157 DROP PROCEDURE p1col3;
3158 CREATE TABLE t1 (a INT, b VARCHAR(32));
3159 CREATE FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32))
3160 AS
3161 BEGIN
3162 RETURN (SELECT * FROM t1);
3163 END;
3164 $$
3165 CREATE PROCEDURE p1
3166 AS
3167 r ROW(a INT, b VARCHAR(32)) := f1();
3168 BEGIN
3169 SELECT r.a, r.b;
3170 END;
3171 $$
3172 # Testing with no rows
3173 CALL p1;
3174 r.a r.b
3175 NULL NULL
3176 # Testing with one row
3177 INSERT INTO t1 VALUES (1,'b1');
3178 CALL p1;
3179 r.a r.b
3180 1 b1
3181 # Testing with two rows
3182 INSERT INTO t1 VALUES (1,'b1');
3183 CALL p1;
3184 ERROR 21000: Subquery returns more than 1 row
3185 DROP FUNCTION f1;
3186 DROP PROCEDURE p1;
3187 DROP TABLE t1;
3188 #
3189 # End of 11.7 tests
3190 #