Debian Codegraph @

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

raw
1 --echo # Start of 11.8 tests
2
3 --echo #
4 --echo # MDEV-34317 DECLARE TYPE type_name IS RECORD (..) with scalar members in stored routines
5 --echo #
6
7 set sql_mode=oracle;
8
9 --echo #
10 --echo # Basic RECORD
11 --echo #
12 DELIMITER $$;
13 DECLARE
14 TYPE DeptRecTyp IS RECORD (
15 dept_id NUMBER(4),
16 dept_name VARCHAR2(30),
17 mgr_id NUMBER(6),
18 loc_id NUMBER(4)
19 );
20 dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
21 str VARCHAR(1024);
22 BEGIN
23 str:=
24 'dept_id: ' || dept_rec.dept_id || '; ' ||
25 'dept_name: ' || dept_rec.dept_name || '; ' ||
26 'mgr_id: ' || dept_rec.mgr_id || '; ' ||
27 'loc_id: ' || dept_rec.loc_id;
28 SELECT str;
29 END;
30 $$
31 DELIMITER ;$$
32
33 --echo #
34 --echo # RECORD fields with anchored type
35 --echo #
36 CREATE TABLE t1
37 (
38 dept_id NUMBER(4),
39 dept_name VARCHAR2(30),
40 mgr_id NUMBER(6),
41 loc_id NUMBER(4)
42 );
43
44 DELIMITER $$;
45 DECLARE
46 TYPE DeptRecTyp IS RECORD (
47 dept_id t1.dept_id%TYPE,
48 dept_name t1.dept_name%TYPE,
49 mgr_id t1.mgr_id%TYPE,
50 loc_id t1.loc_id%TYPE
51 );
52 dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
53 str VARCHAR(1024);
54 BEGIN
55 str:=
56 'dept_id: ' || dept_rec.dept_id || '; ' ||
57 'dept_name: ' || dept_rec.dept_name || '; ' ||
58 'mgr_id: ' || dept_rec.mgr_id || '; ' ||
59 'loc_id: ' || dept_rec.loc_id;
60 SELECT str;
61 END;
62 $$
63 DELIMITER ;$$
64 DROP TABLE t1;
65
66
67 CREATE TABLE t1 (a VARCHAR(5) CHARACTER SET utf8mb4, b INT, c DOUBLE);
68 DELIMITER $$;
69 CREATE OR REPLACE PROCEDURE p1 AS
70 TYPE RecT IS RECORD (
71 a t1.a%TYPE,
72 b t1.b%TYPE,
73 c t1.c%TYPE
74 );
75 rec RecT:= RecT('a',2,3.5);
76 BEGIN
77 CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c;
78 SHOW CREATE TABLE t2;
79 SELECT * FROM t2;
80 DROP TABLE t2;
81 END;
82 $$
83 DELIMITER ;$$
84 CALL p1;
85 DROP PROCEDURE p1;
86 DROP TABLE t1;
87
88
89 --echo #
90 --echo # RECORD using SUBTYPE
91 --echo # This is not supported yet
92 --echo #
93 CREATE TABLE t1
94 (
95 dept_id NUMBER(4),
96 dept_name VARCHAR2(30),
97 mgr_id NUMBER(6),
98 loc_id NUMBER(4)
99 );
100 DELIMITER $$;
101 --error ER_UNKNOWN_DATA_TYPE
102 DECLARE
103 SUBTYPE DeptRecTyp IS t1%ROWTYPE;
104 dept_rec DeptRecTyp;
105 str VARCHAR(1024);
106 BEGIN
107 dept_rec.dept_id := 11;
108 dept_rec.dept_name:= 'a';
109 dept_rec.mgr_id := 201;
110 dept_rec.loc_id := 1700;
111 str:=
112 'dept_id: ' || dept_rec.dept_id || '; ' ||
113 'dept_name: ' || dept_rec.dept_name || '; ' ||
114 'mgr_id: ' || dept_rec.mgr_id || '; ' ||
115 'loc_id: ' || dept_rec.loc_id;
116 SELECT str;
117 END;
118 $$
119 DELIMITER ;$$
120 DROP TABLE t1;
121
122 --echo #
123 --echo # RECORD with NOT NULL or default clauses
124 --echo # This is not supported yet
125 --echo #
126 DELIMITER $$;
127 --error ER_PARSE_ERROR
128 DECLARE
129 TYPE DeptRecTyp IS RECORD (
130 dept_id NUMBER(4) NOT NULL := 10,
131 dept_name VARCHAR2(30) NOT NULL := 'Administration',
132 mgr_id NUMBER(6) := 200,
133 loc_id NUMBER(4) := 1700
134 );
135 dept_rec DeptRecTyp;
136 str VARCHAR(1024);
137 BEGIN
138 str:=
139 'dept_id: ' || dept_rec.dept_id || '; ' ||
140 'dept_name: ' || dept_rec.dept_name || chr(13) ||
141 'mgr_id: ' || dept_rec.mgr_id || chr(13) ||
142 'loc_id: ' || dept_rec.loc_id;
143 SELECT str;
144 END;
145 $$
146 DELIMITER ;$$
147
148 --echo #
149 --echo # Nested RECORDs
150 --echo # This is not supported yet
151 --echo #
152 DELIMITER $$;
153 --error ER_UNKNOWN_DATA_TYPE
154 DECLARE
155 TYPE name_rec IS RECORD (
156 first VARCHAR(64),
157 last VARCHAR(64)
158 );
159
160 TYPE contact IS RECORD (
161 name name_rec, -- a nested record
162 phone VARCHAR(32)
163 );
164 person contact;
165 str VARCHAR(1024);
166 BEGIN
167 person.name.first := 'John';
168 person.name.last := 'Brown';
169 person.phone := '1-654-222-1234';
170
171 str:=
172 person.name.first || ' ' ||
173 person.name.last || ', ' ||
174 person.phone;
175
176 SELECT str;
177 END;
178 $$
179 DELIMITER ;$$
180
181 --echo #
182 --echo # RECORD duplicate type declaration
183 --echo #
184 DELIMITER $$;
185 --error ER_SP_DUP_DECL
186 DECLARE
187 TYPE RcType IS RECORD (
188 a NUMBER(4)
189 );
190 TYPE RcType IS RECORD (
191 b NUMBER(4)
192 );
193 BEGIN
194 END;
195 $$
196 DELIMITER ;$$
197
198 --echo #
199 --echo # RECORD with no field
200 --echo #
201 DELIMITER $$;
202 --error ER_PARSE_ERROR
203 DECLARE
204 TYPE RcType IS RECORD (
205 );
206 rec RcType;
207 str VARCHAR(1024);
208 BEGIN
209 END;
210 $$
211 DELIMITER ;$$
212
213 --echo #
214 --echo # RECORD field test
215 --echo #
216 DELIMITER $$;
217 DECLARE
218 TYPE RcType IS RECORD (
219 int11 INT,
220 dec103 DECIMAL(10,3),
221 flt0 FLOAT,
222 dbl0 DOUBLE,
223 enum0 ENUM('a','b'),
224 bit3 BIT(3),
225
226 varchar10 VARCHAR(10),
227 text1 TEXT,
228 tinytext1 TINYTEXT,
229 mediumtext1 MEDIUMTEXT CHARACTER SET utf8,
230 longtext1 LONGTEXT,
231
232 time3 TIME(3),
233 datetime4 DATETIME(4),
234 timestamp5 TIMESTAMP(5),
235 date0 DATE
236 );
237 rec1,rec2 RcType:= RcType(4, 11.2, 6.7, 111.43234663, 'a', b'101',
238 'bbbb', 'TEXT', 'TINY', 'MEDIUM', 'LONG',
239 '10:14:22','2001-07-22 10:14:22', '2001-07-22 12:12:12', '2001-07-22');
240 rec3 RcType;
241 BEGIN
242 SELECT rec1.int11, rec2.timestamp5, rec3.date0;
243 END;
244 $$
245 DELIMITER ;$$
246
247 --echo #
248 --echo # RECORD with field manipulations
249 --echo #
250 DELIMITER $$;
251 DECLARE
252 TYPE DeptRecTyp IS RECORD (
253 dept_id NUMBER(4)
254 );
255 dept_rec DeptRecTyp;
256 test INT:=4;
257 BEGIN
258 dept_rec.dept_id:= 2;
259 test:= test + dept_rec.dept_id;
260 dept_rec.dept_id:= 5;
261 SELECT dept_rec.dept_id, test;
262 END;
263 $$
264 DELIMITER ;$$
265
266 --echo #
267 --echo # RECORD comparison
268 --echo #
269 DELIMITER $$;
270 DECLARE
271 TYPE DeptRecTyp IS RECORD (
272 dept_id NUMBER(4),
273 dept_name VARCHAR(20)
274 );
275 dept_rec1 DeptRecTyp := DeptRecTyp(1,'a');
276 dept_rec2 DeptRecTyp := DeptRecTyp(1,'a');
277 BEGIN
278 SELECT dept_rec1 = dept_rec2;
279 dept_rec2.dept_id:= 2;
280 SELECT dept_rec1 = dept_rec2;
281 END;
282 $$
283 DELIMITER ;$$
284
285 --echo #
286 --echo # RECORD comparison
287 --echo # RECORD type comparison is not supported for records with only
288 --echo # one field (bug, see MDEV-35274)
289 --echo #
290 DELIMITER $$;
291 --error ER_OPERAND_COLUMNS
292 DECLARE
293 TYPE DeptRecTyp IS RECORD (
294 dept_id NUMBER(4)
295 );
296 dept_rec1 DeptRecTyp;
297 dept_rec2 DeptRecTyp;
298 BEGIN
299 SELECT dept_rec1 = dept_rec2;
300 END;
301 $$
302 DELIMITER ;$$
303
304 --echo #
305 --echo # RECORD assignment with the same type
306 --echo #
307 DELIMITER $$;
308 DECLARE
309 TYPE DeptRecTyp IS RECORD (
310 dept_id NUMBER(4)
311 );
312 dept_rec1 DeptRecTyp:= DeptRecTyp(11);
313 dept_rec2 DeptRecTyp:= DeptRecTyp(12);
314 BEGIN
315 dept_rec1:= dept_rec2;
316 SELECT dept_rec1.dept_id, dept_rec2.dept_id;
317 END;
318 $$
319 DELIMITER ;$$
320
321 --echo #
322 --echo # RECORD assignment with different types, but the same fields
323 --echo # This fails in ORACLE TODO
324 --echo #
325 DELIMITER $$;
326 DECLARE
327 TYPE DeptRecTyp1 IS RECORD (
328 dept_id NUMBER(4)
329 );
330 TYPE DeptRecTyp2 IS RECORD (
331 dept_id NUMBER(4)
332 );
333 dept_rec1 DeptRecTyp1:= DeptRecTyp1(11);
334 dept_rec2 DeptRecTyp2:= DeptRecTyp2(12);
335 BEGIN
336 dept_rec1:= dept_rec2;
337 SELECT dept_rec1.dept_id, dept_rec2.dept_id;
338 END;
339 $$
340 DELIMITER ;$$
341
342 --echo #
343 --echo # RECORD assignment with different types
344 --echo # This fails in ORACLE with
345 --echo # PLS-00382: expression is of wrong type
346 --echo # In MariaDB we need to set sql_mode='strict_all_tables'
347 --echo # to get a similar effect. Otherwise we only get a warning.
348 --echo #
349 DELIMITER $$;
350 DECLARE
351 TYPE DeptRecTyp1 IS RECORD (
352 dept_id NUMBER(4)
353 );
354 TYPE DeptRecTyp2 IS RECORD (
355 dept_id VARCHAR2(30)
356 );
357 dept_rec1 DeptRecTyp1:= DeptRecTyp1(11);
358 dept_rec2 DeptRecTyp2:= DeptRecTyp2('a');
359 BEGIN
360 dept_rec1:= dept_rec2;
361 SELECT dept_rec1.dept_id, dept_rec2.dept_id;
362 END;
363 $$
364 DELIMITER ;$$
365
366 --echo #
367 --echo # RECORD assignment with anchored ROWTYPE from table
368 --echo #
369 CREATE TABLE t1
370 (
371 dept_id NUMBER(4)
372 );
373 INSERT INTO t1 VALUES(12);
374 DELIMITER $$;
375 DECLARE
376 TYPE DeptRecTyp IS RECORD (
377 dept_id NUMBER(4)
378 );
379 dept_rec1 DeptRecTyp:= DeptRecTyp(11);
380 dept_rec2 t1%ROWTYPE;
381 BEGIN
382 SELECT dept_id INTO dept_rec2 FROM t1 WHERE ROWNUM < 2;
383 dept_rec1:= dept_rec2;
384 SELECT dept_rec1.dept_id, dept_rec2.dept_id;
385 END;
386 $$
387 DELIMITER ;$$
388 DROP TABLE t1;
389
390 --echo #
391 --echo # RECORD assignment with anchored ROWTYPE from cursor (1)
392 --echo #
393 CREATE TABLE t1
394 (
395 dept_id NUMBER(4)
396 );
397 INSERT INTO t1 VALUES(12);
398 DELIMITER $$;
399 DECLARE
400 TYPE DeptRecTyp IS RECORD (
401 dept_id NUMBER(4)
402 );
403 CURSOR c1 IS SELECT * FROM t1;
404 dept_rec1 DeptRecTyp:= DeptRecTyp(11);
405 BEGIN
406 DECLARE
407 dept_rec2 c1%ROWTYPE;
408 BEGIN
409 SELECT dept_id INTO dept_rec2 FROM t1 WHERE ROWNUM < 2;
410 dept_rec1:= dept_rec2;
411 SELECT dept_rec1.dept_id, dept_rec2.dept_id;
412 END;
413 END;
414 $$
415 DELIMITER ;$$
416 DROP TABLE t1;
417
418 --echo #
419 --echo # RECORD assignment with anchored ROWTYPE from cursor (2)
420 --echo #
421 CREATE TABLE t1 (a INT, b VARCHAR(10));
422 DELIMITER $$;
423 CREATE PROCEDURE p1()
424 AS
425 TYPE DeptRecTyp IS RECORD (
426 dept_id NUMBER(4),
427 b VARCHAR(10)
428 );
429 CURSOR cur1 IS SELECT * FROM t1;
430 BEGIN
431 DECLARE
432 rec1 DeptRecTyp:=DeptRecTyp(11,'a');
433 rec2 cur1%ROWTYPE;
434 BEGIN
435 rec2.a:= 10;
436 rec2.b:= 'bbb';
437
438 rec1:= rec2;
439 SELECT rec1.dept_id, rec2.a;
440 END;
441 END;
442 $$
443 DELIMITER ;$$
444 CALL p1();
445 DROP TABLE t1;
446 DROP PROCEDURE p1;
447
448 --echo #
449 --echo # RECORD within stored procedures
450 --echo #
451 DELIMITER $$;
452 CREATE OR REPLACE PROCEDURE p1() AS
453 TYPE DeptRecTyp IS RECORD(dept_id NUMBER(4));
454 dept_rec DeptRecTyp;
455 BEGIN
456 END;
457 $$
458 DELIMITER ;$$
459 DROP PROCEDURE p1;
460
461 --echo #
462 --echo # SELECT INTO RECORD (??)
463 --echo #
464 CREATE TABLE t1
465 (
466 dept_id NUMBER(4),
467 dept_name VARCHAR2(30),
468 mgr_id NUMBER(6),
469 loc_id NUMBER(4)
470 );
471 INSERT INTO t1 VALUES(12, 'b',202,2000);
472
473 DELIMITER $$;
474 DECLARE
475 TYPE DeptRecTyp IS RECORD (
476 dept_id t1.dept_id%TYPE,
477 dept_name t1.dept_name%TYPE,
478 mgr_id t1.mgr_id%TYPE,
479 loc_id t1.loc_id%TYPE
480 );
481 dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
482 str VARCHAR(1024);
483 BEGIN
484 SELECT * INTO dept_rec FROM t1 WHERE ROWNUM <= 1;
485 str:=
486 'dept_id: ' || dept_rec.dept_id || '; ' ||
487 'dept_name: ' || dept_rec.dept_name || '; ' ||
488 'mgr_id: ' || dept_rec.mgr_id || '; ' ||
489 'loc_id: ' || dept_rec.loc_id;
490 SELECT str;
491 END;
492 $$
493 DELIMITER ;$$
494 DROP TABLE t1;
495
496 --echo #
497 --echo # INSERT RECORDs INTO table
498 --echo # Not supported (?)
499 --echo # This is supported in Oracle with `VALUES row_constructor_list`
500 CREATE TABLE t1
501 (
502 dept_id NUMBER(4),
503 dept_name VARCHAR2(30),
504 mgr_id NUMBER(6),
505 loc_id NUMBER(4)
506 );
507
508 DELIMITER $$;
509 --error ER_PARSE_ERROR
510 DECLARE
511 TYPE DeptRecTyp IS RECORD (
512 dept_id t1.dept_id%TYPE,
513 dept_name t1.dept_name%TYPE,
514 mgr_id t1.mgr_id%TYPE,
515 loc_id t1.loc_id%TYPE
516 );
517 dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
518 str VARCHAR(1024);
519 BEGIN
520 INSERT INTO t1 VALUES dept_rec;
521 SELECT * FROM t1;
522 END;
523 $$
524 DELIMITER ;$$
525 DROP TABLE t1;
526
527 --echo #
528 --echo # UPDATE table using RECORD
529 --echo # This is supported in Oracle
530 CREATE TABLE t1
531 (
532 dept_id NUMBER(4)
533 );
534 INSERT INTO t1 VALUES(12);
535
536 DELIMITER $$;
537 --error ER_BAD_FIELD_ERROR
538 DECLARE
539 TYPE DeptRecTyp IS RECORD(dept_id NUMBER(4));
540 dept_rec DeptRecTyp:= DeptRecTyp(11);
541 BEGIN
542 UPDATE t1 SET ROW = dept_rec WHERE dept_id = 12;
543 END;
544 $$
545 DELIMITER ;$$
546 DROP TABLE t1;
547
548 --echo #
549 --echo # Wrong parameter count to RECORD's constructor (actual < expected)
550 --echo #
551 DELIMITER $$;
552 --error ER_OPERAND_COLUMNS
553 DECLARE
554 TYPE DeptRecTyp IS RECORD (
555 dept_id NUMBER(4),
556 dept_name VARCHAR2(30),
557 mgr_id NUMBER(6),
558 loc_id NUMBER(4)
559 );
560 dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201);
561 str VARCHAR(1024);
562 BEGIN
563 str:=
564 'dept_id: ' || dept_rec.dept_id || '; ' ||
565 'dept_name: ' || dept_rec.dept_name || '; ' ||
566 'mgr_id: ' || dept_rec.mgr_id || '; ' ||
567 'loc_id: ' || dept_rec.loc_id;
568 select str;
569 END;
570 $$
571 DELIMITER ;$$
572
573 --echo #
574 --echo # Wrong parameter count to RECORD's constructor (actual > expected)
575 --echo #
576 DELIMITER $$;
577 --error ER_OPERAND_COLUMNS
578 DECLARE
579 TYPE DeptRecTyp IS RECORD (
580 dept_id NUMBER(4),
581 dept_name VARCHAR2(30),
582 mgr_id NUMBER(6),
583 loc_id NUMBER(4)
584 );
585 dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700,2000);
586 str VARCHAR(1024);
587 BEGIN
588 str:=
589 'dept_id: ' || dept_rec.dept_id || '; ' ||
590 'dept_name: ' || dept_rec.dept_name || '; ' ||
591 'mgr_id: ' || dept_rec.mgr_id || '; ' ||
592 'loc_id: ' || dept_rec.loc_id;
593 SELECT str;
594 END;
595 $$
596 DELIMITER ;$$
597
598 --echo #
599 --echo # Case insensitivity test for RECORD variable
600 --echo #
601 DELIMITER $$;
602 DECLARE
603 TYPE DeptRecTyp IS RECORD (
604 dept_id NUMBER(4)
605 );
606 dept_rec DeptRecTyp:= DeptRecTyp(11);
607 str VARCHAR(1024);
608 BEGIN
609 SELECT DEPT_rec.DEPT_id;
610 END;
611 $$
612 DELIMITER ;$$
613
614 --echo #
615 --echo # Quoted indentifier test for RECORD variable
616 --echo #
617 DELIMITER $$;
618 DECLARE
619 TYPE DeptRecTyp IS RECORD (
620 dept_id NUMBER(4)
621 );
622 "dept/rec" DeptRecTyp:= DeptRecTyp(11);
623 str VARCHAR(1024);
624 BEGIN
625 SELECT "dept/rec".dept_id;
626 END;
627 $$
628 DELIMITER ;$$
629
630 --echo #
631 --echo # RECORD type used in a stored PROCEDURE
632 --echo #
633 DELIMITER $$;
634 CREATE PROCEDURE p1(v NUMBER) AS
635 TYPE DeptRecTyp IS RECORD (
636 dept_id NUMBER(4)
637 );
638 dept_rec DeptRecTyp:= DeptRecTyp(11);
639 BEGIN
640 SELECT dept_rec.dept_id + v;
641 END;
642 $$
643 DELIMITER ;$$
644 CALL p1(4);
645 DROP PROCEDURE p1;
646
647 --echo #
648 --echo # RECORD type used in a stored FUNCTION
649 --echo #
650 DELIMITER $$;
651 CREATE FUNCTION f1(v NUMBER)
652 RETURN NUMBER IS
653 TYPE DeptRecTyp IS RECORD (
654 dept_id NUMBER(4)
655 );
656 dept_rec DeptRecTyp:= DeptRecTyp(11);
657 BEGIN
658 RETURN dept_rec.dept_id + v;
659 END;
660 $$
661 DELIMITER ;$$
662 SELECT f1(4);
663 DROP FUNCTION f1;
664
665 --echo #
666 --echo # RECORD keyword as identifier (sp var name)
667 --echo #
668 DELIMITER $$;
669 DECLARE
670 RECORD NUMBER(4);
671 BEGIN
672 SELECT RECORD;
673 END;
674 $$
675 DELIMITER ;$$
676
677 --echo #
678 --echo # RECORD keyword as identifier (function name)
679 --echo #
680 DELIMITER $$;
681 --disable_warnings
682 CREATE FUNCTION RECORD(a IN NUMBER)
683 RETURN NUMBER
684 IS b NUMBER(11,2);
685 BEGIN
686 b:= a;
687 RETURN(b);
688 END;
689 $$
690 DELIMITER ;$$
691 DROP FUNCTION RECORD;
692 --enable_warnings
693
694
695 SET sql_mode=default;
696
697 --echo #
698 --echo # Basic RECORD, anonymous block sql_mode=default;
699 --echo #
700 DELIMITER $$;
701 --error ER_UNKNOWN_DATA_TYPE
702 BEGIN NOT ATOMIC
703 DECLARE TYPE DeptRecTyp IS RECORD (
704 dept_id INT
705 );
706 DECLARE dept_rec DeptRecTyp DEFAULT DeptRecTyp(11);
707 SELECT dept_rec.dept_id;
708 END;
709 $$
710 DELIMITER ;$$
711
712 --echo #
713 --echo # Basic RECORD, stored procedure sql_mode=default;
714 --echo #
715 DELIMITER $$;
716 --error ER_UNKNOWN_DATA_TYPE
717 CREATE OR REPLACE PROCEDURE p1()
718 BEGIN
719 DECLARE TYPE DeptRecTyp IS RECORD (
720 dept_id INT
721 );
722 DECLARE dept_rec DeptRecTyp DEFAULT DeptRecTyp(11);
723 SELECT dept_rec.dept_id;
724 END;
725 $$
726 DELIMITER ;$$
727 --error ER_SP_DOES_NOT_EXIST
728 CALL p1();
729 --error ER_SP_DOES_NOT_EXIST
730 DROP PROCEDURE p1;
731
732 --echo #
733 --echo # Basic RECORD, stored function sql_mode=default;
734 --echo #
735 DELIMITER $$;
736 --error ER_UNKNOWN_DATA_TYPE
737 CREATE OR REPLACE FUNCTION f1() RETURNS INT
738 BEGIN
739 DECLARE TYPE DeptRecTyp IS RECORD (
740 dept_id INT
741 );
742 DECLARE dept_rec DeptRecTyp DEFAULT DeptRecTyp(11);
743 RETURN dept_rec.dept_id;
744 END;
745 $$
746 DELIMITER ;$$
747 --error ER_SP_DOES_NOT_EXIST
748 SELECT f1();
749 --error ER_SP_DOES_NOT_EXIST
750 DROP FUNCTION f1;
751
752 SET sql_mode=ORACLE;
753
754 --echo #
755 --echo # Ensure keyword TYPE can still be used when sql_mode=ORACLE
756 --echo # for DATABASE
757 --echo #
758 CREATE DATABASE TYPE;
759 DROP DATABASE TYPE;
760
761 --echo #
762 --echo # Ensure keyword TYPE can still be used when sql_mode=ORACLE
763 --echo # for TABLE
764 --echo #
765 CREATE TABLE TYPE(a INTEGER);
766 INSERT INTO TYPE VALUES (1);
767 SELECT * FROM TYPE;
768 DROP TABLE TYPE;
769
770
771 --echo #
772 --echo # Ensure keyword TYPE can still be used when sql_mode=ORACLE
773 --echo # for COLUMN name
774 --echo #
775 CREATE TABLE t1(TYPE INTEGER);
776 INSERT INTO t1 VALUES (1);
777 SELECT TYPE FROM t1 WHERE TYPE=1;
778 SELECT b.TYPE FROM t1 b WHERE b.TYPE=1;
779 DROP TABLE t1;
780
781 --echo #
782 --echo # Ensure that DATE field of a record maps correctly to MariaDB
783 --echo # DATETIME
784 --echo #
785 DELIMITER $$;
786 DECLARE
787 TYPE rec_t IS RECORD (
788 a INT,
789 b DATE
790 );
791 rec rec_t:= rec_t(1,'2021-01-01 10:20:30');
792 BEGIN
793 CREATE TABLE t1 AS SELECT rec.a, rec.b;
794 SHOW CREATE TABLE t1;
795 SELECT * FROM t1;
796 DROP TABLE t1;
797 END;
798 $$
799 DELIMITER ;$$
800
801
802 --echo #
803 --echo # Ensure that nested record types are properly parsed (without crash, etc)
804 --echo #
805 SET sql_mode=ORACLE;
806 DELIMITER $$;
807 --error ER_UNKNOWN_DATA_TYPE
808 DECLARE
809 TYPE rec_t IS RECORD (
810 a INT,
811 b DATE
812 );
813 rec rec_t:= rec_t(1,'2021-01-01 10:20:30');
814 TYPE rec2_t IS RECORD
815 (
816 a INT,
817 b rec_t
818 );
819 BEGIN
820 NULL;
821 END;
822 $$
823 DELIMITER ;$$
824
825
826 --echo #
827 --echo # Multiple RECORD variable declaration+initialization test
828 --echo #
829 DELIMITER $$;
830 DECLARE
831 TYPE rec_t IS RECORD (
832 a NUMBER(4),
833 b VARCHAR2(30)
834 );
835 rec0, rec1, rec2 rec_t := rec_t(1,'a'); BEGIN
836 SELECT rec0.a || rec0.b AS r0, rec1.a || rec1.b AS t1, rec2.a || rec2.b AS r2; END;
837 $$
838 DELIMITER ;$$
839
840 --echo # End of 11.8 tests