MDEV-14139 Anchored data types for variables

9 files changed, 345 insertions, 2 deletions

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 ;$$