Debian Codegraph @

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

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