#
# Start of 10.3 tests
#
#
# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types
#
#
# Referring to a table in a non-existing database
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF test2.t1;
END;
$$
CALL p1();
ERROR 42S02: Table 'test2.t1' doesn't exist
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
CALL p1();
ERROR 42S02: Table 'test2.t1' doesn't exist
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Referring to a table in the current database
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF t1;
CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
SHOW CREATE TABLE t2;
DROP TABLE t2;
END;
$$
CALL p1();
ERROR 42S02: Table 'test.t1' doesn't exist
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
CALL p1();
Table	Create Table
t2	CREATE TABLE `t2` (
  `rec.a` int(11) DEFAULT NULL,
  `rec.b` varchar(10) DEFAULT NULL,
  `rec.c` double DEFAULT NULL,
  `rec.d` decimal(10,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Referring to a table in an explicitly specified database
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF test.t1;
CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
SHOW CREATE TABLE t2;
DROP TABLE t2;
END;
$$
CALL p1();
ERROR 42S02: Table 'test.t1' doesn't exist
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
CALL p1();
Table	Create Table
t2	CREATE TABLE `t2` (
  `rec.a` int(11) DEFAULT NULL,
  `rec.b` varchar(10) DEFAULT NULL,
  `rec.c` double DEFAULT NULL,
  `rec.d` decimal(10,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Referring to a view in the current database
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF v1;
CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
SHOW CREATE TABLE t2;
DROP TABLE t2;
END;
$$
CALL p1();
ERROR 42S02: Table 'test.v1' doesn't exist
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
CREATE VIEW v1 AS SELECT * FROM t1;
CALL p1();
Table	Create Table
t2	CREATE TABLE `t2` (
  `rec.a` int(11) DEFAULT NULL,
  `rec.b` varchar(10) DEFAULT NULL,
  `rec.c` double DEFAULT NULL,
  `rec.d` decimal(10,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP VIEW v1;
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Referring to a view in an explicitly specified database
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF test.v1;
CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
SHOW CREATE TABLE t2;
DROP TABLE t2;
END;
$$
CALL p1();
ERROR 42S02: Table 'test.v1' doesn't exist
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
CREATE VIEW v1 AS SELECT * FROM t1;
CALL p1();
Table	Create Table
t2	CREATE TABLE `t2` (
  `rec.a` int(11) DEFAULT NULL,
  `rec.b` varchar(10) DEFAULT NULL,
  `rec.c` double DEFAULT NULL,
  `rec.d` decimal(10,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP VIEW v1;
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Checking that all table ROW TYPE fields are NULL by default
#
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
SELECT rec1.a, rec1.b, rec1.c, rec1.d;
END;
$$
CALL p1();
rec1.a	rec1.b	rec1.c	rec1.d
NULL	NULL	NULL	NULL
DROP TABLE t1;
DROP PROCEDURE p1;
#
# A table ROW TYPE variable with a ROW expression as a default
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb');
SELECT rec1.a, rec1.b;
END;
$$
CALL p1();
rec1.a	rec1.b
10	bbb
DROP TABLE t1;
DROP PROCEDURE p1;
#
# A table ROW TYPE variable with an incompatible ROW expression as a default
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb','ccc');
SELECT rec1.a, rec1.b;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP TABLE t1;
DROP PROCEDURE p1;
#
# A table ROW TYPE variable with a ROW variable as a default
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW(a INT, b VARCHAR(10)) DEFAULT ROW(10,'bbb');
DECLARE rec2 ROW TYPE OF t1 DEFAULT rec1;
SELECT rec2.a, rec2.b;
END;
$$
CALL p1();
rec2.a	rec2.b
10	bbb
DROP TABLE t1;
DROP PROCEDURE p1;
#
# A ROW variable using a table ROW TYPE variable as a default
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb');
DECLARE rec2 ROW(a INT, b VARCHAR(10)) DEFAULT rec1;
SELECT rec2.a, rec2.b;
END;
$$
CALL p1();
rec2.a	rec2.b
10	bbb
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Assigning table ROW TYPE variables with a different column count
#
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE);
CREATE TABLE t2 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
DECLARE rec2 ROW TYPE OF t2;
SET rec2=rec1;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
DECLARE rec2 ROW TYPE OF t2;
SET rec1=rec2;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 3 column(s)
DROP TABLE t2;
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Assigning compatible table ROW TYPE variables (equal number of fields)
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE TABLE t2 (x INT, y VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
DECLARE rec2 ROW TYPE OF t2;
SET rec1.a= 10;
SET rec1.b= 'bbb';
SET rec2=rec1;
SELECT rec2.x, rec2.y;
END;
$$
CALL p1();
rec2.x	rec2.y
10	bbb
DROP TABLE t2;
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Assigning between incompatible table ROW TYPE and explicit ROW variables
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
DECLARE rec2 ROW(x INT,y INT,z INT);
SET rec2.x= 10;
SET rec2.y= 20;
SET rec2.z= 30;
SET rec1= rec2;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Assigning between compatible table ROW TYPE and explicit ROW variables
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
DECLARE rec2 ROW(x INT,y INT);
SET rec2.x= 10;
SET rec2.y= 20;
SET rec1= rec2;
SELECT rec1.a, rec1.b;
SET rec1.a= 11;
SET rec1.b= 21;
SET rec2= rec1;
SELECT rec2.x, rec2.y;
END;
$$
CALL p1();
rec1.a	rec1.b
10	20
rec2.x	rec2.y
11	21
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Assigning table ROW TYPE from a ROW expression
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
SET rec1= ROW(10,20);
SELECT rec1.a, rec1.b;
END;
$$
CALL p1();
rec1.a	rec1.b
10	20
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Fetching a cursor into a table ROW TYPE variable with a wrong field count
#
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
CREATE TABLE t2 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
CREATE PROCEDURE p1()
BEGIN
DECLARE rec2 ROW TYPE OF t2;
DECLARE cur1 CURSOR FOR SELECT * FROM t1;
OPEN cur1;
FETCH cur1 INTO rec2;
CLOSE cur1;
END;
$$
CALL p1();
ERROR HY000: Incorrect number of FETCH variables
DROP TABLE t2;
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Fetching a cursor into a table ROW TYPE variable
#
CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
CREATE TABLE t2 LIKE t1;
INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
INSERT INTO t1 VALUES (20,'bb2',222.222e2, 12.32);
INSERT INTO t1 VALUES (30,'bb3',333.333e2, 12.33);
CREATE PROCEDURE p1()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE rec ROW TYPE OF t1;
DECLARE cur CURSOR FOR SELECT * FROM t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO rec;
IF done THEN
LEAVE read_loop;
END IF;
SELECT rec.a, rec.b, rec.c, rec.d;
INSERT INTO t2 VALUES (rec.a, rec.b, rec.c, rec.d);
END LOOP;
CLOSE cur;
END;
$$
CALL p1();
rec.a	rec.b	rec.c	rec.d
10	bb1	11111.1	12.31
rec.a	rec.b	rec.c	rec.d
20	bb2	22222.2	12.32
rec.a	rec.b	rec.c	rec.d
30	bb3	33333.3	12.33
SELECT * FROM t2;
a	b	c	d
10	bb1	11111.1	12.31
20	bb2	22222.2	12.32
30	bb3	33333.3	12.33
DROP TABLE t2;
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Fetching a cursor into a table ROW TYPE variable with different column names
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE TABLE t2 (x INT, y VARCHAR(10));
INSERT INTO t1 VALUES (10,'bbb');
CREATE PROCEDURE p1()
BEGIN
DECLARE rec2 ROW TYPE OF t2;
DECLARE cur1 CURSOR FOR SELECT * FROM t1;
OPEN cur1;
FETCH cur1 INTO rec2;
SELECT rec2.x, rec2.y;
CLOSE cur1;
END;
$$
CALL p1();
rec2.x	rec2.y
10	bbb
DROP TABLE t2;
DROP TABLE t1;
DROP PROCEDURE p1;
#
# Fetching a cursor into a table ROW TYPE variable, with truncation
#
SET sql_mode='';
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t1 VALUES (10,'11x');
CREATE PROCEDURE p1()
BEGIN
DECLARE rec2 ROW TYPE OF t2;
DECLARE cur1 CURSOR FOR SELECT * FROM t1;
OPEN cur1;
FETCH cur1 INTO rec2;
SELECT rec2.a, rec2.b;
CLOSE cur1;
END;
$$
CALL p1();
rec2.a	rec2.b
10	11
Warnings:
Warning	1265	Data truncated for column 'b' at row 1
DROP TABLE t2;
DROP TABLE t1;
DROP PROCEDURE p1;
SET sql_mode=DEFAULT;
#
# table ROW TYPE variables are not allowed in LIMIT
#
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,2);
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1 DEFAULT (1,2);
SELECT * FROM t1 LIMIT rec1.a;
END;
$$
ERROR HY000: A variable of a non-integer based type in LIMIT clause
DROP TABLE t1;
#
# table ROW TYPE variable fields as OUT parameters
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1(OUT a INT,OUT b VARCHAR(10))
BEGIN
SET a=10;
SET b='bb';
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
CALL p1(rec1.a, rec1.b);
SELECT rec1.a, rec1.b;
END;
$$
CALL p2();
rec1.a	rec1.b
10	bb
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Passing the entire table ROW TYPE variable
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10)))
BEGIN
SELECT a.a, a.b;
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bb');
CALL p1(rec1);
END;
$$
CALL p2();
a.a	a.b
10	bb
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Passing the entire table ROW TYPE variable as an OUT parameter
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1(OUT a ROW(a INT, b VARCHAR(10)))
BEGIN
SET a= ROW(10,'bb');
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
CALL p1(rec1);
SELECT rec1.a, rec1.b;
END;
$$
CALL p2();
rec1.a	rec1.b
10	bb
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Assigning a table ROW TYPE field to an OUT parameter
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1 (INOUT res INTEGER)
BEGIN
DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'b0');
SET res=rec1.a;
END;
$$
CALL p1(@res);
SELECT @res;
@res
10
SET @res=NULL;
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Testing Item_splocal_row_field_by_name::print
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb');
EXPLAIN EXTENDED SELECT rec.a, rec.b;
END;
$$
CALL p1();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select rec.a@0["a"] AS `rec.a`,rec.b@0["b"] AS `rec.b`
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Non-existing field
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF t1;
SELECT rec.c;
END;
$$
CALL p1();
ERROR HY000: Row variable 'rec' does not have a field 'c'
ALTER TABLE t1 ADD c INT;
CALL p1();
rec.c
NULL
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Testing that field names are case insensitive
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb');
SELECT rec.A, rec.B;
END;
$$
CALL p1();
rec.A	rec.B
10	bb
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Testing that table ROW TYPE uses temporary tables vs shadowed real tables
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb');
SELECT rec.A, rec.B;
END;
$$
CALL p1();
ERROR HY000: Row variable 'rec' does not have a field 'A'
DROP TEMPORARY TABLE t1;
CALL p1();
rec.A	rec.B
10	bb
DROP PROCEDURE p1;
DROP TABLE t1;
#
# Testing that the structure of table ROW TYPE variables is determined at the very beginning and is not changed after ALTER
#
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
CREATE PROCEDURE p1()
BEGIN
ALTER TABLE t1 ADD c INT;
BEGIN
DECLARE rec ROW TYPE OF t1; -- this will not have column "c"
    SET rec.c=10;
END;
END;
$$
CALL p1();
ERROR HY000: Row variable 'rec' does not have a field 'c'
DROP TABLE t1;
DROP PROCEDURE p1;
#
# SELECT INTO + table ROW TYPE variable with a wrong column count
#
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
SELECT 10,'a','b' FROM t1 INTO rec1;
SELECT rec1.a, rec1.b;
END;
$$
CALL p1();
ERROR 21000: The used SELECT statements have a different number of columns
DROP TABLE t1;
DROP PROCEDURE p1;
#
# SELECT INTO + multiple table ROW TYPE variables
#
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
SELECT 10,'a' FROM t1 INTO rec1, rec1;
SELECT rec1.a, rec1.b;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP TABLE t1;
DROP PROCEDURE p1;
#
# SELECT INTO + table ROW TYPE working example
#
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW TYPE OF t1;
SELECT * FROM t1 INTO rec1;
SELECT rec1.a, rec1.b;
END;
$$
CALL p1();
rec1.a	rec1.b
10	b10
DROP TABLE t1;
DROP PROCEDURE p1;
#
# End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types
#
#
# MDEV-12347 Valgrind reports invalid read errors in Item_field_row::element_index_by_name
#
CREATE TABLE t1 (a INT, b ENUM('b0','b1','b12','b3'));
CREATE PROCEDURE p1()
BEGIN
BEGIN
DECLARE rec ROW TYPE OF t1;
SET rec.b='b0';
SELECT rec.b;
END;
END;
$$
CALL p1();
rec.b
b0
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT, b SET('b0','b1','b12','b3'));
CREATE PROCEDURE p1()
BEGIN
BEGIN
DECLARE rec ROW TYPE OF t1;
SET rec.b='b0';
SELECT rec.b;
END;
END;
$$
CALL p1();
rec.b
b0
DROP TABLE t1;
DROP PROCEDURE p1;
#
# MDEV-13581 ROW TYPE OF t1 and t1%ROWTYPE for routine parameters
#
CREATE TABLE t1 (a INT, b TEXT, c ENUM('a','b','c'));
CREATE PROCEDURE p1 (a ROW TYPE OF t1)
BEGIN
CREATE TABLE t2 AS SELECT a.a AS a, a.b AS b, a.c AS c;
SHOW CREATE TABLE t2;
DROP TABLE t2;
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE a ROW TYPE OF t1;
CALL p1(a);
END;
$$
CALL p2();
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` text DEFAULT NULL,
  `c` varchar(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP TABLE t1;
CREATE TABLE t1 (a INT, b TEXT);
CREATE PROCEDURE p1 (OUT a ROW TYPE OF t1)
BEGIN
SET a.a=10;
SET a.b='text';
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE a ROW TYPE OF t1;
CALL p1(a);
SELECT a.a, a.b;
END;
$$
CREATE FUNCTION f1(a ROW TYPE OF t1) RETURNS TEXT
BEGIN
RETURN CONCAT(a.a, ' ', a.b);
END;
$$
CREATE FUNCTION f2() RETURNS TEXT
BEGIN
DECLARE a ROW TYPE OF t1;
CALL p1(a);
RETURN f1(a);
END;
$$
CALL p2();
a.a	a.b
10	text
SELECT f2();
f2()
10 text
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP FUNCTION f2;
DROP FUNCTION f1;
DROP TABLE t1;
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT, b TEXT);
CREATE PROCEDURE p1 (OUT a ROW TYPE OF db1.t1)
BEGIN
SET a.a=10;
SET a.b='text';
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE a ROW TYPE OF db1.t1;
CALL p1(a);
SELECT a.a, a.b;
END;
$$
CREATE FUNCTION f1(a ROW TYPE OF db1.t1) RETURNS TEXT
BEGIN
RETURN CONCAT(a.a, ' ', a.b);
END;
$$
CREATE FUNCTION f2() RETURNS TEXT
BEGIN
DECLARE a ROW TYPE OF db1.t1;
CALL p1(a);
RETURN f1(a);
END;
$$
CALL p2();
a.a	a.b
10	text
SELECT f2();
f2()
10 text
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP FUNCTION f2;
DROP FUNCTION f1;
DROP DATABASE db1;
#
# MDEV-14139 Anchored data types for variables
#
CREATE TABLE t1 (int11 INT, text0 TEXT);
BEGIN NOT ATOMIC
DECLARE row1 ROW TYPE OF t1;
DECLARE a_row1 TYPE OF row1;
DECLARE aa_row1 TYPE OF a_row1;
CREATE TABLE t2 AS SELECT a_row1.int11 AS int11, a_row1.text0 AS text0;
SHOW CREATE TABLE t2;
DROP TABLE t2;
CREATE TABLE t2 AS SELECT aa_row1.int11 AS int11, aa_row1.text0 AS text0;
SHOW CREATE TABLE t2;
DROP TABLE t2;
END;
$$
Table	Create Table
t2	CREATE TABLE `t2` (
  `int11` int(11) DEFAULT NULL,
  `text0` text DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
Table	Create Table
t2	CREATE TABLE `t2` (
  `int11` int(11) DEFAULT NULL,
  `text0` text DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
#
# End of 10.3 tests
#
#
# Start of 10.4 tests
#
#
# MDEV-31250 ROW variables do not get assigned from subselects
#
CREATE TABLE t1 (a INT, b TEXT);
INSERT INTO t1 VALUES (1,'b1');
BEGIN NOT ATOMIC
DECLARE r ROW TYPE OF t1;
SELECT * INTO r FROM t1 WHERE a=1;
SELECT r.a, r.b;
END;
$$
r.a	r.b
1	b1
DROP TABLE t1;
CREATE TABLE t1 (a INT, b TEXT);
INSERT INTO t1 VALUES (1,'b1');
BEGIN NOT ATOMIC
DECLARE r ROW TYPE OF t1;
SET r=(SELECT * FROM t1 WHERE a=1);
SELECT r.a, r.b;
END;
$$
r.a	r.b
1	b1
DROP TABLE t1;
CREATE TABLE t1 (a INT, b TEXT);
INSERT INTO t1 VALUES (1,'b1');
BEGIN NOT ATOMIC
DECLARE r ROW TYPE OF t1 DEFAULT (SELECT * FROM t1 WHERE a=1);
SELECT r.a, r.b;
END;
$$
r.a	r.b
1	b1
DROP TABLE t1;
#
# End of 10.4 tests
#
#
# Start of 11.7 tests
#
#
# MDEV-12252 ROW data type for stored function return values
#
CREATE FUNCTION f1() RETURNS ROW TYPE OF step1.step2.step3 RETURN ROW(1,2);
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 '.step3 RETURN ROW(1,2)' at line 1
CREATE FUNCTION f1() RETURNS ROW TYPE OF t1 RETURN ROW(1,2);
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS ROW TYPE OF `test`.`t1`
RETURN ROW(1,2)	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT f1();
ERROR 42S02: Table 'test.t1' doesn't exist
DROP FUNCTION f1;
CREATE FUNCTION f1() RETURNS ROW TYPE OF test.t1 RETURN ROW(1,2);
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS ROW TYPE OF `test`.`t1`
RETURN ROW(1,2)	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
SELECT f1();
ERROR 42S02: Table 'test.t1' doesn't exist
DROP FUNCTION f1;
CREATE TABLE t1 (a INT, b VARCHAR(32));
CREATE FUNCTION f1() RETURNS ROW TYPE OF test.t1 RETURN (SELECT * FROM t1);
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS ROW TYPE OF `test`.`t1`
RETURN (SELECT * FROM t1)	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
CREATE PROCEDURE p1()
BEGIN
DECLARE r ROW TYPE OF t1 DEFAULT f1();
SELECT r.a, r.b;
END;
$$
CALL p1();
r.a	r.b
NULL	NULL
SELECT f1();
ERROR 21000: Operand should contain 1 column(s)
SELECT f1()=ROW(1,'b1') AS c;
c
NULL
SELECT f1()=ROW(NULL,NULL) AS c;
c
NULL
INSERT INTO t1 VALUES (1,'b1');
CALL p1();
r.a	r.b
1	b1
SELECT f1();
ERROR 21000: Operand should contain 1 column(s)
SELECT f1()=ROW(1,'b1') AS c;
c
1
SELECT f1()=ROW(1,'') AS c;
c
0
SELECT f1()=ROW(2,'b1') AS c;
c
0
SELECT f1()=ROW(1,NULL) AS c;
c
NULL
SELECT f1()=ROW(NULL,'b1') AS c;
c
NULL
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS ROW TYPE OF `test`.`t1`
RETURN (SELECT * FROM t1)	latin1	latin1_swedish_ci	utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES (2,'b2');
CALL p1();
ERROR 21000: Subquery returns more than 1 row
SELECT f1();
ERROR 21000: Operand should contain 1 column(s)
SELECT f1()=ROW(1,'b1') AS c;
ERROR 21000: Subquery returns more than 1 row
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP TABLE t1;
#
# End of 11.7 tests
#