MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations

24 files changed, 2106 insertions, 58 deletions

diff --git a/mysql-test/suite/compat/oracle/r/binlog_stm_sp.result b/mysql-test/suite/compat/oracle/r/binlog_stm_sp.result
index 55d0dc13e56..a927157356a 100644
--- a/mysql-test/suite/compat/oracle/r/binlog_stm_sp.result
+++ b/mysql-test/suite/compat/oracle/r/binlog_stm_sp.result
@@ -177,3 +177,154 @@ master-bin.000002	#	Query	#	#	use `test`; DROP PROCEDURE p1
 #
 # End of MDEV-10914 ROW data type for stored routine variables
 #
+#
+#  MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
+#
+CREATE TABLE t1 (a INT, b INT);
+CREATE PROCEDURE p1
+AS
+rec t1%ROWTYPE;
+BEGIN
+rec.a:=100;
+rec.b:=200;
+INSERT INTO t1 VALUES (rec.a,rec.b);
+INSERT INTO t1 VALUES (10, rec=ROW(100,200));
+INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
+INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
+INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
+rec.a:=NULL;
+INSERT INTO t1 VALUES (11, rec=ROW(100,200));
+INSERT INTO t1 VALUES (11, rec=ROW(100,201));
+INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
+INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
+INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
+INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
+rec.b:=NULL;
+INSERT INTO t1 VALUES (12, rec=ROW(100,200));
+INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
+INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
+INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
+END;
+$$
+CALL p1();
+SELECT * FROM t1;
+a	b
+100	200
+10	1
+10	1
+10	20
+10	21
+11	NULL
+11	0
+11	NULL
+11	0
+12	NULL
+12	NULL
+DROP TABLE t1;
+DROP PROCEDURE p1;
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000002	#	Binlog_checkpoint	#	#	master-bin.000002
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT)
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (10),(10)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; CREATE TABLE t2 (a INT)
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
+AS
+a INT:= 1;
+rec ROW(a INT);
+BEGIN
+rec.a:= 1;
+INSERT INTO t2 SELECT 1 FROM t1 LIMIT a;
+INSERT INTO t2 SELECT 2 FROM t1 LIMIT rec.a;
+END
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t2 SELECT 1 FROM t1 LIMIT 1
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t2 SELECT 2 FROM t1 LIMIT 1
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; DROP TABLE "t1","t2" /* generated by server */
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; DROP PROCEDURE p1
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT, b INT)
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
+AS
+rec t1%ROWTYPE;
+BEGIN
+rec.a:=100;
+rec.b:=200;
+INSERT INTO t1 VALUES (rec.a,rec.b);
+INSERT INTO t1 VALUES (10, rec=ROW(100,200));
+INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
+INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
+INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
+rec.a:=NULL;
+INSERT INTO t1 VALUES (11, rec=ROW(100,200));
+INSERT INTO t1 VALUES (11, rec=ROW(100,201));
+INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
+INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
+INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
+INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
+rec.b:=NULL;
+INSERT INTO t1 VALUES (12, rec=ROW(100,200));
+INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
+INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
+INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
+END
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES ( NAME_CONST('rec.a',100), NAME_CONST('rec.b',200))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (10, ROW(100,200)=ROW(100,200))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (10, ROW(100,200)=ROW(100,200))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE ROW(100,200)=ROW(100,200)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=ROW(100,200)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (11, ROW(NULL,200)=ROW(100,200))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (11, ROW(NULL,200)=ROW(100,201))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (11, ROW(100,200)=ROW(NULL,200))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (11, ROW(100,201)=ROW(NULL,200))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE ROW(NULL,200)=ROW(100,200)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=ROW(NULL,200)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (12, ROW(NULL,NULL)=ROW(100,200))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (12, ROW(100,200)=ROW(NULL,NULL))
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE ROW(NULL,NULL)=ROW(100,200)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=ROW(NULL,NULL)
+master-bin.000002	#	Query	#	#	COMMIT
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; DROP TABLE "t1" /* generated by server */
+master-bin.000002	#	Gtid	#	#	GTID #-#-#
+master-bin.000002	#	Query	#	#	use `test`; DROP PROCEDURE p1
diff --git a/mysql-test/suite/compat/oracle/r/sp-code.result b/mysql-test/suite/compat/oracle/r/sp-code.result
index 8bc940fc154..9fc8574cbc8 100644
--- a/mysql-test/suite/compat/oracle/r/sp-code.result
+++ b/mysql-test/suite/compat/oracle/r/sp-code.result
@@ -982,3 +982,28 @@ DROP PROCEDURE p1;
 #
 # End of MDEV-10914 ROW data type for stored routine variables
 #
+#
+# MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE;
+BEGIN
+rec1.a:= 10;
+rec1.b:= 'bbb';
+rec1.c:= 10e2;
+rec1.d:= 10.12;
+rec1.c:= rec1.d;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos	Instruction
+0	set rec1@0 NULL
+1	set rec1.a@0["a"] 10
+2	set rec1.b@0["b"] 'bbb'
+3	set rec1.c@0["c"] 10e2
+4	set rec1.d@0["d"] 10.12
+5	set rec1.c@0["c"] rec1.d@0["d"]
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-row.result b/mysql-test/suite/compat/oracle/r/sp-row.result
index bffdb7c619f..4846f6ccf0a 100644
--- a/mysql-test/suite/compat/oracle/r/sp-row.result
+++ b/mysql-test/suite/compat/oracle/r/sp-row.result
@@ -2198,3 +2198,624 @@ DROP PROCEDURE p1;
 #
 # End of MDEV-10914 ROW data type for stored routine variables
 #
