MDEV-14139 Anchored data types for variables

20 files changed, 915 insertions, 81 deletions

diff --git a/mysql-test/r/sp-anchor-row-type-cursor.result b/mysql-test/r/sp-anchor-row-type-cursor.result
index a2435e17f9f..add771c534c 100644
--- a/mysql-test/r/sp-anchor-row-type-cursor.result
+++ b/mysql-test/r/sp-anchor-row-type-cursor.result
@@ -1001,3 +1001,33 @@ t1	CREATE TABLE `t1` (
   `rec1.a` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP PROCEDURE p1;
+#
+# MDEV-14139 Anchored data types for variables
+#
+BEGIN NOT ATOMIC
+DECLARE c1 CURSOR FOR SELECT 10 AS a, 'bbb' AS b, TIME'10:20:30' AS c;
+BEGIN
+DECLARE row1 ROW TYPE OF c1;
+DECLARE a_row1 TYPE OF row1;
+DECLARE aa_row1 TYPE OF a_row1;
+CREATE TABLE t2 AS SELECT a_row1.a AS a, a_row1.b AS b, a_row1.c AS c;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT aa_row1.a AS a, aa_row1.b AS b, aa_row1.c AS c;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+END;
+$$
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(3) DEFAULT NULL,
+  `c` time DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(3) DEFAULT NULL,
+  `c` time DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
diff --git a/mysql-test/r/sp-anchor-row-type-table.result b/mysql-test/r/sp-anchor-row-type-table.result
index e3ad1aa831e..28a5180e6c9 100644
--- a/mysql-test/r/sp-anchor-row-type-table.result
+++ b/mysql-test/r/sp-anchor-row-type-table.result
@@ -781,3 +781,30 @@ 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=latin1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `int11` int(11) DEFAULT NULL,
+  `text0` text DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
diff --git a/mysql-test/r/sp-anchor-type.result b/mysql-test/r/sp-anchor-type.result
index 44f67531b2f..b2de14bca1c 100644
--- a/mysql-test/r/sp-anchor-type.result
+++ b/mysql-test/r/sp-anchor-type.result
@@ -965,3 +965,109 @@ DROP TABLE t1;
 #
 # End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types
 #
+#
+# MDEV-14139 Anchored data types for variables
+#
+BEGIN NOT ATOMIC
+DECLARE a TYPE OF a;
+END;
+$$
+ERROR 42000: Undeclared variable: a
+BEGIN NOT ATOMIC
+DECLARE int11 INT;
+DECLARE dec103 DECIMAL(10,3);
+DECLARE flt0 FLOAT;
+DECLARE dbl0 DOUBLE;
+DECLARE enum0 ENUM('a','b');
+DECLARE bit3 BIT(3);
+DECLARE varchar10 VARCHAR(10);
+DECLARE text1 TEXT;
+DECLARE tinytext1 TINYTEXT;
+DECLARE mediumtext1 MEDIUMTEXT;
+DECLARE longtext1 LONGTEXT;
+DECLARE time3 TIME(3);
+DECLARE datetime4 DATETIME(4);
+DECLARE timestamp5 TIMESTAMP(5);
+DECLARE date0 DATE;
+DECLARE a_int11 TYPE OF int11;
+DECLARE a_dec103 TYPE OF dec103;
+DECLARE a_flt0 TYPE OF flt0;
+DECLARE a_dbl0 TYPE OF dbl0;
+DECLARE a_bit3 TYPE OF bit3;
+DECLARE a_enum0 TYPE OF enum0;
+DECLARE a_varchar10 TYPE OF varchar10;
+DECLARE a_text1 TYPE OF text1;
+DECLARE a_tinytext1 TYPE OF tinytext1;
+DECLARE a_mediumtext1 TYPE OF mediumtext1;
+DECLARE a_longtext1 TYPE OF longtext1;
+DECLARE a_time3 TYPE OF time3;
+DECLARE a_datetime4 TYPE OF datetime4;
+DECLARE a_timestamp5 TYPE OF timestamp5;
+DECLARE a_date0 TYPE OF date0;
+DECLARE aa_int11 TYPE OF a_int11;
+DECLARE aa_dec103 TYPE OF a_dec103;
+DECLARE aa_flt0 TYPE OF a_flt0;
+DECLARE aa_dbl0 TYPE OF a_dbl0;
+DECLARE aa_bit3 TYPE OF a_bit3;
+DECLARE aa_enum0 TYPE OF a_enum0;
+DECLARE aa_varchar10 TYPE OF a_varchar10;
+DECLARE aa_text1 TYPE OF a_text1;
+DECLARE aa_tinytext1 TYPE OF a_tinytext1;
+DECLARE aa_mediumtext1 TYPE OF a_mediumtext1;
+DECLARE aa_longtext1 TYPE OF a_longtext1;
+DECLARE aa_time3 TYPE OF a_time3;
+DECLARE aa_datetime4 TYPE OF a_datetime4;
+DECLARE aa_timestamp5 TYPE OF a_timestamp5;
+DECLARE aa_date0 TYPE OF a_date0;
+CREATE TABLE t1 AS
+SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3,
+a_enum0,a_varchar10,
+a_text1,a_tinytext1,a_mediumtext1,a_longtext1,
+a_time3,a_datetime4,a_timestamp5,a_date0;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE TABLE t1 AS
+SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3,
+aa_enum0,aa_varchar10,
+aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1,
+aa_time3,aa_datetime4,aa_timestamp5,aa_date0;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+END;
+$$
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a_int11` int(11) DEFAULT NULL,
+  `a_dec103` decimal(10,3) DEFAULT NULL,
+  `a_flt0` float DEFAULT NULL,
+  `a_dbl0` double DEFAULT NULL,
+  `a_bit3` bit(3) DEFAULT NULL,
+  `a_enum0` varchar(1) DEFAULT NULL,
+  `a_varchar10` varchar(10) DEFAULT NULL,
+  `a_text1` text DEFAULT NULL,
+  `a_tinytext1` tinytext DEFAULT NULL,
+  `a_mediumtext1` mediumtext DEFAULT NULL,
+  `a_longtext1` longtext DEFAULT NULL,
+  `a_time3` time(3) DEFAULT NULL,
+  `a_datetime4` datetime(4) DEFAULT NULL,
+  `a_timestamp5` timestamp(5) NULL DEFAULT NULL,
+  `a_date0` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `aa_int11` int(11) DEFAULT NULL,
+  `aa_dec103` decimal(10,3) DEFAULT NULL,
+  `aa_flt0` float DEFAULT NULL,
+  `aa_dbl0` double DEFAULT NULL,
+  `aa_bit3` bit(3) DEFAULT NULL,
+  `aa_enum0` varchar(1) DEFAULT NULL,
+  `aa_varchar10` varchar(10) DEFAULT NULL,
+  `aa_text1` text DEFAULT NULL,
+  `aa_tinytext1` tinytext DEFAULT NULL,
+  `aa_mediumtext1` mediumtext DEFAULT NULL,
+  `aa_longtext1` longtext DEFAULT NULL,
+  `aa_time3` time(3) DEFAULT NULL,
+  `aa_datetime4` datetime(4) DEFAULT NULL,
+  `aa_timestamp5` timestamp(5) NULL DEFAULT NULL,
+  `aa_date0` date DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
diff --git a/mysql-test/r/sp-row.result b/mysql-test/r/sp-row.result
index 61c0660cf71..adb67030feb 100644
--- a/mysql-test/r/sp-row.result
+++ b/mysql-test/r/sp-row.result
@@ -2256,3 +2256,28 @@ 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
diff --git a/mysql-test/suite/compat/oracle/r/sp-anchor-row-type-table.result b/mysql-test/suite/compat/oracle/r/sp-anchor-row-type-table.result
index f2c8429a54d..b878042174b 100644
--- a/mysql-test/suite/compat/oracle/r/sp-anchor-row-type-table.result
+++ b/mysql-test/suite/compat/oracle/r/sp-anchor-row-type-table.result
@@ -101,3 +101,31 @@ 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);
+DECLARE
+row1 t1%ROWTYPE;
+a_row1 row1%TYPE;
+aa_row1 a_row1%TYPE;
+BEGIN
+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
+)
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "int11" int(11) DEFAULT NULL,
+  "text0" text DEFAULT NULL
+)
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-code.result b/mysql-test/suite/compat/oracle/r/sp-code.result
index 1087e10552b..f1dd4180854 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -1030,8 +1030,8 @@ Pos	Instruction
 0	cpush cur1@0
 1	cpush cur2@1
 2	cursor_copy_struct cur1 rec1@0
-3	set rec1@0 NULL
-4	cursor_copy_struct cur1 rec2@1
+3	cursor_copy_struct cur1 rec2@1
+4	set rec1@0 NULL
 5	set rec2@1 NULL
 6	cursor_copy_struct cur2 rec3@2
 7	set rec3@2 NULL
diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
index 726f8bcf9ca..3030a3dc658 100644
--- a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
+++ b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result
@@ -1330,3 +1330,32 @@ rec2.a	rec2.b
 2012	bbbb3
 DROP PROCEDURE p1;
 DROP TABLE t1;
+#
+# MDEV-14139 Anchored data types for variables
+#
+DECLARE
+CURSOR c1 IS SELECT 10 AS a, 'bbb' AS b, TIME'10:20:30' AS c;
+row1 c1%ROWTYPE;
+a_row1 row1%TYPE;
+aa_row1 a_row1%TYPE;
+BEGIN
+CREATE TABLE t2 AS SELECT a_row1.a AS a, a_row1.b AS b, a_row1.c AS c;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT aa_row1.a AS a, aa_row1.b AS b, aa_row1.c AS c;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "a" int(11) DEFAULT NULL,
+  "b" varchar(3) DEFAULT NULL,
+  "c" time DEFAULT NULL
+)
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "a" int(11) DEFAULT NULL,
+  "b" varchar(3) DEFAULT NULL,
+  "c" time DEFAULT NULL
+)
diff --git a/mysql-test/suite/compat/oracle/r/sp-row.result b/mysql-test/suite/compat/oracle/r/sp-row.result
index 16421cd45ac..f95817420f1 100644
--- a/mysql-test/suite/compat/oracle/r/sp-row.result
+++ b/mysql-test/suite/compat/oracle/r/sp-row.result
@@ -3081,3 +3081,29 @@ 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
+#
+DECLARE
+row1 ROW(int11 INT,text1 TEXT);
+a_row1 row1%TYPE;
+aa_row1 a_row1%TYPE;
+BEGIN
+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
+)
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "int11" int(11) DEFAULT NULL,
+  "text1" text DEFAULT NULL
+)
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index 97d637c6e1a..53411e80251 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -2297,3 +2297,110 @@ SELECT exception FROM v1;
 exception
 DROP VIEW v1;
 DROP TABLE t1;
+#
+# MDEV-14139 Anchored data types for variables
+#
+BEGIN NOT ATOMIC
+DECLARE a a%TYPE;
+END;
+$$
+ERROR 42000: Undeclared variable: a
+DECLARE
+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;
+longtext1 LONGTEXT;
+time3 TIME(3);
+datetime4 DATETIME(4);
+timestamp5 TIMESTAMP(5);
+date0 DATE;
+a_int11 int11%TYPE;
+a_dec103 dec103%TYPE;
+a_flt0 flt0%TYPE;
+a_dbl0 dbl0%TYPE;
+a_bit3 bit3%TYPE;
+a_enum0 enum0%TYPE;
+a_varchar10 varchar10%TYPE;
+a_text1 text1%TYPE;
+a_tinytext1 tinytext1%TYPE;
+a_mediumtext1 mediumtext1%TYPE;
+a_longtext1 longtext1%TYPE;
+a_time3 time3%TYPE;
+a_datetime4 datetime4%TYPE;
+a_timestamp5 timestamp5%TYPE;
+a_date0 date0%TYPE;
+aa_int11 a_int11%TYPE;
+aa_dec103 a_dec103%TYPE;
+aa_flt0 a_flt0%TYPE;
+aa_dbl0 a_dbl0%TYPE;
+aa_bit3 a_bit3%TYPE;
+aa_enum0 a_enum0%TYPE;
+aa_varchar10 a_varchar10%TYPE;
+aa_text1 a_text1%TYPE;
+aa_tinytext1 a_tinytext1%TYPE;
+aa_mediumtext1 a_mediumtext1%TYPE;
+aa_longtext1 a_longtext1%TYPE;
+aa_time3 a_time3%TYPE;
+aa_datetime4 a_datetime4%TYPE;
+aa_timestamp5 a_timestamp5%TYPE;
+aa_date0 a_date0%TYPE;
+BEGIN
+CREATE TABLE t1 AS
+SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3,
+a_enum0,a_varchar10,
+a_text1,a_tinytext1,a_mediumtext1,a_longtext1,
+a_time3,a_datetime4,a_timestamp5,a_date0;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE TABLE t1 AS
+SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3,
+aa_enum0,aa_varchar10,
+aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1,
+aa_time3,aa_datetime4,aa_timestamp5,aa_date0;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+END;
+$$
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "a_int11" int(11) DEFAULT NULL,
+  "a_dec103" decimal(10,3) DEFAULT NULL,
+  "a_flt0" float DEFAULT NULL,
+  "a_dbl0" double DEFAULT NULL,
+  "a_bit3" bit(3) DEFAULT NULL,
+  "a_enum0" varchar(1) DEFAULT NULL,
+  "a_varchar10" varchar(10) DEFAULT NULL,
+  "a_text1" text DEFAULT NULL,
+  "a_tinytext1" tinytext DEFAULT NULL,
+  "a_mediumtext1" mediumtext DEFAULT NULL,
+  "a_longtext1" longtext DEFAULT NULL,
+  "a_time3" time(3) DEFAULT NULL,
+  "a_datetime4" datetime(4) DEFAULT NULL,
+  "a_timestamp5" timestamp(5) NULL DEFAULT NULL,
+  "a_date0" datetime DEFAULT NULL
+)
+Table	Create Table
+t1	CREATE TABLE "t1" (
+  "aa_int11" int(11) DEFAULT NULL,
+  "aa_dec103" decimal(10,3) DEFAULT NULL,
+  "aa_flt0" float DEFAULT NULL,
+  "aa_dbl0" double DEFAULT NULL,
+  "aa_bit3" bit(3) DEFAULT NULL,
+  "aa_enum0" varchar(1) DEFAULT NULL,
+  "aa_varchar10" varchar(10) DEFAULT NULL,
+  "aa_text1" text DEFAULT NULL,
+  "aa_tinytext1" tinytext DEFAULT NULL,
+  "aa_mediumtext1" mediumtext DEFAULT NULL,
+  "aa_longtext1" longtext DEFAULT NULL,
+  "aa_time3" time(3) DEFAULT NULL,
+  "aa_datetime4" datetime(4) DEFAULT NULL,
+  "aa_timestamp5" timestamp(5) NULL DEFAULT NULL,
+  "aa_date0" datetime DEFAULT NULL
+)
diff --git a/mysql-test/suite/compat/oracle/t/sp-anchor-row-type-table.test b/mysql-test/suite/compat/oracle/t/sp-anchor-row-type-table.test
index b904cc39139..6e13a6164a9 100644
--- a/mysql-test/suite/compat/oracle/t/sp-anchor-row-type-table.test
+++ b/mysql-test/suite/compat/oracle/t/sp-anchor-row-type-table.test
@@ -99,3 +99,26 @@ DROP PROCEDURE p1;
 DROP FUNCTION f2;
 DROP FUNCTION f1;
 DROP DATABASE db1;
+
+
+--echo #
+--echo # MDEV-14139 Anchored data types for variables
+--echo #
+
+CREATE TABLE t1 (int11 INT, text0 TEXT);
+DELIMITER $$;
+DECLARE
+  row1 t1%ROWTYPE;
+  a_row1 row1%TYPE;
+  aa_row1 a_row1%TYPE;
+BEGIN
+  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;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test b/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test
index 3c37a732d8c..19a50eacca1 100644
--- a/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test
+++ b/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test
@@ -1422,3 +1422,25 @@ DELIMITER ;$$
 CALL p1();
 DROP PROCEDURE p1;
 DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-14139 Anchored data types for variables
+--echo #
+
+DELIMITER $$;
+DECLARE
+  CURSOR c1 IS SELECT 10 AS a, 'bbb' AS b, TIME'10:20:30' AS c;
+  row1 c1%ROWTYPE;
+  a_row1 row1%TYPE;
+  aa_row1 a_row1%TYPE;
+BEGIN
+  CREATE TABLE t2 AS SELECT a_row1.a AS a, a_row1.b AS b, a_row1.c AS c;
+  SHOW CREATE TABLE t2;
+  DROP TABLE t2;
+  CREATE TABLE t2 AS SELECT aa_row1.a AS a, aa_row1.b AS b, aa_row1.c AS c;
+  SHOW CREATE TABLE t2;
+  DROP TABLE t2;
+END;
+$$
+DELIMITER ;$$
diff --git a/mysql-test/suite/compat/oracle/t/sp-row.test b/mysql-test/suite/compat/oracle/t/sp-row.test
index 94cc5db9802..b26cae5a5c6 100644
--- a/mysql-test/suite/compat/oracle/t/sp-row.test
+++ b/mysql-test/suite/compat/oracle/t/sp-row.test
@@ -2388,3 +2388,24 @@ BEGIN
 END;
 $$
 DELIMITER ;$$
+
+
+--echo #
+--echo # MDEV-14139 Anchored data types for variables
+--echo #
+
+DELIMITER $$;
+DECLARE
+  row1 ROW(int11 INT,text1 TEXT);
+  a_row1 row1%TYPE;
+  aa_row1 a_row1%TYPE;
+BEGIN
+  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;
+$$
+DELIMITER ;$$
diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test
index 7e9e76d379f..61a37459483 100644
--- a/mysql-test/suite/compat/oracle/t/sp.test
+++ b/mysql-test/suite/compat/oracle/t/sp.test
@@ -2146,3 +2146,90 @@ CREATE VIEW v1 AS SELECT c1 exception FROM t1;
 SELECT exception FROM v1;
 DROP VIEW v1;
 DROP TABLE t1;
+
+
+
+--echo #
+--echo # MDEV-14139 Anchored data types for variables
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+BEGIN NOT ATOMIC
+  DECLARE a a%TYPE;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+DECLARE
+  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;
+  longtext1 LONGTEXT;
+
+  time3 TIME(3);
+  datetime4 DATETIME(4);
+  timestamp5 TIMESTAMP(5);
+  date0 DATE;
+
+  a_int11 int11%TYPE;
+  a_dec103 dec103%TYPE;
+  a_flt0 flt0%TYPE;
+  a_dbl0 dbl0%TYPE;
+  a_bit3 bit3%TYPE;
+  a_enum0 enum0%TYPE;
+  a_varchar10 varchar10%TYPE;
+  a_text1 text1%TYPE;
+  a_tinytext1 tinytext1%TYPE;
+  a_mediumtext1 mediumtext1%TYPE;
+  a_longtext1 longtext1%TYPE;
+  a_time3 time3%TYPE;
+  a_datetime4 datetime4%TYPE;
+  a_timestamp5 timestamp5%TYPE;
+  a_date0 date0%TYPE;
+
+  aa_int11 a_int11%TYPE;
+  aa_dec103 a_dec103%TYPE;
+  aa_flt0 a_flt0%TYPE;
+  aa_dbl0 a_dbl0%TYPE;
+  aa_bit3 a_bit3%TYPE;
+  aa_enum0 a_enum0%TYPE;
+  aa_varchar10 a_varchar10%TYPE;
+  aa_text1 a_text1%TYPE;
+  aa_tinytext1 a_tinytext1%TYPE;
+  aa_mediumtext1 a_mediumtext1%TYPE;
+  aa_longtext1 a_longtext1%TYPE;
+  aa_time3 a_time3%TYPE;
+  aa_datetime4 a_datetime4%TYPE;
+  aa_timestamp5 a_timestamp5%TYPE;
+  aa_date0 a_date0%TYPE;
+BEGIN
+  CREATE TABLE t1 AS
+    SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3,
+           a_enum0,a_varchar10,
+           a_text1,a_tinytext1,a_mediumtext1,a_longtext1,
+           a_time3,a_datetime4,a_timestamp5,a_date0;
+  SHOW CREATE TABLE t1;
+  DROP TABLE t1;
+
+  CREATE TABLE t1 AS
+    SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3,
+           aa_enum0,aa_varchar10,
+           aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1,
+           aa_time3,aa_datetime4,aa_timestamp5,aa_date0;
+  SHOW CREATE TABLE t1;
+  DROP TABLE t1;
+
+END;
+$$
+DELIMITER ;$$
diff --git a/mysql-test/t/sp-anchor-row-type-cursor.test b/mysql-test/t/sp-anchor-row-type-cursor.test
index fe4b55aaf1a..7089175507c 100644
--- a/mysql-test/t/sp-anchor-row-type-cursor.test
+++ b/mysql-test/t/sp-anchor-row-type-cursor.test
@@ -1093,3 +1093,26 @@ $$
 DELIMITER ;$$
 CALL p1();
 DROP PROCEDURE p1;
+
+
+--echo #
+--echo # MDEV-14139 Anchored data types for variables
+--echo #
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE c1 CURSOR FOR SELECT 10 AS a, 'bbb' AS b, TIME'10:20:30' AS c;
+  BEGIN
+    DECLARE row1 ROW TYPE OF c1;
+    DECLARE a_row1 TYPE OF row1;
+    DECLARE aa_row1 TYPE OF a_row1;
+    CREATE TABLE t2 AS SELECT a_row1.a AS a, a_row1.b AS b, a_row1.c AS c;
+    SHOW CREATE TABLE t2;
+    DROP TABLE t2;
+    CREATE TABLE t2 AS SELECT aa_row1.a AS a, aa_row1.b AS b, aa_row1.c AS c;
+    SHOW CREATE TABLE t2;
+    DROP TABLE t2;
+  END;
+END;
+$$
+DELIMITER ;$$
diff --git a/mysql-test/t/sp-anchor-row-type-table.test b/mysql-test/t/sp-anchor-row-type-table.test
index 1171ae2d7b7..3f04dc68586 100644
--- a/mysql-test/t/sp-anchor-row-type-table.test
+++ b/mysql-test/t/sp-anchor-row-type-table.test
@@ -860,3 +860,24 @@ DROP PROCEDURE p1;
 DROP FUNCTION f2;
 DROP FUNCTION f1;
 DROP DATABASE db1;
+
+--echo #
+--echo # MDEV-14139 Anchored data types for variables
+--echo #
+
+CREATE TABLE t1 (int11 INT, text0 TEXT);
+DELIMITER $$;
+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;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
diff --git a/mysql-test/t/sp-anchor-type.test b/mysql-test/t/sp-anchor-type.test
index f57342eb67c..7f952d626e0 100644
--- a/mysql-test/t/sp-anchor-type.test
+++ b/mysql-test/t/sp-anchor-type.test
@@ -675,3 +675,88 @@ DROP TABLE t1;
 --echo #
 --echo # End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types
 --echo #
+
+--echo #
+--echo # MDEV-14139 Anchored data types for variables
+--echo #
+
+DELIMITER $$;
+--error ER_SP_UNDECLARED_VAR
+BEGIN NOT ATOMIC
+  DECLARE a TYPE OF a;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE int11 INT;
+  DECLARE dec103 DECIMAL(10,3);
+  DECLARE flt0 FLOAT;
+  DECLARE dbl0 DOUBLE;
+  DECLARE enum0 ENUM('a','b');
+  DECLARE bit3 BIT(3);
+
+  DECLARE varchar10 VARCHAR(10);
+  DECLARE text1 TEXT;
+  DECLARE tinytext1 TINYTEXT;
+  DECLARE mediumtext1 MEDIUMTEXT;
+  DECLARE longtext1 LONGTEXT;
+
+  DECLARE time3 TIME(3);
+  DECLARE datetime4 DATETIME(4);
+  DECLARE timestamp5 TIMESTAMP(5);
+  DECLARE date0 DATE;
+
+  DECLARE a_int11 TYPE OF int11;
+  DECLARE a_dec103 TYPE OF dec103;
+  DECLARE a_flt0 TYPE OF flt0;
+  DECLARE a_dbl0 TYPE OF dbl0;
+  DECLARE a_bit3 TYPE OF bit3;
+  DECLARE a_enum0 TYPE OF enum0;
+  DECLARE a_varchar10 TYPE OF varchar10;
+  DECLARE a_text1 TYPE OF text1;
+  DECLARE a_tinytext1 TYPE OF tinytext1;
+  DECLARE a_mediumtext1 TYPE OF mediumtext1;
+  DECLARE a_longtext1 TYPE OF longtext1;
+  DECLARE a_time3 TYPE OF time3;
+  DECLARE a_datetime4 TYPE OF datetime4;
+  DECLARE a_timestamp5 TYPE OF timestamp5;
+  DECLARE a_date0 TYPE OF date0;
+
+  DECLARE aa_int11 TYPE OF a_int11;
+  DECLARE aa_dec103 TYPE OF a_dec103;
+  DECLARE aa_flt0 TYPE OF a_flt0;
+  DECLARE aa_dbl0 TYPE OF a_dbl0;
+  DECLARE aa_bit3 TYPE OF a_bit3;
+  DECLARE aa_enum0 TYPE OF a_enum0;
+  DECLARE aa_varchar10 TYPE OF a_varchar10;
+  DECLARE aa_text1 TYPE OF a_text1;
+  DECLARE aa_tinytext1 TYPE OF a_tinytext1;
+  DECLARE aa_mediumtext1 TYPE OF a_mediumtext1;
+  DECLARE aa_longtext1 TYPE OF a_longtext1;
+  DECLARE aa_time3 TYPE OF a_time3;
+  DECLARE aa_datetime4 TYPE OF a_datetime4;
+  DECLARE aa_timestamp5 TYPE OF a_timestamp5;
+  DECLARE aa_date0 TYPE OF a_date0;
+
+  CREATE TABLE t1 AS
+    SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3,
+           a_enum0,a_varchar10,
+           a_text1,a_tinytext1,a_mediumtext1,a_longtext1,
+           a_time3,a_datetime4,a_timestamp5,a_date0;
+  SHOW CREATE TABLE t1;
+  DROP TABLE t1;
+
+  CREATE TABLE t1 AS
+    SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3,
+           aa_enum0,aa_varchar10,
+           aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1,
+           aa_time3,aa_datetime4,aa_timestamp5,aa_date0;
+  SHOW CREATE TABLE t1;
+  DROP TABLE t1;
+
+END;
+$$
+DELIMITER ;$$
diff --git a/mysql-test/t/sp-row.test b/mysql-test/t/sp-row.test
index e213d4dbc2d..5928c8cb76d 100644
--- a/mysql-test/t/sp-row.test
+++ b/mysql-test/t/sp-row.test
@@ -1484,3 +1484,23 @@ BEGIN NOT ATOMIC
 END;
 $$
 DELIMITER ;$$
+
+
+--echo #
+--echo # MDEV-14139 Anchored data types for variables
+--echo #
+
+DELIMITER $$;
+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;
+$$
+DELIMITER ;$$
diff --git a/sql/field.h b/sql/field.h
index 951d6940a88..5999f3e0a34 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -4061,6 +4061,23 @@ public:
         || unireg_check == Field::TIMESTAMP_DNUN_FIELD;
   }
 
+  void set_type(const Column_definition &other)
+  {
+    set_handler(other.type_handler());
+    length= other.length;
+    char_length= other.char_length;
+    decimals= other.decimals;
+    flags= other.flags;
+    pack_length= other.pack_length;
+    key_length= other.key_length;
+    unireg_check= other.unireg_check;
+    interval= other.interval;
+    charset= other.charset;
+    srid= other.srid;
+    geom_type= other.geom_type;
+    pack_flag= other.pack_flag;
+  }
+
   // Replace the entire value by another definition
   void set_column_definition(const Column_definition *def)
   {
@@ -4127,12 +4144,14 @@ class Spvar_definition: public Column_definition
   class Qualified_column_ident *m_column_type_ref; // for %TYPE
   class Table_ident *m_table_rowtype_ref;          // for table%ROWTYPE
   bool m_cursor_rowtype_ref;                       // for cursor%ROWTYPE
+  uint m_cursor_rowtype_offset;                    // for cursor%ROWTYPE
   Row_definition_list *m_row_field_definitions;    // for ROW
 public:
   Spvar_definition()
    :m_column_type_ref(NULL),
     m_table_rowtype_ref(NULL),
     m_cursor_rowtype_ref(false),
+    m_cursor_rowtype_offset(0),
     m_row_field_definitions(NULL)
   { }
   Spvar_definition(THD *thd, Field *field)
@@ -4140,6 +4159,7 @@ public:
     m_column_type_ref(NULL),
     m_table_rowtype_ref(NULL),
     m_cursor_rowtype_ref(false),
+    m_cursor_rowtype_offset(0),
     m_row_field_definitions(NULL)
   { }
   const Type_handler *type_handler() const
@@ -4168,9 +4188,15 @@ public:
   {
     m_table_rowtype_ref= ref;
   }
-  void set_cursor_rowtype_ref(bool ref)
+
+  uint cursor_rowtype_offset() const
+  {
+    return m_cursor_rowtype_offset;
+  }
+  void set_cursor_rowtype_ref(bool ref, uint offset)
   {
     m_cursor_rowtype_ref= ref;
+    m_cursor_rowtype_offset= offset;
   }
 
   /*
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 5f198e40254..938b386a816 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5259,15 +5259,69 @@ void LEX::sp_variable_declarations_init(THD *thd, int nvars)
                             thd->variables.collation_database);
 }
 
+
+bool LEX::sp_variable_declarations_set_default(THD *thd, int nvars,
+                                               Item *dflt_value_item)
+{
+  if (!dflt_value_item &&
+      !(dflt_value_item= new (thd->mem_root) Item_null(thd)))
+    return true;
+
+  for (uint i= 0 ; i < (uint) nvars ; i++)
+  {
+    sp_variable *spvar= spcont->get_last_context_variable((uint) nvars - 1 - i);
+    bool last= i + 1 == (uint) nvars;
+    spvar->default_value= dflt_value_item;
+    /* The last instruction is responsible for freeing LEX. */
+    sp_instr_set *is= new (this->thd->mem_root)
+                      sp_instr_set(sphead->instructions(),
+                                   spcont, spvar->offset, dflt_value_item,
+                                   this, last);
+    if (is == NULL || sphead->add_instr(is))
+      return true;
+  }
+  return false;
+}
+
+
+bool
+LEX::sp_variable_declarations_copy_type_finalize(THD *thd, int nvars,
+                                                 const Column_definition &ref,
+                                                 Row_definition_list *fields,
+                                                 Item *default_value)
+{
+  for (uint i= 0 ; i < (uint) nvars; i++)
+  {
+    sp_variable *spvar= spcont->get_last_context_variable((uint) nvars - 1 - i);
+    spvar->field_def.set_type(ref);
+    spvar->field_def.set_row_field_definitions(fields);
+    spvar->field_def.field_name= spvar->name;
+  }
+  if (sp_variable_declarations_set_default(thd, nvars, default_value))
+    return true;
+  spcont->declare_var_boundary(0);
+  return sphead->restore_lex(thd);
+}
+
+
 bool LEX::sp_variable_declarations_finalize(THD *thd, int nvars,
                                             const Column_definition *cdef,
-                                            Row_definition_list *row,
                                             Item *dflt_value_item)
 {
-  if (!dflt_value_item &&
-      !(dflt_value_item= new (thd->mem_root) Item_null(thd)))
+  DBUG_ASSERT(cdef);
+  Column_definition tmp(*cdef);
+  if (sphead->fill_spvar_definition(thd, &tmp))
     return true;
+  return sp_variable_declarations_copy_type_finalize(thd, nvars, tmp, NULL,
+                                                     dflt_value_item);
+}
+
 
+bool LEX::sp_variable_declarations_row_finalize(THD *thd, int nvars,
+                                                Row_definition_list *row,
+                                                Item *dflt_value_item)
+{
+  DBUG_ASSERT(row);
   /*
     Prepare all row fields.
     Note, we do it only one time outside of the below loop.
@@ -5280,37 +5334,19 @@ bool LEX::sp_variable_declarations_finalize(THD *thd, int nvars,
         ...
       END;
   */
-  if (row && sphead->row_fill_field_definitions(thd, row))
+  if (sphead->row_fill_field_definitions(thd, row))
     return true;
 
   for (uint i= 0 ; i < (uint) nvars ; i++)
   {
     sp_variable *spvar= spcont->get_last_context_variable((uint) nvars - 1 - i);
-    bool last= i + 1 == (uint) nvars;
-
-    if (!spvar)
-      return true;
-
-    spvar->default_value= dflt_value_item;
-
-    if (cdef)
-    {
-      if (!last)
-        spvar->field_def.set_column_definition(cdef);
-    }
     if (sphead->fill_spvar_definition(thd, &spvar->field_def, &spvar->name))
       return true;
     spvar->field_def.set_row_field_definitions(row);
-
-    /* The last instruction is responsible for freeing LEX. */
-    sp_instr_set *is= new (this->thd->mem_root)
-                      sp_instr_set(sphead->instructions(),
-                                   spcont, spvar->offset, dflt_value_item,
-                                   this, last);
-    if (is == NULL || sphead->add_instr(is))
-      return true;
   }
 
+  if (sp_variable_declarations_set_default(thd, nvars, dflt_value_item))
+    return true;
   spcont->declare_var_boundary(0);
   return sphead->restore_lex(thd);
 }
@@ -5334,57 +5370,76 @@ LEX::sp_variable_declarations_rowtype_finalize(THD *thd, int nvars,
   const sp_pcursor *pcursor= ref->table.str && ref->db.str ? NULL :
                              spcont->find_cursor(&ref->m_column, &coffp,
                                                  false);
+  if (pcursor)
+    return sp_variable_declarations_cursor_rowtype_finalize(thd, nvars,
+                                                            coffp, def);
+  /*
+    When parsing a qualified identifier chain, the parser does not know yet
+    if it's going to be a qualified column name (for %TYPE),
+    or a qualified table name (for %ROWTYPE). So it collects the chain
+    into Qualified_column_ident.
+    Now we know that it was actually a qualified table name (%ROWTYPE).
+    Create a new Table_ident from Qualified_column_ident,
+    shifting fields as follows:
+    - ref->m_column becomes table_ref->table
+    - ref->table    becomes table_ref->db
+  */
+  return sp_variable_declarations_table_rowtype_finalize(thd, nvars,
+                                                         ref->table,
+                                                         ref->m_column,
+                                                         def);
+}
+
 
-  if (!def && !(def= new (thd->mem_root) Item_null(thd)))
+bool
+LEX::sp_variable_declarations_table_rowtype_finalize(THD *thd, int nvars,
+                                                     const LEX_CSTRING &db,
+                                                     const LEX_CSTRING &table,
+                                                     Item *def)
+{
+  Table_ident *table_ref;
+  if (!(table_ref= new (thd->mem_root) Table_ident(thd, &db, &table, false)))
     return true;
+  // Loop through all variables in the same declaration
+  for (uint i= 0 ; i < (uint) nvars; i++)
+  {
+    sp_variable *spvar= spcont->get_last_context_variable((uint) nvars - 1 - i);
+    spvar->field_def.set_table_rowtype_ref(table_ref);
+    sphead->fill_spvar_definition(thd, &spvar->field_def, &spvar->name);
+  }
+  if (sp_variable_declarations_set_default(thd, nvars, def))
+    return true;
+  // Make sure sp_rcontext is created using the invoker security context:
+  sphead->m_flags|= sp_head::HAS_COLUMN_TYPE_REFS;
+  spcont->declare_var_boundary(0);
+  return sphead->restore_lex(thd);
+}
+
+
+bool
+LEX::sp_variable_declarations_cursor_rowtype_finalize(THD *thd, int nvars,
+                                                      uint offset,
+                                                      Item *def)
+{
+  const sp_pcursor *pcursor= spcont->find_cursor(offset);
 
   // Loop through all variables in the same declaration
   for (uint i= 0 ; i < (uint) nvars; i++)
   {
-    bool last= i + 1 == (uint) nvars;
     sp_variable *spvar= spcont->get_last_context_variable((uint) nvars - 1 - i);
 
-    if (pcursor)
-    {
-      spvar->field_def.set_cursor_rowtype_ref(true);
-      sp_instr_cursor_copy_struct *instr=
-        new (thd->mem_root) sp_instr_cursor_copy_struct(sphead->instructions(),
-                                                        spcont, pcursor->lex(),
-                                                        spvar->offset);
-      if (instr == NULL || sphead->add_instr(instr))
-       return true;
-    }
-    else
-    {
-      /*
-        When parsing a qualified identifier chain, the parser does not know yet
-        if it's going to be a qualified column name (for %TYPE),
-        or a qualified table name (for %ROWTYPE). So it collects the chain
-        into Qualified_column_ident.
-        Now we know that it was actually a qualified table name (%ROWTYPE).
-        Create a new Table_ident from Qualified_column_ident,
-        shifting fields as follows:
-        - ref->m_column becomes table_ref->table
-        - ref->table    becomes table_ref->db
-      */
-      Table_ident *table_ref;
-      if (!(table_ref= new (thd->mem_root) Table_ident(thd,
-                                                       &ref->table,
-                                                       &ref->m_column,
-                                                       false)))
-        return true;
-      spvar->field_def.set_table_rowtype_ref(table_ref);
-    }
+    spvar->field_def.set_cursor_rowtype_ref(true, offset);
+    sp_instr_cursor_copy_struct *instr=
+      new (thd->mem_root) sp_instr_cursor_copy_struct(sphead->instructions(),
+                                                      spcont, pcursor->lex(),
+                                                      spvar->offset);
+    if (instr == NULL || sphead->add_instr(instr))
+     return true;
+
     sphead->fill_spvar_definition(thd, &spvar->field_def, &spvar->name);
-    spvar->default_value= def;
-    /* The last instruction is responsible for freeing LEX. */
-    sp_instr_set *is= new (this->thd->mem_root)
-                      sp_instr_set(sphead->instructions(),
-                                   spcont, spvar->offset, def,
-                                   this, last);
-    if (is == NULL || sphead->add_instr(is))
-      return true;
   }
+  if (sp_variable_declarations_set_default(thd, nvars, def))
+    return true;
   // Make sure sp_rcontext is created using the invoker security context:
   sphead->m_flags|= sp_head::HAS_COLUMN_TYPE_REFS;
   spcont->declare_var_boundary(0);
@@ -5392,11 +5447,28 @@ LEX::sp_variable_declarations_rowtype_finalize(THD *thd, int nvars,
 }
 
 
+/*
+  Add declarations for table column and SP variable anchor types:
+  - DECLARE spvar1 TYPE OF db1.table1.column1;
+  - DECLARE spvar1 TYPE OF table1.column1;
+  - DECLARE spvar1 TYPE OF spvar0;
+*/
 bool
 LEX::sp_variable_declarations_with_ref_finalize(THD *thd, int nvars,
                                                 Qualified_column_ident *ref,
                                                 Item *def)
 {
+  return ref->db.length == 0 && ref->table.length == 0 ?
+    sp_variable_declarations_vartype_finalize(thd, nvars, ref->m_column, def) :
+    sp_variable_declarations_column_type_finalize(thd, nvars, ref, def);
+}
+
+
+bool
+LEX::sp_variable_declarations_column_type_finalize(THD *thd, int nvars,
+                                                   Qualified_column_ident *ref,
+                                                   Item *def)
+{
   for (uint i= 0 ; i < (uint) nvars; i++)
   {
     sp_variable *spvar= spcont->get_last_context_variable((uint) nvars - 1 - i);
@@ -5404,7 +5476,55 @@ LEX::sp_variable_declarations_with_ref_finalize(THD *thd, int nvars,
     spvar->field_def.field_name= spvar->name;
   }
   sphead->m_flags|= sp_head::HAS_COLUMN_TYPE_REFS;
-  return sp_variable_declarations_finalize(thd, nvars, NULL, NULL, def);
+  if (sp_variable_declarations_set_default(thd, nvars, def))
+    return true;
+  spcont->declare_var_boundary(0);
+  return sphead->restore_lex(thd);
+}
+
+
+bool
+LEX::sp_variable_declarations_vartype_finalize(THD *thd, int nvars,
+                                               const LEX_CSTRING &ref,
+                                               Item *default_value)
+{
+  sp_variable *t;
+  if (!spcont || !(t= spcont->find_variable(&ref, false)))
+  {
+    my_error(ER_SP_UNDECLARED_VAR, MYF(0), ref.str);
+    return true;
+  }
+
+  if (t->field_def.is_cursor_rowtype_ref())
+  {
+    uint offset= t->field_def.cursor_rowtype_offset();
+    return sp_variable_declarations_cursor_rowtype_finalize(thd, nvars,
+                                                            offset,
+                                                            default_value);
+  }
+
+  if (t->field_def.is_column_type_ref())
+  {
+    Qualified_column_ident *tmp= t->field_def.column_type_ref();
+    return sp_variable_declarations_column_type_finalize(thd, nvars, tmp,
+                                                         default_value);
+  }
+
+  if (t->field_def.is_table_rowtype_ref())
+  {
+    const Table_ident *tmp= t->field_def.table_rowtype_ref();
+    return sp_variable_declarations_table_rowtype_finalize(thd, nvars,
+                                                           tmp->db,
+                                                           tmp->table,
+                                                           default_value);
+  }
+
+  // A reference to a scalar or a row variable with an explicit data type
+  return sp_variable_declarations_copy_type_finalize(thd, nvars,
+                                                     t->field_def,
+                                                     t->field_def.
+                                                       row_field_definitions(),
+                                                     default_value);
 }
 
 
@@ -5472,7 +5592,7 @@ LEX::sp_add_for_loop_cursor_variable(THD *thd,
   if (!(spvar->default_value= new (thd->mem_root) Item_null(thd)))
     return NULL;
 
-  spvar->field_def.set_cursor_rowtype_ref(true);
+  spvar->field_def.set_cursor_rowtype_ref(true, coffset);
 
   if (sphead->add_for_loop_open_cursor(thd, spcont, spvar, pcursor, coffset,
                                        param_lex, parameters))
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 3f02158b9b1..8ace65aedf7 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3195,26 +3195,34 @@ public:
   void sp_variable_declarations_init(THD *thd, int nvars);
   bool sp_variable_declarations_finalize(THD *thd, int nvars,
                                          const Column_definition *cdef,
-                                         Row_definition_list *row,
                                          Item *def);
-  bool sp_variable_declarations_finalize(THD *thd, int nvars,
-                                         const Column_definition *cdef,
-                                         Item *def)
-  {
-    return sp_variable_declarations_finalize(thd, nvars, cdef, NULL, def);
-  }
+  bool sp_variable_declarations_set_default(THD *thd, int nvars, Item *def);
   bool sp_variable_declarations_row_finalize(THD *thd, int nvars,
                                              Row_definition_list *row,
-                                             Item *def)
-  {
-    return sp_variable_declarations_finalize(thd, nvars, NULL, row, def);
-  }
+                                             Item *def);
   bool sp_variable_declarations_with_ref_finalize(THD *thd, int nvars,
                                                   Qualified_column_ident *col,
                                                   Item *def);
   bool sp_variable_declarations_rowtype_finalize(THD *thd, int nvars,
                                                  Qualified_column_ident *,
                                                  Item *def);
+  bool sp_variable_declarations_cursor_rowtype_finalize(THD *thd, int nvars,
+                                                        uint offset,
+                                                        Item *def);
+  bool sp_variable_declarations_table_rowtype_finalize(THD *thd, int nvars,
+                                                       const LEX_CSTRING &db,
+                                                       const LEX_CSTRING &table,
+                                                       Item *def);
+  bool sp_variable_declarations_column_type_finalize(THD *thd, int nvars,
+                                                     Qualified_column_ident *ref,
+                                                     Item *def);
+  bool sp_variable_declarations_vartype_finalize(THD *thd, int nvars,
+                                                 const LEX_CSTRING &name,
+                                                 Item *def);
+  bool sp_variable_declarations_copy_type_finalize(THD *thd, int nvars,
+                                                   const Column_definition &ref,
+                                                   Row_definition_list *fields,
+                                                   Item *def);
   bool sp_handler_declaration_init(THD *thd, int type);
   bool sp_handler_declaration_finalize(THD *thd, int type);