#
# MDEV-10914 ROW data type for stored routine variables
#
#
# ROW of ROWs is not supported yet
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW(a ROW(a INT));
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 'ROW(a INT));
END' at line 3
#
# Returning the entire ROW parameter from a function
#
CREATE FUNCTION f1(a ROW(a INT, b INT)) RETURNS INT
BEGIN
RETURN a;
END;
$$
SELECT f1(ROW(10,20));
ERROR 21000: Operand should contain 1 column(s)
DROP FUNCTION f1;
#
# ROW as an SP parameter
#
CREATE FUNCTION f1(a ROW(a INT,b INT)) RETURNS INT
BEGIN
RETURN a.b;
END;
$$
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW(a INT,b INT) DEFAULT (11,21);
SELECT f1(a);
END;
$$
SELECT f1(ROW(10,20));
f1(ROW(10,20))
20
SELECT f1(10);
ERROR 21000: Operand should contain 2 column(s)
SELECT f1(ROW(10,20,30));
ERROR 21000: Operand should contain 2 column(s)
CALL p1();
f1(a)
21
DROP PROCEDURE p1;
DROP FUNCTION f1;
CREATE PROCEDURE p1(a ROW(a INT,b INT))
BEGIN
SELECT a.a, a.b;
END;
$$
CALL p1(ROW(10,20));
a.a	a.b
10	20
CALL p1(10);
ERROR 21000: Operand should contain 2 column(s)
CALL p1(ROW(10,20,30));
ERROR 21000: Operand should contain 2 column(s)
DROP PROCEDURE p1;
#
# ROW as an SP OUT parameter
#
CREATE PROCEDURE p1(OUT a ROW(a INT,b INT))
BEGIN
SET a.a=10;
SET a.b=20;
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE a ROW(a INT,b INT) DEFAULT (11,21);
CALL p1(a);
SELECT a.a,a.b;
END;
$$
CALL p2();
a.a	a.b
10	20
DROP PROCEDURE p2;
DROP PROCEDURE p1;
#
# ROW as an SP return value is not supported yet
#
CREATE FUNCTION p1() RETURNS ROW(a INT)
BEGIN
RETURN NULL;
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 'ROW(a INT)
BEGIN
RETURN NULL;
END' at line 1
#
# Diplicate row field
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT, a DOUBLE);
SELECT a.a;
END;
$$
ERROR 42S21: Duplicate column name 'a'
#
# Bad scalar default value
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT, b DOUBLE) DEFAULT 1;
SELECT a.a;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP PROCEDURE p1;
#
# Bad ROW default value with a wrong number of fields
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT, b DOUBLE) DEFAULT ROW(1,2,3);
SELECT a.a;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP PROCEDURE p1;
#
# Scalar variable vs table alias cause no ambiguity
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a INT;
-- a.x is a table column here (not a row variable field)
SELECT a.x FROM a;
SELECT a.x FROM t1 a;
END;
$$
DROP PROCEDURE p1;
#
# Using the entire ROW variable in select list
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT);
SELECT a;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 1 column(s)
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT,b INT);
SELECT a;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 1 column(s)
DROP PROCEDURE p1;
#
# Using the entire ROW variable in functions
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT);
SELECT COALESCE(a);
END;
$$
CALL p1();
ERROR 21000: Operand should contain 1 column(s)
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT,b INT);
SELECT COALESCE(a);
END;
$$
CALL p1();
ERROR 21000: Operand should contain 1 column(s)
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT);
SELECT a+1;
END;
$$
CALL p1();
ERROR HY000: Illegal parameter data types row and int for operation '+'
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT,b INT);
SELECT a+1;
END;
$$
CALL p1();
ERROR HY000: Illegal parameter data types row and int for operation '+'
DROP PROCEDURE p1;
#
# Comparing the entire ROW to a scalar value
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT,b INT);
SELECT a=1;
END;
$$
CALL p1();
ERROR HY000: Illegal parameter data types row and int for operation '='
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT,b INT);
SELECT 1=a;
END;
$$
CALL p1();
ERROR HY000: Illegal parameter data types int and row for operation '='
DROP PROCEDURE p1;
#
# Passing the entire ROW to a stored function
#
CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
RETURN a;
END;
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (a INT,b INT);
SELECT f1(a);
END;
$$
CALL p1();
ERROR 21000: Operand should contain 1 column(s)
DROP PROCEDURE p1;
DROP FUNCTION f1;
#
# Assigning a scalar value to a ROW variable with 1 column
#
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT);
SET rec=1;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 1 column(s)
DROP PROCEDURE p1;
#
# Assigning a scalar value to a ROW variable with 2 columns
#
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT,b INT);
SET rec=1;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP PROCEDURE p1;
#
# Assigning a ROW value to a ROW variable with different number of columns
#
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT,b INT);
SET rec=ROW(1,2,3);
END;
$$
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP PROCEDURE p1;
#
# Returning the entire ROW from a function is not supported yet
# This syntax would be needed: SELECT f1().x FROM DUAL;
#
CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
DECLARE rec ROW(a INT);
RETURN rec;
END;
$$
SELECT f1(10);
ERROR 21000: Operand should contain 1 column(s)
DROP FUNCTION f1;
#
# Using the entire ROW in SELECT..CREATE
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT,b INT);
CREATE TABLE t1 AS SELECT rec;
END;
$$
CALL p1();
ERROR 21000: Operand should contain 1 column(s)
DROP PROCEDURE p1;
#
# Using the entire ROW in LIMIT
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT);
SET rec.a= '10';
SELECT * FROM t1 LIMIT rec;
END;
$$
ERROR HY000: A variable of a non-integer based type in LIMIT clause
#
# Setting ROW fields using a SET command
#
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT,b DOUBLE,c VARCHAR(10));
DECLARE a INT;
SET @a= 10, rec.a=10, rec.b=20, rec.c= 'test', a= 5;
SELECT rec.a, rec.b, rec.c, a;
END;
$$
CALL p1();
rec.a	rec.b	rec.c	a
10	20	test	5
DROP PROCEDURE p1;
#
# Assigning a ROW variable from a ROW value
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT,b INT);
SET rec=ROW(1,2);
SELECT rec.a, rec.b;
END;
$$
CALL p1();
rec.a	rec.b
1	2
DROP PROCEDURE p1;
#
# Assigning a ROW variable from another ROW value
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW(a INT,b INT);
DECLARE rec2 ROW(a INT,b INT);
SET rec1=ROW(1,2);
SET rec2=rec1;
SELECT rec2.a, rec2.b;
END;
$$
CALL p1();
rec2.a	rec2.b
1	2
DROP PROCEDURE p1;
#
# Comparing a ROW variable to a ROW() function
#
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT,b INT);
SET rec.a= 1;
SET rec.b= 2;
SELECT rec=(0,0), rec=ROW(0,0), (0,0)=rec, ROW(0,0)=rec;
SELECT rec=(1,2), rec=ROW(1,2), (1,2)=rec, ROW(1,2)=rec;
SELECT rec=(NULL,0), rec=ROW(NULL,0);
SELECT rec=(NULL,2), rec=ROW(NULL,2);
SELECT rec<>(0,0), rec<>ROW(0,0);
SELECT rec<>(1,2), rec<>ROW(1,2);
SELECT rec<>(NULL,0), rec<>ROW(NULL,0);
SELECT rec<>(NULL,2), rec<>ROW(NULL,2);
SELECT rec IN ((0,0)), rec IN (ROW(0,0));
SELECT rec IN ((1,2)), rec IN (ROW(1,2));
SELECT rec IN ((0,NULL),(1,2));
SELECT rec NOT IN ((0,NULL),(1,1));
SELECT rec NOT IN ((1,NULL),(1,1));
END;
$$
CALL p1();
rec=(0,0)	rec=ROW(0,0)	(0,0)=rec	ROW(0,0)=rec
0	0	0	0
rec=(1,2)	rec=ROW(1,2)	(1,2)=rec	ROW(1,2)=rec
1	1	1	1
rec=(NULL,0)	rec=ROW(NULL,0)
0	0
rec=(NULL,2)	rec=ROW(NULL,2)
NULL	NULL
rec<>(0,0)	rec<>ROW(0,0)
1	1
rec<>(1,2)	rec<>ROW(1,2)
0	0
rec<>(NULL,0)	rec<>ROW(NULL,0)
1	1
rec<>(NULL,2)	rec<>ROW(NULL,2)
NULL	NULL
rec IN ((0,0))	rec IN (ROW(0,0))
0	0
rec IN ((1,2))	rec IN (ROW(1,2))
1	1
rec IN ((0,NULL),(1,2))
1
rec NOT IN ((0,NULL),(1,1))
1
rec NOT IN ((1,NULL),(1,1))
NULL
DROP PROCEDURE p1;
#
# Comparing a ROW variable to another ROW variable
#
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE rec1,rec2,rec3 ROW(a INT,b INT);
SET rec1.a= 1;
SET rec1.b= 2;
SET rec2.a= 11;
SET rec2.b= 12;
SET rec3.a= 11;
SET rec3.b= 12;
SELECT rec1=rec2, rec2=rec1, rec2=rec3, rec3=rec2;
END;
$$
CALL p1();
rec1=rec2	rec2=rec1	rec2=rec3	rec3=rec2
0	0	1	1
DROP PROCEDURE p1;
#
# Referencing a non-existing row variable
#
CREATE PROCEDURE p1()
BEGIN
SET a.b=1;
END;
$$
ERROR HY000: Unknown structured system variable or ROW routine variable 'a'
#
# Referencing a non-existing row field
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW(a INT,b INT);
SELECT a.c FROM t1;
END;
$$
ERROR HY000: Row variable 'a' does not have a field 'c'
#
# ROW and scalar variables with the same name shadowing each other
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW(a INT);
SET a.a=100;
SELECT a.a;
BEGIN
DECLARE a INT DEFAULT 200;
SELECT a;
BEGIN
DECLARE a ROW(a INT);
SET a.a=300;
SELECT a.a;
END;
SELECT a;
END;
SELECT a.a;
END;
$$
CALL p1();
a.a
100
a
200
a.a
300
a
200
a.a
100
DROP PROCEDURE p1;
#
# ROW with good default values
#
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW(a INT,b INT) DEFAULT (10,20);
DECLARE b ROW(a INT,b INT) DEFAULT (11,21);
DECLARE c ROW(a INT,b INT) DEFAULT a;
SELECT a.a, a.b, b.a, b.b, c.a, c.b FROM DUAL;
END;
$$
CALL p1;
a.a	a.b	b.a	b.b	c.a	c.b
10	20	11	21	10	20
DROP PROCEDURE p1;
#
# ROW in WHERE clause
#
CREATE TABLE t1 (a INT,b INT);
INSERT INTO t1 VALUES (10,20);
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT,b INT) DEFAULT ROW(10,20);
SELECT * FROM t1 WHERE rec=ROW(a,b);
SELECT * FROM t1 WHERE ROW(a,b)=rec;
SELECT * FROM t1 WHERE rec=ROW(10,20);
SELECT * FROM t1 WHERE ROW(10,20)=rec;
END;
$$
CALL p1();
a	b
10	20
a	b
10	20
a	b
10	20
a	b
10	20
DROP TABLE t1;
DROP PROCEDURE p1;
#
# ROW fields in WHERE clause
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT);
SET rec.a= 10;
SELECT * FROM t1 WHERE a=rec.a;
END;
$$
CALL p1();
a
10
DROP TABLE t1;
DROP PROCEDURE p1;
#
# ROW fields in HAVING clause
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT);
SET rec.a= 10;
SELECT * FROM t1 HAVING a=rec.a;
SELECT * FROM t1 HAVING MIN(a)=rec.a;
END;
$$
CALL p1();
a
10
a
10
DROP TABLE t1;
DROP PROCEDURE p1;
#
# ROW fields in LIMIT clause
#
CREATE TABLE t1 (a INT);
SELECT 1 FROM t1 LIMIT t1.a;
ERROR 42000: Undeclared variable: t1
DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT);
SET rec.a= 10;
SELECT * FROM t1 LIMIT rec.a;
END;
$$
CALL p1();
a
10
20
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a VARCHAR(10));
SET rec.a= '10';
SELECT * FROM t1 LIMIT rec.a;
END;
$$
ERROR HY000: A variable of a non-integer based type in LIMIT clause
#
# ROW fields in select list
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
CREATE PROCEDURE p1()
BEGIN
DECLARE t1 ROW(a INT);
SET t1.a= 10;
SELECT t1.a, 'This is the variable t1.a value, rather than the column t1.a' AS comm FROM t1;
SELECT t1.a, t2.a, t1.a+t2.a FROM t1 t2;
END;
$$
CALL p1();
t1.a	comm
10	This is the variable t1.a value, rather than the column t1.a
10	This is the variable t1.a value, rather than the column t1.a
t1.a	a	t1.a+t2.a
10	10	20
10	20	30
DROP TABLE t1;
DROP PROCEDURE p1;
#
# ROW fields as insert values
#
CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT, b VARCHAR(10));
SET rec.a= 10;
SET rec.b= 'test';
INSERT INTO t1 VALUES (rec.a, rec.b);
END;
$$
CALL p1();
SELECT * FROM t1;
a	b
10	test
DROP TABLE t1;
DROP PROCEDURE p1;
#
# ROW fields as SP out parameters
#
CREATE PROCEDURE p1(OUT a INT, OUT b VARCHAR(32))
BEGIN
SET a= 10;
SET b= 'test';
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE rec ROW(a INT, b VARCHAR(10));
CALL p1(rec.a, rec.b);
SELECT rec.a, rec.b;
END;
$$
CALL p2;
rec.a	rec.b
10	test
DROP PROCEDURE p1;
DROP PROCEDURE p2;
#
# ROW fields as dynamic SQL out parameters
#
CREATE PROCEDURE p1(OUT a INT, OUT b VARCHAR(32))
BEGIN
SET a= 20;
SET b= 'test-dynamic-sql';
END;
$$
CREATE PROCEDURE p2()
BEGIN
DECLARE rec ROW(a INT, b VARCHAR(30));
EXECUTE IMMEDIATE 'CALL p1(?,?)' USING rec.a, rec.b;
SELECT rec.a, rec.b;
END;
$$
CALL p2;
rec.a	rec.b
20	test-dynamic-sql
DROP PROCEDURE p1;
DROP PROCEDURE p2;
#
# ROW fields as SELECT..INTO targets
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT, b VARCHAR(10));
SELECT 10,'test' INTO rec.a,rec.b;
SELECT rec.a, rec.b;
END;
$$
CALL p1;
rec.a	rec.b
10	test
DROP PROCEDURE p1;
#
# Implicit default NULL handling
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec ROW(a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,0), e TIME, f DATETIME);
SELECT rec.a, rec.b, rec.c, rec.d, rec.e, rec.f FROM DUAL;
END;
$$
CALL p1();
rec.a	rec.b	rec.c	rec.d	rec.e	rec.f
NULL	NULL	NULL	NULL	NULL	NULL
DROP PROCEDURE p1;
#
# NULL handling
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW(a INT, b VARCHAR(10)) DEFAULT (NULL,NULL);
DECLARE rec2 ROW(a INT, b VARCHAR(10)) DEFAULT rec1;
SELECT rec1.a, rec1.b, rec2.a, rec2.b;
SET rec1= (10,20);
SET rec2= rec1;
SELECT rec1.a, rec1.b, rec2.a, rec2.b;
SET rec1= (NULL,20);
SET rec2= rec1;
SELECT rec1.a, rec1.b, rec2.a, rec2.b;
SET rec1= (10,NULL);
SET rec2= rec1;
SELECT rec1.a, rec1.b, rec2.a, rec2.b;
SET rec1= (NULL,NULL);
SET rec2= rec1;
SELECT rec1.a, rec1.b, rec2.a, rec2.b;
END;
$$
CALL p1;
rec1.a	rec1.b	rec2.a	rec2.b
NULL	NULL	NULL	NULL
rec1.a	rec1.b	rec2.a	rec2.b
10	20	10	20
rec1.a	rec1.b	rec2.a	rec2.b
NULL	20	NULL	20
rec1.a	rec1.b	rec2.a	rec2.b
10	NULL	10	NULL
rec1.a	rec1.b	rec2.a	rec2.b
NULL	NULL	NULL	NULL
DROP PROCEDURE p1;
#
# Testing multiple ROW variable declarations
# This makes sure that fill_field_definitions() is called only once
# per a ROW field, so create length is not converted to internal length
# multiple times.
#
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1, rec2, rec3 ROW(a VARCHAR(10) CHARACTER SET utf8);
CREATE TABLE t1 AS SELECT rec1.a, rec2.a, rec3.a;
END;
$$
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `rec1.a` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `rec2.a` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `rec3.a` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# INT
#
CREATE PROCEDURE p1() BEGIN DECLARE var INT; DECLARE rec ROW(var INT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(1); DECLARE rec ROW(var INT(1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(2); DECLARE rec ROW(var INT(2)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(3); DECLARE rec ROW(var INT(3)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(4); DECLARE rec ROW(var INT(4)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(5); DECLARE rec ROW(var INT(5)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(6); DECLARE rec ROW(var INT(6)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(7); DECLARE rec ROW(var INT(7)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(8); DECLARE rec ROW(var INT(8)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(9); DECLARE rec ROW(var INT(9)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(10); DECLARE rec ROW(var INT(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(11); DECLARE rec ROW(var INT(11)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(11) DEFAULT NULL,
  `rec.var` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(12); DECLARE rec ROW(var INT(12)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(12) DEFAULT NULL,
  `rec.var` int(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(13); DECLARE rec ROW(var INT(13)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(13) DEFAULT NULL,
  `rec.var` int(13) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(14); DECLARE rec ROW(var INT(14)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(14) DEFAULT NULL,
  `rec.var` int(14) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(20); DECLARE rec ROW(var INT(20)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(20) DEFAULT NULL,
  `rec.var` int(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var INT(21); DECLARE rec ROW(var INT(21)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` int(21) DEFAULT NULL,
  `rec.var` int(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# TINYINT
#
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT; DECLARE rec ROW(var TINYINT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(4) DEFAULT NULL,
  `rec.var` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(1); DECLARE rec ROW(var TINYINT(1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(4) DEFAULT NULL,
  `rec.var` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(2); DECLARE rec ROW(var TINYINT(2)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(4) DEFAULT NULL,
  `rec.var` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(3); DECLARE rec ROW(var TINYINT(3)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(4) DEFAULT NULL,
  `rec.var` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(4); DECLARE rec ROW(var TINYINT(4)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(4) DEFAULT NULL,
  `rec.var` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(5); DECLARE rec ROW(var TINYINT(5)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(5) DEFAULT NULL,
  `rec.var` tinyint(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(6); DECLARE rec ROW(var TINYINT(6)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(6) DEFAULT NULL,
  `rec.var` tinyint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(7); DECLARE rec ROW(var TINYINT(7)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(7) DEFAULT NULL,
  `rec.var` tinyint(7) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(8); DECLARE rec ROW(var TINYINT(8)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(8) DEFAULT NULL,
  `rec.var` tinyint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(9); DECLARE rec ROW(var TINYINT(9)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(9) DEFAULT NULL,
  `rec.var` tinyint(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(10); DECLARE rec ROW(var TINYINT(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(10) DEFAULT NULL,
  `rec.var` tinyint(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(11); DECLARE rec ROW(var TINYINT(11)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(11) DEFAULT NULL,
  `rec.var` tinyint(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(12); DECLARE rec ROW(var TINYINT(12)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(12) DEFAULT NULL,
  `rec.var` tinyint(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(13); DECLARE rec ROW(var TINYINT(13)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(13) DEFAULT NULL,
  `rec.var` tinyint(13) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(14); DECLARE rec ROW(var TINYINT(14)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(14) DEFAULT NULL,
  `rec.var` tinyint(14) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(20); DECLARE rec ROW(var TINYINT(20)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(20) DEFAULT NULL,
  `rec.var` tinyint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYINT(21); DECLARE rec ROW(var TINYINT(21)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinyint(21) DEFAULT NULL,
  `rec.var` tinyint(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# SMALLINT
#
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT; DECLARE rec ROW(var SMALLINT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(6) DEFAULT NULL,
  `rec.var` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(1); DECLARE rec ROW(var SMALLINT(1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(6) DEFAULT NULL,
  `rec.var` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(2); DECLARE rec ROW(var SMALLINT(2)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(6) DEFAULT NULL,
  `rec.var` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(3); DECLARE rec ROW(var SMALLINT(3)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(6) DEFAULT NULL,
  `rec.var` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(4); DECLARE rec ROW(var SMALLINT(4)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(6) DEFAULT NULL,
  `rec.var` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(5); DECLARE rec ROW(var SMALLINT(5)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(6) DEFAULT NULL,
  `rec.var` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(6); DECLARE rec ROW(var SMALLINT(6)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(6) DEFAULT NULL,
  `rec.var` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(7); DECLARE rec ROW(var SMALLINT(7)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(7) DEFAULT NULL,
  `rec.var` smallint(7) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(8); DECLARE rec ROW(var SMALLINT(8)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(8) DEFAULT NULL,
  `rec.var` smallint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(9); DECLARE rec ROW(var SMALLINT(9)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(9) DEFAULT NULL,
  `rec.var` smallint(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(10); DECLARE rec ROW(var SMALLINT(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(10) DEFAULT NULL,
  `rec.var` smallint(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(11); DECLARE rec ROW(var SMALLINT(11)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(11) DEFAULT NULL,
  `rec.var` smallint(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(12); DECLARE rec ROW(var SMALLINT(12)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(12) DEFAULT NULL,
  `rec.var` smallint(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(13); DECLARE rec ROW(var SMALLINT(13)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(13) DEFAULT NULL,
  `rec.var` smallint(13) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(14); DECLARE rec ROW(var SMALLINT(14)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(14) DEFAULT NULL,
  `rec.var` smallint(14) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(20); DECLARE rec ROW(var SMALLINT(20)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(20) DEFAULT NULL,
  `rec.var` smallint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var SMALLINT(21); DECLARE rec ROW(var SMALLINT(21)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` smallint(21) DEFAULT NULL,
  `rec.var` smallint(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# MEDIUMINT
#
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT; DECLARE rec ROW(var MEDIUMINT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(9) DEFAULT NULL,
  `rec.var` mediumint(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(1); DECLARE rec ROW(var MEDIUMINT(1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(8) DEFAULT NULL,
  `rec.var` mediumint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(2); DECLARE rec ROW(var MEDIUMINT(2)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(8) DEFAULT NULL,
  `rec.var` mediumint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(3); DECLARE rec ROW(var MEDIUMINT(3)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(8) DEFAULT NULL,
  `rec.var` mediumint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(4); DECLARE rec ROW(var MEDIUMINT(4)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(8) DEFAULT NULL,
  `rec.var` mediumint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(5); DECLARE rec ROW(var MEDIUMINT(5)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(8) DEFAULT NULL,
  `rec.var` mediumint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(6); DECLARE rec ROW(var MEDIUMINT(6)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(8) DEFAULT NULL,
  `rec.var` mediumint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(7); DECLARE rec ROW(var MEDIUMINT(7)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(8) DEFAULT NULL,
  `rec.var` mediumint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(8); DECLARE rec ROW(var MEDIUMINT(8)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(8) DEFAULT NULL,
  `rec.var` mediumint(8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(9); DECLARE rec ROW(var MEDIUMINT(9)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(9) DEFAULT NULL,
  `rec.var` mediumint(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(10); DECLARE rec ROW(var MEDIUMINT(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(10) DEFAULT NULL,
  `rec.var` mediumint(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(11); DECLARE rec ROW(var MEDIUMINT(11)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(11) DEFAULT NULL,
  `rec.var` mediumint(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(12); DECLARE rec ROW(var MEDIUMINT(12)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(12) DEFAULT NULL,
  `rec.var` mediumint(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(13); DECLARE rec ROW(var MEDIUMINT(13)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(13) DEFAULT NULL,
  `rec.var` mediumint(13) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(14); DECLARE rec ROW(var MEDIUMINT(14)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(14) DEFAULT NULL,
  `rec.var` mediumint(14) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(20); DECLARE rec ROW(var MEDIUMINT(20)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(20) DEFAULT NULL,
  `rec.var` mediumint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMINT(21); DECLARE rec ROW(var MEDIUMINT(21)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumint(21) DEFAULT NULL,
  `rec.var` mediumint(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# BIGINT
#
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT; DECLARE rec ROW(var BIGINT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(1); DECLARE rec ROW(var BIGINT(1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(2); DECLARE rec ROW(var BIGINT(2)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(3); DECLARE rec ROW(var BIGINT(3)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(4); DECLARE rec ROW(var BIGINT(4)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(5); DECLARE rec ROW(var BIGINT(5)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(6); DECLARE rec ROW(var BIGINT(6)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(7); DECLARE rec ROW(var BIGINT(7)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(8); DECLARE rec ROW(var BIGINT(8)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(9); DECLARE rec ROW(var BIGINT(9)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(10); DECLARE rec ROW(var BIGINT(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(11); DECLARE rec ROW(var BIGINT(11)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(12); DECLARE rec ROW(var BIGINT(12)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(13); DECLARE rec ROW(var BIGINT(13)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(14); DECLARE rec ROW(var BIGINT(14)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(20); DECLARE rec ROW(var BIGINT(20)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(20) DEFAULT NULL,
  `rec.var` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BIGINT(21); DECLARE rec ROW(var BIGINT(21)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` bigint(21) DEFAULT NULL,
  `rec.var` bigint(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# DOUBLE
#
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE; DECLARE rec ROW(var DOUBLE); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double DEFAULT NULL,
  `rec.var` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,1); DECLARE rec ROW(var DOUBLE(30,1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,1) DEFAULT NULL,
  `rec.var` double(30,1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,2); DECLARE rec ROW(var DOUBLE(30,2)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,2) DEFAULT NULL,
  `rec.var` double(30,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,3); DECLARE rec ROW(var DOUBLE(30,3)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,3) DEFAULT NULL,
  `rec.var` double(30,3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,4); DECLARE rec ROW(var DOUBLE(30,4)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,4) DEFAULT NULL,
  `rec.var` double(30,4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,5); DECLARE rec ROW(var DOUBLE(30,5)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,5) DEFAULT NULL,
  `rec.var` double(30,5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,6); DECLARE rec ROW(var DOUBLE(30,6)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,6) DEFAULT NULL,
  `rec.var` double(30,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,7); DECLARE rec ROW(var DOUBLE(30,7)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,7) DEFAULT NULL,
  `rec.var` double(30,7) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,8); DECLARE rec ROW(var DOUBLE(30,8)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,8) DEFAULT NULL,
  `rec.var` double(30,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,9); DECLARE rec ROW(var DOUBLE(30,9)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,9) DEFAULT NULL,
  `rec.var` double(30,9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,10); DECLARE rec ROW(var DOUBLE(30,10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,10) DEFAULT NULL,
  `rec.var` double(30,10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,11); DECLARE rec ROW(var DOUBLE(30,11)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,11) DEFAULT NULL,
  `rec.var` double(30,11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,12); DECLARE rec ROW(var DOUBLE(30,12)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,12) DEFAULT NULL,
  `rec.var` double(30,12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,13); DECLARE rec ROW(var DOUBLE(30,13)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,13) DEFAULT NULL,
  `rec.var` double(30,13) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,14); DECLARE rec ROW(var DOUBLE(30,14)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,14) DEFAULT NULL,
  `rec.var` double(30,14) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,20); DECLARE rec ROW(var DOUBLE(30,20)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,20) DEFAULT NULL,
  `rec.var` double(30,20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DOUBLE(30,21); DECLARE rec ROW(var DOUBLE(30,21)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` double(30,21) DEFAULT NULL,
  `rec.var` double(30,21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# VARCHAR
#
CREATE PROCEDURE p1() BEGIN DECLARE var CHAR; DECLARE rec ROW(var CHAR); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` char(1) DEFAULT NULL,
  `rec.var` char(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BINARY; DECLARE rec ROW(var BINARY); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` binary(1) DEFAULT NULL,
  `rec.var` binary(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var CHAR(1); DECLARE rec ROW(var CHAR(1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` char(1) DEFAULT NULL,
  `rec.var` char(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var CHAR(10); DECLARE rec ROW(var CHAR(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` char(10) DEFAULT NULL,
  `rec.var` char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var NCHAR(10); DECLARE rec ROW(var NCHAR(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `rec.var` char(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var BINARY(10); DECLARE rec ROW(var BINARY(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` binary(10) DEFAULT NULL,
  `rec.var` binary(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var VARBINARY(10); DECLARE rec ROW(var VARBINARY(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` varbinary(10) DEFAULT NULL,
  `rec.var` varbinary(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var VARCHAR(10); DECLARE rec ROW(var VARCHAR(10)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` varchar(10) DEFAULT NULL,
  `rec.var` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var VARCHAR(10) CHARACTER SET utf8; DECLARE rec ROW(var VARCHAR(10) CHARACTER SET utf8); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `rec.var` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin; DECLARE rec ROW(var VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `rec.var` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# TIME
#
CREATE PROCEDURE p1() BEGIN DECLARE var TIME; DECLARE rec ROW(var TIME); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` time DEFAULT NULL,
  `rec.var` time DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TIME(1); DECLARE rec ROW(var TIME(1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` time(1) DEFAULT NULL,
  `rec.var` time(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TIME(2); DECLARE rec ROW(var TIME(2)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` time(2) DEFAULT NULL,
  `rec.var` time(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TIME(3); DECLARE rec ROW(var TIME(3)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` time(3) DEFAULT NULL,
  `rec.var` time(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TIME(4); DECLARE rec ROW(var TIME(4)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` time(4) DEFAULT NULL,
  `rec.var` time(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TIME(5); DECLARE rec ROW(var TIME(5)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` time(5) DEFAULT NULL,
  `rec.var` time(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TIME(6); DECLARE rec ROW(var TIME(6)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` time(6) DEFAULT NULL,
  `rec.var` time(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# DATETIME
#
CREATE PROCEDURE p1() BEGIN DECLARE var DATETIME; DECLARE rec ROW(var DATETIME); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` datetime DEFAULT NULL,
  `rec.var` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DATETIME(1); DECLARE rec ROW(var DATETIME(1)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` datetime(1) DEFAULT NULL,
  `rec.var` datetime(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DATETIME(2); DECLARE rec ROW(var DATETIME(2)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` datetime(2) DEFAULT NULL,
  `rec.var` datetime(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DATETIME(3); DECLARE rec ROW(var DATETIME(3)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` datetime(3) DEFAULT NULL,
  `rec.var` datetime(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DATETIME(4); DECLARE rec ROW(var DATETIME(4)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` datetime(4) DEFAULT NULL,
  `rec.var` datetime(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DATETIME(5); DECLARE rec ROW(var DATETIME(5)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` datetime(5) DEFAULT NULL,
  `rec.var` datetime(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var DATETIME(6); DECLARE rec ROW(var DATETIME(6)); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` datetime(6) DEFAULT NULL,
  `rec.var` datetime(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# LOB
#
CREATE PROCEDURE p1() BEGIN DECLARE var TEXT; DECLARE rec ROW(var TEXT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` text DEFAULT NULL,
  `rec.var` text DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYTEXT; DECLARE rec ROW(var TINYTEXT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinytext DEFAULT NULL,
  `rec.var` tinytext DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMTEXT; DECLARE rec ROW(var MEDIUMTEXT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumtext DEFAULT NULL,
  `rec.var` mediumtext DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var LONGTEXT; DECLARE rec ROW(var LONGTEXT); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` longtext DEFAULT NULL,
  `rec.var` longtext DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TEXT CHARACTER SET utf8; DECLARE rec ROW(var TEXT CHARACTER SET utf8); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` text CHARACTER SET utf8 DEFAULT NULL,
  `rec.var` text CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var TINYTEXT CHARACTER SET utf8; DECLARE rec ROW(var TINYTEXT CHARACTER SET utf8); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` tinytext CHARACTER SET utf8 DEFAULT NULL,
  `rec.var` tinytext CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var MEDIUMTEXT CHARACTER SET utf8; DECLARE rec ROW(var MEDIUMTEXT CHARACTER SET utf8); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` mediumtext CHARACTER SET utf8 DEFAULT NULL,
  `rec.var` mediumtext CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE PROCEDURE p1() BEGIN DECLARE var LONGTEXT CHARACTER SET utf8; DECLARE rec ROW(var LONGTEXT CHARACTER SET utf8); CREATE TABLE t1 AS SELECT var,rec.var FROM DUAL;END;
CALL p1();
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `var` longtext CHARACTER SET utf8 DEFAULT NULL,
  `rec.var` longtext CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
#
# End of MDEV-10914 ROW data type for stored routine variables
#
#
# MDEV-12291 Allow ROW variables as SELECT INTO targets
#
# ROW 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(a INT, b VARCHAR(32), c DOUBLE);
SELECT * FROM t1 INTO rec1;
SELECT rec1.a, rec1.b;
END;
$$
Warnings:
Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
CALL p1();
ERROR 21000: The used SELECT statements have a different number of columns
DROP TABLE t1;
DROP PROCEDURE p1;
# Multiple ROW variables
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW(a INT, b VARCHAR(32));
SELECT * FROM t1 INTO rec1, rec1;
SELECT rec1.a, rec1.b;
END;
$$
Warnings:
Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
CALL p1();
ERROR 21000: Operand should contain 2 column(s)
DROP TABLE t1;
DROP PROCEDURE p1;
# ROW variables working example
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
CREATE PROCEDURE p1()
BEGIN
DECLARE rec1 ROW(a INT, b VARCHAR(32));
SELECT * FROM t1 INTO rec1;
SELECT rec1.a, rec1.b;
END;
$$
Warnings:
Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
CALL p1();
rec1.a	rec1.b
10	b10
DROP TABLE t1;
DROP PROCEDURE p1;
#
# MDEV-13273 Confusion between table alias and ROW type variable
#
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (0,0);
CREATE PROCEDURE p1()
BEGIN
DECLARE a INT;
DECLARE b INT;
-- a.c1 is a table column
SELECT a.c1 INTO b
FROM t1 a
WHERE a.c2 = 0;
SELECT b;
END;
$$
CALL p1;
b
0
DROP PROCEDURE p1;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (0,0);
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW (c1 INT, c2 INT) DEFAULT ROW(101,102);
DECLARE b INT;
-- a.c1 is a ROW variable field
SELECT a.c1 INTO b
FROM t1 a
WHERE a.c2 = 102;
SELECT b;
END;
$$
CALL p1;
b
101
DROP PROCEDURE p1;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (0,0);
CREATE PROCEDURE p1()
BEGIN
DECLARE a ROW TYPE OF t1 DEFAULT ROW (10,20);
DECLARE b INT;
-- a.c1 is a ROW variable field
SELECT a.c1 INTO b
FROM t1 a
WHERE a.c2 = 20;
SELECT b;
END;
$$
CALL p1;
b
10
DROP PROCEDURE p1;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (0,0);
CREATE PROCEDURE p1()
BEGIN
DECLARE cur1 CURSOR FOR SELECT * FROM t1;
BEGIN
DECLARE a ROW TYPE OF cur1 DEFAULT ROW (10,20);
DECLARE b INT;
-- a.c1 is a ROW variable field
SELECT a.c1 INTO b
FROM t1 a
WHERE a.c2 = 20;
SELECT b;
END;
END;
$$
CALL p1;
b
10
DROP PROCEDURE p1;
DROP TABLE t1;
#
# MDEV-13527 Crash when EXPLAIN SELECT .. INTO row_sp_variable.field
#
BEGIN NOT ATOMIC
DECLARE a ROW(a INT);
EXPLAIN SELECT 1 INTO a.a;
END;
$$
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
#
# MDEV-14139 Anchored data types for variables
#
BEGIN NOT ATOMIC
DECLARE row1 ROW(int11 INT,text1 TEXT);
DECLARE a_row1 TYPE OF row1;
DECLARE aa_row1 TYPE OF a_row1;
CREATE TABLE t1 AS SELECT a_row1.int11 AS int11, a_row1.text1 AS text1;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT aa_row1.int11 AS int11, aa_row1.text1 AS text1;
SHOW CREATE TABLE t1;
DROP TABLE t1;
END;
$$
Table	Create Table
t1	CREATE TABLE `t1` (
  `int11` int(11) DEFAULT NULL,
  `text1` text DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Table	Create Table
t1	CREATE TABLE `t1` (
  `int11` int(11) DEFAULT NULL,
  `text1` text DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
#
# MDEV-16385 ROW SP variable is allowed in unexpected context
#
CREATE TABLE t1 (a INT);
BEGIN NOT ATOMIC
DECLARE row ROW(a INT);
SELECT * FROM t1 ORDER BY row;
END;
$$
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
CREATE TABLE t1 (a INT);
BEGIN NOT ATOMIC
DECLARE row ROW(a INT);
SELECT * FROM t1 HAVING row;
END;
$$
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
BEGIN NOT ATOMIC
DECLARE a ROW(a INT);
SELECT 1 LIKE 2 ESCAPE a;
END;
$$
ERROR 21000: Operand should contain 1 column(s)