+#
+# MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
+#
+#
+# Referring to a table in a non-existing database
+#
+CREATE PROCEDURE p1()
+AS
+rec test2.t1%ROWTYPE;
+BEGIN
+NULL;
+END;
+$$
+CALL p1();
+ERROR 42S02: Table 'test2.t1' doesn't exist
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CALL p1();
+ERROR 42S02: Table 'test2.t1' doesn't exist
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Referring to a table in the current database
+#
+CREATE PROCEDURE p1()
+AS
+rec t1%ROWTYPE;
+BEGIN
+CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+ERROR 42S02: Table 'test.t1' doesn't exist
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CALL p1();
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "rec.a" bigint(11) DEFAULT NULL,
+  "rec.b" varchar(10) DEFAULT NULL,
+  "rec.c" double DEFAULT NULL,
+  "rec.d" decimal(10,0) DEFAULT NULL
+)
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Referring to a table in an explicitly specified database
+#
+CREATE PROCEDURE p1()
+AS
+rec test.t1%ROWTYPE;
+BEGIN
+CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+ERROR 42S02: Table 'test.t1' doesn't exist
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CALL p1();
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "rec.a" bigint(11) DEFAULT NULL,
+  "rec.b" varchar(10) DEFAULT NULL,
+  "rec.c" double DEFAULT NULL,
+  "rec.d" decimal(10,0) DEFAULT NULL
+)
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Referring to a view in the current database
+#
+CREATE PROCEDURE p1()
+AS
+rec v1%ROWTYPE;
+BEGIN
+CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+ERROR 42S02: Table 'test.v1' doesn't exist
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CREATE VIEW v1 AS SELECT * FROM t1;
+CALL p1();
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "rec.a" bigint(11) DEFAULT NULL,
+  "rec.b" varchar(10) DEFAULT NULL,
+  "rec.c" double DEFAULT NULL,
+  "rec.d" decimal(10,0) DEFAULT NULL
+)
+DROP VIEW v1;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Referring to a view in an explicitly specified database
+#
+CREATE PROCEDURE p1()
+AS
+rec test.v1%ROWTYPE;
+BEGIN
+CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+ERROR 42S02: Table 'test.v1' doesn't exist
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CREATE VIEW v1 AS SELECT * FROM t1;
+CALL p1();
+Table	Create Table
+t2	CREATE TABLE "t2" (
+  "rec.a" bigint(11) DEFAULT NULL,
+  "rec.b" varchar(10) DEFAULT NULL,
+  "rec.c" double DEFAULT NULL,
+  "rec.d" decimal(10,0) DEFAULT NULL
+)
+DROP VIEW v1;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Checking that all table%ROWTYPE fields are NULL by default
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE;
+BEGIN
+SELECT rec1.a, rec1.b, rec1.c, rec1.d;
+END;
+$$
+CALL p1();
+rec1.a	rec1.b	rec1.c	rec1.d
+NULL	NULL	NULL	NULL
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# A table%ROWTYPE variable with a ROW expression as a default
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE DEFAULT ROW(10,'bbb');
+BEGIN
+SELECT rec1.a, rec1.b;
+END;
+$$
+CALL p1();
+rec1.a	rec1.b
+10	bbb
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# A table%ROWTYPE variable with an incompatible ROW expression as a default
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE DEFAULT ROW(10,'bbb','ccc');
+BEGIN
+SELECT rec1.a, rec1.b;
+END;
+$$
+CALL p1();
+ERROR 21000: Operand should contain 2 column(s)
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# A table%ROWTYPE variable with a ROW variable as a default
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 ROW(a INT, b VARCHAR(10)):= ROW(10,'bbb');
+rec2 t1%ROWTYPE DEFAULT rec1;
+BEGIN
+SELECT rec2.a, rec2.b;
+END;
+$$
+CALL p1();
+rec2.a	rec2.b
+10	bbb
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# A ROW variable using a table%ROWTYPE variable as a default
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE := ROW(10,'bbb');
+rec2 ROW(a INT, b VARCHAR(10)):= rec1;
+BEGIN
+SELECT rec2.a, rec2.b;
+END;
+$$
+CALL p1();
+rec2.a	rec2.b
+10	bbb
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Assigning table%ROWTYPE variables with a different column count
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE);
+CREATE TABLE t2 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE;
+rec2 t2%ROWTYPE;
+BEGIN
+rec2:=rec1;
+END;
+$$
+CALL p1();
+ERROR 21000: Operand should contain 2 column(s)
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE;
+rec2 t2%ROWTYPE;
+BEGIN
+rec1:=rec2;
+END;
+$$
+CALL p1();
+ERROR 21000: Operand should contain 3 column(s)
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Assigning compatible table%ROWTYPE variables (equal number of fields)
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE TABLE t2 (x INT, y VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE;
+rec2 t2%ROWTYPE;
+BEGIN
+rec1.a:= 10;
+rec1.b:= 'bbb';
+rec2:=rec1;
+SELECT rec2.x, rec2.y;
+END;
+$$
+CALL p1();
+rec2.x	rec2.y
+10	bbb
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Assigning between incompatible table%ROWTYPE and explicit ROW variables
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE;
+rec2 ROW(x INT,y INT,z INT);
+BEGIN
+rec2.x:= 10;
+rec2.y:= 20;
+rec2.z:= 30;
+rec1:= rec2;
+END;
+$$
+CALL p1();
+ERROR 21000: Operand should contain 2 column(s)
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Assigning between compatible table%ROWTYPE and explicit ROW variables
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE;
+rec2 ROW(x INT,y INT);
+BEGIN
+rec2.x:= 10;
+rec2.y:= 20;
+rec1:= rec2;
+SELECT rec1.a, rec1.b;
+rec1.a:= 11;
+rec1.b:= 21;
+rec2:= rec1;
+SELECT rec2.x, rec2.y;
+END;
+$$
+CALL p1();
+rec1.a	rec1.b
+10	20
+rec2.x	rec2.y
+11	21
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Assigning table%ROWTYPE from a ROW expression
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE;
+BEGIN
+rec1:= ROW(10,20);
+SELECT rec1.a, rec1.b;
+END;
+$$
+CALL p1();
+rec1.a	rec1.b
+10	20
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Fetching a cursor into a table%ROWTYPE variable with a wrong field count
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+CREATE TABLE t2 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
+CREATE PROCEDURE p1()
+AS
+rec2 t2%ROWTYPE;
+CURSOR cur1 IS SELECT * FROM t1;
+BEGIN
+OPEN cur1;
+FETCH cur1 INTO rec2;
+CLOSE cur1;
+END;
+$$
+CALL p1();
+ERROR HY000: Incorrect number of FETCH variables
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Fetching a cursor into a table%ROWTYPE variable
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
+INSERT INTO t1 VALUES (20,'bb2',222.222e2, 12.32);
+INSERT INTO t1 VALUES (30,'bb3',333.333e2, 12.33);
+CREATE PROCEDURE p1()
+AS
+rec t1%ROWTYPE;
+CURSOR cur IS SELECT * FROM t1;
+BEGIN
+OPEN cur;
+LOOP
+FETCH cur INTO rec;
+EXIT WHEN cur%NOTFOUND;
+SELECT rec.a, rec.b, rec.c, rec.d;
+INSERT INTO t2 VALUES (rec.a, rec.b, rec.c, rec.d);
+END LOOP;
+CLOSE cur;
+END;
+$$
+CALL p1();
+rec.a	rec.b	rec.c	rec.d
+10	bb1	11111.1	12.31
+rec.a	rec.b	rec.c	rec.d
+20	bb2	22222.2	12.32
+rec.a	rec.b	rec.c	rec.d
+30	bb3	33333.3	12.33
+SELECT * FROM t2;
+a	b	c	d
+10	bb1	11111.1	12.31
+20	bb2	22222.2	12.32
+30	bb3	33333.3	12.33
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Fetching a cursor into a table%ROWTYPE variable with different column names
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE TABLE t2 (x INT, y VARCHAR(10));
+INSERT INTO t1 VALUES (10,'bbb');
+CREATE PROCEDURE p1()
+AS
+rec2 t2%ROWTYPE;
+CURSOR cur1 IS SELECT * FROM t1;
+BEGIN
+OPEN cur1;
+FETCH cur1 INTO rec2;
+SELECT rec2.x, rec2.y;
+CLOSE cur1;
+END;
+$$
+CALL p1();
+rec2.x	rec2.y
+10	bbb
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# Fetching a cursor into a table%ROWTYPE variable, with truncation
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t1 VALUES (10,'11x');
+CREATE PROCEDURE p1()
+AS
+rec2 t2%ROWTYPE;
+CURSOR cur1 IS SELECT * FROM t1;
+BEGIN
+OPEN cur1;
+FETCH cur1 INTO rec2;
+SELECT rec2.a, rec2.b;
+CLOSE cur1;
+END;
+$$
+CALL p1();
+rec2.a	rec2.b
+10	11
+Warnings:
+Warning	1265	Data truncated for column 'b' at row 1
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# table%ROWTYPE variables are not allowed in LIMIT
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,2);
+CREATE PROCEDURE p1()
+AS
+rec1 t1%ROWTYPE:=(1,2);
+BEGIN
+SELECT * FROM t1 LIMIT rec1.a;
+END;
+$$
+ERROR HY000: A variable of a non-integer based type in LIMIT clause
+DROP TABLE t1;
+#
+# table%ROWTYPE variable fields as OUT parameters
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1(a OUT INT,b OUT VARCHAR(10))
+AS
+BEGIN
+a:=10;
+b:='bb';
+END;
+$$
+CREATE PROCEDURE p2()
+AS
+rec1 t1%ROWTYPE;
+BEGIN
+CALL p1(rec1.a, rec1.b);
+SELECT rec1.a, rec1.b;
+END;
+$$
+CALL p2();
+rec1.a	rec1.b
+10	bb
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Passing the entire table%ROWTYPE variable
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10)))
+AS
+BEGIN
+SELECT a.a, a.b;
+END;
+$$
+CREATE PROCEDURE p2()
+AS
+rec1 t1%ROWTYPE:= ROW(10,'bb');
+BEGIN
+CALL p1(rec1);
+END;
+$$
+CALL p2();
+a.a	a.b
+10	bb
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Passing the entire table%ROWTYPE variable as an OUT parameter
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1(a OUT ROW(a INT, b VARCHAR(10)))
+AS
+BEGIN
+a:= ROW(10,'bb');
+END;
+$$
+CREATE PROCEDURE p2()
+AS
+rec1 t1%ROWTYPE;
+BEGIN
+CALL p1(rec1);
+SELECT rec1.a, rec1.b;
+END;
+$$
+CALL p2();
+rec1.a	rec1.b
+10	bb
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Assigning a table%ROWTYPE field to an OUT parameter
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1 (res IN OUT INTEGER)
+AS
+rec1 t1%ROWTYPE:=ROW(10,'b0');
+BEGIN
+res:=rec1.a;
+END;
+$$
+CALL p1(@res);
+SELECT @res;
+@res
+10
+SET @res=NULL;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Testing Item_splocal_row_field_by_name::print
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1
+AS
+rec t1%ROWTYPE:=ROW(10,'bb');
+BEGIN
+EXPLAIN EXTENDED SELECT rec.a, rec.b;
+END;
+$$
+CALL p1();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select rec.a@0["a"] AS "rec.a",rec.b@0["b"] AS "rec.b"
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Non-existing field
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1
+AS
+rec t1%ROWTYPE;
+BEGIN
+SELECT rec.c;
+END;
+$$
+CALL p1();
+ERROR HY000: Row variable 'rec' does not have a field 'c'
+ALTER TABLE t1 ADD c INT;
+CALL p1();
+rec.c
+NULL
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Testing that field names are case insensitive
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1
+AS
+rec t1%ROWTYPE:=ROW(10,'bb');
+BEGIN
+SELECT rec.A, rec.B;
+END;
+$$
+CALL p1();
+rec.A	rec.B
+10	bb
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Testing that table%ROWTYPE uses temporary tables vs shadowed real tables
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
+CREATE PROCEDURE p1
+AS
+rec t1%ROWTYPE:=ROW(10,'bb');
+BEGIN
+SELECT rec.A, rec.B;
+END;
+$$
+CALL p1();
+ERROR HY000: Row variable 'rec' does not have a field 'A'
+DROP TEMPORARY TABLE t1;
+CALL p1();
+rec.A	rec.B
+10	bb
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Testing that the structure of table%ROWTYPE variables is determined at the very beginning and is not changed after ALTER
+#
+CREATE TABLE t1 (a INT, b VARCHAR(32));
+INSERT INTO t1 VALUES (10,'b10');
+CREATE PROCEDURE p1 AS
+BEGIN
+ALTER TABLE t1 ADD c INT;
+DECLARE
+rec t1%ROWTYPE; -- this will not have column "c"
+  BEGIN
+rec.c:=10;
+END;
+END;
+$$
+CALL p1();
+ERROR HY000: Row variable 'rec' does not have a field 'c'
+DROP TABLE t1;
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-security.result b/mysql-test/suite/compat/oracle/r/sp-security.result
index c6fd9ee5560..b98ecaca972 100644
--- a/mysql-test/suite/compat/oracle/r/sp-security.result
+++ b/mysql-test/suite/compat/oracle/r/sp-security.result
@@ -63,6 +63,16 @@ $$
 CALL p1;
 ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1'
 DROP PROCEDURE p1;
