# Start of 11.8 tests
# 
# MDEV-34317 DECLARE TYPE type_name IS RECORD (..) with scalar members in stored routines
# 
set sql_mode=oracle;
#
# Basic RECORD
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4),
dept_name  VARCHAR2(30),
mgr_id     NUMBER(6),
loc_id     NUMBER(4)
);
dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
str VARCHAR(1024);
BEGIN
str:= 
'dept_id:   ' || dept_rec.dept_id   || '; ' ||
'dept_name: ' || dept_rec.dept_name || '; ' ||
'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
'loc_id:    ' || dept_rec.loc_id;
SELECT str;
END;
$$
str
dept_id:   11; dept_name: a; mgr_id:    201; loc_id:    1700
#
# RECORD fields with anchored type
#
CREATE TABLE t1
(
dept_id    NUMBER(4),
dept_name  VARCHAR2(30),
mgr_id     NUMBER(6),
loc_id     NUMBER(4)
);
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    t1.dept_id%TYPE,
dept_name  t1.dept_name%TYPE,
mgr_id     t1.mgr_id%TYPE,
loc_id     t1.loc_id%TYPE
);
dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
str VARCHAR(1024);
BEGIN
str:= 
'dept_id:   ' || dept_rec.dept_id   || '; ' ||
'dept_name: ' || dept_rec.dept_name || '; ' ||
'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
'loc_id:    ' || dept_rec.loc_id;
SELECT str;
END;
$$
str
dept_id:   11; dept_name: a; mgr_id:    201; loc_id:    1700
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(5) CHARACTER SET utf8mb4, b INT, c DOUBLE);
CREATE OR REPLACE PROCEDURE p1 AS
TYPE RecT IS RECORD (
a t1.a%TYPE,
b t1.b%TYPE,
c t1.c%TYPE
);
rec RecT:= RecT('a',2,3.5);
BEGIN
CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c;
SHOW CREATE TABLE t2;
SELECT * FROM t2;
DROP TABLE t2;
END;
$$
CALL p1;
Table	Create Table
t2	CREATE TABLE "t2" (
  "rec.a" varchar(5) DEFAULT NULL,
  "rec.b" int(11) DEFAULT NULL,
  "rec.c" double DEFAULT NULL
)
rec.a	rec.b	rec.c
a	2	3.5
DROP PROCEDURE p1;
DROP TABLE t1;
#
# RECORD using SUBTYPE
# This is not supported yet
#
CREATE TABLE t1
(
dept_id    NUMBER(4),
dept_name  VARCHAR2(30),
mgr_id     NUMBER(6),
loc_id     NUMBER(4)
);
DECLARE
SUBTYPE DeptRecTyp IS t1%ROWTYPE;
dept_rec DeptRecTyp;
str VARCHAR(1024);
BEGIN
dept_rec.dept_id  := 11;
dept_rec.dept_name:= 'a';
dept_rec.mgr_id   := 201;
dept_rec.loc_id   := 1700;
str:= 
'dept_id:   ' || dept_rec.dept_id   || '; ' ||
'dept_name: ' || dept_rec.dept_name || '; ' ||
'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
'loc_id:    ' || dept_rec.loc_id;
SELECT str;
END;
$$
ERROR HY000: Unknown data type: 'DeptRecTyp'
DROP TABLE t1;
#
# RECORD with NOT NULL or default clauses
# This is not supported yet
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4) NOT NULL := 10,
dept_name  VARCHAR2(30) NOT NULL := 'Administration',
mgr_id     NUMBER(6) := 200,
loc_id     NUMBER(4) := 1700
);
dept_rec DeptRecTyp;
str VARCHAR(1024);
BEGIN
str:= 
'dept_id:   ' || dept_rec.dept_id   || '; ' ||
'dept_name: ' || dept_rec.dept_name || chr(13) ||
'mgr_id:    ' || dept_rec.mgr_id    || chr(13) ||
'loc_id:    ' || dept_rec.loc_id;
SELECT str;
END;
$$
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,
dept_name  VARCHAR2(30) NOT NULL := 'Administration',
mgr_id ...' at line 3
#
# Nested RECORDs
# This is not supported yet
#
DECLARE
TYPE name_rec IS RECORD (
first  VARCHAR(64),
last   VARCHAR(64)
);
TYPE contact IS RECORD (
name  name_rec,     -- a nested record
phone VARCHAR(32)
);
person contact;
str VARCHAR(1024);
BEGIN
person.name.first := 'John';
person.name.last := 'Brown';
person.phone := '1-654-222-1234';
str:= 
person.name.first  || ' ' ||
person.name.last   || ', ' ||
person.phone;
SELECT str;
END;
$$
ERROR HY000: Unknown data type: 'name_rec'
#
# RECORD duplicate type declaration
#
DECLARE
TYPE RcType IS RECORD (
a NUMBER(4)
);
TYPE RcType IS RECORD (
b NUMBER(4)
);
BEGIN
END;
$$
ERROR HY000: Duplicate declaration: 'RcType'
#
# RECORD with no field
#
DECLARE
TYPE RcType IS RECORD (
);
rec RcType;
str VARCHAR(1024);
BEGIN
END;
$$
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 ');
rec RcType;
str VARCHAR(1024);
BEGIN
END' at line 3
#
# RECORD field test
#
DECLARE
TYPE RcType IS RECORD (
int11 INT,
dec103 DECIMAL(10,3),
flt0 FLOAT,
dbl0 DOUBLE,
enum0 ENUM('a','b'),
bit3 BIT(3),
varchar10 VARCHAR(10),
text1 TEXT,
tinytext1 TINYTEXT,
mediumtext1 MEDIUMTEXT CHARACTER SET utf8,
longtext1 LONGTEXT,
time3 TIME(3),
datetime4 DATETIME(4),
timestamp5 TIMESTAMP(5),
date0 DATE
);
rec1,rec2 RcType:= RcType(4, 11.2, 6.7, 111.43234663, 'a', b'101',
'bbbb', 'TEXT', 'TINY', 'MEDIUM', 'LONG',
'10:14:22','2001-07-22 10:14:22', '2001-07-22 12:12:12', '2001-07-22');
rec3 RcType;
BEGIN
SELECT rec1.int11, rec2.timestamp5, rec3.date0;
END;
$$
rec1.int11	rec2.timestamp5	rec3.date0
4	2001-07-22 12:12:12.00000	NULL
#
# RECORD with field manipulations
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
dept_rec DeptRecTyp;
test INT:=4;
BEGIN
dept_rec.dept_id:= 2;
test:= test + dept_rec.dept_id;
dept_rec.dept_id:= 5;
SELECT dept_rec.dept_id, test;
END;
$$
dept_rec.dept_id	test
5	6
#
# RECORD comparison
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4),
dept_name  VARCHAR(20)
);
dept_rec1 DeptRecTyp := DeptRecTyp(1,'a');
dept_rec2 DeptRecTyp := DeptRecTyp(1,'a');
BEGIN
SELECT dept_rec1 = dept_rec2;
dept_rec2.dept_id:= 2;
SELECT dept_rec1 = dept_rec2;
END;
$$
dept_rec1 = dept_rec2
1
dept_rec1 = dept_rec2
0
#
# RECORD comparison
# RECORD type comparison is not supported for records with only
# one field (bug, see MDEV-35274)
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
dept_rec1 DeptRecTyp;
dept_rec2 DeptRecTyp;
BEGIN
SELECT dept_rec1 = dept_rec2;
END;
$$
ERROR 21000: Operand should contain 1 column(s)
#
# RECORD assignment with the same type
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
dept_rec1 DeptRecTyp:= DeptRecTyp(11);
dept_rec2 DeptRecTyp:= DeptRecTyp(12);
BEGIN
dept_rec1:= dept_rec2;
SELECT dept_rec1.dept_id, dept_rec2.dept_id;
END;
$$
dept_rec1.dept_id	dept_rec2.dept_id
12	12
#
# RECORD assignment with different types, but the same fields
# This fails in ORACLE TODO
#
DECLARE
TYPE DeptRecTyp1 IS RECORD (
dept_id    NUMBER(4)
);
TYPE DeptRecTyp2 IS RECORD (
dept_id    NUMBER(4)
);
dept_rec1 DeptRecTyp1:= DeptRecTyp1(11);
dept_rec2 DeptRecTyp2:= DeptRecTyp2(12);
BEGIN
dept_rec1:= dept_rec2;
SELECT dept_rec1.dept_id, dept_rec2.dept_id;
END;
$$
dept_rec1.dept_id	dept_rec2.dept_id
12	12
#
# RECORD assignment with different types
# This fails in ORACLE with
# PLS-00382: expression is of wrong type
# In MariaDB we need to set sql_mode='strict_all_tables'
# to get a similar effect. Otherwise we only get a warning.
#
DECLARE
TYPE DeptRecTyp1 IS RECORD (
dept_id    NUMBER(4)
);
TYPE DeptRecTyp2 IS RECORD (
dept_id    VARCHAR2(30)
);
dept_rec1 DeptRecTyp1:= DeptRecTyp1(11);
dept_rec2 DeptRecTyp2:= DeptRecTyp2('a');
BEGIN
dept_rec1:= dept_rec2;
SELECT dept_rec1.dept_id, dept_rec2.dept_id;
END;
$$
dept_rec1.dept_id	dept_rec2.dept_id
0	a
Warnings:
Warning	1366	Incorrect decimal value: 'a' for column ``.``.`dept_id` at row 0
#
# RECORD assignment with anchored ROWTYPE from table
#
CREATE TABLE t1
(
dept_id    NUMBER(4)
);
INSERT INTO t1 VALUES(12);
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
dept_rec1 DeptRecTyp:= DeptRecTyp(11);
dept_rec2 t1%ROWTYPE;
BEGIN
SELECT dept_id INTO dept_rec2 FROM t1 WHERE ROWNUM < 2;
dept_rec1:= dept_rec2;
SELECT dept_rec1.dept_id, dept_rec2.dept_id;
END;
$$
dept_rec1.dept_id	dept_rec2.dept_id
12	12
DROP TABLE t1;
#
# RECORD assignment with anchored ROWTYPE from cursor (1)
#
CREATE TABLE t1
(
dept_id    NUMBER(4)
);
INSERT INTO t1 VALUES(12);
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
CURSOR c1 IS SELECT * FROM t1;
dept_rec1 DeptRecTyp:= DeptRecTyp(11);
BEGIN
DECLARE
dept_rec2 c1%ROWTYPE;
BEGIN
SELECT dept_id INTO dept_rec2 FROM t1 WHERE ROWNUM < 2;
dept_rec1:= dept_rec2;
SELECT dept_rec1.dept_id, dept_rec2.dept_id;
END;
END;
$$
dept_rec1.dept_id	dept_rec2.dept_id
12	12
DROP TABLE t1;
#
# RECORD assignment with anchored ROWTYPE from cursor (2)
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
AS
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4),
b VARCHAR(10)
);
CURSOR cur1 IS SELECT * FROM t1;
BEGIN
DECLARE
rec1 DeptRecTyp:=DeptRecTyp(11,'a');
rec2 cur1%ROWTYPE;
BEGIN
rec2.a:= 10;
rec2.b:= 'bbb';
rec1:= rec2;
SELECT rec1.dept_id, rec2.a;
END;
END;
$$
CALL p1();
rec1.dept_id	rec2.a
10	10
DROP TABLE t1;
DROP PROCEDURE p1;
#
# RECORD within stored procedures
#
CREATE OR REPLACE PROCEDURE p1() AS
TYPE DeptRecTyp IS RECORD(dept_id    NUMBER(4));
dept_rec DeptRecTyp;
BEGIN
END;
$$
DROP PROCEDURE p1;
#
# SELECT INTO RECORD (??)
#
CREATE TABLE t1
(
dept_id    NUMBER(4),
dept_name  VARCHAR2(30),
mgr_id     NUMBER(6),
loc_id     NUMBER(4)
);
INSERT INTO t1 VALUES(12, 'b',202,2000);
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    t1.dept_id%TYPE,
dept_name  t1.dept_name%TYPE,
mgr_id     t1.mgr_id%TYPE,
loc_id     t1.loc_id%TYPE
);
dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
str VARCHAR(1024);
BEGIN
SELECT * INTO dept_rec FROM t1 WHERE ROWNUM <= 1;
str:= 
'dept_id:   ' || dept_rec.dept_id   || '; ' ||
'dept_name: ' || dept_rec.dept_name || '; ' ||
'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
'loc_id:    ' || dept_rec.loc_id;
SELECT str;
END;
$$
str
dept_id:   12; dept_name: b; mgr_id:    202; loc_id:    2000
DROP TABLE t1;
#
# INSERT RECORDs INTO table
# Not supported (?)
# This is supported in Oracle with `VALUES row_constructor_list`
CREATE TABLE t1
(
dept_id    NUMBER(4),
dept_name  VARCHAR2(30),
mgr_id     NUMBER(6),
loc_id     NUMBER(4)
);
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    t1.dept_id%TYPE,
dept_name  t1.dept_name%TYPE,
mgr_id     t1.mgr_id%TYPE,
loc_id     t1.loc_id%TYPE
);
dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700);
str VARCHAR(1024);
BEGIN
INSERT INTO t1 VALUES dept_rec;
SELECT * FROM t1;
END;
$$
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;
SELECT * FROM t1;
END' at line 11
DROP TABLE t1;
#
# UPDATE table using RECORD
# This is supported in Oracle
CREATE TABLE t1
(
dept_id    NUMBER(4)
);
INSERT INTO t1 VALUES(12);
DECLARE
TYPE DeptRecTyp IS RECORD(dept_id    NUMBER(4));
dept_rec DeptRecTyp:= DeptRecTyp(11);
BEGIN
UPDATE t1 SET ROW = dept_rec WHERE dept_id = 12;
END;
$$
ERROR 42S22: Unknown column 'ROW' in 'SET'
DROP TABLE t1;
#
# Wrong parameter count to RECORD's constructor (actual < expected)
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4),
dept_name  VARCHAR2(30),
mgr_id     NUMBER(6),
loc_id     NUMBER(4)
);
dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201);
str VARCHAR(1024);
BEGIN
str:= 
'dept_id:   ' || dept_rec.dept_id   || '; ' ||
'dept_name: ' || dept_rec.dept_name || '; ' ||
'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
'loc_id:    ' || dept_rec.loc_id;
select str;
END;
$$
ERROR 21000: Operand should contain 4 column(s)
#
# Wrong parameter count to RECORD's constructor (actual > expected)
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4),
dept_name  VARCHAR2(30),
mgr_id     NUMBER(6),
loc_id     NUMBER(4)
);
dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700,2000);
str VARCHAR(1024);
BEGIN
str:= 
'dept_id:   ' || dept_rec.dept_id   || '; ' ||
'dept_name: ' || dept_rec.dept_name || '; ' ||
'mgr_id:    ' || dept_rec.mgr_id    || '; ' ||
'loc_id:    ' || dept_rec.loc_id;
SELECT str;
END;
$$
ERROR 21000: Operand should contain 4 column(s)
#
# Case insensitivity test for RECORD variable
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
dept_rec DeptRecTyp:= DeptRecTyp(11);
str VARCHAR(1024);
BEGIN
SELECT DEPT_rec.DEPT_id;
END;
$$
DEPT_rec.DEPT_id
11
#
# Quoted indentifier test for RECORD variable
#
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
"dept/rec" DeptRecTyp:= DeptRecTyp(11);
str VARCHAR(1024);
BEGIN
SELECT "dept/rec".dept_id;
END;
$$
"dept/rec".dept_id
11
#
# RECORD type used in a stored PROCEDURE
#
CREATE PROCEDURE p1(v NUMBER) AS
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
dept_rec DeptRecTyp:= DeptRecTyp(11);
BEGIN
SELECT dept_rec.dept_id + v;
END;
$$
CALL p1(4);
dept_rec.dept_id + v
15
DROP PROCEDURE p1;
#
# RECORD type used in a stored FUNCTION
#
CREATE FUNCTION f1(v NUMBER)
RETURN NUMBER IS
TYPE DeptRecTyp IS RECORD (
dept_id    NUMBER(4)
);
dept_rec DeptRecTyp:= DeptRecTyp(11);
BEGIN
RETURN dept_rec.dept_id + v;
END;
$$
SELECT f1(4);
f1(4)
15
DROP FUNCTION f1;
#
# RECORD keyword as identifier (sp var name)
#
DECLARE
RECORD NUMBER(4);
BEGIN
SELECT RECORD;
END;
$$
RECORD
NULL
#
# RECORD keyword as identifier (function name)
#
CREATE FUNCTION RECORD(a IN NUMBER) 
RETURN NUMBER 
IS b NUMBER(11,2);
BEGIN
b:= a;
RETURN(b); 
END;
$$
DROP FUNCTION RECORD;
SET sql_mode=default;
#
# Basic RECORD, anonymous block sql_mode=default;
#
BEGIN NOT ATOMIC
DECLARE TYPE DeptRecTyp IS RECORD (
dept_id    INT
);
DECLARE dept_rec DeptRecTyp DEFAULT DeptRecTyp(11);
SELECT dept_rec.dept_id;
END;
$$
ERROR HY000: Unknown data type: 'DeptRecTyp'
#
# Basic RECORD, stored procedure sql_mode=default;
#
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE TYPE DeptRecTyp IS RECORD (
dept_id    INT
);
DECLARE dept_rec DeptRecTyp DEFAULT DeptRecTyp(11);
SELECT dept_rec.dept_id;
END;
$$
ERROR HY000: Unknown data type: 'DeptRecTyp'
CALL p1();
ERROR 42000: PROCEDURE test.p1 does not exist
DROP PROCEDURE p1;
ERROR 42000: PROCEDURE test.p1 does not exist
#
# Basic RECORD, stored function sql_mode=default;
#
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
DECLARE TYPE DeptRecTyp IS RECORD (
dept_id    INT
);
DECLARE dept_rec DeptRecTyp DEFAULT DeptRecTyp(11);
RETURN dept_rec.dept_id;
END;
$$
ERROR HY000: Unknown data type: 'DeptRecTyp'
SELECT f1();
ERROR 42000: FUNCTION test.f1 does not exist
DROP FUNCTION f1;
ERROR 42000: FUNCTION test.f1 does not exist
SET sql_mode=ORACLE;
#
# Ensure keyword TYPE can still be used when sql_mode=ORACLE
# for DATABASE
#
CREATE DATABASE TYPE;
DROP DATABASE TYPE;
#
# Ensure keyword TYPE can still be used when sql_mode=ORACLE
# for TABLE
#
CREATE TABLE TYPE(a INTEGER);
INSERT INTO TYPE VALUES (1);
SELECT * FROM TYPE;
a
1
DROP TABLE TYPE;
#
# Ensure keyword TYPE can still be used when sql_mode=ORACLE
# for COLUMN name
#
CREATE TABLE t1(TYPE INTEGER);
INSERT INTO t1 VALUES (1);
SELECT TYPE FROM t1 WHERE TYPE=1;
TYPE
1
SELECT b.TYPE FROM t1 b WHERE b.TYPE=1;
TYPE
1
DROP TABLE t1;
#
# Ensure that DATE field of a record maps correctly to MariaDB
# DATETIME
#
DECLARE
TYPE rec_t IS RECORD (
a INT,
b DATE
);
rec rec_t:= rec_t(1,'2021-01-01 10:20:30');
BEGIN
CREATE TABLE t1 AS SELECT rec.a, rec.b;
SHOW CREATE TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;
END;
$$
Table	Create Table
t1	CREATE TABLE "t1" (
  "rec.a" int(11) DEFAULT NULL,
  "rec.b" datetime DEFAULT NULL
)
rec.a	rec.b
1	2021-01-01 10:20:30
#
# Ensure that nested record types are properly parsed (without crash, etc)
#
SET sql_mode=ORACLE;
DECLARE
TYPE rec_t IS RECORD (
a INT,
b DATE
);
rec rec_t:= rec_t(1,'2021-01-01 10:20:30');
TYPE rec2_t IS RECORD
(
a INT,
b rec_t
);
BEGIN
NULL;
END;
$$
ERROR HY000: Unknown data type: 'rec_t'
#
# Multiple RECORD variable declaration+initialization test
#
DECLARE
TYPE rec_t IS RECORD (
a  NUMBER(4),
b  VARCHAR2(30)
);
rec0, rec1, rec2 rec_t := rec_t(1,'a'); BEGIN
SELECT rec0.a || rec0.b AS r0, rec1.a || rec1.b AS t1, rec2.a || rec2.b AS r2; END;
$$
r0	t1	r2
1a	1a	1a
# End of 11.8 tests