Debian Codegraph @

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

raw
1
2 SET sql_mode=ORACLE;
3
4
5 --echo #
6 --echo # MDEV-10914 ROW data type for stored routine variables
7 --echo #
8
9
10
11 --echo #
12 --echo # ROW of ROWs is not supported yet
13 --echo #
14
15 DELIMITER $$;
16 --error ER_PARSE_ERROR
17 CREATE PROCEDURE p1()
18 AS
19 a ROW(a ROW(a INT));
20 BEGIN
21 END;
22 $$
23 DELIMITER ;$$
24
25
26 --echo #
27 --echo # Returning the entire ROW parameter from a function
28 --echo #
29 # TODO: this should probably return an error at compile time
30 DELIMITER $$;
31 CREATE FUNCTION f1(a ROW(a INT, b INT)) RETURN INT
32 AS
33 BEGIN
34 RETURN a;
35 END;
36 $$
37 DELIMITER ;$$
38 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
39 SELECT f1(ROW(10,20));
40 DROP FUNCTION f1;
41
42
43
44 --echo #
45 --echo # ROW as an SP parameter
46 --echo #
47
48 DELIMITER $$;
49 CREATE FUNCTION f1(a ROW(a INT,b INT)) RETURN INT
50 AS
51 BEGIN
52 RETURN a.b;
53 END;
54 $$
55 CREATE PROCEDURE p1()
56 AS
57 a ROW(a INT,b INT):=(11,21);
58 BEGIN
59 SELECT f1(a);
60 END;
61 $$
62 DELIMITER ;$$
63 SELECT f1(ROW(10,20));
64 --error ER_OPERAND_COLUMNS
65 SELECT f1(10);
66 --error ER_OPERAND_COLUMNS
67 SELECT f1(ROW(10,20,30));
68 CALL p1();
69 DROP PROCEDURE p1;
70 DROP FUNCTION f1;
71
72 DELIMITER $$;
73 CREATE PROCEDURE p1(a ROW(a INT,b INT))
74 AS
75 BEGIN
76 SELECT a.a, a.b;
77 END;
78 $$
79 DELIMITER ;$$
80 CALL p1(ROW(10,20));
81 --error ER_OPERAND_COLUMNS
82 CALL p1(10);
83 --error ER_OPERAND_COLUMNS
84 CALL p1(ROW(10,20,30));
85 DROP PROCEDURE p1;
86
87
88 --echo #
89 --echo # ROW as an SP OUT parameter
90 --echo #
91
92 DELIMITER $$;
93 CREATE PROCEDURE p1(a OUT ROW(a INT,b INT))
94 AS
95 BEGIN
96 a.a:=10;
97 a.b:=20;
98 END;
99 $$
100 CREATE PROCEDURE p2
101 AS
102 a ROW(a INT,b INT):=(11,21);
103 BEGIN
104 CALL p1(a);
105 SELECT a.a,a.b;
106 END;
107 $$
108 DELIMITER ;$$
109 CALL p2();
110 DROP PROCEDURE p2;
111 DROP PROCEDURE p1;
112
113
114 --echo #
115 --echo # Diplicate row field
116 --echo #
117 DELIMITER $$;
118 --error ER_DUP_FIELDNAME
119 CREATE PROCEDURE p1()
120 AS
121 a ROW (a INT, a DOUBLE);
122 BEGIN
123 SELECT a.a;
124 END;
125 $$
126 DELIMITER ;$$
127
128
129 --echo #
130 --echo # Bad scalar default value
131 --echo #
132 DELIMITER $$;
133 CREATE PROCEDURE p1()
134 AS
135 a ROW (a INT, b DOUBLE):= 1;
136 BEGIN
137 SELECT a.a;
138 END;
139 $$
140 DELIMITER ;$$
141 --error ER_OPERAND_COLUMNS
142 CALL p1();
143 DROP PROCEDURE p1;
144
145 --echo #
146 --echo # Bad ROW default value with a wrong number of fields
147 --echo #
148
149 DELIMITER $$;
150 CREATE PROCEDURE p1()
151 AS
152 a ROW (a INT, b DOUBLE):= ROW(1,2,3);
153 BEGIN
154 SELECT a.a;
155 END;
156 $$
157 DELIMITER ;$$
158 --error ER_OPERAND_COLUMNS
159 CALL p1();
160 DROP PROCEDURE p1;
161
162
163 --echo #
164 --echo # Scalar variable vs table alias cause no ambiguity
165 --echo #
166
167 DELIMITER $$;
168 CREATE PROCEDURE p1()
169 AS
170 a INT;
171 BEGIN
172 -- a.x is a table column here (not a row variable field)
173 SELECT a.x FROM a;
174 SELECT a.x FROM t1 a;
175 END;
176 $$
177 DELIMITER ;$$
178 DROP PROCEDURE p1;
179
180 --echo #
181 --echo # Using the entire ROW variable in select list
182 --echo #
183
184 DELIMITER $$;
185 CREATE PROCEDURE p1()
186 AS
187 a ROW (a INT);
188 BEGIN
189 SELECT a;
190 END;
191 $$
192 DELIMITER ;$$
193 --error ER_OPERAND_COLUMNS
194 CALL p1();
195 DROP PROCEDURE p1;
196
197 DELIMITER $$;
198 CREATE PROCEDURE p1()
199 AS
200 a ROW (a INT,b INT);
201 BEGIN
202 SELECT a;
203 END;
204 $$
205 DELIMITER ;$$
206 --error ER_OPERAND_COLUMNS
207 CALL p1();
208 DROP PROCEDURE p1;
209
210
211 --echo #
212 --echo # Using the entire ROW variable in functions
213 --echo #
214
215 DELIMITER $$;
216 CREATE PROCEDURE p1()
217 AS
218 a ROW (a INT);
219 BEGIN
220 SELECT COALESCE(a);
221 END;
222 $$
223 DELIMITER ;$$
224 --error ER_OPERAND_COLUMNS
225 CALL p1();
226 DROP PROCEDURE p1;
227
228
229 DELIMITER $$;
230 CREATE PROCEDURE p1()
231 AS
232 a ROW (a INT,b INT);
233 BEGIN
234 SELECT COALESCE(a);
235 END;
236 $$
237 DELIMITER ;$$
238 --error ER_OPERAND_COLUMNS
239 CALL p1();
240 DROP PROCEDURE p1;
241
242
243 DELIMITER $$;
244 CREATE PROCEDURE p1()
245 AS
246 a ROW (a INT);
247 BEGIN
248 SELECT a+1;
249 END;
250 $$
251 DELIMITER ;$$
252 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
253 CALL p1();
254 DROP PROCEDURE p1;
255
256
257 DELIMITER $$;
258 CREATE PROCEDURE p1()
259 AS
260 a ROW (a INT,b INT);
261 BEGIN
262 SELECT a+1;
263 END;
264 $$
265 DELIMITER ;$$
266 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
267 CALL p1();
268 DROP PROCEDURE p1;
269
270
271 --echo #
272 --echo # Comparing the entire ROW to a scalar value
273 --echo #
274
275 DELIMITER $$;
276 CREATE PROCEDURE p1()
277 AS
278 a ROW (a INT,b INT);
279 BEGIN
280 SELECT a=1;
281 END;
282 $$
283 DELIMITER ;$$
284 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
285 CALL p1();
286 DROP PROCEDURE p1;
287
288
289 DELIMITER $$;
290 CREATE PROCEDURE p1()
291 AS
292 a ROW (a INT,b INT);
293 BEGIN
294 SELECT 1=a;
295 END;
296 $$
297 DELIMITER ;$$
298 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
299 CALL p1();
300 DROP PROCEDURE p1;
301
302
303 --echo #
304 --echo # Passing the entire ROW to a stored function
305 --echo #
306
307 DELIMITER $$;
308 CREATE FUNCTION f1(a INT) RETURN INT
309 AS
310 BEGIN
311 RETURN a;
312 END;
313 $$
314 CREATE PROCEDURE p1()
315 AS
316 a ROW (a INT,b INT);
317 BEGIN
318 SELECT f1(a);
319 END;
320 $$
321 DELIMITER ;$$
322 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
323 CALL p1();
324 DROP PROCEDURE p1;
325 DROP FUNCTION f1;
326
327
328 DELIMITER $$;
329 CREATE FUNCTION f1(a INT) RETURN INT
330 AS
331 BEGIN
332 RETURN a;
333 END;
334 $$
335 CREATE PROCEDURE p1()
336 AS
337 a ROW (a INT);
338 BEGIN
339 SELECT f1(a);
340 END;
341 $$
342 DELIMITER ;$$
343 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
344 CALL p1();
345 DROP PROCEDURE p1;
346 DROP FUNCTION f1;
347
348
349 --echo #
350 --echo # Assigning a scalar value to a ROW variable with 1 column
351 --echo #
352
353 DELIMITER $$;
354 CREATE OR REPLACE PROCEDURE p1
355 AS
356 rec ROW(a INT);
357 BEGIN
358 rec:=1;
359 END;
360 $$
361 DELIMITER ;$$
362 --error ER_OPERAND_COLUMNS
363 CALL p1();
364 DROP PROCEDURE p1;
365
366
367 --echo #
368 --echo # Assigning a scalar value to a ROW variable with 2 columns
369 --echo #
370
371 DELIMITER $$;
372 CREATE OR REPLACE PROCEDURE p1
373 AS
374 rec ROW(a INT,b INT);
375 BEGIN
376 rec:=1;
377 END;
378 $$
379 DELIMITER ;$$
380 --error ER_OPERAND_COLUMNS
381 CALL p1();
382 DROP PROCEDURE p1;
383
384
385 --echo #
386 --echo # Assigning a ROW value to a ROW variable with different number of columns
387 --echo #
388
389 DELIMITER $$;
390 CREATE OR REPLACE PROCEDURE p1
391 AS
392 rec ROW(a INT,b INT);
393 BEGIN
394 rec:=ROW(1,2,3);
395 END;
396 $$
397 DELIMITER ;$$
398 --error ER_OPERAND_COLUMNS
399 CALL p1();
400 DROP PROCEDURE p1;
401
402 --echo #
403 --echo # Returning the entire ROW from a function is not supported yet
404 --echo # This syntax would be needed: SELECT f1().x FROM DUAL;
405 --echo #
406 DELIMITER $$;
407 CREATE FUNCTION f1(a INT) RETURN INT
408 AS
409 rec ROW(a INT);
410 BEGIN
411 RETURN rec;
412 END;
413 $$
414 DELIMITER ;$$
415 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
416 SELECT f1(10);
417 DROP FUNCTION f1;
418
419
420 --echo #
421 --echo # Using the entire ROW in SELECT..CREATE
422 --echo #
423
424 DELIMITER $$;
425 CREATE PROCEDURE p1
426 AS
427 rec ROW(a INT,b INT);
428 BEGIN
429 CREATE TABLE t1 AS SELECT rec;
430 END;
431 $$
432 DELIMITER ;$$
433 --error ER_OPERAND_COLUMNS
434 CALL p1();
435 DROP PROCEDURE p1;
436
437
438 --echo #
439 --echo # Using the entire ROW in LIMIT
440 --echo #
441 DELIMITER $$;
442 --error ER_WRONG_SPVAR_TYPE_IN_LIMIT
443 CREATE PROCEDURE p1()
444 AS
445 rec ROW(a INT);
446 BEGIN
447 rec.a:= '10';
448 SELECT * FROM t1 LIMIT rec;
449 END;
450 $$
451 DELIMITER ;$$
452
453
454 --echo #
455 --echo # Setting ROW fields using a SET command
456 --echo #
457 DELIMITER $$;
458 CREATE OR REPLACE PROCEDURE p1
459 AS
460 rec ROW(a INT,b DOUBLE,c VARCHAR(10));
461 a INT;
462 BEGIN
463 SET @a= 10, rec.a=10, rec.b=20, rec.c= 'test', a= 5;
464 SELECT rec.a, rec.b, rec.c, a;
465 END;
466 $$
467 DELIMITER ;$$
468 CALL p1();
469 DROP PROCEDURE p1;
470
471
472 --echo #
473 --echo # Assigning a ROW variable from a ROW value
474 --echo #
475 DELIMITER $$;
476 CREATE PROCEDURE p1
477 AS
478 rec ROW(a INT,b INT);
479 BEGIN
480 rec:=ROW(1,2);
481 SELECT rec.a, rec.b;
482 END;
483 $$
484 DELIMITER ;$$
485 CALL p1();
486 DROP PROCEDURE p1;
487
488
489 --echo #
490 --echo # Assigning a ROW variable from another ROW value
491 --echo #
492 DELIMITER $$;
493 CREATE PROCEDURE p1
494 AS
495 rec1 ROW(a INT,b INT);
496 rec2 ROW(a INT,b INT);
497 BEGIN
498 rec1:=ROW(1,2);
499 rec2:=rec1;
500 SELECT rec2.a, rec2.b;
501 END;
502 $$
503 DELIMITER ;$$
504 CALL p1();
505 DROP PROCEDURE p1;
506
507
508 --echo #
509 --echo # Comparing a ROW variable to a ROW() function
510 --echo #
511
512 DELIMITER $$;
513 CREATE OR REPLACE PROCEDURE p1
514 AS
515 rec ROW(a INT,b INT);
516 BEGIN
517 rec.a:= 1;
518 rec.b:= 2;
519 SELECT rec=(0,0), rec=ROW(0,0), (0,0)=rec, ROW(0,0)=rec;
520 SELECT rec=(1,2), rec=ROW(1,2), (1,2)=rec, ROW(1,2)=rec;
521 SELECT rec=(NULL,0), rec=ROW(NULL,0);
522 SELECT rec=(NULL,2), rec=ROW(NULL,2);
523 SELECT rec<>(0,0), rec<>ROW(0,0);
524 SELECT rec<>(1,2), rec<>ROW(1,2);
525 SELECT rec<>(NULL,0), rec<>ROW(NULL,0);
526 SELECT rec<>(NULL,2), rec<>ROW(NULL,2);
527 SELECT rec IN ((0,0)), rec IN (ROW(0,0));
528 SELECT rec IN ((1,2)), rec IN (ROW(1,2));
529 SELECT rec IN ((0,NULL),(1,2));
530 SELECT rec NOT IN ((0,NULL),(1,1));
531 SELECT rec NOT IN ((1,NULL),(1,1));
532 END;
533 $$
534 DELIMITER ;$$
535 CALL p1();
536 DROP PROCEDURE p1;
537
538
539 --echo #
540 --echo # Comparing a ROW variable to another ROW variable
541 --echo #
542
543 DELIMITER $$;
544 CREATE OR REPLACE PROCEDURE p1
545 AS
546 rec1,rec2,rec3 ROW(a INT,b INT);
547 BEGIN
548 rec1.a:= 1;
549 rec1.b:= 2;
550 rec2.a:= 11;
551 rec2.b:= 12;
552 rec3.a:= 11;
553 rec3.b:= 12;
554 SELECT rec1=rec2, rec2=rec1, rec2=rec3, rec3=rec2;
555 END;
556 $$
557 DELIMITER ;$$
558 CALL p1();
559 DROP PROCEDURE p1;
560
561
562 --echo #
563 --echo # Referencing a non-existing row variable
564 --echo #
565 DELIMITER $$;
566 --error ER_UNKNOWN_STRUCTURED_VARIABLE
567 CREATE PROCEDURE p1()
568 AS
569 BEGIN
570 SET a.b=1;
571 END;
572 $$
573 DELIMITER ;$$
574
575 DELIMITER $$;
576 --error ER_UNKNOWN_STRUCTURED_VARIABLE
577 CREATE PROCEDURE p1()
578 AS
579 BEGIN
580 a.b:=1;
581 END;
582 $$
583 DELIMITER ;$$
584
585
586 --echo #
587 --echo # Referencing a non-existing row field
588 --echo #
589 DELIMITER $$;
590 --error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
591 CREATE PROCEDURE p1()
592 AS
593 a ROW(a INT,b INT);
594 BEGIN
595 SELECT a.c FROM t1;
596 END;
597 $$
598 DELIMITER ;$$
599
600
601 --echo #
602 --echo # ROW and scalar variables with the same name shadowing each other
603 --echo #
604 DELIMITER $$;
605 CREATE PROCEDURE p1()
606 AS
607 a ROW(a INT);
608 BEGIN
609 a.a:=100;
610 DECLARE
611 a INT:= 200;
612 BEGIN
613 SELECT a;
614 DECLARE
615 a ROW(a INT);
616 BEGIN
617 a.a:=300;
618 SELECT a.a;
619 END;
620 SELECT a;
621 END;
622 SELECT a.a;
623 END;
624 $$
625 DELIMITER ;$$
626 CALL p1();
627 DROP PROCEDURE p1;
628
629
630 --echo #
631 --echo # ROW with good default values
632 --echo #
633 DELIMITER $$;
634 CREATE PROCEDURE p1()
635 AS
636 a ROW(a INT,b INT):= (10,20);
637 b ROW(a INT,b INT):= (11,21);
638 c ROW(a INT,b INT):= a;
639 BEGIN
640 SELECT a.a, a.b, b.a, b.b, c.a, c.b FROM DUAL;
641 END;
642 $$
643 DELIMITER ;$$
644 CALL p1;
645 DROP PROCEDURE p1;
646
647
648 --echo #
649 --echo # ROW in WHERE clause
650 --echo #
651
652 CREATE TABLE t1 (a INT,b INT);
653 INSERT INTO t1 VALUES (10,20);
654 DELIMITER $$;
655 CREATE PROCEDURE p1()
656 AS
657 rec ROW(a INT,b INT):=ROW(10,20);
658 BEGIN
659 SELECT * FROM t1 WHERE rec=ROW(a,b);
660 SELECT * FROM t1 WHERE ROW(a,b)=rec;
661 SELECT * FROM t1 WHERE rec=ROW(10,20);
662 SELECT * FROM t1 WHERE ROW(10,20)=rec;
663 END;
664 $$
665 DELIMITER ;$$
666 CALL p1();
667 DROP TABLE t1;
668 DROP PROCEDURE p1;
669
670
671 --echo #
672 --echo # ROW fields in WHERE clause
673 --echo #
674
675 CREATE TABLE t1 (a INT);
676 INSERT INTO t1 VALUES (10),(20);
677 DELIMITER $$;
678 CREATE PROCEDURE p1()
679 AS
680 rec ROW(a INT);
681 BEGIN
682 rec.a:= 10;
683 SELECT * FROM t1 WHERE a=rec.a;
684 END;
685 $$
686 DELIMITER ;$$
687 CALL p1();
688 DROP TABLE t1;
689 DROP PROCEDURE p1;
690
691
692 --echo #
693 --echo # ROW fields in HAVING clause
694 --echo #
695
696 CREATE TABLE t1 (a INT);
697 INSERT INTO t1 VALUES (10),(20);
698 DELIMITER $$;
699 CREATE PROCEDURE p1()
700 AS
701 rec ROW(a INT);
702 BEGIN
703 rec.a:= 10;
704 SELECT * FROM t1 HAVING a=rec.a;
705 SELECT * FROM t1 HAVING MIN(a)=rec.a;
706 END;
707 $$
708 DELIMITER ;$$
709 CALL p1();
710 DROP TABLE t1;
711 DROP PROCEDURE p1;
712
713
714 --echo #
715 --echo # ROW fields in LIMIT clause
716 --echo #
717
718 CREATE TABLE t1 (a INT);
719 --error ER_SP_UNDECLARED_VAR
720 SELECT 1 FROM t1 LIMIT t1.a;
721 DROP TABLE t1;
722
723 CREATE TABLE t1 (a INT);
724 INSERT INTO t1 VALUES (10),(20);
725 DELIMITER $$;
726 CREATE PROCEDURE p1()
727 AS
728 rec ROW(a INT);
729 BEGIN
730 rec.a:= 10;
731 SELECT * FROM t1 LIMIT rec.a;
732 END;
733 $$
734 DELIMITER ;$$
735 CALL p1();
736 DROP TABLE t1;
737 DROP PROCEDURE p1;
738
739
740 DELIMITER $$;
741 --error ER_WRONG_SPVAR_TYPE_IN_LIMIT
742 CREATE PROCEDURE p1()
743 AS
744 rec ROW(a VARCHAR(10));
745 BEGIN
746 rec.a:= '10';
747 SELECT * FROM t1 LIMIT rec.a;
748 END;
749 $$
750 DELIMITER ;$$
751
752
753 --echo #
754 --echo # ROW fields in select list
755 --echo #
756 CREATE TABLE t1 (a INT);
757 INSERT INTO t1 VALUES (10),(20);
758 DELIMITER $$;
759 CREATE PROCEDURE p1()
760 AS
761 t1 ROW(a INT);
762 BEGIN
763 t1.a:= 10;
764 SELECT t1.a, 'This is the variable t1.a value, rather than the column t1.a' AS comm FROM t1;
765 SELECT t1.a, t2.a, t1.a+t2.a FROM t1 t2;
766 END;
767 $$
768 DELIMITER ;$$
769 CALL p1();
770 DROP TABLE t1;
771 DROP PROCEDURE p1;
772
773
774 --echo #
775 --echo # ROW fields as insert values
776 --echo #
777
778 CREATE TABLE t1 (a INT, b VARCHAR(10));
779 DELIMITER $$;
780 CREATE PROCEDURE p1()
781 AS
782 rec ROW(a INT, b VARCHAR(10));
783 BEGIN
784 rec.a:= 10;
785 rec.b:= 'test';
786 INSERT INTO t1 VALUES (rec.a, rec.b);
787 END;
788 $$
789 DELIMITER ;$$
790 CALL p1();
791 SELECT * FROM t1;
792 DROP TABLE t1;
793 DROP PROCEDURE p1;
794
795
796 --echo #
797 --echo # ROW fields as SP out parameters
798 --echo #
799
800 DELIMITER $$;
801 CREATE PROCEDURE p1(a OUT INT, b OUT VARCHAR)
802 AS
803 BEGIN
804 a:= 10;
805 b:= 'test';
806 END;
807 $$
808 CREATE PROCEDURE p2
809 AS
810 rec ROW(a INT, b VARCHAR(10));
811 BEGIN
812 CALL p1(rec.a, rec.b);
813 SELECT rec.a, rec.b;
814 END;
815 $$
816 DELIMITER ;$$
817 CALL p2;
818 DROP PROCEDURE p1;
819 DROP PROCEDURE p2;
820
821
822 --echo #
823 --echo # ROW fields as dynamic SQL out parameters
824 --echo #
825
826 DELIMITER $$;
827 CREATE PROCEDURE p1(a OUT INT, b OUT VARCHAR)
828 AS
829 BEGIN
830 a:= 20;
831 b:= 'test-dynamic-sql';
832 END;
833 $$
834 CREATE PROCEDURE p2
835 AS
836 rec ROW(a INT, b VARCHAR(30));
837 BEGIN
838 EXECUTE IMMEDIATE 'CALL p1(?,?)' USING rec.a, rec.b;
839 SELECT rec.a, rec.b;
840 END;
841 $$
842 DELIMITER ;$$
843 CALL p2;
844 DROP PROCEDURE p1;
845 DROP PROCEDURE p2;
846
847
848 --echo #
849 --echo # ROW fields as SELECT..INTO targets
850 --echo #
851
852 --enable_prepare_warnings
853
854 DELIMITER $$;
855 CREATE PROCEDURE p1
856 AS
857 rec ROW(a INT, b VARCHAR(10));
858 BEGIN
859 SELECT 10,'test' INTO rec.a,rec.b;
860 SELECT rec.a, rec.b;
861 END;
862 $$
863 DELIMITER ;$$
864 CALL p1;
865 DROP PROCEDURE p1;
866
867 --disable_prepare_warnings
868
869 --echo #
870 --echo # Implicit default NULL handling
871 --echo #
872
873 DELIMITER $$;
874 CREATE PROCEDURE p1
875 AS
876 rec ROW(a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,0), e TIME, f DATETIME);
877 BEGIN
878 SELECT rec.a, rec.b, rec.c, rec.d, rec.e, rec.f FROM DUAL;
879 END;
880 $$
881 DELIMITER ;$$
882 CALL p1();
883 DROP PROCEDURE p1;
884
885
886 --echo #
887 --echo # NULL handling
888 --echo #
889
890 DELIMITER $$;
891 CREATE PROCEDURE p1
892 AS
893 rec1 ROW(a INT, b VARCHAR(10)):=(NULL,NULL);
894 rec2 ROW(a INT, b VARCHAR(10)):=rec1;
895 BEGIN
896 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
897
898 rec1:= (10,20);
899 rec2:= rec1;
900 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
901
902 rec1:= (NULL,20);
903 rec2:= rec1;
904 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
905
906 rec1:= (10,NULL);
907 rec2:= rec1;
908 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
909
910 rec1:= (NULL,NULL);
911 rec2:= rec1;
912 SELECT rec1.a, rec1.b, rec2.a, rec2.b;
913 END;
914 $$
915 DELIMITER ;$$
916 CALL p1;
917 DROP PROCEDURE p1;
918
919
920 --echo #
921 --echo # Testing multiple ROW variable declarations
922 --echo # This makes sure that fill_field_definitions() is called only once
923 --echo # per a ROW field, so create length is not converted to internal length
924 --echo # multiple times.
925 --echo #
926 DELIMITER $$;
927 CREATE PROCEDURE p1
928 AS
929 rec1, rec2, rec3 ROW(a VARCHAR(10) CHARACTER SET utf8);
930 BEGIN
931 CREATE TABLE t1 AS SELECT rec1.a, rec2.a, rec3.a;
932 END;
933 $$
934 DELIMITER ;$$
935 CALL p1();
936 SHOW CREATE TABLE t1;
937 DROP TABLE t1;
938 DROP PROCEDURE p1;
939
940 --echo #
941 --echo # INT
942 --echo #
943
944 --let type=INT
945 --source sp-row-vs-var.inc
946
947 --let type=INT(1)
948 --source sp-row-vs-var.inc
949
950 --let type=INT(2)
951 --source sp-row-vs-var.inc
952
953 --let type=INT(3)
954 --source sp-row-vs-var.inc
955
956 --let type=INT(4)
957 --source sp-row-vs-var.inc
958
959 --let type=INT(5)
960 --source sp-row-vs-var.inc
961
962 --let type=INT(6)
963 --source sp-row-vs-var.inc
964
965 --let type=INT(7)
966 --source sp-row-vs-var.inc
967
968 --let type=INT(8)
969 --source sp-row-vs-var.inc
970
971 --let type=INT(9)
972 --source sp-row-vs-var.inc
973
974 --let type=INT(10)
975 --source sp-row-vs-var.inc
976
977 --let type=INT(11)
978 --source sp-row-vs-var.inc
979
980 --let type=INT(12)
981 --source sp-row-vs-var.inc
982
983 --let type=INT(13)
984 --source sp-row-vs-var.inc
985
986 --let type=INT(14)
987 --source sp-row-vs-var.inc
988
989 --let type=INT(20)
990 --source sp-row-vs-var.inc
991
992 --let type=INT(21)
993 --source sp-row-vs-var.inc
994
995
996 --echo #
997 --echo # TINYINT
998 --echo #
999
1000 --let type=TINYINT
1001 --source sp-row-vs-var.inc
1002
1003 --let type=TINYINT(1)
1004 --source sp-row-vs-var.inc
1005
1006 --let type=TINYINT(2)
1007 --source sp-row-vs-var.inc
1008
1009 --let type=TINYINT(3)
1010 --source sp-row-vs-var.inc
1011
1012 --let type=TINYINT(4)
1013 --source sp-row-vs-var.inc
1014
1015 --let type=TINYINT(5)
1016 --source sp-row-vs-var.inc
1017
1018 --let type=TINYINT(6)
1019 --source sp-row-vs-var.inc
1020
1021 --let type=TINYINT(7)
1022 --source sp-row-vs-var.inc
1023
1024 --let type=TINYINT(8)
1025 --source sp-row-vs-var.inc
1026
1027 --let type=TINYINT(9)
1028 --source sp-row-vs-var.inc
1029
1030 --let type=TINYINT(10)
1031 --source sp-row-vs-var.inc
1032
1033 --let type=TINYINT(11)
1034 --source sp-row-vs-var.inc
1035
1036 --let type=TINYINT(12)
1037 --source sp-row-vs-var.inc
1038
1039 --let type=TINYINT(13)
1040 --source sp-row-vs-var.inc
1041
1042 --let type=TINYINT(14)
1043 --source sp-row-vs-var.inc
1044
1045 --let type=TINYINT(20)
1046 --source sp-row-vs-var.inc
1047
1048 --let type=TINYINT(21)
1049 --source sp-row-vs-var.inc
1050
1051 --echo #
1052 --echo # SMALLINT
1053 --echo #
1054
1055 --let type=SMALLINT
1056 --source sp-row-vs-var.inc
1057
1058 --let type=SMALLINT(1)
1059 --source sp-row-vs-var.inc
1060
1061 --let type=SMALLINT(2)
1062 --source sp-row-vs-var.inc
1063
1064 --let type=SMALLINT(3)
1065 --source sp-row-vs-var.inc
1066
1067 --let type=SMALLINT(4)
1068 --source sp-row-vs-var.inc
1069
1070 --let type=SMALLINT(5)
1071 --source sp-row-vs-var.inc
1072
1073 --let type=SMALLINT(6)
1074 --source sp-row-vs-var.inc
1075
1076 --let type=SMALLINT(7)
1077 --source sp-row-vs-var.inc
1078
1079 --let type=SMALLINT(8)
1080 --source sp-row-vs-var.inc
1081
1082 --let type=SMALLINT(9)
1083 --source sp-row-vs-var.inc
1084
1085 --let type=SMALLINT(10)
1086 --source sp-row-vs-var.inc
1087
1088 --let type=SMALLINT(11)
1089 --source sp-row-vs-var.inc
1090
1091 --let type=SMALLINT(12)
1092 --source sp-row-vs-var.inc
1093
1094 --let type=SMALLINT(13)
1095 --source sp-row-vs-var.inc
1096
1097 --let type=SMALLINT(14)
1098 --source sp-row-vs-var.inc
1099
1100 --let type=SMALLINT(20)
1101 --source sp-row-vs-var.inc
1102
1103 --let type=SMALLINT(21)
1104 --source sp-row-vs-var.inc
1105
1106
1107 --echo #
1108 --echo # MEDIUMINT
1109 --echo #
1110
1111 --let type=MEDIUMINT
1112 --source sp-row-vs-var.inc
1113
1114 --let type=MEDIUMINT(1)
1115 --source sp-row-vs-var.inc
1116
1117 --let type=MEDIUMINT(2)
1118 --source sp-row-vs-var.inc
1119
1120 --let type=MEDIUMINT(3)
1121 --source sp-row-vs-var.inc
1122
1123 --let type=MEDIUMINT(4)
1124 --source sp-row-vs-var.inc
1125
1126 --let type=MEDIUMINT(5)
1127 --source sp-row-vs-var.inc
1128
1129 --let type=MEDIUMINT(6)
1130 --source sp-row-vs-var.inc
1131
1132 --let type=MEDIUMINT(7)
1133 --source sp-row-vs-var.inc
1134
1135 --let type=MEDIUMINT(8)
1136 --source sp-row-vs-var.inc
1137
1138 --let type=MEDIUMINT(9)
1139 --source sp-row-vs-var.inc
1140
1141 --let type=MEDIUMINT(10)
1142 --source sp-row-vs-var.inc
1143
1144 --let type=MEDIUMINT(11)
1145 --source sp-row-vs-var.inc
1146
1147 --let type=MEDIUMINT(12)
1148 --source sp-row-vs-var.inc
1149
1150 --let type=MEDIUMINT(13)
1151 --source sp-row-vs-var.inc
1152
1153 --let type=MEDIUMINT(14)
1154 --source sp-row-vs-var.inc
1155
1156 --let type=MEDIUMINT(20)
1157 --source sp-row-vs-var.inc
1158
1159 --let type=MEDIUMINT(21)
1160 --source sp-row-vs-var.inc
1161
1162
1163 --echo #
1164 --echo # BIGINT
1165 --echo #
1166
1167 --let type=BIGINT
1168 --source sp-row-vs-var.inc
1169
1170 --let type=BIGINT(1)
1171 --source sp-row-vs-var.inc
1172
1173 --let type=BIGINT(2)
1174 --source sp-row-vs-var.inc
1175
1176 --let type=BIGINT(3)
1177 --source sp-row-vs-var.inc
1178
1179 --let type=BIGINT(4)
1180 --source sp-row-vs-var.inc
1181
1182 --let type=BIGINT(5)
1183 --source sp-row-vs-var.inc
1184
1185 --let type=BIGINT(6)
1186 --source sp-row-vs-var.inc
1187
1188 --let type=BIGINT(7)
1189 --source sp-row-vs-var.inc
1190
1191 --let type=BIGINT(8)
1192 --source sp-row-vs-var.inc
1193
1194 --let type=BIGINT(9)
1195 --source sp-row-vs-var.inc
1196
1197 --let type=BIGINT(10)
1198 --source sp-row-vs-var.inc
1199
1200 --let type=BIGINT(11)
1201 --source sp-row-vs-var.inc
1202
1203 --let type=BIGINT(12)
1204 --source sp-row-vs-var.inc
1205
1206 --let type=BIGINT(13)
1207 --source sp-row-vs-var.inc
1208
1209 --let type=BIGINT(14)
1210 --source sp-row-vs-var.inc
1211
1212 --let type=BIGINT(20)
1213 --source sp-row-vs-var.inc
1214
1215 --let type=BIGINT(21)
1216 --source sp-row-vs-var.inc
1217
1218
1219 --echo #
1220 --echo # DOUBLE
1221 --echo #
1222
1223 --let type=DOUBLE
1224 --source sp-row-vs-var.inc
1225
1226 --let type=DOUBLE(30,1)
1227 --source sp-row-vs-var.inc
1228
1229 --let type=DOUBLE(30,2)
1230 --source sp-row-vs-var.inc
1231
1232 --let type=DOUBLE(30,3)
1233 --source sp-row-vs-var.inc
1234
1235 --let type=DOUBLE(30,4)
1236 --source sp-row-vs-var.inc
1237
1238 --let type=DOUBLE(30,5)
1239 --source sp-row-vs-var.inc
1240
1241 --let type=DOUBLE(30,6)
1242 --source sp-row-vs-var.inc
1243
1244 --let type=DOUBLE(30,7)
1245 --source sp-row-vs-var.inc
1246
1247 --let type=DOUBLE(30,8)
1248 --source sp-row-vs-var.inc
1249
1250 --let type=DOUBLE(30,9)
1251 --source sp-row-vs-var.inc
1252
1253 --let type=DOUBLE(30,10)
1254 --source sp-row-vs-var.inc
1255
1256 --let type=DOUBLE(30,11)
1257 --source sp-row-vs-var.inc
1258
1259 --let type=DOUBLE(30,12)
1260 --source sp-row-vs-var.inc
1261
1262 --let type=DOUBLE(30,13)
1263 --source sp-row-vs-var.inc
1264
1265 --let type=DOUBLE(30,14)
1266 --source sp-row-vs-var.inc
1267
1268 --let type=DOUBLE(30,20)
1269 --source sp-row-vs-var.inc
1270
1271 --let type=DOUBLE(30,21)
1272 --source sp-row-vs-var.inc
1273
1274 --echo #
1275 --echo # VARCHAR
1276 --echo #
1277
1278 --let type=CHAR
1279 --source sp-row-vs-var.inc
1280
1281 --let type=BINARY
1282 --source sp-row-vs-var.inc
1283
1284 --let type=CHAR(1)
1285 --source sp-row-vs-var.inc
1286
1287 --let type=CHAR(10)
1288 --source sp-row-vs-var.inc
1289
1290 --let type=NCHAR(10)
1291 --source sp-row-vs-var.inc
1292
1293 --let type=BINARY(10)
1294 --source sp-row-vs-var.inc
1295
1296 --let type=VARBINARY(10)
1297 --source sp-row-vs-var.inc
1298
1299 --let type=VARCHAR(10)
1300 --source sp-row-vs-var.inc
1301
1302 --let type=VARCHAR(10) CHARACTER SET utf8
1303 --source sp-row-vs-var.inc
1304
1305 --let type=VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin
1306 --source sp-row-vs-var.inc
1307
1308 --echo #
1309 --echo # TIME
1310 --echo #
1311
1312 --let type=TIME
1313 --source sp-row-vs-var.inc
1314
1315 --let type=TIME(1)
1316 --source sp-row-vs-var.inc
1317
1318 --let type=TIME(2)
1319 --source sp-row-vs-var.inc
1320
1321 --let type=TIME(3)
1322 --source sp-row-vs-var.inc
1323
1324 --let type=TIME(4)
1325 --source sp-row-vs-var.inc
1326
1327 --let type=TIME(5)
1328 --source sp-row-vs-var.inc
1329
1330 --let type=TIME(6)
1331 --source sp-row-vs-var.inc
1332
1333 --echo #
1334 --echo # DATETIME
1335 --echo #
1336
1337 --let type=DATETIME
1338 --source sp-row-vs-var.inc
1339
1340 --let type=DATETIME(1)
1341 --source sp-row-vs-var.inc
1342
1343 --let type=DATETIME(2)
1344 --source sp-row-vs-var.inc
1345
1346 --let type=DATETIME(3)
1347 --source sp-row-vs-var.inc
1348
1349 --let type=DATETIME(4)
1350 --source sp-row-vs-var.inc
1351
1352 --let type=DATETIME(5)
1353 --source sp-row-vs-var.inc
1354
1355 --let type=DATETIME(6)
1356 --source sp-row-vs-var.inc
1357
1358
1359 --echo #
1360 --echo # LOB
1361 --echo #
1362
1363 --let type=TEXT
1364 --source sp-row-vs-var.inc
1365
1366 --let type=TINYTEXT
1367 --source sp-row-vs-var.inc
1368
1369 --let type=MEDIUMTEXT
1370 --source sp-row-vs-var.inc
1371
1372 --let type=LONGTEXT
1373 --source sp-row-vs-var.inc
1374
1375 --let type=TEXT CHARACTER SET utf8
1376 --source sp-row-vs-var.inc
1377
1378 --let type=TINYTEXT CHARACTER SET utf8
1379 --source sp-row-vs-var.inc
1380
1381 --let type=MEDIUMTEXT CHARACTER SET utf8
1382 --source sp-row-vs-var.inc
1383
1384 --let type=LONGTEXT CHARACTER SET utf8
1385 --source sp-row-vs-var.inc
1386
1387
1388 --echo #
1389 --echo # End of MDEV-10914 ROW data type for stored routine variables
1390 --echo #
1391
1392
1393 --echo #
1394 --echo # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
1395 --echo #
1396
1397 --echo #
1398 --echo # Referring to a table in a non-existing database
1399 --echo #
1400
1401 DELIMITER $$;
1402 CREATE PROCEDURE p1()
1403 AS
1404 rec test2.t1%ROWTYPE;
1405 BEGIN
1406 NULL;
1407 END;
1408 $$
1409 DELIMITER ;$$
1410 --error ER_NO_SUCH_TABLE
1411 CALL p1();
1412 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
1413 --error ER_NO_SUCH_TABLE
1414 CALL p1();
1415 DROP TABLE t1;
1416 DROP PROCEDURE p1;
1417
1418
1419 --echo #
1420 --echo # Referring to a table in the current database
1421 --echo #
1422
1423 DELIMITER $$;
1424 CREATE PROCEDURE p1()
1425 AS
1426 rec t1%ROWTYPE;
1427 BEGIN
1428 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
1429 SHOW CREATE TABLE t2;
1430 DROP TABLE t2;
1431 END;
1432 $$
1433 DELIMITER ;$$
1434 --error ER_NO_SUCH_TABLE
1435 CALL p1();
1436 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
1437 CALL p1();
1438 DROP TABLE t1;
1439 DROP PROCEDURE p1;
1440
1441
1442 --echo #
1443 --echo # Referring to a table in an explicitly specified database
1444 --echo #
1445
1446 DELIMITER $$;
1447 CREATE PROCEDURE p1()
1448 AS
1449 rec test.t1%ROWTYPE;
1450 BEGIN
1451 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
1452 SHOW CREATE TABLE t2;
1453 DROP TABLE t2;
1454 END;
1455 $$
1456 DELIMITER ;$$
1457 --error ER_NO_SUCH_TABLE
1458 CALL p1();
1459 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
1460 CALL p1();
1461 DROP TABLE t1;
1462 DROP PROCEDURE p1;
1463
1464
1465 --echo #
1466 --echo # Referring to a view in the current database
1467 --echo #
1468
1469 DELIMITER $$;
1470 CREATE PROCEDURE p1()
1471 AS
1472 rec v1%ROWTYPE;
1473 BEGIN
1474 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
1475 SHOW CREATE TABLE t2;
1476 DROP TABLE t2;
1477 END;
1478 $$
1479 DELIMITER ;$$
1480 --error ER_NO_SUCH_TABLE
1481 CALL p1();
1482 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
1483 CREATE VIEW v1 AS SELECT * FROM t1;
1484 CALL p1();
1485 DROP VIEW v1;
1486 DROP TABLE t1;
1487 DROP PROCEDURE p1;
1488
1489
1490 --echo #
1491 --echo # Referring to a view in an explicitly specified database
1492 --echo #
1493
1494 DELIMITER $$;
1495 CREATE PROCEDURE p1()
1496 AS
1497 rec test.v1%ROWTYPE;
1498 BEGIN
1499 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
1500 SHOW CREATE TABLE t2;
1501 DROP TABLE t2;
1502 END;
1503 $$
1504 DELIMITER ;$$
1505 --error ER_NO_SUCH_TABLE
1506 CALL p1();
1507 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
1508 CREATE VIEW v1 AS SELECT * FROM t1;
1509 CALL p1();
1510 DROP VIEW v1;
1511 DROP TABLE t1;
1512 DROP PROCEDURE p1;
1513
1514
1515 --echo #
1516 --echo # Checking that all table%ROWTYPE fields are NULL by default
1517 --echo #
1518 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
1519 DELIMITER $$;
1520 CREATE PROCEDURE p1()
1521 AS
1522 rec1 t1%ROWTYPE;
1523 BEGIN
1524 SELECT rec1.a, rec1.b, rec1.c, rec1.d;
1525 END;
1526 $$
1527 DELIMITER ;$$
1528 CALL p1();
1529 DROP TABLE t1;
1530 DROP PROCEDURE p1;
1531
1532
1533 --echo #
1534 --echo # A table%ROWTYPE variable with a ROW expression as a default
1535 --echo #
1536 CREATE TABLE t1 (a INT, b VARCHAR(10));
1537 DELIMITER $$;
1538 CREATE PROCEDURE p1()
1539 AS
1540 rec1 t1%ROWTYPE DEFAULT ROW(10,'bbb');
1541 BEGIN
1542 SELECT rec1.a, rec1.b;
1543 END;
1544 $$
1545 DELIMITER ;$$
1546 CALL p1();
1547 DROP TABLE t1;
1548 DROP PROCEDURE p1;
1549
1550
1551 --echo #
1552 --echo # A table%ROWTYPE variable with an incompatible ROW expression as a default
1553 --echo #
1554 CREATE TABLE t1 (a INT, b VARCHAR(10));
1555 DELIMITER $$;
1556 CREATE PROCEDURE p1()
1557 AS
1558 rec1 t1%ROWTYPE DEFAULT ROW(10,'bbb','ccc');
1559 BEGIN
1560 SELECT rec1.a, rec1.b;
1561 END;
1562 $$
1563 DELIMITER ;$$
1564 --error ER_OPERAND_COLUMNS
1565 CALL p1();
1566 DROP TABLE t1;
1567 DROP PROCEDURE p1;
1568
1569
1570 --echo #
1571 --echo # A table%ROWTYPE variable with a ROW variable as a default
1572 --echo #
1573 CREATE TABLE t1 (a INT, b VARCHAR(10));
1574 DELIMITER $$;
1575 CREATE PROCEDURE p1()
1576 AS
1577 rec1 ROW(a INT, b VARCHAR(10)):= ROW(10,'bbb');
1578 rec2 t1%ROWTYPE DEFAULT rec1;
1579 BEGIN
1580 SELECT rec2.a, rec2.b;
1581 END;
1582 $$
1583 DELIMITER ;$$
1584 CALL p1();
1585 DROP TABLE t1;
1586 DROP PROCEDURE p1;
1587
1588
1589 --echo #
1590 --echo # A ROW variable using a table%ROWTYPE variable as a default
1591 --echo #
1592 CREATE TABLE t1 (a INT, b VARCHAR(10));
1593 DELIMITER $$;
1594 CREATE PROCEDURE p1()
1595 AS
1596 rec1 t1%ROWTYPE := ROW(10,'bbb');
1597 rec2 ROW(a INT, b VARCHAR(10)):= rec1;
1598 BEGIN
1599 SELECT rec2.a, rec2.b;
1600 END;
1601 $$
1602 DELIMITER ;$$
1603 CALL p1();
1604 DROP TABLE t1;
1605 DROP PROCEDURE p1;
1606
1607
1608 --echo #
1609 --echo # Assigning table%ROWTYPE variables with a different column count
1610 --echo #
1611 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE);
1612 CREATE TABLE t2 (a INT, b VARCHAR(10));
1613 DELIMITER $$;
1614 CREATE PROCEDURE p1()
1615 AS
1616 rec1 t1%ROWTYPE;
1617 rec2 t2%ROWTYPE;
1618 BEGIN
1619 rec2:=rec1;
1620 END;
1621 $$
1622 DELIMITER ;$$
1623 --error ER_OPERAND_COLUMNS
1624 CALL p1();
1625 DROP PROCEDURE p1;
1626 DELIMITER $$;
1627 CREATE PROCEDURE p1()
1628 AS
1629 rec1 t1%ROWTYPE;
1630 rec2 t2%ROWTYPE;
1631 BEGIN
1632 rec1:=rec2;
1633 END;
1634 $$
1635 DELIMITER ;$$
1636 --error ER_OPERAND_COLUMNS
1637 CALL p1();
1638 DROP TABLE t2;
1639 DROP TABLE t1;
1640 DROP PROCEDURE p1;
1641
1642
1643 --echo #
1644 --echo # Assigning compatible table%ROWTYPE variables (equal number of fields)
1645 --echo #
1646 CREATE TABLE t1 (a INT, b VARCHAR(10));
1647 CREATE TABLE t2 (x INT, y VARCHAR(10));
1648 DELIMITER $$;
1649 CREATE PROCEDURE p1()
1650 AS
1651 rec1 t1%ROWTYPE;
1652 rec2 t2%ROWTYPE;
1653 BEGIN
1654 rec1.a:= 10;
1655 rec1.b:= 'bbb';
1656 rec2:=rec1;
1657 SELECT rec2.x, rec2.y;
1658 END;
1659 $$
1660 DELIMITER ;$$
1661 CALL p1();
1662 DROP TABLE t2;
1663 DROP TABLE t1;
1664 DROP PROCEDURE p1;
1665
1666
1667 --echo #
1668 --echo # Assigning between incompatible table%ROWTYPE and explicit ROW variables
1669 --echo #
1670
1671 CREATE TABLE t1 (a INT, b VARCHAR(10));
1672 DELIMITER $$;
1673 CREATE PROCEDURE p1()
1674 AS
1675 rec1 t1%ROWTYPE;
1676 rec2 ROW(x INT,y INT,z INT);
1677 BEGIN
1678 rec2.x:= 10;
1679 rec2.y:= 20;
1680 rec2.z:= 30;
1681 rec1:= rec2;
1682 END;
1683 $$
1684 DELIMITER ;$$
1685 --error ER_OPERAND_COLUMNS
1686 CALL p1();
1687 DROP TABLE t1;
1688 DROP PROCEDURE p1;
1689
1690
1691 --echo #
1692 --echo # Assigning between compatible table%ROWTYPE and explicit ROW variables
1693 --echo #
1694
1695 CREATE TABLE t1 (a INT, b VARCHAR(10));
1696 DELIMITER $$;
1697 CREATE PROCEDURE p1()
1698 AS
1699 rec1 t1%ROWTYPE;
1700 rec2 ROW(x INT,y INT);
1701 BEGIN
1702 rec2.x:= 10;
1703 rec2.y:= 20;
1704 rec1:= rec2;
1705 SELECT rec1.a, rec1.b;
1706 rec1.a:= 11;
1707 rec1.b:= 21;
1708 rec2:= rec1;
1709 SELECT rec2.x, rec2.y;
1710 END;
1711 $$
1712 DELIMITER ;$$
1713 CALL p1();
1714 DROP TABLE t1;
1715 DROP PROCEDURE p1;
1716
1717
1718 --echo #
1719 --echo # Assigning table%ROWTYPE from a ROW expression
1720 --echo #
1721
1722 CREATE TABLE t1 (a INT, b VARCHAR(10));
1723 DELIMITER $$;
1724 CREATE PROCEDURE p1()
1725 AS
1726 rec1 t1%ROWTYPE;
1727 BEGIN
1728 rec1:= ROW(10,20);
1729 SELECT rec1.a, rec1.b;
1730 END;
1731 $$
1732 DELIMITER ;$$
1733 CALL p1();
1734 DROP TABLE t1;
1735 DROP PROCEDURE p1;
1736
1737
1738 --echo #
1739 --echo # Fetching a cursor into a table%ROWTYPE variable with a wrong field count
1740 --echo #
1741
1742 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
1743 CREATE TABLE t2 (a INT, b VARCHAR(10));
1744 INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
1745 DELIMITER $$;
1746 CREATE PROCEDURE p1()
1747 AS
1748 rec2 t2%ROWTYPE;
1749 CURSOR cur1 IS SELECT * FROM t1;
1750 BEGIN
1751 OPEN cur1;
1752 FETCH cur1 INTO rec2;
1753 CLOSE cur1;
1754 END;
1755 $$
1756 DELIMITER ;$$
1757 --error ER_SP_WRONG_NO_OF_FETCH_ARGS
1758 CALL p1();
1759 DROP TABLE t2;
1760 DROP TABLE t1;
1761 DROP PROCEDURE p1;
1762
1763
1764 --echo #
1765 --echo # Fetching a cursor into a table%ROWTYPE variable
1766 --echo #
1767
1768 CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
1769 CREATE TABLE t2 LIKE t1;
1770 INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
1771 INSERT INTO t1 VALUES (20,'bb2',222.222e2, 12.32);
1772 INSERT INTO t1 VALUES (30,'bb3',333.333e2, 12.33);
1773 DELIMITER $$;
1774 CREATE PROCEDURE p1()
1775 AS
1776 rec t1%ROWTYPE;
1777 CURSOR cur IS SELECT * FROM t1;
1778 BEGIN
1779 OPEN cur;
1780 LOOP
1781 FETCH cur INTO rec;
1782 EXIT WHEN cur%NOTFOUND;
1783 SELECT rec.a, rec.b, rec.c, rec.d;
1784 INSERT INTO t2 VALUES (rec.a, rec.b, rec.c, rec.d);
1785 END LOOP;
1786 CLOSE cur;
1787 END;
1788 $$
1789 DELIMITER ;$$
1790 CALL p1();
1791 SELECT * FROM t2;
1792 DROP TABLE t2;
1793 DROP TABLE t1;
1794 DROP PROCEDURE p1;
1795
1796 --echo #
1797 --echo # Fetching a cursor into a table%ROWTYPE variable with different column names
1798 --echo #
1799
1800 CREATE TABLE t1 (a INT, b VARCHAR(10));
1801 CREATE TABLE t2 (x INT, y VARCHAR(10));
1802 INSERT INTO t1 VALUES (10,'bbb');
1803 DELIMITER $$;
1804 CREATE PROCEDURE p1()
1805 AS
1806 rec2 t2%ROWTYPE;
1807 CURSOR cur1 IS SELECT * FROM t1;
1808 BEGIN
1809 OPEN cur1;
1810 FETCH cur1 INTO rec2;
1811 SELECT rec2.x, rec2.y;
1812 CLOSE cur1;
1813 END;
1814 $$
1815 DELIMITER ;$$
1816 CALL p1();
1817 DROP TABLE t2;
1818 DROP TABLE t1;
1819 DROP PROCEDURE p1;
1820
1821
1822 --echo #
1823 --echo # Fetching a cursor into a table%ROWTYPE variable, with truncation
1824 --echo #
1825
1826 CREATE TABLE t1 (a INT, b VARCHAR(10));
1827 CREATE TABLE t2 (a INT, b INT);
1828 INSERT INTO t1 VALUES (10,'11x');
1829 DELIMITER $$;
1830 CREATE PROCEDURE p1()
1831 AS
1832 rec2 t2%ROWTYPE;
1833 CURSOR cur1 IS SELECT * FROM t1;
1834 BEGIN
1835 OPEN cur1;
1836 FETCH cur1 INTO rec2;
1837 SELECT rec2.a, rec2.b;
1838 CLOSE cur1;
1839 END;
1840 $$
1841 DELIMITER ;$$
1842 CALL p1();
1843 DROP TABLE t2;
1844 DROP TABLE t1;
1845 DROP PROCEDURE p1;
1846
1847
1848 --echo #
1849 --echo # table%ROWTYPE variables are not allowed in LIMIT
1850 --echo #
1851 CREATE TABLE t1 (a INT, b INT);
1852 INSERT INTO t1 VALUES (1,2);
1853 DELIMITER $$;
1854 --error ER_WRONG_SPVAR_TYPE_IN_LIMIT
1855 CREATE PROCEDURE p1()
1856 AS
1857 rec1 t1%ROWTYPE:=(1,2);
1858 BEGIN
1859 SELECT * FROM t1 LIMIT rec1.a;
1860 END;
1861 $$
1862 DELIMITER ;$$
1863 DROP TABLE t1;
1864
1865
1866 --echo #
1867 --echo # table%ROWTYPE variable fields as OUT parameters
1868 --echo #
1869 CREATE TABLE t1 (a INT, b VARCHAR(10));
1870 DELIMITER $$;
1871 CREATE PROCEDURE p1(a OUT INT,b OUT VARCHAR(10))
1872 AS
1873 BEGIN
1874 a:=10;
1875 b:='bb';
1876 END;
1877 $$
1878 CREATE PROCEDURE p2()
1879 AS
1880 rec1 t1%ROWTYPE;
1881 BEGIN
1882 CALL p1(rec1.a, rec1.b);
1883 SELECT rec1.a, rec1.b;
1884 END;
1885 $$
1886 DELIMITER ;$$
1887 CALL p2();
1888 DROP PROCEDURE p2;
1889 DROP PROCEDURE p1;
1890 DROP TABLE t1;
1891
1892
1893 --echo #
1894 --echo # Passing the entire table%ROWTYPE variable
1895 --echo #
1896
1897 CREATE TABLE t1 (a INT, b VARCHAR(10));
1898 DELIMITER $$;
1899 CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10)))
1900 AS
1901 BEGIN
1902 SELECT a.a, a.b;
1903 END;
1904 $$
1905 CREATE PROCEDURE p2()
1906 AS
1907 rec1 t1%ROWTYPE:= ROW(10,'bb');
1908 BEGIN
1909 CALL p1(rec1);
1910 END;
1911 $$
1912 DELIMITER ;$$
1913 CALL p2();
1914 DROP PROCEDURE p2;
1915 DROP PROCEDURE p1;
1916 DROP TABLE t1;
1917
1918
1919 --echo #
1920 --echo # Passing the entire table%ROWTYPE variable as an OUT parameter
1921 --echo #
1922
1923 CREATE TABLE t1 (a INT, b VARCHAR(10));
1924 DELIMITER $$;
1925 CREATE PROCEDURE p1(a OUT ROW(a INT, b VARCHAR(10)))
1926 AS
1927 BEGIN
1928 a:= ROW(10,'bb');
1929 END;
1930 $$
1931 CREATE PROCEDURE p2()
1932 AS
1933 rec1 t1%ROWTYPE;
1934 BEGIN
1935 CALL p1(rec1);
1936 SELECT rec1.a, rec1.b;
1937 END;
1938 $$
1939 DELIMITER ;$$
1940 CALL p2();
1941 DROP PROCEDURE p2;
1942 DROP PROCEDURE p1;
1943 DROP TABLE t1;
1944
1945
1946 --echo #
1947 --echo # Assigning a table%ROWTYPE field to an OUT parameter
1948 --echo #
1949
1950 CREATE TABLE t1 (a INT, b VARCHAR(10));
1951 DELIMITER $$;
1952 CREATE PROCEDURE p1 (res IN OUT INTEGER)
1953 AS
1954 rec1 t1%ROWTYPE:=ROW(10,'b0');
1955 BEGIN
1956 res:=rec1.a;
1957 END;
1958 $$
1959 DELIMITER ;$$
1960 CALL p1(@res);
1961 SELECT @res;
1962 SET @res=NULL;
1963 DROP PROCEDURE p1;
1964 DROP TABLE t1;
1965
1966
1967 --echo #
1968 --echo # Testing Item_splocal_row_field_by_name::print
1969 --echo #
1970
1971 CREATE TABLE t1 (a INT, b VARCHAR(10));
1972 DELIMITER $$;
1973 CREATE PROCEDURE p1
1974 AS
1975 rec t1%ROWTYPE:=ROW(10,'bb');
1976 BEGIN
1977 EXPLAIN EXTENDED SELECT rec.a, rec.b;
1978 END;
1979 $$
1980 DELIMITER ;$$
1981 CALL p1();
1982 DROP PROCEDURE p1;
1983 DROP TABLE t1;
1984
1985 --echo #
1986 --echo # Non-existing field
1987 --echo #
1988
1989 CREATE TABLE t1 (a INT, b VARCHAR(10));
1990 DELIMITER $$;
1991 CREATE PROCEDURE p1
1992 AS
1993 rec t1%ROWTYPE;
1994 BEGIN
1995 SELECT rec.c;
1996 END;
1997 $$
1998 DELIMITER ;$$
1999 --error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
2000 CALL p1();
2001 ALTER TABLE t1 ADD c INT;
2002 CALL p1();
2003 DROP PROCEDURE p1;
2004 DROP TABLE t1;
2005
2006
2007 --echo #
2008 --echo # Testing that field names are case insensitive
2009 --echo #
2010
2011 CREATE TABLE t1 (a INT, b VARCHAR(10));
2012 DELIMITER $$;
2013 CREATE PROCEDURE p1
2014 AS
2015 rec t1%ROWTYPE:=ROW(10,'bb');
2016 BEGIN
2017 SELECT rec.A, rec.B;
2018 END;
2019 $$
2020 DELIMITER ;$$
2021 CALL p1();
2022 DROP PROCEDURE p1;
2023 DROP TABLE t1;
2024
2025
2026 --echo #
2027 --echo # Testing that table%ROWTYPE uses temporary tables vs shadowed real tables
2028 --echo #
2029
2030 CREATE TABLE t1 (a INT, b VARCHAR(10));
2031 CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
2032 DELIMITER $$;
2033 CREATE PROCEDURE p1
2034 AS
2035 rec t1%ROWTYPE:=ROW(10,'bb');
2036 BEGIN
2037 SELECT rec.A, rec.B;
2038 END;
2039 $$
2040 DELIMITER ;$$
2041 --error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
2042 CALL p1();
2043 DROP TEMPORARY TABLE t1;
2044 CALL p1();
2045 DROP PROCEDURE p1;
2046 DROP TABLE t1;
2047
2048 --echo #
2049 --echo # Testing that the structure of table%ROWTYPE variables is determined at the very beginning and is not changed after ALTER
2050 --echo #
2051
2052 CREATE TABLE t1 (a INT, b VARCHAR(32));
2053 INSERT INTO t1 VALUES (10,'b10');
2054 DELIMITER $$;
2055 CREATE PROCEDURE p1 AS
2056 BEGIN
2057 ALTER TABLE t1 ADD c INT;
2058 DECLARE
2059 rec t1%ROWTYPE; -- this will not have column "c"
2060 BEGIN
2061 rec.c:=10;
2062 END;
2063 END;
2064 $$
2065 DELIMITER ;$$
2066 --error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
2067 CALL p1();
2068 DROP TABLE t1;
2069 DROP PROCEDURE p1;
2070
2071
2072 --echo #
2073 --echo # MDEV-12291 Allow ROW variables as SELECT INTO targets
2074 --echo #
2075
2076
2077 --enable_prepare_warnings
2078 --echo # ROW variable with a wrong column count
2079 CREATE TABLE t1 (a INT, b VARCHAR(32));
2080 INSERT INTO t1 VALUES (10,'b10');
2081 DELIMITER $$;
2082 CREATE PROCEDURE p1 AS
2083 rec1 ROW(a INT, b VARCHAR(32), c DOUBLE);
2084 BEGIN
2085 SELECT * FROM t1 INTO rec1;
2086 SELECT rec1.a, rec1.b;
2087 END;
2088 $$
2089 DELIMITER ;$$
2090 --error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
2091 CALL p1();
2092 DROP TABLE t1;
2093 DROP PROCEDURE p1;
2094
2095
2096 --echo # Multiple ROW variables
2097 CREATE TABLE t1 (a INT, b VARCHAR(32));
2098 INSERT INTO t1 VALUES (10,'b10');
2099 DELIMITER $$;
2100 CREATE PROCEDURE p1 AS
2101 rec1 ROW(a INT, b VARCHAR(32));
2102 BEGIN
2103 SELECT * FROM t1 INTO rec1, rec1;
2104 SELECT rec1.a, rec1.b;
2105 END;
2106 $$
2107 DELIMITER ;$$
2108 --error ER_OPERAND_COLUMNS
2109 CALL p1();
2110 DROP TABLE t1;
2111 DROP PROCEDURE p1;
2112
2113
2114 --echo # ROW variables working example
2115 CREATE TABLE t1 (a INT, b VARCHAR(32));
2116 INSERT INTO t1 VALUES (10,'b10');
2117 DELIMITER $$;
2118 CREATE PROCEDURE p1 AS
2119 rec1 ROW(a INT, b VARCHAR(32));
2120 BEGIN
2121 SELECT * FROM t1 INTO rec1;
2122 SELECT rec1.a, rec1.b;
2123 END;
2124 $$
2125 DELIMITER ;$$
2126 CALL p1();
2127 DROP TABLE t1;
2128 DROP PROCEDURE p1;
2129
2130
2131 --echo # table%ROWTYPE variable with a wrong column count
2132 CREATE TABLE t1 (a INT, b VARCHAR(32));
2133 INSERT INTO t1 VALUES (10,'b10');
2134 DELIMITER $$;
2135 CREATE PROCEDURE p1 AS
2136 rec1 t1%ROWTYPE;
2137 BEGIN
2138 SELECT 10,'a','b' FROM t1 INTO rec1;
2139 SELECT rec1.a, rec1.b;
2140 END;
2141 $$
2142 DELIMITER ;$$
2143 --error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
2144 CALL p1();
2145 DROP TABLE t1;
2146 DROP PROCEDURE p1;
2147
2148
2149 --echo # Multiple table%ROWTYPE variables
2150 CREATE TABLE t1 (a INT, b VARCHAR(32));
2151 INSERT INTO t1 VALUES (10,'b10');
2152 DELIMITER $$;
2153 CREATE PROCEDURE p1 AS
2154 rec1 t1%ROWTYPE;
2155 BEGIN
2156 SELECT 10,'a' FROM t1 INTO rec1, rec1;
2157 SELECT rec1.a, rec1.b;
2158 END;
2159 $$
2160 DELIMITER ;$$
2161 --error ER_OPERAND_COLUMNS
2162 CALL p1();
2163 DROP TABLE t1;
2164 DROP PROCEDURE p1;
2165
2166
2167 --echo # table%ROWTYPE working example
2168 CREATE TABLE t1 (a INT, b VARCHAR(32));
2169 INSERT INTO t1 VALUES (10,'b10');
2170 DELIMITER $$;
2171 CREATE PROCEDURE p1 AS
2172 rec1 t1%ROWTYPE;
2173 BEGIN
2174 SELECT * FROM t1 INTO rec1;
2175 SELECT rec1.a, rec1.b;
2176 END;
2177 $$
2178 DELIMITER ;$$
2179 CALL p1();
2180 DROP TABLE t1;
2181 DROP PROCEDURE p1;
2182
2183
2184 --echo # cursor%ROWTYPE variable with a wrong column count
2185 CREATE TABLE t1 (a INT, b VARCHAR(32));
2186 INSERT INTO t1 VALUES (10,'b10');
2187 DELIMITER $$;
2188 CREATE PROCEDURE p1 AS
2189 CURSOR cur1 IS SELECT 10, 'b0', 'c0';
2190 rec1 cur1%ROWTYPE;
2191 BEGIN
2192 SELECT * FROM t1 INTO rec1;
2193 SELECT rec1.a, rec1.b;
2194 END;
2195 $$
2196 DELIMITER ;$$
2197 --error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
2198 CALL p1();
2199 DROP TABLE t1;
2200 DROP PROCEDURE p1;
2201
2202
2203 --echo # Multiple cursor%ROWTYPE variables
2204 CREATE TABLE t1 (a INT, b VARCHAR(32));
2205 INSERT INTO t1 VALUES (10,'b10');
2206 DELIMITER $$;
2207 CREATE PROCEDURE p1 AS
2208 CURSOR cur1 IS SELECT * FROM t1;
2209 rec1 cur1%ROWTYPE;
2210 BEGIN
2211 SELECT * FROM t1 INTO rec1, rec1;
2212 SELECT rec1.a, rec1.b;
2213 END;
2214 $$
2215 DELIMITER ;$$
2216 --error ER_OPERAND_COLUMNS
2217 CALL p1();
2218 DROP TABLE t1;
2219 DROP PROCEDURE p1;
2220
2221
2222 --echo # cursor%ROWTYPE working example
2223 CREATE TABLE t1 (a INT, b VARCHAR(32));
2224 INSERT INTO t1 VALUES (10,'b10');
2225 DELIMITER $$;
2226 CREATE PROCEDURE p1 AS
2227 CURSOR cur1 IS SELECT * FROM t1;
2228 rec1 cur1%ROWTYPE;
2229 BEGIN
2230 SELECT * FROM t1 INTO rec1;
2231 SELECT rec1.a, rec1.b;
2232 END;
2233 $$
2234 DELIMITER ;$$
2235 CALL p1();
2236 DROP TABLE t1;
2237 DROP PROCEDURE p1;
2238 --disable_prepare_warnings
2239
2240 --echo #
2241 --echo # MDEV-12347 Valgrind reports invalid read errors in Item_field_row::element_index_by_name
2242 --echo #
2243
2244 # An additional test for MDEV-12347, to make sure that
2245 # Column_definition::interval creates a permanent copy of TYPELIB on
2246 # the memory root when processing %ROWTYPE for a table with ENUM/SET column,
2247 # rather than reuses the TYPELIB from table->field[i], which is freed in the
2248 # end of sp_rcontext::resolve_table_rowtype_ref().
2249
2250 CREATE TABLE t1 (a INT, b ENUM('b0','b1','b12','b3'));
2251 DELIMITER $$;
2252 CREATE PROCEDURE p1 AS
2253 BEGIN
2254 DECLARE
2255 rec t1%ROWTYPE;
2256 BEGIN
2257 rec.b:='b0';
2258 SELECT rec.b;
2259 END;
2260 END;
2261 $$
2262 DELIMITER ;$$
2263 CALL p1();
2264 DROP TABLE t1;
2265 DROP PROCEDURE p1;
2266
2267
2268 CREATE TABLE t1 (a INT, b SET('b0','b1','b12','b3'));
2269 DELIMITER $$;
2270 CREATE PROCEDURE p1 AS
2271 BEGIN
2272 DECLARE
2273 rec t1%ROWTYPE;
2274 BEGIN
2275 rec.b:='b0';
2276 SELECT rec.b;
2277 END;
2278 END;
2279 $$
2280 DELIMITER ;$$
2281 CALL p1();
2282 DROP TABLE t1;
2283 DROP PROCEDURE p1;
2284
2285
2286 --echo #
2287 --echo # MDEV-13273 Confusion between table alias and ROW type variable
2288 --echo #
2289
2290 CREATE TABLE t1 (c1 INT, c2 INT);
2291 INSERT INTO t1 VALUES (0,0);
2292 DELIMITER $$;
2293 CREATE PROCEDURE p1
2294 AS
2295 a INT;
2296 b INT;
2297 BEGIN
2298 -- a.c1 is a table column
2299 SELECT a.c1 INTO b
2300 FROM t1 a
2301 WHERE a.c2 = 0;
2302 SELECT b;
2303 END;
2304 $$
2305 DELIMITER ;$$
2306 CALL p1;
2307 DROP PROCEDURE p1;
2308 DROP TABLE t1;
2309
2310 CREATE TABLE t1 (c1 INT, c2 INT);
2311 INSERT INTO t1 VALUES (0,0);
2312 DELIMITER $$;
2313 CREATE PROCEDURE p1
2314 AS
2315 a ROW (c1 INT, c2 INT) := ROW(101,102);
2316 b INT;
2317 BEGIN
2318 -- a.c1 is a ROW variable field
2319 SELECT a.c1 INTO b
2320 FROM t1 a
2321 WHERE a.c2 = 102;
2322 SELECT b;
2323 END;
2324 $$
2325 DELIMITER ;$$
2326 CALL p1;
2327 DROP PROCEDURE p1;
2328 DROP TABLE t1;
2329
2330 CREATE TABLE t1 (c1 INT, c2 INT);
2331 INSERT INTO t1 VALUES (0,0);
2332 DELIMITER $$;
2333 CREATE PROCEDURE p1
2334 AS
2335 a t1%ROWTYPE := ROW (10,20);
2336 b INT;
2337 BEGIN
2338 -- a.c1 is a ROW variable field
2339 SELECT a.c1 INTO b
2340 FROM t1 a
2341 WHERE a.c2 = 20;
2342 SELECT b;
2343 END;
2344 $$
2345 DELIMITER ;$$
2346 CALL p1;
2347 DROP PROCEDURE p1;
2348 DROP TABLE t1;
2349
2350 CREATE TABLE t1 (c1 INT, c2 INT);
2351 INSERT INTO t1 VALUES (0,0);
2352 DELIMITER $$;
2353 CREATE PROCEDURE p1
2354 AS
2355 CURSOR cur1 IS SELECT * FROM t1;
2356 a cur1%ROWTYPE := ROW (10,20);
2357 b INT;
2358 BEGIN
2359 -- a.c1 is a ROW variable field
2360 SELECT a.c1 INTO b
2361 FROM t1 a
2362 WHERE a.c2 = 20;
2363 SELECT b;
2364 END;
2365 $$
2366 DELIMITER ;$$
2367 CALL p1;
2368 DROP PROCEDURE p1;
2369 DROP TABLE t1;
2370
2371 --echo #
2372 --echo # MDEV-13527 Crash when EXPLAIN SELECT .. INTO row_sp_variable.field
2373 --echo #
2374
2375 DELIMITER $$;
2376 DECLARE
2377 a ROW(a INT);
2378 BEGIN
2379 EXPLAIN SELECT 1 INTO a.a;
2380 END;
2381 $$
2382 DELIMITER ;$$
2383
2384
2385 --echo #
2386 --echo # MDEV-14139 Anchored data types for variables
2387 --echo #
2388
2389 DELIMITER $$;
2390 DECLARE
2391 row1 ROW(int11 INT,text1 TEXT);
2392 a_row1 row1%TYPE;
2393 aa_row1 a_row1%TYPE;
2394 BEGIN
2395 CREATE TABLE t1 AS SELECT a_row1.int11 AS int11, a_row1.text1 AS text1;
2396 SHOW CREATE TABLE t1;
2397 DROP TABLE t1;
2398 CREATE TABLE t1 AS SELECT aa_row1.int11 AS int11, aa_row1.text1 AS text1;
2399 SHOW CREATE TABLE t1;
2400 DROP TABLE t1;
2401 END;
2402 $$
2403 DELIMITER ;$$
2404
2405 --echo #
2406 --echo # Start of 11.7 tests
2407 --echo #
2408
2409 --echo #
2410 --echo # MDEV-12252 ROW data type for stored function return values
2411 --echo #
2412
2413 DELIMITER $$;
2414 CREATE FUNCTION f1() RETURN ROW(a INT)
2415 AS
2416 BEGIN
2417 RETURN NULL;
2418 END;
2419 $$
2420 DELIMITER ;$$
2421 SHOW CREATE FUNCTION f1;
2422 # This crashes
2423 #SELECT f1();
2424 DROP FUNCTION f1;
2425
2426
2427 DELIMITER $$;
2428 CREATE FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32))
2429 AS
2430 BEGIN
2431 RETURN ROW(1,'b1');
2432 END;
2433 $$
2434 CREATE PROCEDURE p1col2
2435 AS
2436 r ROW(a INT, b VARCHAR(32)) := f1();
2437 BEGIN
2438 SELECT r.a, r.b;
2439 END;
2440 $$
2441 CREATE PROCEDURE p1col3
2442 AS
2443 r ROW(a INT, b VARCHAR(32),c DOUBLE) := f1();
2444 BEGIN
2445 SELECT r.a, r.b, r.c;
2446 END;
2447 $$
2448 DELIMITER ;$$
2449 SHOW CREATE FUNCTION f1;
2450 CALL p1col2();
2451 --error ER_OPERAND_COLUMNS
2452 CALL p1col3();
2453 --error ER_OPERAND_COLUMNS
2454 SELECT f1();
2455 SELECT f1()=ROW(1,'b1') AS c;
2456 DROP FUNCTION f1;
2457 DROP PROCEDURE p1col2;
2458 DROP PROCEDURE p1col3;
2459
2460
2461 CREATE TABLE t1 (a INT, b VARCHAR(32));
2462 DELIMITER $$;
2463 CREATE FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32))
2464 AS
2465 BEGIN
2466 RETURN (SELECT * FROM t1);
2467 END;
2468 $$
2469 CREATE PROCEDURE p1
2470 AS
2471 r ROW(a INT, b VARCHAR(32)) := f1();
2472 BEGIN
2473 SELECT r.a, r.b;
2474 END;
2475 $$
2476 DELIMITER ;$$
2477
2478 --echo # Testing with no rows
2479 CALL p1;
2480
2481 --echo # Testing with one row
2482 INSERT INTO t1 VALUES (1,'b1');
2483 CALL p1;
2484
2485 --echo # Testing with two rows
2486 INSERT INTO t1 VALUES (1,'b1');
2487 --error ER_SUBQUERY_NO_1_ROW
2488 CALL p1;
2489
2490 DROP FUNCTION f1;
2491 DROP PROCEDURE p1;
2492 DROP TABLE t1;
2493
2494 --echo #
2495 --echo # End of 11.7 tests
2496 --echo #