+CREATE PROCEDURE p1()
+AS
+a db1.t1%ROWTYPE;
+BEGIN
+SELECT a.a;
+END;
+$$
+CALL p1;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1'
+DROP PROCEDURE p1;
 #
 # Stored procedure: Using %TYPE for with a table that we don't have access to
 # DEFINER root, SQL SECURITY INVOKER
@@ -80,6 +90,19 @@ connection conn1;
 CALL p1;
 ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1'
 DROP PROCEDURE p1;
+connection default;
+CREATE PROCEDURE p1()
+SQL SECURITY INVOKER
+AS
+a db1.t1%ROWTYPE;
+BEGIN
+SELECT a.a;
+END;
+$$
+connection conn1;
+CALL p1;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1'
+DROP PROCEDURE p1;
 #
 # Stored procedure: Using %TYPE for with a table that we don't have access to
 # DEFINER root, SQL SECURITY DEFINER
@@ -98,6 +121,21 @@ CALL p1;
 a
 10
 DROP PROCEDURE p1;
+connection default;
+CREATE PROCEDURE p1()
+SQL SECURITY DEFINER
+AS
+a db1.t1%ROWTYPE;
+BEGIN
+a.a:= 10;
+SELECT a.a;
+END;
+$$
+connection conn1;
+CALL p1;
+a.a
+10
+DROP PROCEDURE p1;
 #
 # Stored function: Using %TYPE for with a table that we don't have access to
 # DEFINER user1, SQL SECURITY DEFAULT
@@ -227,6 +265,17 @@ $$
 CALL p1;
 ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1'
 DROP PROCEDURE p1;
+CREATE PROCEDURE p1()
+AS
+b db1.t1%ROWTYPE;
+BEGIN
+b.b:=10;
+SELECT b.b;
+END;
+$$
+CALL p1;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1'
+DROP PROCEDURE p1;
 #
 # Clean up
 #
diff --git a/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test b/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test
index cd0d9111107..ee4c5c8065a 100644
--- a/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test
+++ b/mysql-test/suite/compat/oracle/t/binlog_stm_sp.test
@@ -79,3 +79,43 @@ source include/show_binlog_events.inc;
 --echo #
 --echo # End of MDEV-10914 ROW data type for stored routine variables
 --echo #
+
+
+--echo #
+--echo #  MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec t1%ROWTYPE;
+BEGIN
+  rec.a:=100;
+  rec.b:=200;
+  INSERT INTO t1 VALUES (rec.a,rec.b);
+  INSERT INTO t1 VALUES (10, rec=ROW(100,200));
+  INSERT INTO t1 VALUES (10, ROW(100,200)=rec);
+  INSERT INTO t1 SELECT 10, 20 FROM DUAL WHERE rec=ROW(100,200);
+  INSERT INTO t1 SELECT 10, 21 FROM DUAL WHERE ROW(100,200)=rec;
+  rec.a:=NULL;
+  INSERT INTO t1 VALUES (11, rec=ROW(100,200));
+  INSERT INTO t1 VALUES (11, rec=ROW(100,201));
+  INSERT INTO t1 VALUES (11, ROW(100,200)=rec);
+  INSERT INTO t1 VALUES (11, ROW(100,201)=rec);
+  INSERT INTO t1 SELECT 11, 20 FROM DUAL WHERE rec=ROW(100,200);
+  INSERT INTO t1 SELECT 11, 21 FROM DUAL WHERE ROW(100,200)=rec;
+  rec.b:=NULL;
+  INSERT INTO t1 VALUES (12, rec=ROW(100,200));
+  INSERT INTO t1 VALUES (12, ROW(100,200)=rec);
+  INSERT INTO t1 SELECT 12, 20 FROM DUAL WHERE rec=ROW(100,200);
+  INSERT INTO t1 SELECT 12, 21 FROM DUAL WHERE ROW(100,200)=rec;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+SELECT * FROM t1;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+--let $binlog_file = LAST
+source include/show_binlog_events.inc;
diff --git a/mysql-test/suite/compat/oracle/t/sp-code.test b/mysql-test/suite/compat/oracle/t/sp-code.test
index 5e20db1ceda..53ffeb7bc01 100644
--- a/mysql-test/suite/compat/oracle/t/sp-code.test
+++ b/mysql-test/suite/compat/oracle/t/sp-code.test
@@ -742,3 +742,25 @@ DROP PROCEDURE p1;
 --echo #
 --echo # End of MDEV-10914 ROW data type for stored routine variables
 --echo #
+
+--echo #
+--echo # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+BEGIN
+  rec1.a:= 10;
+  rec1.b:= 'bbb';
+  rec1.c:= 10e2;
+  rec1.d:= 10.12;
+  rec1.c:= rec1.d;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-row.test b/mysql-test/suite/compat/oracle/t/sp-row.test
index b86b087f7b6..d499cf2591f 100644
--- a/mysql-test/suite/compat/oracle/t/sp-row.test
+++ b/mysql-test/suite/compat/oracle/t/sp-row.test
@@ -598,7 +598,7 @@ DELIMITER ;$$
 --echo # Referencing a non-existing row field
 --echo #
 DELIMITER $$;
---error ER_UNKNOWN_ERROR
+--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
 CREATE PROCEDURE p1()
 AS
   a ROW(a INT,b INT);
@@ -1396,3 +1396,682 @@ DROP PROCEDURE p1;
 --echo #
 --echo # End of MDEV-10914 ROW data type for stored routine variables
 --echo #
