summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/suite/compat/oracle/r/binlog_stm_sp.result151
-rw-r--r--mysql-test/suite/compat/oracle/r/sp-code.result25
-rw-r--r--mysql-test/suite/compat/oracle/r/sp-row.result621
-rw-r--r--mysql-test/suite/compat/oracle/r/sp-security.result49
-rw-r--r--mysql-test/suite/compat/oracle/t/binlog_stm_sp.test40
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-code.test22
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-row.test681
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-security.test60
-rw-r--r--mysql-test/t/sp-row.test2
-rw-r--r--sql/field.h18
-rw-r--r--sql/item.cc91
-rw-r--r--sql/item.h31
-rw-r--r--sql/lex.h1
-rw-r--r--sql/share/errmsg-utf8.txt2
-rw-r--r--sql/sp_head.cc87
-rw-r--r--sql/sp_head.h39
-rw-r--r--sql/sp_pcontext.cc5
-rw-r--r--sql/sp_rcontext.cc79
-rw-r--r--sql/sp_rcontext.h2
-rw-r--r--sql/sql_class.h4
-rw-r--r--sql/sql_lex.cc105
-rw-r--r--sql/sql_lex.h15
-rw-r--r--sql/sql_yacc.yy2
-rw-r--r--sql/sql_yacc_ora.yy32
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);
}
View Lorry Controller Status