+
+
+--echo #
+--echo # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
+--echo #
+
+--echo #
+--echo # Referring to a table in a non-existing database
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec test2.t1%ROWTYPE;
+BEGIN
+  NULL;
+END;
+$$
+DELIMITER ;$$
+--error ER_NO_SUCH_TABLE
+CALL p1();
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+--error ER_NO_SUCH_TABLE
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Referring to a table in the current database
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec t1%ROWTYPE;
+BEGIN
+  CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
+  SHOW CREATE TABLE t2;
+  DROP TABLE t2;
+END;
+$$
+DELIMITER ;$$
+--error ER_NO_SUCH_TABLE
+CALL p1();
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Referring to a table in an explicitly specified database
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec test.t1%ROWTYPE;
+BEGIN
+  CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
+  SHOW CREATE TABLE t2;
+  DROP TABLE t2;
+END;
+$$
+DELIMITER ;$$
+--error ER_NO_SUCH_TABLE
+CALL p1();
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Referring to a view in the current database
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec v1%ROWTYPE;
+BEGIN
+  CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
+  SHOW CREATE TABLE t2;
+  DROP TABLE t2;
+END;
+$$
+DELIMITER ;$$
+--error ER_NO_SUCH_TABLE
+CALL p1();
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CREATE VIEW v1 AS SELECT * FROM t1;
+CALL p1();
+DROP VIEW v1;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Referring to a view in an explicitly specified database
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec test.v1%ROWTYPE;
+BEGIN
+  CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d;
+  SHOW CREATE TABLE t2;
+  DROP TABLE t2;
+END;
+$$
+DELIMITER ;$$
+--error ER_NO_SUCH_TABLE
+CALL p1();
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10));
+CREATE VIEW v1 AS SELECT * FROM t1;
+CALL p1();
+DROP VIEW v1;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Checking that all table%ROWTYPE fields are NULL by default
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+BEGIN
+  SELECT rec1.a, rec1.b, rec1.c, rec1.d;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # A table%ROWTYPE variable with a ROW expression as a default
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE DEFAULT ROW(10,'bbb');
+BEGIN
+  SELECT rec1.a, rec1.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # A table%ROWTYPE variable with an incompatible ROW expression as a default
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE DEFAULT ROW(10,'bbb','ccc');
+BEGIN
+  SELECT rec1.a, rec1.b;
+END;
+$$
+DELIMITER ;$$
+--error ER_OPERAND_COLUMNS
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # A table%ROWTYPE variable with a ROW variable as a default
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 ROW(a INT, b VARCHAR(10)):= ROW(10,'bbb');
+  rec2 t1%ROWTYPE DEFAULT rec1;
+BEGIN
+  SELECT rec2.a, rec2.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # A ROW variable using a table%ROWTYPE variable as a default
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE := ROW(10,'bbb');
+  rec2 ROW(a INT, b VARCHAR(10)):= rec1;
+BEGIN
+  SELECT rec2.a, rec2.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Assigning table%ROWTYPE variables with a different column count
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE);
+CREATE TABLE t2 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+  rec2 t2%ROWTYPE;
+BEGIN
+  rec2:=rec1;
+END;
+$$
+DELIMITER ;$$
+--error ER_OPERAND_COLUMNS
+CALL p1();
+DROP PROCEDURE p1;
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+  rec2 t2%ROWTYPE;
+BEGIN
+  rec1:=rec2;
+END;
+$$
+DELIMITER ;$$
+--error ER_OPERAND_COLUMNS
+CALL p1();
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Assigning compatible table%ROWTYPE variables (equal number of fields)
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE TABLE t2 (x INT, y VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+  rec2 t2%ROWTYPE;
+BEGIN
+  rec1.a:= 10;
+  rec1.b:= 'bbb';
+  rec2:=rec1;
+  SELECT rec2.x, rec2.y;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Assigning between incompatible table%ROWTYPE and explicit ROW variables
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+  rec2 ROW(x INT,y INT,z INT);
+BEGIN
+  rec2.x:= 10;
+  rec2.y:= 20;
+  rec2.z:= 30;
+  rec1:= rec2;
+END;
+$$
+DELIMITER ;$$
+--error ER_OPERAND_COLUMNS
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Assigning between compatible table%ROWTYPE and explicit ROW variables
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+  rec2 ROW(x INT,y INT);
+BEGIN
+  rec2.x:= 10;
+  rec2.y:= 20;
+  rec1:= rec2;
+  SELECT rec1.a, rec1.b;
+  rec1.a:= 11;
+  rec1.b:= 21;
+  rec2:= rec1;
+  SELECT rec2.x, rec2.y;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Assigning table%ROWTYPE from a ROW expression
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+BEGIN
+  rec1:= ROW(10,20);
+  SELECT rec1.a, rec1.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Fetching a cursor into a table%ROWTYPE variable with a wrong field count
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+CREATE TABLE t2 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec2 t2%ROWTYPE;
+  CURSOR cur1 IS SELECT * FROM t1;
+BEGIN
+  OPEN cur1;
+  FETCH cur1 INTO rec2;
+  CLOSE cur1;
+END;
+$$
+DELIMITER ;$$
+--error ER_SP_WRONG_NO_OF_FETCH_ARGS
+CALL p1();
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Fetching a cursor into a table%ROWTYPE variable
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t1 VALUES (10,'bb1',111.111e2, 12.31);
+INSERT INTO t1 VALUES (20,'bb2',222.222e2, 12.32);
+INSERT INTO t1 VALUES (30,'bb3',333.333e2, 12.33);
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec t1%ROWTYPE;
+  CURSOR cur IS SELECT * FROM t1;
+BEGIN
+  OPEN cur;
+  LOOP
+    FETCH cur INTO rec;
+    EXIT WHEN cur%NOTFOUND;
+    SELECT rec.a, rec.b, rec.c, rec.d;
+    INSERT INTO t2 VALUES (rec.a, rec.b, rec.c, rec.d);
+  END LOOP;
+  CLOSE cur;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+SELECT * FROM t2;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # Fetching a cursor into a table%ROWTYPE variable with different column names
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE TABLE t2 (x INT, y VARCHAR(10));
+INSERT INTO t1 VALUES (10,'bbb');
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec2 t2%ROWTYPE;
+  CURSOR cur1 IS SELECT * FROM t1;
+BEGIN
+  OPEN cur1;
+  FETCH cur1 INTO rec2;
+  SELECT rec2.x, rec2.y;
+  CLOSE cur1;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Fetching a cursor into a table%ROWTYPE variable, with truncation
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t1 VALUES (10,'11x');
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec2 t2%ROWTYPE;
+  CURSOR cur1 IS SELECT * FROM t1;
+BEGIN
+  OPEN cur1;
+  FETCH cur1 INTO rec2;
+  SELECT rec2.a, rec2.b;
+  CLOSE cur1;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP TABLE t2;
+DROP TABLE t1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # table%ROWTYPE variables are not allowed in LIMIT
+--echo #
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,2);
+DELIMITER $$;
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE:=(1,2);
+BEGIN
+  SELECT * FROM t1 LIMIT rec1.a;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
+
+
+--echo #
+--echo # table%ROWTYPE variable fields as OUT parameters
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1(a OUT INT,b OUT VARCHAR(10))
+AS
+BEGIN
+  a:=10;
+  b:='bb';
+END;
+$$
+CREATE PROCEDURE p2()
+AS
+  rec1 t1%ROWTYPE;
+BEGIN
+  CALL p1(rec1.a, rec1.b);
+  SELECT rec1.a, rec1.b;
+END;
+$$
+DELIMITER ;$$
+CALL p2();
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Passing the entire table%ROWTYPE variable
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10)))
+AS
+BEGIN
+  SELECT a.a, a.b;
+END;
+$$
+CREATE PROCEDURE p2()
+AS
+  rec1 t1%ROWTYPE:= ROW(10,'bb');
+BEGIN
+  CALL p1(rec1);
+END;
+$$
+DELIMITER ;$$
+CALL p2();
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Passing the entire table%ROWTYPE variable as an OUT parameter
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1(a OUT ROW(a INT, b VARCHAR(10)))
+AS
+BEGIN
+  a:= ROW(10,'bb');
+END;
+$$
+CREATE PROCEDURE p2()
+AS
+  rec1 t1%ROWTYPE;
+BEGIN
+  CALL p1(rec1);
+  SELECT rec1.a, rec1.b;
+END;
+$$
+DELIMITER ;$$
+CALL p2();
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Assigning a table%ROWTYPE field to an OUT parameter
+--echo #
+
+DELIMITER $$;
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE PROCEDURE p1 (res IN OUT INTEGER)
+AS
+  rec1 t1%ROWTYPE:=ROW(10,'b0');
+BEGIN
+  res:=rec1.a;
+END;
+$$
+DELIMITER ;$$
+CALL p1(@res);
+SELECT @res;
+SET @res=NULL;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Testing Item_splocal_row_field_by_name::print
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec t1%ROWTYPE:=ROW(10,'bb');
+BEGIN
+  EXPLAIN EXTENDED SELECT rec.a, rec.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # Non-existing field
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec t1%ROWTYPE;
+BEGIN
+  SELECT rec.c;
+END;
+$$
+DELIMITER ;$$
+--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
+CALL p1();
+ALTER TABLE t1 ADD c INT;
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Testing that field names are case insensitive
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec t1%ROWTYPE:=ROW(10,'bb');
+BEGIN
+  SELECT rec.A, rec.B;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Testing that table%ROWTYPE uses temporary tables vs shadowed real tables
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+CREATE TEMPORARY TABLE t1 (x INT, y VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec t1%ROWTYPE:=ROW(10,'bb');
+BEGIN
+  SELECT rec.A, rec.B;
+END;
+$$
+DELIMITER ;$$
+--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
+CALL p1();
+DROP TEMPORARY TABLE t1;
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # Testing that the structure of table%ROWTYPE variables is determined at the very beginning and is not changed after ALTER
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(32));
+INSERT INTO t1 VALUES (10,'b10');
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+BEGIN
+  ALTER TABLE t1 ADD c INT;
+  DECLARE
+    rec t1%ROWTYPE; -- this will not have column "c"
+  BEGIN
+    rec.c:=10;
+  END;
+END;
+$$
+DELIMITER ;$$
+--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
+CALL p1();
+DROP TABLE t1;
+DROP PROCEDURE p1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-security.test b/mysql-test/suite/compat/oracle/t/sp-security.test
index be2d791f803..1732c0b80db 100644
--- a/mysql-test/suite/compat/oracle/t/sp-security.test
+++ b/mysql-test/suite/compat/oracle/t/sp-security.test
@@ -77,6 +77,19 @@ DELIMITER ;$$
 CALL p1;
 DROP PROCEDURE p1;
 
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  a db1.t1%ROWTYPE;
+BEGIN
+  SELECT a.a;
+END;
+$$
+DELIMITER ;$$
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL p1;
+DROP PROCEDURE p1;
+
 
 --echo #
 --echo # Stored procedure: Using %TYPE for with a table that we don't have access to
@@ -100,6 +113,23 @@ CALL p1;
 DROP PROCEDURE p1;
 
 
+connection default;
+DELIMITER $$;
+CREATE PROCEDURE p1()
+SQL SECURITY INVOKER
+AS
+  a db1.t1%ROWTYPE;
+BEGIN
+  SELECT a.a;
+END;
+$$
+DELIMITER ;$$
+connection conn1;
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL p1;
+DROP PROCEDURE p1;
+
+
 --echo #
 --echo # Stored procedure: Using %TYPE for with a table that we don't have access to
 --echo # DEFINER root, SQL SECURITY DEFINER
@@ -120,6 +150,22 @@ connection conn1;
 CALL p1;
 DROP PROCEDURE p1;
 
+connection default;
+DELIMITER $$;
+CREATE PROCEDURE p1()
+SQL SECURITY DEFINER
+AS
+  a db1.t1%ROWTYPE;
+BEGIN
+  a.a:= 10;
+  SELECT a.a;
+END;
+$$
+DELIMITER ;$$
+connection conn1;
+CALL p1;
+DROP PROCEDURE p1;
+
 
 --echo #
 --echo # Stored function: Using %TYPE for with a table that we don't have access to
@@ -270,6 +316,20 @@ DELIMITER ;$$
 CALL p1;
 DROP PROCEDURE p1;
 
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  b db1.t1%ROWTYPE;
+BEGIN
+  b.b:=10;
+  SELECT b.b;
+END;
+$$
+DELIMITER ;$$
+--error ER_COLUMNACCESS_DENIED_ERROR
+CALL p1;
+DROP PROCEDURE p1;
+
 
 --echo #
 --echo # Clean up
diff --git a/mysql-test/t/sp-row.test b/mysql-test/t/sp-row.test
index 42fafb75077..cf1f99468be 100644
--- a/mysql-test/t/sp-row.test
+++ b/mysql-test/t/sp-row.test
@@ -548,7 +548,7 @@ DELIMITER ;$$
 --echo # Referencing a non-existing row field
 --echo #
 DELIMITER $$;
---error ER_UNKNOWN_ERROR
+--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
 CREATE PROCEDURE p1()
 BEGIN
   DECLARE a ROW(a INT,b INT);
diff --git a/sql/field.h b/sql/field.h
index b3b8dcd703e..e110e1f8146 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -4009,12 +4009,21 @@ public:
 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
   Row_definition_list *m_row_field_definitions;    // for ROW
 public:
   Spvar_definition()
    :m_column_type_ref(NULL),
+    m_table_rowtype_ref(NULL),
     m_row_field_definitions(NULL) { }
+  Spvar_definition(THD *thd, Field *field)
+   :Column_definition(thd, field, NULL),
+    m_column_type_ref(NULL),
+    m_table_rowtype_ref(NULL),
+    m_row_field_definitions(NULL)
+  { }
   bool is_column_type_ref() const { return m_column_type_ref != 0; }
+  bool is_table_rowtype_ref() const { return m_table_rowtype_ref != 0; }
   class Qualified_column_ident *column_type_ref() const
   {
     return m_column_type_ref;
@@ -4024,6 +4033,15 @@ public:
     m_column_type_ref= ref;
   }
 
+  class Table_ident *table_rowtype_ref() const
+  {
+    return m_table_rowtype_ref;
+  }
+  void set_table_rowtype_ref(class Table_ident *ref)
+  {
+    m_table_rowtype_ref= ref;
+  }
+
   /*
     Find a ROW field by name.
     See Row_field_list::find_row_field_by_name() for details.
diff --git a/sql/item.cc b/sql/item.cc
index 56428fb39bb..7c76d9f6b51 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1501,12 +1501,9 @@ Item_sp_variable::Item_sp_variable(THD *thd, char *sp_var_name_str,
 }
 
 
-bool Item_sp_variable::fix_fields(THD *thd, Item **)
+bool Item_sp_variable::fix_fields_from_item(THD *thd, Item **, const Item *it)
 {
-  Item *it;
-
   m_thd= thd; /* NOTE: this must be set before any this_xxx() */
-  it= this_item();
 
   DBUG_ASSERT(it->fixed);
 
@@ -1612,11 +1609,17 @@ Item_splocal::Item_splocal(THD *thd, const LEX_STRING &sp_var_name,
 }
 
 
+bool Item_splocal::fix_fields(THD *thd, Item **ref)
+{
+  return fix_fields_from_item(thd, ref, thd->spcont->get_item(m_var_idx));
+}
+
+
 Item *
 Item_splocal::this_item()
 {
   DBUG_ASSERT(m_sp == m_thd->spcont->sp);
-
+  DBUG_ASSERT(fixed);
   return m_thd->spcont->get_item(m_var_idx);
 }
 
@@ -1624,7 +1627,7 @@ const Item *
 Item_splocal::this_item() const
 {
   DBUG_ASSERT(m_sp == m_thd->spcont->sp);
-
+  DBUG_ASSERT(fixed);
   return m_thd->spcont->get_item(m_var_idx);
 }
 
@@ -1633,7 +1636,7 @@ Item **
 Item_splocal::this_item_addr(THD *thd, Item **)
 {
   DBUG_ASSERT(m_sp == thd->spcont->sp);
-
+  DBUG_ASSERT(fixed);
   return thd->spcont->get_item_addr(m_var_idx);
 }
 
@@ -1729,10 +1732,18 @@ bool Item_splocal::check_cols(uint n)
 }
 
 
+bool Item_splocal_row_field::fix_fields(THD *thd, Item **ref)
+{
+  Item *item= thd->spcont->get_item(m_var_idx)->element_index(m_field_idx);
+  return fix_fields_from_item(thd, ref, item);
+}
+
+
 Item *
 Item_splocal_row_field::this_item()
 {
   DBUG_ASSERT(m_sp == m_thd->spcont->sp);
+  DBUG_ASSERT(fixed);
   return m_thd->spcont->get_item(m_var_idx)->element_index(m_field_idx);
 }
 
@@ -1741,6 +1752,7 @@ const Item *
 Item_splocal_row_field::this_item() const
 {
   DBUG_ASSERT(m_sp == m_thd->spcont->sp);
+  DBUG_ASSERT(fixed);
   return m_thd->spcont->get_item(m_var_idx)->element_index(m_field_idx);
 }
 
@@ -1749,6 +1761,7 @@ Item **
 Item_splocal_row_field::this_item_addr(THD *thd, Item **)
 {
   DBUG_ASSERT(m_sp == thd->spcont->sp);
+  DBUG_ASSERT(fixed);
   return thd->spcont->get_item(m_var_idx)->addr(m_field_idx);
 }
 
@@ -1773,6 +1786,43 @@ bool Item_splocal_row_field::set_value(THD *thd, sp_rcontext *ctx, Item **it)
 }
 
 
+bool Item_splocal_row_field_by_name::fix_fields(THD *thd, Item **it)
+{
+  m_thd= thd;
+  Item *row= m_thd->spcont->get_item(m_var_idx);
+  if (row->element_index_by_name(&m_field_idx, m_field_name))
+  {
+    my_error(ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD, MYF(0),
+             m_name.str, m_field_name.str);
+    return true;
+  }
+  Item *item= row->element_index(m_field_idx);
+  set_handler(item->type_handler());
+  return fix_fields_from_item(thd, it, item);
+}
+
+
+void Item_splocal_row_field_by_name::print(String *str, enum_query_type)
+{
+  str->reserve(m_name.length + 2 * m_field_name.length + 8);
+  str->append(m_name.str, m_name.length);
+  str->append('.');
+  str->append(m_field_name.str, m_field_name.length);
+  str->append('@');
+  str->append_ulonglong(m_var_idx);
+  str->append("[\"", 2);
+  str->append(m_field_name.str, m_field_name.length);
+  str->append("\"]", 2);
+}
+
+
+bool Item_splocal_row_field_by_name::set_value(THD *thd, sp_rcontext *ctx, Item **it)
+{
+  DBUG_ASSERT(fixed); // Make sure m_field_idx is already set
+  return Item_splocal_row_field::set_value(thd, ctx, it);
+}
+
+
 /*****************************************************************************
   Item_case_expr methods
 *****************************************************************************/
@@ -1784,6 +1834,13 @@ Item_case_expr::Item_case_expr(THD *thd, uint case_expr_id):
 }
 
 
+bool Item_case_expr::fix_fields(THD *thd, Item **ref)
+{
+  Item *item= thd->spcont->get_case_expr(m_case_expr_id);
+  return fix_fields_from_item(thd, ref, item);
+}
+
+
 Item *
 Item_case_expr::this_item()
 {
@@ -7398,6 +7455,26 @@ void Item_field::print(String *str, enum_query_type query_type)
 }
 
 
+bool Item_field_row::element_index_by_name(uint *idx,
+                                           const LEX_STRING &name) const
+{
+  Field *field;
+  for (uint i= 0; (field= get_row_field(i)); i++)
+  {
+    // Use the same comparison style with sp_context::find_variable()
+    if (!my_strnncoll(system_charset_info,
+                      (const uchar *) field->field_name,
+                      strlen(field->field_name),
+                      (const uchar *) name.str, name.length))
+    {
+      *idx= i;
+      return false;
+    }
+  }
+  return true;
+}
+
+
 void Item_temptable_field::print(String *str, enum_query_type query_type)
 {
   /*
diff --git a/sql/item.h b/sql/item.h
index 01573c9e6ea..9eabe307ce0 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1622,6 +1622,10 @@ public:
   // Row emulation
   virtual uint cols() { return 1; }
   virtual Item* element_index(uint i) { return this; }
+  virtual bool element_index_by_name(uint *idx, const LEX_STRING &name) const
+  {
+    return true;
+  }
   virtual Item** addr(uint i) { return 0; }
   virtual bool check_cols(uint c);
   // It is not row => null inside is impossible
@@ -2086,6 +2090,7 @@ protected:
   */
   THD *m_thd;
 
+  bool fix_fields_from_item(THD *thd, Item **, const Item *);
 public:
   LEX_STRING m_name;
 
@@ -2102,7 +2107,7 @@ public:
   Item_sp_variable(THD *thd, char *sp_var_name_str, uint sp_var_name_length);
 
 public:
-  bool fix_fields(THD *thd, Item **);
+  bool fix_fields(THD *thd, Item **)= 0;
 
   double val_real();
   longlong val_int();
@@ -2175,6 +2180,7 @@ public:
                enum_field_types sp_var_type,
                uint pos_in_q= 0, uint len_in_q= 0);
 
+  bool fix_fields(THD *, Item **);
   Item *this_item();
   const Item *this_item() const;
   Item **this_item_addr(THD *thd, Item **);
@@ -2273,6 +2279,7 @@ public:
 */
 class Item_splocal_row_field :public Item_splocal
 {
+protected:
   LEX_STRING m_field_name;
   uint m_field_idx;
   bool set_value(THD *thd, sp_rcontext *ctx, Item **it);
@@ -2288,6 +2295,7 @@ public:
     m_field_name(sp_field_name),
     m_field_idx(sp_field_idx)
   { }
+  bool fix_fields(THD *thd, Item **);
   Item *this_item();
   const Item *this_item() const;
   Item **this_item_addr(THD *thd, Item **);
@@ -2296,6 +2304,25 @@ public:
 };
 
 
+class Item_splocal_row_field_by_name :public Item_splocal_row_field
+{
+  bool set_value(THD *thd, sp_rcontext *ctx, Item **it);
+public:
+  Item_splocal_row_field_by_name(THD *thd,
+                                 const LEX_STRING &sp_var_name,
+                                 const LEX_STRING &sp_field_name,
+                                 uint sp_var_idx,
+                                 enum_field_types sp_var_type,
+                                 uint pos_in_q= 0, uint len_in_q= 0)
+   :Item_splocal_row_field(thd, sp_var_name, sp_field_name,
+                           sp_var_idx, 0 /* field index will be set later */,
+                           sp_var_type, pos_in_q, len_in_q)
+  { }
+  bool fix_fields(THD *thd, Item **it);
+  void print(String *str, enum_query_type query_type);
+};
+
+
 /*****************************************************************************
   Item_splocal inline implementation.
 *****************************************************************************/
@@ -2325,6 +2352,7 @@ public:
   Item_case_expr(THD *thd, uint case_expr_id);
 
 public:
+  bool fix_fields(THD *thd, Item **);
   Item *this_item();
   const Item *this_item() const;
   Item **this_item_addr(THD *thd, Item **);
@@ -2758,6 +2786,7 @@ public:
   Item_result result_type() const{ return ROW_RESULT ; }
   Item_result cmp_type() const { return ROW_RESULT; }
   uint cols() { return arg_count; }
+  bool element_index_by_name(uint *idx, const LEX_STRING &name) const;
   Item* element_index(uint i) { return arg_count ? args[i] : this; }
   Item** addr(uint i) { return arg_count ? args + i : NULL; }
   bool check_cols(uint c)
diff --git a/sql/lex.h b/sql/lex.h
index cdd4daffaf8..2a30f7f31ad 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -530,6 +530,7 @@ static SYMBOL symbols[] = {
   { "ROW",		SYM(ROW_SYM)},
   { "ROWCOUNT",         SYM(ROWCOUNT_SYM)}, /* Oracle-N */
   { "ROWS",		SYM(ROWS_SYM)},
+  { "ROWTYPE",          SYM(ROWTYPE_SYM)},
   { "ROW_COUNT",        SYM(ROW_COUNT_SYM)},
   { "ROW_FORMAT",	SYM(ROW_FORMAT_SYM)},
   { "RTREE",		SYM(RTREE_SYM)},
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 8b0bd18e95a..49ade235910 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7462,3 +7462,5 @@ ER_WRONG_PARAMCOUNT_TO_CURSOR 42000
         rus "Некорректное количество параметров для курсора '%-.192s'"
 ER_UNKNOWN_STRUCTURED_VARIABLE
         eng "Unknown structured system variable or ROW routine variable '%-.*s'"
+ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD
+        eng "Row variable '%-.192s' does not have a field '%-.192s'"
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 0d873124b2d..a40ad52676c 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -343,6 +343,12 @@ Item *
 sp_prepare_func_item(THD* thd, Item **it_addr, uint cols)
 {
   DBUG_ENTER("sp_prepare_func_item");
+  if (!(*it_addr)->fixed &&
+      (*it_addr)->fix_fields(thd, it_addr))
+  {
+    DBUG_PRINT("info", ("fix_fields() failed"));
+    DBUG_RETURN(NULL);
+  }
   it_addr= (*it_addr)->this_item_addr(thd, it_addr);
 
   if ((!(*it_addr)->fixed &&
@@ -3413,6 +3419,63 @@ sp_instr_set_row_field::print(String *str)
 
 
 /*
+  sp_instr_set_field_by_name class functions
+*/
+
+int
+sp_instr_set_row_field_by_name::exec_core(THD *thd, uint *nextp)
+{
+  int res;
+  uint idx;
+  Item_field_row *row= (Item_field_row*) thd->spcont->get_item(m_offset);
+  if ((res= row->element_index_by_name(&idx, m_field_name)))
+  {
+    sp_variable *var= m_ctx->find_variable(m_offset);
+    my_error(ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD, MYF(0),
+             var->name.str, m_field_name.str);
+    goto error;
+  }
+  res= thd->spcont->set_variable_row_field(thd, m_offset, idx, &m_value);
+  if (res)
+  {
+    /* Failed to evaluate the value. Reset the variable to NULL. */
+    thd->spcont->set_variable_row_field_to_null(thd, m_offset, idx);
+  }
+error:
+  delete_explain_query(thd->lex);
+  *nextp= m_ip + 1;
+  return res;
+}
+
+
+void
+sp_instr_set_row_field_by_name::print(String *str)
+{
+  /* set name.field@offset["field"] ... */
+  int rsrv= SP_INSTR_UINT_MAXLEN + 6 + 6 + 3 + 2;
+  sp_variable *var= m_ctx->find_variable(m_offset);
+  DBUG_ASSERT(var);
+  DBUG_ASSERT(var->field_def.is_table_rowtype_ref());
+
+  rsrv+= var->name.length + 2 * m_field_name.length;
+  if (str->reserve(rsrv))
+    return;
+  str->qs_append(STRING_WITH_LEN("set "));
+    str->qs_append(var->name.str, var->name.length);
+  str->qs_append('.');
+  str->qs_append(m_field_name.str);
+  str->qs_append('@');
+  str->qs_append(m_offset);
+  str->qs_append("[\"",2);
+  str->qs_append(m_field_name.str);
+  str->qs_append("\"]",2);
+  str->qs_append(' ');
+  m_value->print(str, enum_query_type(QT_ORDINARY |
+                                      QT_ITEM_ORIGINAL_FUNC_NULLIF));
+}
+
+
+/*
   sp_instr_set_trigger_field class functions
 */
 
@@ -4461,8 +4524,7 @@ sp_head::set_local_variable(THD *thd, sp_pcontext *spcont,
 
   sp_instr_set *sp_set= new (thd->mem_root)
                         sp_instr_set(instructions(), spcont,
-                                     spv->offset, val, spv->sql_type(),
-                                     lex, true);
+                                     spv->offset, val, lex, true);
 
   return sp_set == NULL || add_instr(sp_set);
 }
@@ -4484,12 +4546,31 @@ sp_head::set_local_variable_row_field(THD *thd, sp_pcontext *spcont,
                                                          spcont,
                                                          spv->offset,
                                                          field_idx, val,
-                                                         spv->sql_type(),
                                                          lex, true);
   return sp_set == NULL || add_instr(sp_set);
 }
 
 
+bool
+sp_head::set_local_variable_row_field_by_name(THD *thd, sp_pcontext *spcont,
+                                              sp_variable *spv,
+                                              const LEX_STRING &field_name,
+                                              Item *val, LEX *lex)
+{
+  if (!(val= adjust_assignment_source(thd, val, NULL)))
+    return true;
+
+  sp_instr_set_row_field_by_name *sp_set=
+    new (thd->mem_root) sp_instr_set_row_field_by_name(instructions(),
+                                                       spcont,
+                                                       spv->offset,
+                                                       field_name,
+                                                       val,
+                                                       lex, true);
+  return sp_set == NULL || add_instr(sp_set);
+}
+
+
 bool sp_head::add_open_cursor(THD *thd, sp_pcontext *spcont, uint offset,
                               sp_pcontext *param_spcont,
                               List<sp_assignment_lex> *parameters)
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 51e10492050..92f17884285 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -366,6 +366,10 @@ public:
   bool set_local_variable_row_field(THD *thd, sp_pcontext *spcont,
                                     sp_variable *spv, uint field_idx,
                                     Item *val, LEX *lex);
+  bool set_local_variable_row_field_by_name(THD *thd, sp_pcontext *spcont,
+                                            sp_variable *spv,
+                                            const LEX_STRING &field_name,
+                                            Item *val, LEX *lex);
 private:
   /**
     Generate a code to set a single cursor parameter variable.
@@ -1002,9 +1006,9 @@ class sp_instr_set : public sp_instr
 public:
 
   sp_instr_set(uint ip, sp_pcontext *ctx,
-	       uint offset, Item *val, enum enum_field_types type_arg,
+	       uint offset, Item *val,
                LEX *lex, bool lex_resp)
-    : sp_instr(ip, ctx), m_offset(offset), m_value(val), m_type(type_arg),
+    : sp_instr(ip, ctx), m_offset(offset), m_value(val),
       m_lex_keeper(lex, lex_resp)
   {}
 
@@ -1021,7 +1025,6 @@ protected:
 
   uint m_offset;		///< Frame offset
   Item *m_value;
-  enum enum_field_types m_type;	///< The declared type
   sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_set : public sp_instr
@@ -1042,9 +1045,9 @@ public:
 
   sp_instr_set_row_field(uint ip, sp_pcontext *ctx,
                          uint offset, uint field_offset,
-                         Item *val, enum enum_field_types type_arg,
+                         Item *val,
                          LEX *lex, bool lex_resp)
-    : sp_instr_set(ip, ctx, offset, val, type_arg, lex, lex_resp),
+    : sp_instr_set(ip, ctx, offset, val, lex, lex_resp),
       m_field_offset(field_offset)
   {}
 
@@ -1057,6 +1060,32 @@ public:
 }; // class sp_instr_set_field : public sp_instr_set
 
 
+class sp_instr_set_row_field_by_name : public sp_instr_set
+{
+  // Prevent use of this
+  sp_instr_set_row_field_by_name(const sp_instr_set_row_field &);
+  void operator=(sp_instr_set_row_field_by_name &);
+  const LEX_STRING m_field_name;
+
+public:
+
+  sp_instr_set_row_field_by_name(uint ip, sp_pcontext *ctx,
+                                 uint offset, const LEX_STRING &field_name,
+                                 Item *val,
+                                 LEX *lex, bool lex_resp)
+    : sp_instr_set(ip, ctx, offset, val, lex, lex_resp),
+      m_field_name(field_name)
+  {}
+
+  virtual ~sp_instr_set_row_field_by_name()
+  {}
+
+  virtual int exec_core(THD *thd, uint *nextp);
+
+  virtual void print(String *str);
+}; // class sp_instr_set_field_by_name : public sp_instr_set
+
+
 /**
   Set NEW/OLD row field value instruction. Used in triggers.
 */
diff --git a/sql/sp_pcontext.cc b/sql/sp_pcontext.cc
index 628c79a880b..332f43db846 100644
--- a/sql/sp_pcontext.cc
+++ b/sql/sp_pcontext.cc
@@ -634,8 +634,7 @@ sp_variable::find_row_field(const LEX_STRING &var_name,
   const Spvar_definition *def;
   if ((def= field_def.find_row_field_by_name(field_name.str, row_field_offset)))
     return def;
-  my_printf_error(ER_UNKNOWN_ERROR,
-                  "Row variable '%s' does not have a field '%s'",
-                  MYF(0), var_name.str, field_name.str);
+  my_error(ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD, MYF(0),
+           var_name.str, field_name.str);
   return NULL;
 }
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index 3ae455e00c9..b631ad71e49 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -132,13 +132,11 @@ bool sp_rcontext::init_var_table(THD *thd,
 */
 static inline bool
 check_column_grant_for_type_ref(THD *thd, TABLE_LIST *table_list,
-                                const Qualified_column_ident *col)
+                                const char *str, size_t length)
 {
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   table_list->table->grant.want_privilege= SELECT_ACL;
-  return check_column_grant_in_table_ref(thd, table_list,
-                                         col->m_column.str,
-                                         col->m_column.length);
+  return check_column_grant_in_table_ref(thd, table_list, str, length);
 #else
   return false;
 #endif
@@ -175,7 +173,9 @@ bool sp_rcontext::resolve_type_ref(THD *thd, Column_definition *def,
   {
     if ((src= lex.query_tables->table->find_field_by_name(ref->m_column.str)))
     {
-      if (!(rc= check_column_grant_for_type_ref(thd, table_list, ref)))
+      if (!(rc= check_column_grant_for_type_ref(thd, table_list,
+                                                ref->m_column.str,
+                                                ref->m_column.length)))
       {
         *def= Column_definition(thd, src, NULL/*No defaults,no constraints*/);
         def->flags&= (uint) ~NOT_NULL_FLAG;
@@ -195,6 +195,54 @@ bool sp_rcontext::resolve_type_ref(THD *thd, Column_definition *def,
 }
 
 
+bool sp_rcontext::resolve_table_rowtype_ref(THD *thd,
+                                            Row_definition_list &defs,
+                                            Table_ident *ref)
+{
+  Open_tables_backup open_tables_state_backup;
+  thd->reset_n_backup_open_tables_state(&open_tables_state_backup);
+
+  TABLE_LIST *table_list;
+  LEX *save_lex= thd->lex;
+  bool rc= true;
+
+  sp_lex_local lex(thd, thd->lex);
+  thd->lex= &lex;
+
+  lex.context_analysis_only= CONTEXT_ANALYSIS_ONLY_VIEW;
+  // Make %ROWTYPE variables see temporary tables that shadow permanent tables
+  thd->temporary_tables= open_tables_state_backup.temporary_tables;
+
+  if ((table_list= lex.select_lex.add_table_to_list(thd, ref, NULL, 0,
+                                                    TL_READ_NO_INSERT,
+                                                    MDL_SHARED_READ)) &&
+      !check_table_access(thd, SELECT_ACL, table_list, TRUE, UINT_MAX, FALSE) &&
+      !open_tables_only_view_structure(thd, table_list,
+                                       thd->mdl_context.has_locks()))
+  {
+    for (Field **src= lex.query_tables->table->field; *src; src++)
+    {
+      if ((rc= check_column_grant_for_type_ref(thd, table_list,
+                                               src[0]->field_name,
+                                               strlen(src[0]->field_name))))
+        break;
+      Spvar_definition *def= new (thd->mem_root) Spvar_definition(thd, *src);
+      def->flags&= (uint) ~NOT_NULL_FLAG;
+      if ((rc= def->sp_prepare_create_field(thd, thd->mem_root)))
+        break;
+      defs.push_back(def, thd->mem_root);
+    }
+  }
+
+  lex.unit.cleanup();
+  thd->temporary_tables= NULL; // Avoid closing temporary tables
+  close_thread_tables(thd);
+  thd->lex= save_lex;
+  thd->restore_backup_open_tables_state(&open_tables_state_backup);
+  return rc;
+}
+
+
 bool sp_rcontext::resolve_type_refs(THD *thd, List<Spvar_definition> &defs)
 {
   List_iterator<Spvar_definition> it(defs);
@@ -229,7 +277,16 @@ bool sp_rcontext::init_var_items(THD *thd,
   for (uint idx= 0; idx < num_vars; ++idx, def= it++)
   {
     Field *field= m_var_table->field[idx];
-    if (def->is_row())
+    if (def->is_table_rowtype_ref())
+    {
+      Row_definition_list defs;
+      Item_field_row *item= new (thd->mem_root) Item_field_row(thd, field);
+      if (!(m_var_items[idx]= item) ||
+          resolve_table_rowtype_ref(thd, defs, def->table_rowtype_ref()) ||
+          item->row_create_items(thd, &defs))
+        return true;
+    }
+    else if (def->is_row())
     {
       Item_field_row *item= new (thd->mem_root) Item_field_row(thd, field);
       if (!(m_var_items[idx]= item) ||
@@ -706,7 +763,8 @@ int sp_cursor::fetch(THD *thd, List<sp_variable> *vars)
   }
   if (vars->elements != result.get_field_count() &&
       (vars->elements != 1 ||
-       !vars->head()->field_def.is_row(result.get_field_count())))
+       result.get_field_count() !=
+       thd->spcont->get_item(vars->head()->offset)->cols()))
   {
     my_message(ER_SP_WRONG_NO_OF_FETCH_ARGS,
                ER_THD(thd, ER_SP_WRONG_NO_OF_FETCH_ARGS), MYF(0));
@@ -787,8 +845,11 @@ bool sp_cursor::Select_fetch_into_spvars::
 
 int sp_cursor::Select_fetch_into_spvars::send_data(List<Item> &items)
 {
-  return (spvar_list->elements == 1 &&
-          (spvar_list->head())->field_def.is_row(items.elements)) ?
+  Item *item;
+  return spvar_list->elements == 1 &&
+         (item= thd->spcont->get_item(spvar_list->head()->offset)) &&
+         item->type_handler() == &type_handler_row &&
+         item->cols() == items.elements ?
     thd->spcont->set_variable_row(thd, spvar_list->head()->offset, items) :
     send_data_to_variable_list(*spvar_list, items);
 }
diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h
index 79d571faca7..1c47b1caff0 100644
--- a/sql/sp_rcontext.h
+++ b/sql/sp_rcontext.h
@@ -342,6 +342,8 @@ private:
   bool resolve_type_refs(THD *, List<Spvar_definition> &defs);
   bool resolve_type_ref(THD *thd, Column_definition *def,
                                   Qualified_column_ident *ref);
+  bool resolve_table_rowtype_ref(THD *thd, Row_definition_list &defs,
+                                           Table_ident *ref);
 
   /// Create and initialize an Item-adapter (Item_field) for each SP-var field.
   ///
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 1cc2e22bb17..73d2a9f0cf6 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -5376,6 +5376,10 @@ class Qualified_column_ident: public Table_ident
 public:
   LEX_STRING m_column;
 public:
+  Qualified_column_ident(const LEX_STRING column)
+   :Table_ident(null_lex_str),
+    m_column(column)
+  { }
   Qualified_column_ident(const LEX_STRING table, const LEX_STRING column)
    :Table_ident(table),
     m_column(column)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index d6e48e511d9..8f258f50902 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5280,9 +5280,9 @@ bool LEX::sp_variable_declarations_finalize(THD *thd, int nvars,
 
     /* The last instruction is responsible for freeing LEX. */
     sp_instr_set *is= new (this->thd->mem_root)
-                       sp_instr_set(sphead->instructions(),
-                                    spcont, var_idx, dflt_value_item,
-                                    spvar->field_def.sql_type, this, last);
+                      sp_instr_set(sphead->instructions(),
+                                   spcont, var_idx, dflt_value_item,
+                                   this, last);
     if (is == NULL || sphead->add_instr(is))
       return true;
   }
@@ -5293,6 +5293,56 @@ bool LEX::sp_variable_declarations_finalize(THD *thd, int nvars,
 
 
 bool
+LEX::sp_variable_declarations_rowtype_finalize(THD *thd, int nvars,
+                                               Qualified_column_ident *ref,
+                                               Item *def)
+{
+  uint num_vars= spcont->context_var_count();
+
+  if (!def && !(def= new (thd->mem_root) Item_null(thd)))
+    return true;
+
+  for (uint i= num_vars - nvars; i < num_vars; i++)
+  {
+    bool last= i == num_vars - 1;
+    uint var_idx= spcont->var_context2runtime(i);
+    sp_variable *spvar= spcont->find_context_variable(i);
+    /*
+      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.field_name= spvar->name.str;
+    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, var_idx, def,
+                                   this, last);
+    if (is == NULL || sphead->add_instr(is))
+      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_with_ref_finalize(THD *thd, int nvars,
                                                 Qualified_column_ident *ref,
                                                 Item *def)
@@ -6114,12 +6164,12 @@ Item *LEX::create_item_ident_nospvar(THD *thd,
 }
 
 
-Item_splocal_row_field *LEX::create_item_spvar_row_field(THD *thd,
-                                                         const LEX_STRING &a,
-                                                         const LEX_STRING &b,
-                                                         sp_variable *spv,
-                                                         uint pos_in_q,
-                                                         uint length_in_q)
+Item_splocal *LEX::create_item_spvar_row_field(THD *thd,
+                                               const LEX_STRING &a,
+                                               const LEX_STRING &b,
+                                               sp_variable *spv,
+                                               uint pos_in_q,
+                                               uint length_in_q)
 {
   if (!parsing_options.allows_variable)
   {
@@ -6127,17 +6177,28 @@ Item_splocal_row_field *LEX::create_item_spvar_row_field(THD *thd,
     return NULL;
   }
 
-  uint row_field_offset;
-  const Spvar_definition *def;
-  if (!(def= spv->find_row_field(a, b, &row_field_offset)))
-    return NULL;
+  Item_splocal *item;
+  if (spv->field_def.is_table_rowtype_ref())
+  {
+    if (!(item= new (thd->mem_root)
+                Item_splocal_row_field_by_name(thd, a, b, spv->offset,
+                                               MYSQL_TYPE_NULL,
+                                               pos_in_q, length_in_q)))
+      return NULL;
+  }
+  else
+  {
+    uint row_field_offset;
+    const Spvar_definition *def;
+    if (!(def= spv->find_row_field(a, b, &row_field_offset)))
+      return NULL;
 
-  Item_splocal_row_field *item;
-  if (!(item= new (thd->mem_root)
-              Item_splocal_row_field(thd, a, b,
-                                     spv->offset, row_field_offset,
-                                     def->sql_type, pos_in_q, length_in_q)))
-    return NULL;
+    if (!(item= new (thd->mem_root)
+                Item_splocal_row_field(thd, a, b,
+                                       spv->offset, row_field_offset,
+                                       def->sql_type, pos_in_q, length_in_q)))
+      return NULL;
+  }
 #ifndef DBUG_OFF
   item->m_sp= sphead;
 #endif
@@ -6298,7 +6359,7 @@ Item *LEX::create_item_ident_sp(THD *thd, LEX_STRING name,
                                                               spv->offset,
                                                               start_in_q,
                                                               length_in_q) :
-      spv->field_def.is_row() ?
+      spv->field_def.is_row() || spv->field_def.is_table_rowtype_ref() ?
       new (thd->mem_root) Item_splocal_row(thd, name, spv->offset,
                                            start_in_q, length_in_q) :
       new (thd->mem_root) Item_splocal(thd, name,
@@ -6345,6 +6406,10 @@ bool LEX::set_variable(const LEX_STRING &name1,
   sp_variable *spv;
   if (spcont && (spv= spcont->find_variable(name1, false)))
   {
+    if (spv->field_def.is_table_rowtype_ref())
+      return sphead->set_local_variable_row_field_by_name(thd, spcont,
+                                                          spv, name2,
+                                                          item, this);
     // A field of a ROW variable
     uint row_field_offset;
     return !spv->find_row_field(name1, name2, &row_field_offset) ||
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5fec0bdbb28..8edebaf6028 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3149,6 +3149,9 @@ public:
   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_handler_declaration_init(THD *thd, int type);
   bool sp_handler_declaration_finalize(THD *thd, int type);
 
@@ -3202,12 +3205,12 @@ public:
       @pos_in_q         - position in the query (for binary log)
       @length_in_q      - length in the query (for binary log)
   */
-  Item_splocal_row_field *create_item_spvar_row_field(THD *thd,
-                                                      const LEX_STRING &var,
-                                                      const LEX_STRING &field,
-                                                      sp_variable *spvar,
-                                                      uint pos_in_q,
-                                                      uint length_in_q);
+  Item_splocal *create_item_spvar_row_field(THD *thd,
+                                            const LEX_STRING &var,
+                                            const LEX_STRING &field,
+                                            sp_variable *spvar,
+                                            uint pos_in_q,
+                                            uint length_in_q);
   /*
     Create an item from its qualified name.
     Depending on context, it can be either a ROW variable field,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 410352dd328..93fdff57d38 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1384,6 +1384,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %token  ROWCOUNT_SYM                  /* Oracle-N   */
 %token  ROW_SYM                       /* SQL-2003-R */
 %token  ROWS_SYM                      /* SQL-2003-R */
+%token  ROWTYPE_SYM                   /* Oracle-PLSQL-R */
 %token  ROW_COUNT_SYM                 /* SQL-2003-N */
 %token  ROW_FORMAT_SYM
 %token  ROW_NUMBER_SYM
@@ -14419,6 +14420,7 @@ keyword_sp:
         | ROLLUP_SYM               {}
         | ROUTINE_SYM              {}
         | ROWCOUNT_SYM             {}
+        | ROWTYPE_SYM              {}
         | ROW_COUNT_SYM            {}
         | ROW_FORMAT_SYM           {}
         | ROW_SYM                  {}
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 13472dcefb0..c72fa8ca0e5 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -802,6 +802,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %token  ROWCOUNT_SYM                  /* Oracle-N   */
 %token  ROW_SYM                       /* SQL-2003-R */
 %token  ROWS_SYM                      /* SQL-2003-R */
+%token  ROWTYPE_SYM                   /* Oracle-PLSQL-R */
 %token  ROW_COUNT_SYM                 /* SQL-2003-N */
 %token  ROW_FORMAT_SYM
 %token  ROW_NUMBER_SYM
@@ -1027,6 +1028,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 
 %type <qualified_column_ident>
         qualified_column_ident
+        optionally_qualified_column_ident
 
 %type <simple_string>
         remember_name remember_end opt_db remember_tok_start
@@ -1077,6 +1079,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
         case_stmt_body opt_bin_mod
         opt_if_exists_table_element opt_if_not_exists_table_element
 	opt_recursive
+        type_or_rowtype
 
 %type <object_ddl_options>
         create_or_replace 
@@ -2456,6 +2459,25 @@ qualified_column_ident:
           }
         ;
 
+optionally_qualified_column_ident:
+          sp_decl_ident
+          {
+            if (!($$= new (thd->mem_root) Qualified_column_ident($1)))
+              MYSQL_YYABORT;
+          }
+        | sp_decl_ident '.' ident
+          {
+            if (!($$= new (thd->mem_root) Qualified_column_ident($1, $3)))
+              MYSQL_YYABORT;
+          }
+        | sp_decl_ident '.' ident '.' ident
+          {
+            if (!($$= new (thd->mem_root) Qualified_column_ident(thd,
+                                                                 $1, $3, $5)))
+              MYSQL_YYABORT;
+          }
+        ;
+
 row_field_name:
           ident_directly_assignable
           {
@@ -2493,6 +2515,10 @@ field_type_row:
           ROW_SYM '(' row_field_definition_list ')' { $$= $3; }
         ;
 
+type_or_rowtype:
+          TYPE_SYM     { $$= 0; }
+        | ROWTYPE_SYM  { $$= 1; }
+        ;
 
 sp_decl_body:
           sp_decl_idents
@@ -2511,10 +2537,12 @@ sp_decl_body:
           {
             Lex->sp_variable_declarations_init(thd, $1);
           }
-          qualified_column_ident '%' TYPE_SYM
+          optionally_qualified_column_ident '%' type_or_rowtype
           sp_opt_default
           {
-            if (Lex->sp_variable_declarations_with_ref_finalize(thd, $1, $3, $6))
+            if ($5 ?
+                Lex->sp_variable_declarations_rowtype_finalize(thd, $1, $3, $6) :
+                Lex->sp_variable_declarations_with_ref_finalize(thd, $1, $3, $6))
               MYSQL_YYABORT;
             $$.init_using_vars($1);
           }