diff options
| -rw-r--r-- | mysql-test/r/sp-anchor-row-type-cursor.result | 982 | ||||
| -rw-r--r-- | mysql-test/r/sp-anchor-row-type-table.result | 681 | ||||
| -rw-r--r-- | mysql-test/r/sp-anchor-type.result | 967 | ||||
| -rw-r--r-- | mysql-test/r/sp-security-anchor-type.result | 263 | ||||
| -rw-r--r-- | mysql-test/r/trigger.result | 20 | ||||
| -rw-r--r-- | mysql-test/t/sp-anchor-row-type-cursor.test | 1073 | ||||
| -rw-r--r-- | mysql-test/t/sp-anchor-row-type-table.test | 761 | ||||
| -rw-r--r-- | mysql-test/t/sp-anchor-type.test | 677 | ||||
| -rw-r--r-- | mysql-test/t/sp-security-anchor-type.test | 328 | ||||
| -rw-r--r-- | mysql-test/t/trigger.test | 23 | ||||
| -rw-r--r-- | sql/lex.h | 1 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 105 | ||||
| -rw-r--r-- | sql/sql_yacc_ora.yy | 2 |
13 files changed, 5866 insertions, 17 deletions
diff --git a/mysql-test/r/sp-anchor-row-type-cursor.result b/mysql-test/r/sp-anchor-row-type-cursor.result new file mode 100644 index 00000000000..144bd2e89c1 --- /dev/null +++ b/mysql-test/r/sp-anchor-row-type-cursor.result @@ -0,0 +1,982 @@ +# +# Start of 10.3 tests +# +# +# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# +# +# A complete working example +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE TABLE t2 LIKE t1; +INSERT INTO t1 VALUES (10,'b10'); +INSERT INTO t1 VALUES (20,'b20'); +INSERT INTO t1 VALUES (30,'b30'); +CREATE PROCEDURE p1() +BEGIN +DECLARE c CURSOR FOR SELECT a,b FROM t1; +BEGIN +DECLARE done INT DEFAULT 0; +DECLARE rec ROW TYPE OF c; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; +OPEN c; +read_loop: LOOP +FETCH c INTO rec; +IF done THEN +LEAVE read_loop; +END IF; +SELECT rec.a ,rec.b FROM dual; +INSERT INTO t2 VALUES (rec.a, rec.b); +END LOOP; +CLOSE c; +END; +END; +$$ +CALL p1(); +rec.a rec.b +10 b10 +rec.a rec.b +20 b20 +rec.a rec.b +30 b30 +SELECT * FROM t2; +a b +10 b10 +20 b20 +30 b30 +DROP PROCEDURE p1; +DROP TABLE t2; +DROP TABLE t1; +# +# cursor ROW TYPE referring to a table in a non-existing database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM tes2.t1; +BEGIN +DECLARE rec ROW TYPE OF cur; +END; +END; +$$ +CALL p1(); +ERROR 42S02: Table 'tes2.t1' doesn't exist +CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10)); +CALL p1(); +ERROR 42S02: Table 'tes2.t1' doesn't exist +DROP TABLE t1; +DROP PROCEDURE p1; +# +# cursor ROW TYPE referring to a table in the current database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec ROW TYPE OF cur; +CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CALL p1(); +ERROR 42S02: Table 'test.t1' doesn't exist +DROP PROCEDURE p1; +# +# cursor ROW TYPE referring to a table in an explicitly specified database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM test.t1; +BEGIN +DECLARE rec ROW TYPE OF cur; +CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Cursor ROW TYPE referring to a view in the current database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM v1; +BEGIN +DECLARE rec ROW TYPE OF cur; +CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; +# +# cursor ROW TYPE referring to a view in an explicitly specified database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM test.v1; +BEGIN +DECLARE rec ROW TYPE OF cur; +CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Checking that all cursor ROW TYPE fields are NULL by default +# +CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +SELECT rec1.a, rec1.b, rec1.c, rec1.d; +END; +END; +$$ +CALL p1(); +rec1.a rec1.b rec1.c rec1.d +NULL NULL NULL NULL +DROP TABLE t1; +DROP PROCEDURE p1; +# +# A cursor ROW TYPE variable with a ROW expression as a default +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1 DEFAULT ROW(10,'bbb'); +SELECT rec1.a, rec1.b; +END; +END; +$$ +CALL p1(); +rec1.a rec1.b +10 bbb +DROP TABLE t1; +DROP PROCEDURE p1; +# +# A cursor ROW TYPE variable with an incompatible ROW expression as a default +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1 DEFAULT ROW(10,'bbb','ccc'); +SELECT rec1.a, rec1.b; +END; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 2 column(s) +DROP TABLE t1; +DROP PROCEDURE p1; +# +# A cursor ROW TYPE variable with a ROW variable as a default +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW(a INT, b VARCHAR(10)) DEFAULT ROW(10,'bbb'); +DECLARE rec2 ROW TYPE OF cur DEFAULT rec1; +SELECT rec2.a, rec2.b; +END; +END; +$$ +CALL p1(); +rec2.a rec2.b +10 bbb +DROP TABLE t1; +DROP PROCEDURE p1; +# +# A ROW variable using a cursor ROW TYPE variable as a default +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1 DEFAULT ROW(10,'bbb'); +DECLARE rec2 ROW(a INT, b VARCHAR(10)) DEFAULT rec1; +SELECT rec2.a, rec2.b; +END; +END; +$$ +CALL p1(); +rec2.a rec2.b +10 bbb +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Assigning cursor ROW TYPE 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() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +DECLARE cur2 CURSOR FOR SELECT * FROM t2; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +DECLARE rec2 ROW TYPE OF cur2; +SET rec2=rec1; +END; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 2 column(s) +DROP PROCEDURE p1; +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +DECLARE cur2 CURSOR FOR SELECT * FROM t2; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +DECLARE rec2 ROW TYPE OF cur2; +SET rec1=rec2; +END; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 3 column(s) +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Assigning compatible cursor ROW TYPE variables (equal number of fields) +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE TABLE t2 (x INT, y VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +DECLARE cur2 CURSOR FOR SELECT * FROM t2; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +DECLARE rec2 ROW TYPE OF cur2; +SET rec1.a= 10; +SET rec1.b= 'bbb'; +SET rec2=rec1; +SELECT rec2.x, rec2.y; +END; +END; +$$ +CALL p1(); +rec2.x rec2.y +10 bbb +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Assigning between incompatible cursor ROW TYPE and explicit ROW variables +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +DECLARE rec2 ROW(x INT,y INT,z INT); +SET rec2.x= 10; +SET rec2.y= 20; +SET rec2.z= 30; +SET rec1= rec2; +END; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 2 column(s) +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Assigning between compatible cursor ROW TYPE and explicit ROW variables +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +DECLARE rec2 ROW(x INT,y INT); +SET rec2.x= 10; +SET rec2.y= 20; +SET rec1= rec2; +SELECT rec1.a, rec1.b; +SET rec1.a= 11; +SET rec1.b= 21; +SET rec2= rec1; +SELECT rec2.x, rec2.y; +END; +END; +$$ +CALL p1(); +rec1.a rec1.b +10 20 +rec2.x rec2.y +11 21 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Assigning cursor ROW TYPE from a ROW expression +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +SET rec1= ROW(10,20); +SELECT rec1.a, rec1.b; +END; +END; +$$ +CALL p1(); +rec1.a rec1.b +10 20 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Fetching a cursor into a cursor ROW TYPE 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() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +DECLARE cur2 CURSOR FOR SELECT * FROM t2; +BEGIN +DECLARE rec2 ROW TYPE OF cur2; +OPEN cur1; +FETCH cur1 INTO rec2; +CLOSE cur1; +END; +END; +$$ +CALL p1(); +ERROR HY000: Incorrect number of FETCH variables +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Fetching a cursor into a cursor ROW TYPE 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() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE done INT DEFAULT 0; +DECLARE rec ROW TYPE OF cur; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; +OPEN cur; +read_loop: LOOP +FETCH cur INTO rec; +IF done THEN +LEAVE read_loop; +END IF; +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; +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 cursor ROW TYPE variable, ROW TYPE OF cur declared inside the LOOP +# +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() +BEGIN +DECLARE done INT DEFAULT 0; +DECLARE cur CURSOR FOR SELECT * FROM t1; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; +OPEN cur; +read_loop: LOOP +BEGIN +DECLARE rec ROW TYPE OF cur; +FETCH cur INTO rec; +IF done THEN +LEAVE read_loop; +END IF; +SELECT rec.a, rec.b, rec.c, rec.d; +INSERT INTO t2 VALUES (rec.a, rec.b, rec.c, rec.d); +END; +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 cursor ROW TYPE 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() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +DECLARE cur2 CURSOR FOR SELECT * FROM t2; +BEGIN +DECLARE rec2 ROW TYPE OF cur2; +OPEN cur1; +FETCH cur1 INTO rec2; +SELECT rec2.x, rec2.y; +CLOSE cur1; +END; +END; +$$ +CALL p1(); +rec2.x rec2.y +10 bbb +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Fetching a cursor into a cursor ROW TYPE variable, with truncation +# +SET sql_mode=''; +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE TABLE t2 (a INT, b INT); +INSERT INTO t1 VALUES (10,'11x'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +DECLARE cur2 CURSOR FOR SELECT * FROM t2; +BEGIN +DECLARE rec2 ROW TYPE OF cur2; +OPEN cur1; +FETCH cur1 INTO rec2; +SELECT rec2.a, rec2.b; +CLOSE cur1; +END; +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; +SET sql_mode=DEFAULT; +# +# cursor ROW TYPE variables are not allowed in LIMIT +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1 DEFAULT (1,2); +SELECT * FROM t1 LIMIT rec1.a; +END; +END; +$$ +ERROR HY000: A variable of a non-integer based type in LIMIT clause +DROP TABLE t1; +# +# cursor ROW TYPE variable fields as OUT parameters +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1(OUT a INT,OUT b VARCHAR(10)) +BEGIN +SET a=10; +SET b='bb'; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +CALL p1(rec1.a, rec1.b); +SELECT rec1.a, rec1.b; +END; +END; +$$ +CALL p2(); +rec1.a rec1.b +10 bb +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Passing the entire cursor ROW TYPE variable +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10))) +BEGIN +SELECT a.a, a.b; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur DEFAULT ROW(10,'bb'); +CALL p1(rec1); +END; +END; +$$ +CALL p2(); +a.a a.b +10 bb +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Passing the entire cursor ROW TYPE variable as an OUT parameter +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1(OUT a ROW(a INT, b VARCHAR(10))) +BEGIN +SET a= ROW(10,'bb'); +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur; +CALL p1(rec1); +SELECT rec1.a, rec1.b; +END; +END; +$$ +CALL p2(); +rec1.a rec1.b +10 bb +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Assigning a cursor ROW TYPE field to an OUT parameter +# +CREATE PROCEDURE p1 (INOUT res INTEGER) +BEGIN +DECLARE a INT DEFAULT 10; +DECLARE cur1 CURSOR FOR SELECT a FROM DUAL; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +OPEN cur1; +FETCH cur1 INTO rec1; +CLOSE cur1; +SET res=rec1.a; +END; +END; +$$ +CALL p1(@res); +SELECT @res; +@res +10 +SET @res=NULL; +DROP PROCEDURE p1; +# +# Testing Item_splocal_row_field_by_name::print +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec ROW TYPE OF cur1 DEFAULT ROW(10,'bb'); +EXPLAIN EXTENDED SELECT rec.a, rec.b; +END; +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; +# +# Run time error in the cursor statement +# +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT +10 AS a, +CONCAT(_latin1'a' COLLATE latin1_bin, +_latin1'a' COLLATE latin1_swedish_ci) AS b; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +OPEN cur1; +FETCH cur1 INTO rec1; +CLOSE cur1; +SELECT a,b; +END; +END; +$$ +CALL p1(); +ERROR HY000: Illegal mix of collations (latin1_bin,EXPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation 'concat' +DROP PROCEDURE p1; +# +# Non-existing field +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec ROW TYPE OF cur1; +SELECT rec.c; +END; +END; +$$ +CALL p1(); +ERROR HY000: Row variable 'rec' does not have a field 'c' +ALTER TABLE t1 ADD c INT; +ALTER PROCEDURE p1 COMMENT 'test'; +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() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec ROW TYPE OF cur DEFAULT ROW(10,'bb'); +SELECT rec.A, rec.B; +END; +END; +$$ +CALL p1(); +rec.A rec.B +10 bb +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Testing that cursor ROW TYPE 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() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec ROW TYPE OF cur DEFAULT ROW(10,'bb'); +SELECT rec.A, rec.B; +END; +END; +$$ +CALL p1(); +ERROR HY000: Row variable 'rec' does not have a field 'A' +DROP TEMPORARY TABLE t1; +ALTER PROCEDURE p1 COMMENT 'test'; +CALL p1(); +rec.A rec.B +10 bb +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Testing that the structure of cursor ROW TYPE variables is determined at the DECLARE CURSOR instantiation time +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a INT, b VARCHAR(32), c INT); +BEGIN +DECLARE rec ROW TYPE OF cur; -- This has a column "c" + SET rec.c=10; +END; +END; +$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec ROW TYPE OF cur; -- This does not have a column "c" + DROP TABLE t1; +CREATE TABLE t1 (a INT, b VARCHAR(32), c INT); +SET rec.c=10; +END; +END; +$$ +CALL p1(); +ERROR HY000: Row variable 'rec' does not have a field 'c' +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Duplicate field nams in a cursor referenced by cursor ROW TYPE +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT * FROM t1, t2; +BEGIN +DECLARE rec ROW TYPE OF cur; +SELECT rec.a; +SET rec.a=10; +END; +END; +$$ +CALL p1(); +ERROR 42S21: Duplicate column name 'a' +DROP PROCEDURE p1; +DROP TABLE t2; +DROP TABLE t1; +# +# Tricky field names a cursor referenced by cursor ROW TYPE +# +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('a'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur CURSOR FOR SELECT a, CONCAT(a,'a'), CONCAT(a,'ö') FROM t1; +BEGIN +DECLARE rec ROW TYPE OF cur; +OPEN cur; +FETCH cur INTO rec; +CLOSE cur; +SELECT rec.a, rec.`CONCAT(a,'a')`, rec.`CONCAT(a,'ö')`; +END; +END; +$$ +CALL p1(); +rec.a rec.`CONCAT(a,'a')` rec.`CONCAT(a,'ö')` +a aa aö +DROP PROCEDURE p1; +DROP TABLE t1; +SET NAMES latin1; +# +# Using definitions recursively (cursor ROW TYPE variables in another cursor SELECT) +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (10,'b1'),(20,'b2'),(30,'b3'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT a,b FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1 DEFAULT ROW(0,'b0'); +DECLARE cur2 CURSOR FOR SELECT rec1.a AS a, rec1.b AS b FROM t1; +BEGIN +DECLARE done INT DEFAULT 0; +DECLARE rec2 ROW TYPE OF cur2; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; +OPEN cur2; +read_loop: LOOP +FETCH cur2 INTO rec2; +IF done THEN +LEAVE read_loop; +END IF; +SELECT rec2.a, rec2.b; +END LOOP; +CLOSE cur2; +END; +END; +END; +$$ +CALL p1(); +rec2.a rec2.b +0 b0 +rec2.a rec2.b +0 b0 +rec2.a rec2.b +0 b0 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Testing queries with auto-generated Items. +# An instance of Item_func_conv_charset is created during the below SELECT query. +# We check here that during an implicit cursor OPEN +# done in sp_instr_cursor_copy_struct::exec_core() +# all temporary Items are created on a proper memory root and are safely destroyed. +# +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, b VARCHAR(10) CHARACTER SET utf8); +INSERT INTO t1 VALUES (0xFF, 'a'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT CONCAT(a,b) AS c FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +OPEN cur1; +FETCH cur1 INTO rec1; +CLOSE cur1; +SELECT HEX(rec1.c); +END; +END; +$$ +CALL p1(); +HEX(rec1.c) +C3BF61 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# SELECT INTO + cursor ROW TYPE variable with a wrong column count +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT 10, 'b0', 'c0'; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +SELECT * FROM t1 INTO rec1; +SELECT rec1.a, rec1.b; +END; +END; +$$ +CALL p1(); +ERROR 21000: The used SELECT statements have a different number of columns +DROP TABLE t1; +DROP PROCEDURE p1; +# +# SELECT INTO + multiple cursor ROW TYPE variables +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +SELECT * FROM t1 INTO rec1, rec1; +SELECT rec1.a, rec1.b; +END; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 2 column(s) +DROP TABLE t1; +DROP PROCEDURE p1; +# SELECT INTO + cursor ROW TYPE working example +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +BEGIN +DECLARE rec1 ROW TYPE OF cur1; +SELECT * FROM t1 INTO rec1; +SELECT rec1.a, rec1.b; +END; +END; +$$ +CALL p1(); +rec1.a rec1.b +10 b10 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# diff --git a/mysql-test/r/sp-anchor-row-type-table.result b/mysql-test/r/sp-anchor-row-type-table.result new file mode 100644 index 00000000000..21c931abf44 --- /dev/null +++ b/mysql-test/r/sp-anchor-row-type-table.result @@ -0,0 +1,681 @@ +# +# Start of 10.3 tests +# +# +# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# +# +# Referring to a table in a non-existing database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE rec ROW TYPE OF test2.t1; +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() +BEGIN +DECLARE rec ROW TYPE OF t1; +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Referring to a table in an explicitly specified database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE rec ROW TYPE OF test.t1; +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Referring to a view in the current database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE rec ROW TYPE OF v1; +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Referring to a view in an explicitly specified database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE rec ROW TYPE OF test.v1; +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP VIEW v1; +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Checking that all table ROW TYPE fields are NULL by default +# +CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +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 ROW TYPE variable with a ROW expression as a default +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb'); +SELECT rec1.a, rec1.b; +END; +$$ +CALL p1(); +rec1.a rec1.b +10 bbb +DROP TABLE t1; +DROP PROCEDURE p1; +# +# A table ROW TYPE variable with an incompatible ROW expression as a default +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb','ccc'); +SELECT rec1.a, rec1.b; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 2 column(s) +DROP TABLE t1; +DROP PROCEDURE p1; +# +# A table ROW TYPE variable with a ROW variable as a default +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW(a INT, b VARCHAR(10)) DEFAULT ROW(10,'bbb'); +DECLARE rec2 ROW TYPE OF t1 DEFAULT rec1; +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 ROW TYPE variable as a default +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb'); +DECLARE rec2 ROW(a INT, b VARCHAR(10)) DEFAULT rec1; +SELECT rec2.a, rec2.b; +END; +$$ +CALL p1(); +rec2.a rec2.b +10 bbb +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Assigning table ROW TYPE 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() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +DECLARE rec2 ROW TYPE OF t2; +SET rec2=rec1; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 2 column(s) +DROP PROCEDURE p1; +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +DECLARE rec2 ROW TYPE OF t2; +SET 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 ROW TYPE variables (equal number of fields) +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE TABLE t2 (x INT, y VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +DECLARE rec2 ROW TYPE OF t2; +SET rec1.a= 10; +SET rec1.b= 'bbb'; +SET 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 ROW TYPE and explicit ROW variables +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +DECLARE rec2 ROW(x INT,y INT,z INT); +SET rec2.x= 10; +SET rec2.y= 20; +SET rec2.z= 30; +SET rec1= rec2; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 2 column(s) +DROP TABLE t1; +DROP PROCEDURE p1; +# +# Assigning between compatible table ROW TYPE and explicit ROW variables +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +DECLARE rec2 ROW(x INT,y INT); +SET rec2.x= 10; +SET rec2.y= 20; +SET rec1= rec2; +SELECT rec1.a, rec1.b; +SET rec1.a= 11; +SET rec1.b= 21; +SET 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 ROW TYPE from a ROW expression +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +SET 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 ROW TYPE 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() +BEGIN +DECLARE rec2 ROW TYPE OF t2; +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +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 ROW TYPE 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() +BEGIN +DECLARE done INT DEFAULT 0; +DECLARE rec ROW TYPE OF t1; +DECLARE cur CURSOR FOR SELECT * FROM t1; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; +OPEN cur; +read_loop: LOOP +FETCH cur INTO rec; +IF done THEN +LEAVE read_loop; +END IF; +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 ROW TYPE 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() +BEGIN +DECLARE rec2 ROW TYPE OF t2; +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +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 ROW TYPE variable, with truncation +# +SET sql_mode=''; +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE TABLE t2 (a INT, b INT); +INSERT INTO t1 VALUES (10,'11x'); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec2 ROW TYPE OF t2; +DECLARE cur1 CURSOR FOR SELECT * FROM t1; +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; +SET sql_mode=DEFAULT; +# +# table ROW TYPE variables are not allowed in LIMIT +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1 DEFAULT (1,2); +SELECT * FROM t1 LIMIT rec1.a; +END; +$$ +ERROR HY000: A variable of a non-integer based type in LIMIT clause +DROP TABLE t1; +# +# table ROW TYPE variable fields as OUT parameters +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1(OUT a INT,OUT b VARCHAR(10)) +BEGIN +SET a=10; +SET b='bb'; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +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 ROW TYPE variable +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10))) +BEGIN +SELECT a.a, a.b; +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bb'); +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 ROW TYPE variable as an OUT parameter +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1(OUT a ROW(a INT, b VARCHAR(10))) +BEGIN +SET a= ROW(10,'bb'); +END; +$$ +CREATE PROCEDURE p2() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +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 ROW TYPE field to an OUT parameter +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +CREATE PROCEDURE p1 (INOUT res INTEGER) +BEGIN +DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'b0'); +SET 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() +BEGIN +DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); +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() +BEGIN +DECLARE rec ROW TYPE OF t1; +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() +BEGIN +DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); +SELECT rec.A, rec.B; +END; +$$ +CALL p1(); +rec.A rec.B +10 bb +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Testing that table ROW TYPE 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() +BEGIN +DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); +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 ROW TYPE 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() +BEGIN +ALTER TABLE t1 ADD c INT; +BEGIN +DECLARE rec ROW TYPE OF t1; -- this will not have column "c" + SET rec.c=10; +END; +END; +$$ +CALL p1(); +ERROR HY000: Row variable 'rec' does not have a field 'c' +DROP TABLE t1; +DROP PROCEDURE p1; +# +# SELECT INTO + table ROW TYPE variable with a wrong column count +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +SELECT 10,'a','b' FROM t1 INTO rec1; +SELECT rec1.a, rec1.b; +END; +$$ +CALL p1(); +ERROR 21000: The used SELECT statements have a different number of columns +DROP TABLE t1; +DROP PROCEDURE p1; +# +# SELECT INTO + multiple table ROW TYPE variables +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +SELECT 10,'a' FROM t1 INTO rec1, rec1; +SELECT rec1.a, rec1.b; +END; +$$ +CALL p1(); +ERROR 21000: Operand should contain 2 column(s) +DROP TABLE t1; +DROP PROCEDURE p1; +# +# SELECT INTO + table ROW TYPE working example +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +DECLARE rec1 ROW TYPE OF t1; +SELECT * FROM t1 INTO rec1; +SELECT rec1.a, rec1.b; +END; +$$ +CALL p1(); +rec1.a rec1.b +10 b10 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# +# +# MDEV-12347 Valgrind reports invalid read errors in Item_field_row::element_index_by_name +# +CREATE TABLE t1 (a INT, b ENUM('b0','b1','b12','b3')); +CREATE PROCEDURE p1() +BEGIN +BEGIN +DECLARE rec ROW TYPE OF t1; +SET rec.b='b0'; +SELECT rec.b; +END; +END; +$$ +CALL p1(); +rec.b +b0 +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE TABLE t1 (a INT, b SET('b0','b1','b12','b3')); +CREATE PROCEDURE p1() +BEGIN +BEGIN +DECLARE rec ROW TYPE OF t1; +SET rec.b='b0'; +SELECT rec.b; +END; +END; +$$ +CALL p1(); +rec.b +b0 +DROP TABLE t1; +DROP PROCEDURE p1; diff --git a/mysql-test/r/sp-anchor-type.result b/mysql-test/r/sp-anchor-type.result new file mode 100644 index 00000000000..44f67531b2f --- /dev/null +++ b/mysql-test/r/sp-anchor-type.result @@ -0,0 +1,967 @@ +# +# Start of 10.3 tests +# +# +# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# +# +# Missing table +# +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF t1.a; +END; +$$ +CALL p1(); +ERROR 42S02: Table 'test.t1' doesn't exist +DROP PROCEDURE p1; +# +# Missing column +# +CREATE TABLE t1 (b INT); +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF t1.a; +END; +$$ +CALL p1(); +ERROR 42S22: Unknown column 'a' in 't1' +DROP PROCEDURE p1; +DROP TABLE t1; +# +# One TYPE OF variable +# +CREATE TABLE t1 (a INT); +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF t1.a; +SET a= 123; +SELECT a; +END; +$$ +CALL p1(); +a +123 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Two TYPE OF variables, with a truncation warning on assignment +# +SET sql_mode=''; +CREATE TABLE t1 (a TINYINT, b INT); +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF t1.a; +DECLARE b TYPE OF t1.b; +SET a= 200; +SET b= 200; +SELECT a, b; +END; +$$ +CALL p1(); +a b +127 200 +Warnings: +Warning 1264 Out of range value for column 'a' at row 1 +DROP PROCEDURE p1; +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# TYPE OF variables for fields with various attributes +# +CREATE TABLE t1 ( +id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, +a TINYINT NOT NULL, +b INT NOT NULL, +ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, +UNIQUE(a) +); +CREATE PROCEDURE p1() +BEGIN +DECLARE id TYPE OF t1.id; +DECLARE a TYPE OF t1.a; +DECLARE b TYPE OF t1.b; +DECLARE ts TYPE OF t1.ts; +SELECT id, a, b, ts; +CREATE TABLE t2 AS SELECT id, a, b, ts; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +$$ +CALL p1(); +id a b ts +NULL NULL NULL NULL +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) DEFAULT NULL, + `a` tinyint(4) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `ts` timestamp NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# TYPE OF + virtual columns +# +CREATE TABLE t1 ( +a INT NOT NULL, +b VARCHAR(32), +c INT AS (a + 10) VIRTUAL, +d VARCHAR(5) AS (left(b,5)) PERSISTENT +); +CREATE PROCEDURE p1() +BEGIN +DECLARE c TYPE OF t1.c; +DECLARE d TYPE OF t1.d; +SELECT c, d; +CREATE TABLE t2 AS SELECT c, d; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +$$ +CALL p1(); +c d +NULL NULL +Table Create Table +t2 CREATE TABLE `t2` ( + `c` int(11) DEFAULT NULL, + `d` varchar(5) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# TYPE OF + the ZEROFILL attribute +# +CREATE TABLE t1 ( +dz DECIMAL(10,3) ZEROFILL +); +CREATE PROCEDURE p1() +BEGIN +DECLARE dzr TYPE OF t1.dz DEFAULT 10; +DECLARE dzt DECIMAL(10,3) ZEROFILL DEFAULT 10; +SELECT dzr, dzt; +CREATE TABLE t2 AS SELECT dzr,dzt; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +$$ +CALL p1(); +dzr dzt +0000010.000 0000010.000 +Table Create Table +t2 CREATE TABLE `t2` ( + `dzr` decimal(10,3) unsigned DEFAULT NULL, + `dzt` decimal(10,3) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Temporary tables shadow real tables for TYPE OF purposes +# +CREATE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('t1'); +CREATE TEMPORARY TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +SELECT * FROM t1; +a +10 +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF t1.a DEFAULT 11; +CREATE TABLE t2 AS SELECT a; +END; +$$ +# +# Should use INT(11) as TYPE OF, as in the temporary table +# +CALL p1(); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +a +11 +DROP TABLE t2; +SELECT * FROM t1; +a +10 +DROP TEMPORARY TABLE t1; +SELECT * FROM t1; +a +t1 +# +# Should use VARCHAR(10) as TYPE OF, as in the real table +# +CALL p1(); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +a +11 +DROP TABLE t2; +DROP PROCEDURE p1; +DROP TABLE t1; +# +# "TYPE OF t1.a" searches for "t1" in the current database +# +CREATE TABLE t1 (a VARCHAR(10)); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (a INT); +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF t1.a DEFAULT 11; +CREATE TABLE test.t2 AS SELECT a; +END; +$$ +# +# This interprets "TYPE OF t1.a" as VARCHAR(10), as in test.t1.a +# +USE test; +CALL test.p1(); +SHOW CREATE TABLE test.t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE test.t2; +# +# This interprets "TYPE OF t1.a" as INT, as in test1.t1.a +# +USE test1; +CALL test.p1(); +SHOW CREATE TABLE test.t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE test.t2; +# +# Error if there is no an active database +# +DROP DATABASE test1; +CALL test.p1(); +ERROR 3D000: No database selected +USE test; +DROP PROCEDURE p1; +DROP TABLE t1; +# +# A reference to a table in a non-existing database +# +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF test1.t1.a; +CREATE TABLE t1 AS SELECT a; +END; +$$ +CALL p1; +ERROR 42S02: Table 'test1.t1' doesn't exist +DROP PROCEDURE p1; +# +# A reference to a table in a different database +# +CREATE TABLE t1(a INT); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (a VARCHAR(10)); +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF t1.a; +DECLARE b TYPE OF test1.t1.a; +CREATE TABLE t2 AS SELECT a,b; +END; +$$ +CALL p1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `b` varchar(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP PROCEDURE p1; +DROP TABLE t2; +DROP DATABASE test1; +DROP TABLE t1; +# +# Using a table before it appears in a TYPE OF declaration + multiple TYPE OF declarations +# +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 (a,b) VALUES (10,'b10'); +CREATE PROCEDURE p1() +BEGIN +INSERT INTO t1 (a,b) VALUES (11, 'b11'); +SELECT * FROM t1; +BEGIN +DECLARE va TYPE OF t1.a DEFAULT 30; +DECLARE vb TYPE OF t1.b DEFAULT 'b30'; +INSERT INTO t1 (a,b) VALUES (12,'b12'); +SELECT * FROM t1; +INSERT INTO t1 (a,b) VALUES (va, vb); +SELECT * FROM t1; +END; +BEGIN +DECLARE va TYPE OF t1.a DEFAULT 40; +DECLARE vb TYPE OF t1.b DEFAULT 'b40'; +INSERT INTO t1 (a,b) VALUES (va,vb); +SELECT * FROM t1; +END; +END; +$$ +CALL p1; +a b +10 b10 +11 b11 +a b +10 b10 +11 b11 +12 b12 +a b +10 b10 +11 b11 +12 b12 +30 b30 +a b +10 b10 +11 b11 +12 b12 +30 b30 +40 b40 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# TYPE OF variables + TABLE vs VIEW +# +CREATE TABLE t1 ( +bit6 BIT(6), +bit7 BIT(7), +bit8 BIT(8), +i1 TINYINT, +i2 SMALLINT, +i3 MEDIUMINT, +i4 INT, +i8 BIGINT, +ff FLOAT, +fd DOUBLE, +cc CHAR(10), +cv VARCHAR(10), +cvu VARCHAR(10) CHARACTER SET utf8, +t1 TINYTEXT, +t2 TEXT, +t3 MEDIUMTEXT, +t4 LONGTEXT, +enum1 ENUM('a','b','c'), +set1 SET('a','b','c'), +blob1 TINYBLOB, +blob2 BLOB, +blob3 MEDIUMBLOB, +blob4 LONGBLOB, +yy YEAR, +dd DATE, +tm0 TIME, +tm3 TIME(3), +tm6 TIME(6), +dt0 DATETIME, +dt3 DATETIME(3), +dt6 DATETIME(6), +ts0 TIMESTAMP, +ts3 TIMESTAMP(3), +ts6 TIMESTAMP(6), +dc100 DECIMAL(10,0), +dc103 DECIMAL(10,3), +dc209 DECIMAL(20,9) +); +CREATE PROCEDURE p1(command enum('create','select')) +BEGIN +DECLARE bit6 TYPE OF t1.bit6 DEFAULT 0x30; +DECLARE bit7 TYPE OF t1.bit7 DEFAULT 0x41; +DECLARE bit8 TYPE OF t1.bit8 DEFAULT 0x7E; +DECLARE i1 TYPE OF t1.i1 DEFAULT 11; +DECLARE i2 TYPE OF t1.i2 DEFAULT 12; +DECLARE i3 TYPE OF t1.i3 DEFAULT 13; +DECLARE i4 TYPE OF t1.i4 DEFAULT 14; +DECLARE i8 TYPE OF t1.i8 DEFAULT 18; +DECLARE ff TYPE OF t1.ff DEFAULT 21; +DECLARE fd TYPE OF t1.fd DEFAULT 22; +DECLARE cc TYPE OF t1.cc DEFAULT 'char'; +DECLARE cv TYPE OF t1.cv DEFAULT 'varchar'; +DECLARE cvu TYPE OF t1.cvu DEFAULT 'varcharu8'; +DECLARE t1 TYPE OF t1.t1 DEFAULT 'text1'; +DECLARE t2 TYPE OF t1.t2 DEFAULT 'text2'; +DECLARE t3 TYPE OF t1.t3 DEFAULT 'text3'; +DECLARE t4 TYPE OF t1.t4 DEFAULT 'text4'; +DECLARE enum1 TYPE OF t1.enum1 DEFAULT 'b'; +DECLARE set1 TYPE OF t1.set1 DEFAULT 'a,c'; +DECLARE blob1 TYPE OF t1.blob1 DEFAULT 'blob1'; +DECLARE blob2 TYPE OF t1.blob2 DEFAULT 'blob2'; +DECLARE blob3 TYPE OF t1.blob3 DEFAULT 'blob3'; +DECLARE blob4 TYPE OF t1.blob4 DEFAULT 'blob4'; +DECLARE yy TYPE OF t1.yy DEFAULT 2001; +DECLARE dd TYPE OF t1.dd DEFAULT '2001-01-01'; +DECLARE tm0 TYPE OF t1.tm0 DEFAULT '00:00:01'; +DECLARE tm3 TYPE OF t1.tm3 DEFAULT '00:00:03.333'; +DECLARE tm6 TYPE OF t1.tm6 DEFAULT '00:00:06.666666'; +DECLARE dt0 TYPE OF t1.dt0 DEFAULT '2001-01-01 00:00:01'; +DECLARE dt3 TYPE OF t1.dt3 DEFAULT '2001-01-03 00:00:01.333'; +DECLARE dt6 TYPE OF t1.dt6 DEFAULT '2001-01-06 00:00:01.666666'; +DECLARE ts0 TYPE OF t1.ts0 DEFAULT '2002-01-01 00:00:01'; +DECLARE ts3 TYPE OF t1.ts3 DEFAULT '2002-01-03 00:00:01.333'; +DECLARE ts6 TYPE OF t1.ts6 DEFAULT '2002-01-06 00:00:01.666666'; +DECLARE dc100 TYPE OF t1.dc100 DEFAULT 10; +DECLARE dc103 TYPE OF t1.dc103 DEFAULT 10.123; +DECLARE dc209 TYPE OF t1.dc209 DEFAULT 10.123456789; +CASE +WHEN command='create' THEN +CREATE TABLE t2 AS SELECT +bit6, bit7, bit8, +i1,i2,i3,i4,i8, +ff,fd, dc100, dc103, dc209, +cc,cv,cvu, +t1,t2,t3,t4, +enum1, set1, +blob1, blob2, blob3, blob4, +dd, yy, +tm0, tm3, tm6, +dt0, dt3, dt6, +ts0, ts3, ts6; +WHEN command='select' THEN +SELECT +bit6, bit7, bit8, +i1,i2,i3,i4,i8, +ff,fd, dc100, dc103, dc209, +cc,cv,cvu, +t1,t2,t3,t4, +enum1, set1, +blob1, blob2, blob3, blob4, +dd, yy, +tm0, tm3, tm6, +dt0, dt3, dt6, +ts0, ts3, ts6; +END CASE; +END; +$$ +# +# TABLE +# +CALL p1('create'); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `bit6` bit(6) DEFAULT NULL, + `bit7` bit(7) DEFAULT NULL, + `bit8` bit(8) DEFAULT NULL, + `i1` tinyint(4) DEFAULT NULL, + `i2` smallint(6) DEFAULT NULL, + `i3` mediumint(9) DEFAULT NULL, + `i4` int(11) DEFAULT NULL, + `i8` bigint(20) DEFAULT NULL, + `ff` float DEFAULT NULL, + `fd` double DEFAULT NULL, + `dc100` decimal(10,0) DEFAULT NULL, + `dc103` decimal(10,3) DEFAULT NULL, + `dc209` decimal(20,9) DEFAULT NULL, + `cc` varchar(10) DEFAULT NULL, + `cv` varchar(10) DEFAULT NULL, + `cvu` varchar(10) CHARACTER SET utf8 DEFAULT NULL, + `t1` tinytext DEFAULT NULL, + `t2` text DEFAULT NULL, + `t3` mediumtext DEFAULT NULL, + `t4` longtext DEFAULT NULL, + `enum1` varchar(1) DEFAULT NULL, + `set1` varchar(5) DEFAULT NULL, + `blob1` tinyblob DEFAULT NULL, + `blob2` blob DEFAULT NULL, + `blob3` mediumblob DEFAULT NULL, + `blob4` longblob DEFAULT NULL, + `dd` date DEFAULT NULL, + `yy` year(4) DEFAULT NULL, + `tm0` time DEFAULT NULL, + `tm3` time(3) DEFAULT NULL, + `tm6` time(6) DEFAULT NULL, + `dt0` datetime DEFAULT NULL, + `dt3` datetime(3) DEFAULT NULL, + `dt6` datetime(6) DEFAULT NULL, + `ts0` timestamp NULL DEFAULT NULL, + `ts3` timestamp(3) NULL DEFAULT NULL, + `ts6` timestamp(6) NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +bit6 0 +bit7 A +bit8 ~ +i1 11 +i2 12 +i3 13 +i4 14 +i8 18 +ff 21 +fd 22 +dc100 10 +dc103 10.123 +dc209 10.123456789 +cc char +cv varchar +cvu varcharu8 +t1 text1 +t2 text2 +t3 text3 +t4 text4 +enum1 b +set1 a,c +blob1 blob1 +blob2 blob2 +blob3 blob3 +blob4 blob4 +dd 2001-01-01 +yy 2001 +tm0 00:00:01 +tm3 00:00:03.333 +tm6 00:00:06.666666 +dt0 2001-01-01 00:00:01 +dt3 2001-01-03 00:00:01.333 +dt6 2001-01-06 00:00:01.666666 +ts0 2002-01-01 00:00:01 +ts3 2002-01-03 00:00:01.333 +ts6 2002-01-06 00:00:01.666666 +DROP TABLE t2; +CALL p1('select'); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def bit6 bit6 16 6 1 Y 32 0 63 +def bit7 bit7 16 7 1 Y 32 0 63 +def bit8 bit8 16 8 1 Y 32 0 63 +def i1 i1 1 4 2 Y 32768 0 63 +def i2 i2 2 6 2 Y 32768 0 63 +def i3 i3 9 9 2 Y 32768 0 63 +def i4 i4 3 11 2 Y 32768 0 63 +def i8 i8 8 20 2 Y 32768 0 63 +def ff ff 4 12 2 Y 32768 31 63 +def fd fd 5 22 2 Y 32768 31 63 +def dc100 dc100 246 11 2 Y 32768 0 63 +def dc103 dc103 246 12 6 Y 32768 3 63 +def dc209 dc209 246 22 12 Y 32768 9 63 +def cc cc 254 10 4 Y 0 0 8 +def cv cv 253 10 7 Y 0 0 8 +def cvu cvu 253 10 9 Y 0 0 8 +def t1 t1 252 255 5 Y 16 0 8 +def t2 t2 252 65535 5 Y 16 0 8 +def t3 t3 252 16777215 5 Y 16 0 8 +def t4 t4 252 4294967295 5 Y 16 0 8 +def enum1 enum1 254 1 1 Y 256 0 8 +def set1 set1 254 5 3 Y 2048 0 8 +def blob1 blob1 252 255 5 Y 144 0 63 +def blob2 blob2 252 65535 5 Y 144 0 63 +def blob3 blob3 252 16777215 5 Y 144 0 63 +def blob4 blob4 252 4294967295 5 Y 144 0 63 +def dd dd 10 10 10 Y 128 0 63 +def yy yy 13 4 4 Y 32864 0 63 +def tm0 tm0 11 10 8 Y 128 0 63 +def tm3 tm3 11 14 12 Y 128 3 63 +def tm6 tm6 11 17 15 Y 128 6 63 +def dt0 dt0 12 19 19 Y 128 0 63 +def dt3 dt3 12 23 23 Y 128 3 63 +def dt6 dt6 12 26 26 Y 128 6 63 +def ts0 ts0 7 19 19 Y 9376 0 63 +def ts3 ts3 7 23 23 Y 160 3 63 +def ts6 ts6 7 26 26 Y 160 6 63 +bit6 0 +bit7 A +bit8 ~ +i1 11 +i2 12 +i3 13 +i4 14 +i8 18 +ff 21 +fd 22 +dc100 10 +dc103 10.123 +dc209 10.123456789 +cc char +cv varchar +cvu varcharu8 +t1 text1 +t2 text2 +t3 text3 +t4 text4 +enum1 b +set1 a,c +blob1 blob1 +blob2 blob2 +blob3 blob3 +blob4 blob4 +dd 2001-01-01 +yy 2001 +tm0 00:00:01 +tm3 00:00:03.333 +tm6 00:00:06.666666 +dt0 2001-01-01 00:00:01 +dt3 2001-01-03 00:00:01.333 +dt6 2001-01-06 00:00:01.666666 +ts0 2002-01-01 00:00:01 +ts3 2002-01-03 00:00:01.333 +ts6 2002-01-06 00:00:01.666666 +# +# VIEW +# +ALTER TABLE t1 RENAME t0; +CREATE VIEW t1 AS SELECT * FROM t0; +CALL p1('create'); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `bit6` bit(6) DEFAULT NULL, + `bit7` bit(7) DEFAULT NULL, + `bit8` bit(8) DEFAULT NULL, + `i1` tinyint(4) DEFAULT NULL, + `i2` smallint(6) DEFAULT NULL, + `i3` mediumint(9) DEFAULT NULL, + `i4` int(11) DEFAULT NULL, + `i8` bigint(20) DEFAULT NULL, + `ff` float DEFAULT NULL, + `fd` double DEFAULT NULL, + `dc100` decimal(10,0) DEFAULT NULL, + `dc103` decimal(10,3) DEFAULT NULL, + `dc209` decimal(20,9) DEFAULT NULL, + `cc` varchar(10) DEFAULT NULL, + `cv` varchar(10) DEFAULT NULL, + `cvu` varchar(10) CHARACTER SET utf8 DEFAULT NULL, + `t1` tinytext DEFAULT NULL, + `t2` text DEFAULT NULL, + `t3` mediumtext DEFAULT NULL, + `t4` longtext DEFAULT NULL, + `enum1` varchar(1) DEFAULT NULL, + `set1` varchar(5) DEFAULT NULL, + `blob1` tinyblob DEFAULT NULL, + `blob2` blob DEFAULT NULL, + `blob3` mediumblob DEFAULT NULL, + `blob4` longblob DEFAULT NULL, + `dd` date DEFAULT NULL, + `yy` year(4) DEFAULT NULL, + `tm0` time DEFAULT NULL, + `tm3` time(3) DEFAULT NULL, + `tm6` time(6) DEFAULT NULL, + `dt0` datetime DEFAULT NULL, + `dt3` datetime(3) DEFAULT NULL, + `dt6` datetime(6) DEFAULT NULL, + `ts0` timestamp NULL DEFAULT NULL, + `ts3` timestamp(3) NULL DEFAULT NULL, + `ts6` timestamp(6) NULL DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +bit6 0 +bit7 A +bit8 ~ +i1 11 +i2 12 +i3 13 +i4 14 +i8 18 +ff 21 +fd 22 +dc100 10 +dc103 10.123 +dc209 10.123456789 +cc char +cv varchar +cvu varcharu8 +t1 text1 +t2 text2 +t3 text3 +t4 text4 +enum1 b +set1 a,c +blob1 blob1 +blob2 blob2 +blob3 blob3 +blob4 blob4 +dd 2001-01-01 +yy 2001 +tm0 00:00:01 +tm3 00:00:03.333 +tm6 00:00:06.666666 +dt0 2001-01-01 00:00:01 +dt3 2001-01-03 00:00:01.333 +dt6 2001-01-06 00:00:01.666666 +ts0 2002-01-01 00:00:01 +ts3 2002-01-03 00:00:01.333 +ts6 2002-01-06 00:00:01.666666 +DROP TABLE t2; +CALL p1('select'); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def bit6 bit6 16 6 1 Y 32 0 63 +def bit7 bit7 16 7 1 Y 32 0 63 +def bit8 bit8 16 8 1 Y 32 0 63 +def i1 i1 1 4 2 Y 32768 0 63 +def i2 i2 2 6 2 Y 32768 0 63 +def i3 i3 9 9 2 Y 32768 0 63 +def i4 i4 3 11 2 Y 32768 0 63 +def i8 i8 8 20 2 Y 32768 0 63 +def ff ff 4 12 2 Y 32768 31 63 +def fd fd 5 22 2 Y 32768 31 63 +def dc100 dc100 246 11 2 Y 32768 0 63 +def dc103 dc103 246 12 6 Y 32768 3 63 +def dc209 dc209 246 22 12 Y 32768 9 63 +def cc cc 254 10 4 Y 0 0 8 +def cv cv 253 10 7 Y 0 0 8 +def cvu cvu 253 10 9 Y 0 0 8 +def t1 t1 252 255 5 Y 16 0 8 +def t2 t2 252 65535 5 Y 16 0 8 +def t3 t3 252 16777215 5 Y 16 0 8 +def t4 t4 252 4294967295 5 Y 16 0 8 +def enum1 enum1 254 1 1 Y 256 0 8 +def set1 set1 254 5 3 Y 2048 0 8 +def blob1 blob1 252 255 5 Y 144 0 63 +def blob2 blob2 252 65535 5 Y 144 0 63 +def blob3 blob3 252 16777215 5 Y 144 0 63 +def blob4 blob4 252 4294967295 5 Y 144 0 63 +def dd dd 10 10 10 Y 128 0 63 +def yy yy 13 4 4 Y 32864 0 63 +def tm0 tm0 11 10 8 Y 128 0 63 +def tm3 tm3 11 14 12 Y 128 3 63 +def tm6 tm6 11 17 15 Y 128 6 63 +def dt0 dt0 12 19 19 Y 128 0 63 +def dt3 dt3 12 23 23 Y 128 3 63 +def dt6 dt6 12 26 26 Y 128 6 63 +def ts0 ts0 7 19 19 Y 160 0 63 +def ts3 ts3 7 23 23 Y 160 3 63 +def ts6 ts6 7 26 26 Y 160 6 63 +bit6 0 +bit7 A +bit8 ~ +i1 11 +i2 12 +i3 13 +i4 14 +i8 18 +ff 21 +fd 22 +dc100 10 +dc103 10.123 +dc209 10.123456789 +cc char +cv varchar +cvu varcharu8 +t1 text1 +t2 text2 +t3 text3 +t4 text4 +enum1 b +set1 a,c +blob1 blob1 +blob2 blob2 +blob3 blob3 +blob4 blob4 +dd 2001-01-01 +yy 2001 +tm0 00:00:01 +tm3 00:00:03.333 +tm6 00:00:06.666666 +dt0 2001-01-01 00:00:01 +dt3 2001-01-03 00:00:01.333 +dt6 2001-01-06 00:00:01.666666 +ts0 2002-01-01 00:00:01 +ts3 2002-01-03 00:00:01.333 +ts6 2002-01-06 00:00:01.666666 +DROP VIEW t1; +DROP TABLE t0; +DROP PROCEDURE p1; +# +# VIEW with subqueries +# +CREATE TABLE t1 (a INT,b INT); +INSERT INTO t1 VALUES (10,1),(20,2),(30,3),(40,4); +SELECT AVG(a) FROM t1; +AVG(a) +25.0000 +CREATE VIEW v1 AS SELECT a,1 as b FROM t1 WHERE a>(SELECT AVG(a) FROM t1) AND b>(SELECT 1); +SELECT * FROM v1; +a b +30 1 +40 1 +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF v1.a DEFAULT 10; +DECLARE b TYPE OF v1.b DEFAULT 1; +SELECT a,b; +END; +$$ +CALL p1; +a b +10 1 +DROP PROCEDURE p1; +CREATE FUNCTION f1() RETURNS INT +BEGIN +DECLARE a TYPE OF v1.a DEFAULT 10; +DECLARE b TYPE OF v1.b DEFAULT 1; +RETURN a+b; +END; +$$ +SELECT f1(); +f1() +11 +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1; +# +# TYPE OF variables + INFORMATION_SCHEMA +# +CREATE PROCEDURE p1() +BEGIN +DECLARE tables_table_name TYPE OF INFORMATION_SCHEMA.TABLES.TABLE_NAME; +DECLARE tables_table_rows TYPE OF INFORMATION_SCHEMA.TABLES.TABLE_ROWS; +DECLARE processlist_info TYPE OF INFORMATION_SCHEMA.PROCESSLIST.INFO; +DECLARE processlist_info_binary TYPE OF INFORMATION_SCHEMA.PROCESSLIST.INFO_BINARY; +CREATE TABLE t1 AS SELECT +tables_table_name, +tables_table_rows, +processlist_info, +processlist_info_binary; +END; +$$ +CALL p1(); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `tables_table_name` varchar(64) CHARACTER SET utf8 DEFAULT NULL, + `tables_table_rows` bigint(21) unsigned DEFAULT NULL, + `processlist_info` longtext CHARACTER SET utf8 DEFAULT NULL, + `processlist_info_binary` blob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# TYPE OF + Table structure change +# Data type for both a0 and a1 is chosen in the very beginning +# +CREATE PROCEDURE p1() +BEGIN +DECLARE a0 TYPE OF t1.a; +ALTER TABLE t1 MODIFY a VARCHAR(10); -- This does not affect a1 +BEGIN +DECLARE a1 TYPE OF t1.a; +CREATE TABLE t2 AS SELECT a0, a1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +END; +END +$$ +CREATE TABLE t1 (a INT); +CALL p1; +Table Create Table +t2 CREATE TABLE `t2` ( + `a0` int(11) DEFAULT NULL, + `a1` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# TYPE OF in parameters +# +CREATE TABLE t1 (a VARCHAR(10)); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (b SMALLINT); +CREATE PROCEDURE p1(a TYPE OF t1.a, b TYPE OF test1.t1.b) +BEGIN +CREATE TABLE t2 AS SELECT a, b; +END; +$$ +CALL p1('test', 123); +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(10) DEFAULT NULL, + `b` smallint(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +a b +test 123 +DROP TABLE t2; +DROP PROCEDURE p1; +DROP TABLE test1.t1; +DROP DATABASE test1; +DROP TABLE t1; +# +# TYPE OF in a stored function variables and arguments +# +CREATE TABLE t1 (a INT); +CREATE FUNCTION f1 (prm TYPE OF t1.a) RETURNS INT +BEGIN +DECLARE a TYPE OF t1.a DEFAULT prm; +RETURN a; +END; +$$ +SELECT f1(20); +f1(20) +20 +DROP FUNCTION f1; +DROP TABLE t1; +# +# TYPE OF in function RETURN clause is not supported yet +# +CREATE FUNCTION f1() RETURN TYPE OF t1.a +BEGIN +RETURN 0; +END; +$$ +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RETURN TYPE OF t1.a +BEGIN +RETURN 0; +END' at line 1 +# +# Using TYPE OF variables as FETCH targets +# +CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3)); +INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123'); +INSERT INTO t1 VALUES (2,'b2','2001-01-02 10:20:30.123'); +CREATE TABLE t2 LIKE t1; +CREATE PROCEDURE p1() +BEGIN +DECLARE done INT DEFAULT 0; +DECLARE v_a TYPE OF t1.a; +DECLARE v_b TYPE OF t1.b; +DECLARE v_c TYPE OF t1.c; +DECLARE c CURSOR FOR SELECT a,b,c FROM t1; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; +OPEN c; +read_loop: LOOP +FETCH c INTO v_a, v_b, v_c; +IF done THEN +LEAVE read_loop; +END IF; +INSERT INTO t2 (a,b,c) VALUES (v_a, v_b, v_c); +END LOOP; +CLOSE c; +END; +$$ +CALL p1(); +SELECT * FROM t2; +a b c +1 b1 2001-01-01 10:20:30.123 +2 b2 2001-01-02 10:20:30.123 +DROP TABLE t2; +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Using TYPE OF variables as SELECT INTO targets +# +CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3)); +INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123'); +CREATE PROCEDURE p1() +BEGIN +DECLARE v_a TYPE OF t1.a; +DECLARE v_b TYPE OF t1.b; +DECLARE v_c TYPE OF t1.c; +SELECT * FROM t1 INTO v_a, v_b, v_c; +SELECT v_a, v_b, v_c; +END; +$$ +CALL p1(); +v_a v_b v_c +1 b1 2001-01-01 10:20:30.123 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# diff --git a/mysql-test/r/sp-security-anchor-type.result b/mysql-test/r/sp-security-anchor-type.result new file mode 100644 index 00000000000..4ee846f9f2e --- /dev/null +++ b/mysql-test/r/sp-security-anchor-type.result @@ -0,0 +1,263 @@ +# +# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# +# +# Initiation: +# - creating database db1 +# - creating user user1 with access rights to db1 +# +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT, b VARCHAR(10)); +CREATE USER user1; +GRANT ALL PRIVILEGES ON test.* TO user1; +connect conn1,localhost,user1,,test; +SELECT database(); +database() +test +SELECT user(); +user() +user1@localhost +# +# Making sure that user1 does not have privileges to db1.t1 +# +SHOW CREATE TABLE db1.t1; +ERROR 42000: SHOW command denied to user 'user1'@'localhost' for table 't1' +SHOW FIELDS IN db1.t1; +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +# +# Trigger: using TYPE OF with a table we don't have access to +# +CREATE TABLE test.t1 (a INT, b INT); +INSERT INTO test.t1 (a,b) VALUES (10,20); +SELECT * FROM t1; +a b +10 20 +CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW +BEGIN +DECLARE b TYPE OF db1.t1.b DEFAULT 20; +SET NEW.b = 10; +END +$$ +INSERT INTO t1 (a) VALUES (10); +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +SELECT * FROM t1; +a b +10 20 +DROP TRIGGER tr1; +DROP TABLE t1; +# +# Stored procedure: Using TYPE OF for with a table that we don't have access to +# DEFINER user1, SQL SECURITY DEFAULT +# +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 10; +SELECT a; +END; +$$ +CALL p1; +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +DROP PROCEDURE p1; +# +# Stored procedure: Using TYPE OF for with a table that we don't have access to +# DEFINER root, SQL SECURITY INVOKER +# +connection default; +CREATE PROCEDURE p1() +SQL SECURITY INVOKER +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 10; +SELECT a; +END; +$$ +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 +BEGIN +DECLARE a ROW TYPE OF db1.t1; +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 OF for with a table that we don't have access to +# DEFINER root, SQL SECURITY DEFINER +# +connection default; +CREATE PROCEDURE p1() +SQL SECURITY DEFINER +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 10; +SELECT a; +END; +$$ +connection conn1; +CALL p1; +a +10 +DROP PROCEDURE p1; +connection default; +CREATE PROCEDURE p1() +SQL SECURITY DEFINER +BEGIN +DECLARE a ROW TYPE OF db1.t1; +SET a.a= 10; +SELECT a.a; +END; +$$ +connection conn1; +CALL p1; +a.a +10 +DROP PROCEDURE p1; +# +# Stored function: Using TYPE OF for with a table that we don't have access to +# DEFINER user1, SQL SECURITY DEFAULT +# +CREATE TABLE t1 (a INT); +CREATE FUNCTION f1() RETURNS INT +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 0; +RETURN OCTET_LENGTH(a); +END; +$$ +SELECT f1(); +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +DROP FUNCTION f1; +DROP TABLE t1; +# +# Stored function: Using TYPE OF for with a table that we don't have access to +# DEFINER root, SQL SECURITY INVOKER +# +connection default; +CREATE TABLE t1 (a INT); +CREATE FUNCTION f1() RETURNS INT +SQL SECURITY INVOKER +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 0; +RETURN OCTET_LENGTH(a); +END; +$$ +connection conn1; +SELECT f1(); +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +DROP FUNCTION f1; +DROP TABLE t1; +# +# Stored function: Using TYPE OF for with a table that we don't have access to +# DEFINER root, SQL SECURITY DEFINER +# +connection default; +CREATE TABLE t1 (a INT); +CREATE FUNCTION f1() RETURNS INT +SQL SECURITY DEFINER +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 0; +RETURN OCTET_LENGTH(a); +END; +$$ +connection conn1; +SELECT f1(); +f1() +1 +DROP FUNCTION f1; +DROP TABLE t1; +connection default; +GRANT SELECT (a) ON db1.t1 TO user1; +connection conn1; +# +# Making sure that user1 has access to db1.t1.a, but not to db1.t1.b +# +SHOW CREATE TABLE db1.t1; +ERROR 42000: SHOW command denied to user 'user1'@'localhost' for table 't1' +SHOW FIELDS IN db1.t1; +Field Type Null Key Default Extra +a int(11) YES NULL +# +# Trigger: Per-column privileges +# +CREATE TABLE test.t1 (a INT, b INT); +INSERT INTO test.t1 (a,b) VALUES (10,20); +SELECT * FROM t1; +a b +10 20 +CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 20; +BEGIN +SET NEW.b := 10; +END; +END +$$ +INSERT INTO t1 (a) VALUES (10); +SELECT * FROM t1; +a b +10 20 +10 10 +DROP TRIGGER tr1; +CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW +BEGIN +DECLARE b TYPE OF db1.t1.b DEFAULT 20; +BEGIN +SET NEW.b = 10; +END; +END +$$ +INSERT INTO t1 (a) VALUES (10); +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1' +SELECT * FROM t1; +a b +10 20 +10 10 +DROP TRIGGER tr1; +DROP TABLE t1; +# +# Stored procedure: Per-column privileges +# DEFINER user1, SQL SECURITY DEFAULT +# +CREATE PROCEDURE p1() +BEGIN +DECLARE a TYPE OF db1.t1.a DEFAULT 10; +SELECT a; +END; +$$ +CALL p1; +a +10 +DROP PROCEDURE p1; +CREATE PROCEDURE p1() +BEGIN +DECLARE b TYPE OF db1.t1.b DEFAULT 10; +SELECT b; +END; +$$ +CALL p1; +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'b' in table 't1' +DROP PROCEDURE p1; +CREATE PROCEDURE p1() +BEGIN +DECLARE b ROW TYPE OF db1.t1; +SET 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 +# +disconnect conn1; +connection default; +DROP USER user1; +DROP DATABASE db1; +# +# End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index e6c8f6a5e2f..9224756407b 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -2368,3 +2368,23 @@ tr1 1 2016-01-01 10:10:10.33 tr2 2 2016-01-01 10:10:10.99 drop table t1; set time_zone= @@global.time_zone; +# +# Start of 10.3 tests +# +# +# MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +# +CREATE TABLE t1 (a INT, b INT, total INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 +FOR EACH ROW +BEGIN +DECLARE va TYPE OF t1.a DEFAULT NEW.a; +DECLARE vb TYPE OF t1.b DEFAULT NEW.b; +SET NEW.total:= va + vb; +END; +$$ +INSERT INTO t1 (a,b) VALUES (10, 20); +SELECT * FROM t1; +a b total +10 20 30 +DROP TABLE t1; diff --git a/mysql-test/t/sp-anchor-row-type-cursor.test b/mysql-test/t/sp-anchor-row-type-cursor.test new file mode 100644 index 00000000000..8d17e242a50 --- /dev/null +++ b/mysql-test/t/sp-anchor-row-type-cursor.test @@ -0,0 +1,1073 @@ + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # + +--echo # +--echo # A complete working example +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE TABLE t2 LIKE t1; +INSERT INTO t1 VALUES (10,'b10'); +INSERT INTO t1 VALUES (20,'b20'); +INSERT INTO t1 VALUES (30,'b30'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE c CURSOR FOR SELECT a,b FROM t1; + BEGIN + DECLARE done INT DEFAULT 0; + DECLARE rec ROW TYPE OF c; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; + OPEN c; + read_loop: LOOP + FETCH c INTO rec; + IF done THEN + LEAVE read_loop; + END IF; + SELECT rec.a ,rec.b FROM dual; + INSERT INTO t2 VALUES (rec.a, rec.b); + END LOOP; + CLOSE c; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +SELECT * FROM t2; +DROP PROCEDURE p1; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # +--echo # cursor ROW TYPE referring to a table in a non-existing database +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM tes2.t1; + BEGIN + DECLARE rec ROW TYPE OF cur; + END; +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 # cursor ROW TYPE referring to a table in the current database +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec ROW TYPE OF cur; + CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; + SHOW CREATE TABLE t2; + DROP TABLE t2; + END; +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; +--error ER_NO_SUCH_TABLE +CALL p1(); +DROP PROCEDURE p1; + + +--echo # +--echo # cursor ROW TYPE referring to a table in an explicitly specified database +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM test.t1; + BEGIN + DECLARE rec ROW TYPE OF cur; + CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; + SHOW CREATE TABLE t2; + DROP TABLE t2; + END; +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 # Cursor ROW TYPE referring to a view in the current database +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM v1; + BEGIN + DECLARE rec ROW TYPE OF cur; + CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; + SHOW CREATE TABLE t2; + DROP TABLE t2; + END; +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 # cursor ROW TYPE referring to a view in an explicitly specified database +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM test.v1; + BEGIN + DECLARE rec ROW TYPE OF cur; + CREATE TABLE t2 AS SELECT rec.a, rec.b, rec.c, rec.d; + SHOW CREATE TABLE t2; + DROP TABLE t2; + END; +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 cursor ROW TYPE fields are NULL by default +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + SELECT rec1.a, rec1.b, rec1.c, rec1.d; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # A cursor ROW TYPE variable with a ROW expression as a default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1 DEFAULT ROW(10,'bbb'); + SELECT rec1.a, rec1.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # A cursor ROW TYPE variable with an incompatible ROW expression as a default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1 DEFAULT ROW(10,'bbb','ccc'); + SELECT rec1.a, rec1.b; + END; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # A cursor ROW TYPE variable with a ROW variable as a default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW(a INT, b VARCHAR(10)) DEFAULT ROW(10,'bbb'); + DECLARE rec2 ROW TYPE OF cur DEFAULT rec1; + SELECT rec2.a, rec2.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # A ROW variable using a cursor ROW TYPE variable as a default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1 DEFAULT ROW(10,'bbb'); + DECLARE rec2 ROW(a INT, b VARCHAR(10)) DEFAULT rec1; + SELECT rec2.a, rec2.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning cursor ROW TYPE 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() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + DECLARE cur2 CURSOR FOR SELECT * FROM t2; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + DECLARE rec2 ROW TYPE OF cur2; + SET rec2=rec1; + END; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP PROCEDURE p1; +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + DECLARE cur2 CURSOR FOR SELECT * FROM t2; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + DECLARE rec2 ROW TYPE OF cur2; + SET rec1=rec2; + END; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning compatible cursor ROW TYPE 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() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + DECLARE cur2 CURSOR FOR SELECT * FROM t2; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + DECLARE rec2 ROW TYPE OF cur2; + SET rec1.a= 10; + SET rec1.b= 'bbb'; + SET rec2=rec1; + SELECT rec2.x, rec2.y; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning between incompatible cursor ROW TYPE and explicit ROW variables +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + DECLARE rec2 ROW(x INT,y INT,z INT); + SET rec2.x= 10; + SET rec2.y= 20; + SET rec2.z= 30; + SET rec1= rec2; + END; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning between compatible cursor ROW TYPE and explicit ROW variables +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + DECLARE rec2 ROW(x INT,y INT); + SET rec2.x= 10; + SET rec2.y= 20; + SET rec1= rec2; + SELECT rec1.a, rec1.b; + SET rec1.a= 11; + SET rec1.b= 21; + SET rec2= rec1; + SELECT rec2.x, rec2.y; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning cursor ROW TYPE from a ROW expression +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + SET rec1= ROW(10,20); + SELECT rec1.a, rec1.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Fetching a cursor into a cursor ROW TYPE 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() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + DECLARE cur2 CURSOR FOR SELECT * FROM t2; + BEGIN + DECLARE rec2 ROW TYPE OF cur2; + OPEN cur1; + FETCH cur1 INTO rec2; + CLOSE cur1; + END; +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 cursor ROW TYPE 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() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE done INT DEFAULT 0; + DECLARE rec ROW TYPE OF cur; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN cur; + read_loop: LOOP + FETCH cur INTO rec; + IF done THEN + LEAVE read_loop; + END IF; + 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; +END; +$$ +DELIMITER ;$$ +CALL p1(); +SELECT * FROM t2; +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Fetching a cursor into a cursor ROW TYPE variable, ROW TYPE OF cur declared inside the LOOP +--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() +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE cur CURSOR FOR SELECT * FROM t1; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN cur; + read_loop: LOOP + BEGIN + DECLARE rec ROW TYPE OF cur; + FETCH cur INTO rec; + IF done THEN + LEAVE read_loop; + END IF; + SELECT rec.a, rec.b, rec.c, rec.d; + INSERT INTO t2 VALUES (rec.a, rec.b, rec.c, rec.d); + END; + 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 cursor ROW TYPE 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() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + DECLARE cur2 CURSOR FOR SELECT * FROM t2; + BEGIN + DECLARE rec2 ROW TYPE OF cur2; + OPEN cur1; + FETCH cur1 INTO rec2; + SELECT rec2.x, rec2.y; + CLOSE cur1; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Fetching a cursor into a cursor ROW TYPE variable, with truncation +--echo # + +SET sql_mode=''; +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() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + DECLARE cur2 CURSOR FOR SELECT * FROM t2; + BEGIN + DECLARE rec2 ROW TYPE OF cur2; + OPEN cur1; + FETCH cur1 INTO rec2; + SELECT rec2.a, rec2.b; + CLOSE cur1; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; +SET sql_mode=DEFAULT; + + +--echo # +--echo # cursor ROW TYPE 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() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1 DEFAULT (1,2); + SELECT * FROM t1 LIMIT rec1.a; + END; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + + +--echo # +--echo # cursor ROW TYPE variable fields as OUT parameters +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1(OUT a INT,OUT b VARCHAR(10)) +BEGIN + SET a=10; + SET b='bb'; +END; +$$ +CREATE PROCEDURE p2() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + CALL p1(rec1.a, rec1.b); + SELECT rec1.a, rec1.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p2(); +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Passing the entire cursor ROW TYPE variable +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10))) +BEGIN + SELECT a.a, a.b; +END; +$$ +CREATE PROCEDURE p2() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur DEFAULT ROW(10,'bb'); + CALL p1(rec1); + END; +END; +$$ +DELIMITER ;$$ +CALL p2(); +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Passing the entire cursor ROW TYPE variable as an OUT parameter +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1(OUT a ROW(a INT, b VARCHAR(10))) +BEGIN + SET a= ROW(10,'bb'); +END; +$$ +CREATE PROCEDURE p2() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur; + CALL p1(rec1); + SELECT rec1.a, rec1.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p2(); +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Assigning a cursor ROW TYPE field to an OUT parameter +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1 (INOUT res INTEGER) +BEGIN + DECLARE a INT DEFAULT 10; + DECLARE cur1 CURSOR FOR SELECT a FROM DUAL; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + OPEN cur1; + FETCH cur1 INTO rec1; + CLOSE cur1; + SET res=rec1.a; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(@res); +SELECT @res; +SET @res=NULL; +DROP PROCEDURE p1; + + +--echo # +--echo # Testing Item_splocal_row_field_by_name::print +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec ROW TYPE OF cur1 DEFAULT ROW(10,'bb'); + EXPLAIN EXTENDED SELECT rec.a, rec.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Run time error in the cursor statement +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT + 10 AS a, + CONCAT(_latin1'a' COLLATE latin1_bin, + _latin1'a' COLLATE latin1_swedish_ci) AS b; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + OPEN cur1; + FETCH cur1 INTO rec1; + CLOSE cur1; + SELECT a,b; + END; +END; +$$ +DELIMITER ;$$ +--error ER_CANT_AGGREGATE_2COLLATIONS +CALL p1(); +DROP PROCEDURE p1; + + + +--echo # +--echo # Non-existing field +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec ROW TYPE OF cur1; + SELECT rec.c; + END; +END; +$$ +DELIMITER ;$$ +--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD +CALL p1(); +ALTER TABLE t1 ADD c INT; +# +# The below ALTER is needed as a workaround to call sp_cache_invalidate() +# Please remove it after fixing MDEV-12166 +# +ALTER PROCEDURE p1 COMMENT 'test'; +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() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec ROW TYPE OF cur DEFAULT ROW(10,'bb'); + SELECT rec.A, rec.B; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Testing that cursor ROW TYPE 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() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec ROW TYPE OF cur DEFAULT ROW(10,'bb'); + SELECT rec.A, rec.B; + END; +END; +$$ +DELIMITER ;$$ +--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD +CALL p1(); +DROP TEMPORARY TABLE t1; +# +# The below ALTER is needed as a workaround to call sp_cache_invalidate() +# Please remove it after fixing MDEV-12166 +# +ALTER PROCEDURE p1 COMMENT 'test'; +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Testing that the structure of cursor ROW TYPE variables is determined at the DECLARE CURSOR instantiation time +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + DROP TABLE t1; + CREATE TABLE t1 (a INT, b VARCHAR(32), c INT); + BEGIN + DECLARE rec ROW TYPE OF cur; -- This has a column "c" + SET rec.c=10; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec ROW TYPE OF cur; -- This does not have a column "c" + DROP TABLE t1; + CREATE TABLE t1 (a INT, b VARCHAR(32), c INT); + SET rec.c=10; + END; +END; +$$ +DELIMITER ;$$ +--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Duplicate field nams in a cursor referenced by cursor ROW TYPE +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT * FROM t1, t2; + BEGIN + DECLARE rec ROW TYPE OF cur; + SELECT rec.a; + SET rec.a=10; + END; +END; +$$ +DELIMITER ;$$ +--error ER_DUP_FIELDNAME +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # +--echo # Tricky field names a cursor referenced by cursor ROW TYPE +--echo # + +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('a'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur CURSOR FOR SELECT a, CONCAT(a,'a'), CONCAT(a,'ö') FROM t1; + BEGIN + DECLARE rec ROW TYPE OF cur; + OPEN cur; + FETCH cur INTO rec; + CLOSE cur; + SELECT rec.a, rec.`CONCAT(a,'a')`, rec.`CONCAT(a,'ö')`; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; +SET NAMES latin1; + + +--echo # +--echo # Using definitions recursively (cursor ROW TYPE variables in another cursor SELECT) +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (10,'b1'),(20,'b2'),(30,'b3'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT a,b FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1 DEFAULT ROW(0,'b0'); + DECLARE cur2 CURSOR FOR SELECT rec1.a AS a, rec1.b AS b FROM t1; + BEGIN + DECLARE done INT DEFAULT 0; + DECLARE rec2 ROW TYPE OF cur2; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN cur2; + read_loop: LOOP + FETCH cur2 INTO rec2; + IF done THEN + LEAVE read_loop; + END IF; + SELECT rec2.a, rec2.b; + END LOOP; + CLOSE cur2; + END; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Testing queries with auto-generated Items. +--echo # An instance of Item_func_conv_charset is created during the below SELECT query. +--echo # We check here that during an implicit cursor OPEN +--echo # done in sp_instr_cursor_copy_struct::exec_core() +--echo # all temporary Items are created on a proper memory root and are safely destroyed. +--echo # + +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, b VARCHAR(10) CHARACTER SET utf8); +INSERT INTO t1 VALUES (0xFF, 'a'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT CONCAT(a,b) AS c FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + OPEN cur1; + FETCH cur1 INTO rec1; + CLOSE cur1; + SELECT HEX(rec1.c); + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # SELECT INTO + cursor ROW TYPE variable with a wrong column count +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT 10, 'b0', 'c0'; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + SELECT * FROM t1 INTO rec1; + SELECT rec1.a, rec1.b; + END; +END; +$$ +DELIMITER ;$$ +--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # SELECT INTO + multiple cursor ROW TYPE variables +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + SELECT * FROM t1 INTO rec1, rec1; + SELECT rec1.a, rec1.b; + END; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # SELECT INTO + cursor ROW TYPE working example +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + BEGIN + DECLARE rec1 ROW TYPE OF cur1; + SELECT * FROM t1 INTO rec1; + SELECT rec1.a, rec1.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # diff --git a/mysql-test/t/sp-anchor-row-type-table.test b/mysql-test/t/sp-anchor-row-type-table.test new file mode 100644 index 00000000000..97e0a1d5805 --- /dev/null +++ b/mysql-test/t/sp-anchor-row-type-table.test @@ -0,0 +1,761 @@ + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # + +--echo # +--echo # Referring to a table in a non-existing database +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec ROW TYPE OF test2.t1; +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() +BEGIN + DECLARE rec ROW TYPE OF t1; + 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() +BEGIN + DECLARE rec ROW TYPE OF test.t1; + 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() +BEGIN + DECLARE rec ROW TYPE OF v1; + 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() +BEGIN + DECLARE rec ROW TYPE OF test.v1; + 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 ROW TYPE fields are NULL by default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + SELECT rec1.a, rec1.b, rec1.c, rec1.d; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # A table ROW TYPE variable with a ROW expression as a default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb'); + SELECT rec1.a, rec1.b; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # A table ROW TYPE variable with an incompatible ROW expression as a default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb','ccc'); + SELECT rec1.a, rec1.b; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # A table ROW TYPE variable with a ROW variable as a default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW(a INT, b VARCHAR(10)) DEFAULT ROW(10,'bbb'); + DECLARE rec2 ROW TYPE OF t1 DEFAULT rec1; + SELECT rec2.a, rec2.b; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # A ROW variable using a table ROW TYPE variable as a default +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bbb'); + DECLARE rec2 ROW(a INT, b VARCHAR(10)) DEFAULT rec1; + SELECT rec2.a, rec2.b; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning table ROW TYPE 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() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + DECLARE rec2 ROW TYPE OF t2; + SET rec2=rec1; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP PROCEDURE p1; +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + DECLARE rec2 ROW TYPE OF t2; + SET rec1=rec2; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP TABLE t2; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning compatible table ROW TYPE 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() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + DECLARE rec2 ROW TYPE OF t2; + SET rec1.a= 10; + SET rec1.b= 'bbb'; + SET 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 ROW TYPE and explicit ROW variables +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + DECLARE rec2 ROW(x INT,y INT,z INT); + SET rec2.x= 10; + SET rec2.y= 20; + SET rec2.z= 30; + SET rec1= rec2; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning between compatible table ROW TYPE and explicit ROW variables +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + DECLARE rec2 ROW(x INT,y INT); + SET rec2.x= 10; + SET rec2.y= 20; + SET rec1= rec2; + SELECT rec1.a, rec1.b; + SET rec1.a= 11; + SET rec1.b= 21; + SET rec2= rec1; + SELECT rec2.x, rec2.y; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # Assigning table ROW TYPE from a ROW expression +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + SET 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 ROW TYPE 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() +BEGIN + DECLARE rec2 ROW TYPE OF t2; + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + 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 ROW TYPE 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() +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE rec ROW TYPE OF t1; + DECLARE cur CURSOR FOR SELECT * FROM t1; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN cur; + read_loop: LOOP + FETCH cur INTO rec; + IF done THEN + LEAVE read_loop; + END IF; + 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 ROW TYPE 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() +BEGIN + DECLARE rec2 ROW TYPE OF t2; + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + 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 ROW TYPE variable, with truncation +--echo # + +SET sql_mode=''; +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() +BEGIN + DECLARE rec2 ROW TYPE OF t2; + DECLARE cur1 CURSOR FOR SELECT * FROM t1; + 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; +SET sql_mode=DEFAULT; + +--echo # +--echo # table ROW TYPE 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() +BEGIN + DECLARE rec1 ROW TYPE OF t1 DEFAULT (1,2); + SELECT * FROM t1 LIMIT rec1.a; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + + +--echo # +--echo # table ROW TYPE variable fields as OUT parameters +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1(OUT a INT,OUT b VARCHAR(10)) +BEGIN + SET a=10; + SET b='bb'; +END; +$$ +CREATE PROCEDURE p2() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + 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 ROW TYPE variable +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1(a ROW(a INT, b VARCHAR(10))) +BEGIN + SELECT a.a, a.b; +END; +$$ +CREATE PROCEDURE p2() +BEGIN + DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'bb'); + CALL p1(rec1); +END; +$$ +DELIMITER ;$$ +CALL p2(); +DROP PROCEDURE p2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Passing the entire table ROW TYPE variable as an OUT parameter +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1(OUT a ROW(a INT, b VARCHAR(10))) +BEGIN + SET a= ROW(10,'bb'); +END; +$$ +CREATE PROCEDURE p2() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + 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 ROW TYPE field to an OUT parameter +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1 (INOUT res INTEGER) +BEGIN + DECLARE rec1 ROW TYPE OF t1 DEFAULT ROW(10,'b0'); + SET 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() +BEGIN + DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); + 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() +BEGIN + DECLARE rec ROW TYPE OF t1; + 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() +BEGIN + DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); + SELECT rec.A, rec.B; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Testing that table ROW TYPE 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() +BEGIN + DECLARE rec ROW TYPE OF t1 DEFAULT ROW(10,'bb'); + 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 ROW TYPE 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() +BEGIN + ALTER TABLE t1 ADD c INT; + BEGIN + DECLARE rec ROW TYPE OF t1; -- this will not have column "c" + SET rec.c=10; + END; +END; +$$ +DELIMITER ;$$ +--error ER_ROW_VARIABLE_DOES_NOT_HAVE_FIELD +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # SELECT INTO + table ROW TYPE variable with a wrong column count +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + SELECT 10,'a','b' FROM t1 INTO rec1; + SELECT rec1.a, rec1.b; +END; +$$ +DELIMITER ;$$ +--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # SELECT INTO + multiple table ROW TYPE variables +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + SELECT 10,'a' FROM t1 INTO rec1, rec1; + SELECT rec1.a, rec1.b; +END; +$$ +DELIMITER ;$$ +--error ER_OPERAND_COLUMNS +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # SELECT INTO + table ROW TYPE working example +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(32)); +INSERT INTO t1 VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE rec1 ROW TYPE OF t1; + SELECT * FROM t1 INTO rec1; + SELECT rec1.a, rec1.b; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # + + +--echo # +--echo # MDEV-12347 Valgrind reports invalid read errors in Item_field_row::element_index_by_name +--echo # + +# An additional test for MDEV-12347, to make sure that +# Column_definition::interval creates a permanent copy of TYPELIB on +# the memory root when processing %ROWTYPE for a table with ENUM/SET column, +# rather than reuses the TYPELIB from table->field[i], which is freed in the +# end of sp_rcontext::resolve_table_rowtype_ref(). + +CREATE TABLE t1 (a INT, b ENUM('b0','b1','b12','b3')); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + BEGIN + DECLARE rec ROW TYPE OF t1; + SET rec.b='b0'; + SELECT rec.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; + + +CREATE TABLE t1 (a INT, b SET('b0','b1','b12','b3')); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + BEGIN + DECLARE rec ROW TYPE OF t1; + SET rec.b='b0'; + SELECT rec.b; + END; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP TABLE t1; +DROP PROCEDURE p1; diff --git a/mysql-test/t/sp-anchor-type.test b/mysql-test/t/sp-anchor-type.test new file mode 100644 index 00000000000..f57342eb67c --- /dev/null +++ b/mysql-test/t/sp-anchor-type.test @@ -0,0 +1,677 @@ + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # + +--echo # +--echo # Missing table +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF t1.a; +END; +$$ +DELIMITER ;$$ +--error ER_NO_SUCH_TABLE +CALL p1(); +DROP PROCEDURE p1; + + +--echo # +--echo # Missing column +--echo # + +CREATE TABLE t1 (b INT); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF t1.a; +END; +$$ +DELIMITER ;$$ +--error ER_BAD_FIELD_ERROR +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # One TYPE OF variable +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF t1.a; + SET a= 123; + SELECT a; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # Two TYPE OF variables, with a truncation warning on assignment +--echo # + +SET sql_mode=''; +CREATE TABLE t1 (a TINYINT, b INT); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF t1.a; + DECLARE b TYPE OF t1.b; + SET a= 200; + SET b= 200; + SELECT a, b; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo # +--echo # TYPE OF variables for fields with various attributes +--echo # + +CREATE TABLE t1 ( + id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, + a TINYINT NOT NULL, + b INT NOT NULL, + ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE(a) +); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE id TYPE OF t1.id; + DECLARE a TYPE OF t1.a; + DECLARE b TYPE OF t1.b; + DECLARE ts TYPE OF t1.ts; + SELECT id, a, b, ts; + CREATE TABLE t2 AS SELECT id, a, b, ts; + SHOW CREATE TABLE t2; + DROP TABLE t2; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # TYPE OF + virtual columns +--echo # + +CREATE TABLE t1 ( + a INT NOT NULL, + b VARCHAR(32), + c INT AS (a + 10) VIRTUAL, + d VARCHAR(5) AS (left(b,5)) PERSISTENT +); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE c TYPE OF t1.c; + DECLARE d TYPE OF t1.d; + SELECT c, d; + CREATE TABLE t2 AS SELECT c, d; + SHOW CREATE TABLE t2; + DROP TABLE t2; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # TYPE OF + the ZEROFILL attribute +--echo # + +CREATE TABLE t1 ( + dz DECIMAL(10,3) ZEROFILL +); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE dzr TYPE OF t1.dz DEFAULT 10; + DECLARE dzt DECIMAL(10,3) ZEROFILL DEFAULT 10; + SELECT dzr, dzt; + CREATE TABLE t2 AS SELECT dzr,dzt; + SHOW CREATE TABLE t2; + DROP TABLE t2; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Temporary tables shadow real tables for TYPE OF purposes +--echo # +CREATE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('t1'); +CREATE TEMPORARY TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +SELECT * FROM t1; + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF t1.a DEFAULT 11; + CREATE TABLE t2 AS SELECT a; +END; +$$ +DELIMITER ;$$ +--echo # +--echo # Should use INT(11) as TYPE OF, as in the temporary table +--echo # +CALL p1(); +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +SELECT * FROM t1; +DROP TEMPORARY TABLE t1; +SELECT * FROM t1; +--echo # +--echo # Should use VARCHAR(10) as TYPE OF, as in the real table +--echo # +CALL p1(); +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # "TYPE OF t1.a" searches for "t1" in the current database +--echo # + +CREATE TABLE t1 (a VARCHAR(10)); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF t1.a DEFAULT 11; + CREATE TABLE test.t2 AS SELECT a; +END; +$$ +DELIMITER ;$$ + +--echo # +--echo # This interprets "TYPE OF t1.a" as VARCHAR(10), as in test.t1.a +--echo # + +USE test; +CALL test.p1(); +SHOW CREATE TABLE test.t2; +DROP TABLE test.t2; + +--echo # +--echo # This interprets "TYPE OF t1.a" as INT, as in test1.t1.a +--echo # + +USE test1; +CALL test.p1(); +SHOW CREATE TABLE test.t2; +DROP TABLE test.t2; + +--echo # +--echo # Error if there is no an active database +--echo # + +DROP DATABASE test1; +--error ER_NO_DB_ERROR +CALL test.p1(); + +USE test; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # A reference to a table in a non-existing database +--echo # +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF test1.t1.a; + CREATE TABLE t1 AS SELECT a; +END; +$$ +DELIMITER ;$$ +--error ER_NO_SUCH_TABLE +CALL p1; +DROP PROCEDURE p1; + + +--echo # +--echo # A reference to a table in a different database +--echo # +CREATE TABLE t1(a INT); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (a VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF t1.a; + DECLARE b TYPE OF test1.t1.a; + CREATE TABLE t2 AS SELECT a,b; +END; +$$ +DELIMITER ;$$ +CALL p1; +SHOW CREATE TABLE t2; +DROP PROCEDURE p1; +DROP TABLE t2; +DROP DATABASE test1; +DROP TABLE t1; + + +--echo # +--echo # Using a table before it appears in a TYPE OF declaration + multiple TYPE OF declarations +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 (a,b) VALUES (10,'b10'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + INSERT INTO t1 (a,b) VALUES (11, 'b11'); + SELECT * FROM t1; + BEGIN + DECLARE va TYPE OF t1.a DEFAULT 30; + DECLARE vb TYPE OF t1.b DEFAULT 'b30'; + INSERT INTO t1 (a,b) VALUES (12,'b12'); + SELECT * FROM t1; + INSERT INTO t1 (a,b) VALUES (va, vb); + SELECT * FROM t1; + END; + BEGIN + DECLARE va TYPE OF t1.a DEFAULT 40; + DECLARE vb TYPE OF t1.b DEFAULT 'b40'; + INSERT INTO t1 (a,b) VALUES (va,vb); + SELECT * FROM t1; + END; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # TYPE OF variables + TABLE vs VIEW +--echo # + +CREATE TABLE t1 ( + bit6 BIT(6), + bit7 BIT(7), + bit8 BIT(8), + i1 TINYINT, + i2 SMALLINT, + i3 MEDIUMINT, + i4 INT, + i8 BIGINT, + ff FLOAT, + fd DOUBLE, + cc CHAR(10), + cv VARCHAR(10), + cvu VARCHAR(10) CHARACTER SET utf8, + t1 TINYTEXT, + t2 TEXT, + t3 MEDIUMTEXT, + t4 LONGTEXT, + enum1 ENUM('a','b','c'), + set1 SET('a','b','c'), + blob1 TINYBLOB, + blob2 BLOB, + blob3 MEDIUMBLOB, + blob4 LONGBLOB, + yy YEAR, + dd DATE, + tm0 TIME, + tm3 TIME(3), + tm6 TIME(6), + dt0 DATETIME, + dt3 DATETIME(3), + dt6 DATETIME(6), + ts0 TIMESTAMP, + ts3 TIMESTAMP(3), + ts6 TIMESTAMP(6), + dc100 DECIMAL(10,0), + dc103 DECIMAL(10,3), + dc209 DECIMAL(20,9) +); + + +DELIMITER $$; +CREATE PROCEDURE p1(command enum('create','select')) +BEGIN + DECLARE bit6 TYPE OF t1.bit6 DEFAULT 0x30; + DECLARE bit7 TYPE OF t1.bit7 DEFAULT 0x41; + DECLARE bit8 TYPE OF t1.bit8 DEFAULT 0x7E; + DECLARE i1 TYPE OF t1.i1 DEFAULT 11; + DECLARE i2 TYPE OF t1.i2 DEFAULT 12; + DECLARE i3 TYPE OF t1.i3 DEFAULT 13; + DECLARE i4 TYPE OF t1.i4 DEFAULT 14; + DECLARE i8 TYPE OF t1.i8 DEFAULT 18; + DECLARE ff TYPE OF t1.ff DEFAULT 21; + DECLARE fd TYPE OF t1.fd DEFAULT 22; + DECLARE cc TYPE OF t1.cc DEFAULT 'char'; + DECLARE cv TYPE OF t1.cv DEFAULT 'varchar'; + DECLARE cvu TYPE OF t1.cvu DEFAULT 'varcharu8'; + DECLARE t1 TYPE OF t1.t1 DEFAULT 'text1'; + DECLARE t2 TYPE OF t1.t2 DEFAULT 'text2'; + DECLARE t3 TYPE OF t1.t3 DEFAULT 'text3'; + DECLARE t4 TYPE OF t1.t4 DEFAULT 'text4'; + DECLARE enum1 TYPE OF t1.enum1 DEFAULT 'b'; + DECLARE set1 TYPE OF t1.set1 DEFAULT 'a,c'; + DECLARE blob1 TYPE OF t1.blob1 DEFAULT 'blob1'; + DECLARE blob2 TYPE OF t1.blob2 DEFAULT 'blob2'; + DECLARE blob3 TYPE OF t1.blob3 DEFAULT 'blob3'; + DECLARE blob4 TYPE OF t1.blob4 DEFAULT 'blob4'; + DECLARE yy TYPE OF t1.yy DEFAULT 2001; + DECLARE dd TYPE OF t1.dd DEFAULT '2001-01-01'; + DECLARE tm0 TYPE OF t1.tm0 DEFAULT '00:00:01'; + DECLARE tm3 TYPE OF t1.tm3 DEFAULT '00:00:03.333'; + DECLARE tm6 TYPE OF t1.tm6 DEFAULT '00:00:06.666666'; + DECLARE dt0 TYPE OF t1.dt0 DEFAULT '2001-01-01 00:00:01'; + DECLARE dt3 TYPE OF t1.dt3 DEFAULT '2001-01-03 00:00:01.333'; + DECLARE dt6 TYPE OF t1.dt6 DEFAULT '2001-01-06 00:00:01.666666'; + DECLARE ts0 TYPE OF t1.ts0 DEFAULT '2002-01-01 00:00:01'; + DECLARE ts3 TYPE OF t1.ts3 DEFAULT '2002-01-03 00:00:01.333'; + DECLARE ts6 TYPE OF t1.ts6 DEFAULT '2002-01-06 00:00:01.666666'; + DECLARE dc100 TYPE OF t1.dc100 DEFAULT 10; + DECLARE dc103 TYPE OF t1.dc103 DEFAULT 10.123; + DECLARE dc209 TYPE OF t1.dc209 DEFAULT 10.123456789; + CASE + WHEN command='create' THEN + CREATE TABLE t2 AS SELECT + bit6, bit7, bit8, + i1,i2,i3,i4,i8, + ff,fd, dc100, dc103, dc209, + cc,cv,cvu, + t1,t2,t3,t4, + enum1, set1, + blob1, blob2, blob3, blob4, + dd, yy, + tm0, tm3, tm6, + dt0, dt3, dt6, + ts0, ts3, ts6; + WHEN command='select' THEN + SELECT + bit6, bit7, bit8, + i1,i2,i3,i4,i8, + ff,fd, dc100, dc103, dc209, + cc,cv,cvu, + t1,t2,t3,t4, + enum1, set1, + blob1, blob2, blob3, blob4, + dd, yy, + tm0, tm3, tm6, + dt0, dt3, dt6, + ts0, ts3, ts6; + END CASE; +END; +$$ +DELIMITER ;$$ + +--echo # +--echo # TABLE +--echo # +CALL p1('create'); +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; + +--disable_ps_protocol +--enable_metadata +--vertical_results +CALL p1('select'); +--horizontal_results +--disable_metadata +--enable_ps_protocol + +--echo # +--echo # VIEW +--echo # +ALTER TABLE t1 RENAME t0; +CREATE VIEW t1 AS SELECT * FROM t0; + +CALL p1('create'); +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; + +--disable_ps_protocol +--enable_metadata +--vertical_results +CALL p1('select'); +--horizontal_results +--disable_metadata +--enable_ps_protocol + +DROP VIEW t1; +DROP TABLE t0; + +DROP PROCEDURE p1; + +--echo # +--echo # VIEW with subqueries +--echo # +CREATE TABLE t1 (a INT,b INT); +INSERT INTO t1 VALUES (10,1),(20,2),(30,3),(40,4); +SELECT AVG(a) FROM t1; +CREATE VIEW v1 AS SELECT a,1 as b FROM t1 WHERE a>(SELECT AVG(a) FROM t1) AND b>(SELECT 1); +SELECT * FROM v1; +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF v1.a DEFAULT 10; + DECLARE b TYPE OF v1.b DEFAULT 1; + SELECT a,b; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; +DELIMITER $$; +CREATE FUNCTION f1() RETURNS INT +BEGIN + DECLARE a TYPE OF v1.a DEFAULT 10; + DECLARE b TYPE OF v1.b DEFAULT 1; + RETURN a+b; +END; +$$ +DELIMITER ;$$ +SELECT f1(); +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # +--echo # TYPE OF variables + INFORMATION_SCHEMA +--echo # +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE tables_table_name TYPE OF INFORMATION_SCHEMA.TABLES.TABLE_NAME; + DECLARE tables_table_rows TYPE OF INFORMATION_SCHEMA.TABLES.TABLE_ROWS; + DECLARE processlist_info TYPE OF INFORMATION_SCHEMA.PROCESSLIST.INFO; + DECLARE processlist_info_binary TYPE OF INFORMATION_SCHEMA.PROCESSLIST.INFO_BINARY; + CREATE TABLE t1 AS SELECT + tables_table_name, + tables_table_rows, + processlist_info, + processlist_info_binary; +END; +$$ +DELIMITER ;$$ +CALL p1(); +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # TYPE OF + Table structure change +--echo # Data type for both a0 and a1 is chosen in the very beginning +--echo # +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a0 TYPE OF t1.a; + ALTER TABLE t1 MODIFY a VARCHAR(10); -- This does not affect a1 + BEGIN + DECLARE a1 TYPE OF t1.a; + CREATE TABLE t2 AS SELECT a0, a1; + SHOW CREATE TABLE t2; + DROP TABLE t2; + END; +END +$$ +DELIMITER ;$$ +CREATE TABLE t1 (a INT); +CALL p1; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # TYPE OF in parameters +--echo # +CREATE TABLE t1 (a VARCHAR(10)); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (b SMALLINT); +DELIMITER $$; +CREATE PROCEDURE p1(a TYPE OF t1.a, b TYPE OF test1.t1.b) +BEGIN + CREATE TABLE t2 AS SELECT a, b; +END; +$$ +DELIMITER ;$$ +CALL p1('test', 123); +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP TABLE test1.t1; +DROP DATABASE test1; +DROP TABLE t1; + +--echo # +--echo # TYPE OF in a stored function variables and arguments +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1 (prm TYPE OF t1.a) RETURNS INT +BEGIN + DECLARE a TYPE OF t1.a DEFAULT prm; + RETURN a; +END; +$$ +DELIMITER ;$$ +SELECT f1(20); +DROP FUNCTION f1; +DROP TABLE t1; + + +--echo # +--echo # TYPE OF in function RETURN clause is not supported yet +--echo # +DELIMITER $$; +--error ER_PARSE_ERROR +CREATE FUNCTION f1() RETURN TYPE OF t1.a +BEGIN + RETURN 0; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Using TYPE OF variables as FETCH targets +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3)); +INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123'); +INSERT INTO t1 VALUES (2,'b2','2001-01-02 10:20:30.123'); +CREATE TABLE t2 LIKE t1; +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE done INT DEFAULT 0; + DECLARE v_a TYPE OF t1.a; + DECLARE v_b TYPE OF t1.b; + DECLARE v_c TYPE OF t1.c; + DECLARE c CURSOR FOR SELECT a,b,c FROM t1; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; + OPEN c; + read_loop: LOOP + FETCH c INTO v_a, v_b, v_c; + IF done THEN + LEAVE read_loop; + END IF; + INSERT INTO t2 (a,b,c) VALUES (v_a, v_b, v_c); + END LOOP; + CLOSE c; +END; +$$ +DELIMITER ;$$ +CALL p1(); +SELECT * FROM t2; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Using TYPE OF variables as SELECT INTO targets +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3)); +INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123'); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE v_a TYPE OF t1.a; + DECLARE v_b TYPE OF t1.b; + DECLARE v_c TYPE OF t1.c; + SELECT * FROM t1 INTO v_a, v_b, v_c; + SELECT v_a, v_b, v_c; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # diff --git a/mysql-test/t/sp-security-anchor-type.test b/mysql-test/t/sp-security-anchor-type.test new file mode 100644 index 00000000000..e61d5e38052 --- /dev/null +++ b/mysql-test/t/sp-security-anchor-type.test @@ -0,0 +1,328 @@ +--source include/not_embedded.inc + +--echo # +--echo # MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # + + +--echo # +--echo # Initiation: +--echo # - creating database db1 +--echo # - creating user user1 with access rights to db1 +--echo # + +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT, b VARCHAR(10)); + +CREATE USER user1; + +GRANT ALL PRIVILEGES ON test.* TO user1; + +connect (conn1,localhost,user1,,test); + +SELECT database(); +SELECT user(); + +--echo # +--echo # Making sure that user1 does not have privileges to db1.t1 +--echo # + +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE db1.t1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW FIELDS IN db1.t1; + + +--echo # +--echo # Trigger: using TYPE OF with a table we don't have access to +--echo # +CREATE TABLE test.t1 (a INT, b INT); +INSERT INTO test.t1 (a,b) VALUES (10,20); +SELECT * FROM t1; +DELIMITER $$; +CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW +BEGIN + DECLARE b TYPE OF db1.t1.b DEFAULT 20; + SET NEW.b = 10; +END +$$ +DELIMITER ;$$ +--error ER_TABLEACCESS_DENIED_ERROR +INSERT INTO t1 (a) VALUES (10); +SELECT * FROM t1; +DROP TRIGGER tr1; +DROP TABLE t1; + + +--echo # +--echo # Stored procedure: Using TYPE OF for with a table that we don't have access to +--echo # DEFINER user1, SQL SECURITY DEFAULT +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF db1.t1.a DEFAULT 10; + SELECT a; +END; +$$ +DELIMITER ;$$ +--error ER_TABLEACCESS_DENIED_ERROR +CALL p1; +DROP PROCEDURE p1; + +#DELIMITER $$; +#CREATE PROCEDURE p1() +#BEGIN +# DECLARE a ROW TYPE OF db1.t1; +# SELECT a.a; +#END; +#$$ +#DELIMITER ;$$ +#--error ER_TABLEACCESS_DENIED_ERROR +#CALL p1; +#DROP PROCEDURE p1; + + +--echo # +--echo # Stored procedure: Using TYPE OF for with a table that we don't have access to +--echo # DEFINER root, SQL SECURITY INVOKER +--echo # + +connection default; +DELIMITER $$; +CREATE PROCEDURE p1() +SQL SECURITY INVOKER +BEGIN + DECLARE a TYPE OF db1.t1.a DEFAULT 10; + SELECT a; +END; +$$ +DELIMITER ;$$ +connection conn1; +--error ER_TABLEACCESS_DENIED_ERROR +CALL p1; +DROP PROCEDURE p1; + + +connection default; +DELIMITER $$; +CREATE PROCEDURE p1() +SQL SECURITY INVOKER +BEGIN + DECLARE a ROW TYPE OF db1.t1; + SELECT a.a; +END; +$$ +DELIMITER ;$$ +connection conn1; +--error ER_TABLEACCESS_DENIED_ERROR +CALL p1; +DROP PROCEDURE p1; + + +--echo # +--echo # Stored procedure: Using TYPE OF for with a table that we don't have access to +--echo # DEFINER root, SQL SECURITY DEFINER +--echo # + +connection default; +DELIMITER $$; +CREATE PROCEDURE p1() +SQL SECURITY DEFINER +BEGIN + DECLARE a TYPE OF db1.t1.a DEFAULT 10; + SELECT a; +END; +$$ +DELIMITER ;$$ +connection conn1; +CALL p1; +DROP PROCEDURE p1; + +connection default; +DELIMITER $$; +CREATE PROCEDURE p1() +SQL SECURITY DEFINER +BEGIN + DECLARE a ROW TYPE OF db1.t1; + SET a.a= 10; + SELECT a.a; +END; +$$ +DELIMITER ;$$ +connection conn1; +CALL p1; +DROP PROCEDURE p1; + + +--echo # +--echo # Stored function: Using TYPE OF for with a table that we don't have access to +--echo # DEFINER user1, SQL SECURITY DEFAULT +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1() RETURNS INT +BEGIN + DECLARE a TYPE OF db1.t1.a DEFAULT 0; + RETURN OCTET_LENGTH(a); +END; +$$ +DELIMITER ;$$ +--error ER_TABLEACCESS_DENIED_ERROR +SELECT f1(); +DROP FUNCTION f1; +DROP TABLE t1; + + +--echo # +--echo # Stored function: Using TYPE OF for with a table that we don't have access to +--echo # DEFINER root, SQL SECURITY INVOKER +--echo # + +connection default; +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1() RETURNS INT +SQL SECURITY INVOKER +BEGIN + DECLARE a TYPE OF db1.t1.a DEFAULT 0; + RETURN OCTET_LENGTH(a); +END; +$$ +DELIMITER ;$$ +connection conn1; +--error ER_TABLEACCESS_DENIED_ERROR +SELECT f1(); +DROP FUNCTION f1; +DROP TABLE t1; + + +--echo # +--echo # Stored function: Using TYPE OF for with a table that we don't have access to +--echo # DEFINER root, SQL SECURITY DEFINER +--echo # + +connection default; +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE FUNCTION f1() RETURNS INT +SQL SECURITY DEFINER +BEGIN + DECLARE a TYPE OF db1.t1.a DEFAULT 0; + RETURN OCTET_LENGTH(a); +END; +$$ +DELIMITER ;$$ +connection conn1; +SELECT f1(); +DROP FUNCTION f1; +DROP TABLE t1; + + +connection default; +GRANT SELECT (a) ON db1.t1 TO user1; +connection conn1; + +--echo # +--echo # Making sure that user1 has access to db1.t1.a, but not to db1.t1.b +--echo # + +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE TABLE db1.t1; +SHOW FIELDS IN db1.t1; + +--echo # +--echo # Trigger: Per-column privileges +--echo # +CREATE TABLE test.t1 (a INT, b INT); +INSERT INTO test.t1 (a,b) VALUES (10,20); +SELECT * FROM t1; +# TYPE OF reference using a column we have access to +DELIMITER $$; +CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW +BEGIN + DECLARE a TYPE OF db1.t1.a DEFAULT 20; + BEGIN + SET NEW.b := 10; + END; +END +$$ +DELIMITER ;$$ +INSERT INTO t1 (a) VALUES (10); +SELECT * FROM t1; +DROP TRIGGER tr1; +# TYPE OF reference using a column that we don't have access to +DELIMITER $$; +CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW +BEGIN + DECLARE b TYPE OF db1.t1.b DEFAULT 20; + BEGIN + SET NEW.b = 10; + END; +END +$$ +DELIMITER ;$$ +--error ER_COLUMNACCESS_DENIED_ERROR +INSERT INTO t1 (a) VALUES (10); +SELECT * FROM t1; +DROP TRIGGER tr1; +DROP TABLE t1; + + + +--echo # +--echo # Stored procedure: Per-column privileges +--echo # DEFINER user1, SQL SECURITY DEFAULT +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE a TYPE OF db1.t1.a DEFAULT 10; + SELECT a; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE b TYPE OF db1.t1.b DEFAULT 10; + SELECT b; +END; +$$ +DELIMITER ;$$ +--error ER_COLUMNACCESS_DENIED_ERROR +CALL p1; +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE b ROW TYPE OF db1.t1; + SET b.b=10; + SELECT b.b; +END; +$$ +DELIMITER ;$$ +--error ER_COLUMNACCESS_DENIED_ERROR +CALL p1; +DROP PROCEDURE p1; + + +--echo # +--echo # Clean up +--echo # +disconnect conn1; +connection default; + +DROP USER user1; +DROP DATABASE db1; + +--echo # +--echo # End of MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 83b6966c676..d6eca47c0d8 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2676,3 +2676,26 @@ select trigger_name, action_order, created from information_schema.triggers where event_object_table = 't1' and trigger_schema='test'; drop table t1; set time_zone= @@global.time_zone; + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12461 TYPE OF and ROW TYPE OF anchored data types +--echo # + +CREATE TABLE t1 (a INT, b INT, total INT); +DELIMITER $$; +CREATE TRIGGER tr1 BEFORE INSERT ON t1 +FOR EACH ROW +BEGIN + DECLARE va TYPE OF t1.a DEFAULT NEW.a; + DECLARE vb TYPE OF t1.b DEFAULT NEW.b; + SET NEW.total:= va + vb; +END; +$$ +DELIMITER ;$$ +INSERT INTO t1 (a,b) VALUES (10, 20); +SELECT * FROM t1; +DROP TABLE t1; diff --git a/sql/lex.h b/sql/lex.h index 2a30f7f31ad..9918683e60b 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -423,6 +423,7 @@ static SYMBOL symbols[] = { { "NUMBER", SYM(NUMBER_SYM)}, { "NUMERIC", SYM(NUMERIC_SYM)}, { "NVARCHAR", SYM(NVARCHAR_SYM)}, + { "OF", SYM(OF_SYM)}, { "OFFSET", SYM(OFFSET_SYM)}, { "OLD_PASSWORD", SYM(OLD_PASSWORD_SYM)}, { "ON", SYM(ON)}, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index e93c9069b4e..869323d03cc 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -799,6 +799,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr) String *string; TABLE_LIST *table_list; Table_ident *table; + Qualified_column_ident *qualified_column_ident; char *simple_string; const char *const_simple_string; chooser_compare_func_creator boolfunc2creator; @@ -1274,6 +1275,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token NUMERIC_SYM /* SQL-2003-R */ %token NTH_VALUE_SYM /* SQL-2011 */ %token NVARCHAR_SYM +%token OF_SYM /* SQL-1992-R, Oracle-R */ %token OFFSET_SYM %token OLD_PASSWORD_SYM %token ON /* SQL-2003-R */ @@ -1592,6 +1594,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); opt_component key_cache_name sp_opt_label BIN_NUM label_ident TEXT_STRING_filesystem ident_or_empty opt_constraint constraint opt_ident + sp_decl_ident sp_block_label %type <lex_string_with_metadata> @@ -1608,6 +1611,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); table_ident table_ident_nodb references xid table_ident_opt_wild create_like +%type <qualified_column_ident> + qualified_column_ident + optionally_qualified_column_ident + %type <simple_string> remember_name remember_end opt_db remember_tok_start wild_and_where @@ -1883,9 +1890,10 @@ END_OF_INPUT %type <NONE> case_stmt_specification %type <NONE> loop_body while_body repeat_body -%type <num> sp_decl_idents sp_handler_type sp_hcond_list +%type <num> sp_decl_idents sp_decl_idents_init_vars +%type <num> sp_handler_type sp_hcond_list %type <spcondvalue> sp_cond sp_hcond sqlstate signal_value opt_signal_value -%type <spblock> sp_decls sp_decl sp_decl_body +%type <spblock> sp_decls sp_decl sp_decl_body sp_decl_variable_list %type <spname> sp_name %type <spvar> sp_param_name sp_param_name_and_type %type <spvar_mode> sp_opt_inout @@ -1909,7 +1917,7 @@ END_OF_INPUT %type <cond_info_list> condition_information; %type <spvar_definition> row_field_name row_field_definition -%type <spvar_definition_list> row_field_definition_list field_type_row +%type <spvar_definition_list> row_field_definition_list row_type_body %type <NONE> opt_window_clause window_def_list window_def window_spec %type <lex_str_ptr> window_name @@ -2864,13 +2872,17 @@ sp_param_name_and_type: if (Lex->sp_param_fill_definition($$= $1)) MYSQL_YYABORT; } - | sp_param_name field_type_row + | sp_param_name TYPE_SYM OF_SYM qualified_column_ident + { + Lex->sphead->fill_spvar_using_type_reference($$= $1, $4); + } + | sp_param_name ROW_SYM row_type_body { $$= $1; $$->field_def.field_name= $$->name.str; Lex->sphead->fill_spvar_definition(thd, &$$->field_def); - Lex->sphead->row_fill_field_definitions(thd, $2); - $$->field_def.set_row_field_definitions($2); + Lex->sphead->row_fill_field_definitions(thd, $3); + $$->field_def.set_row_field_definitions($3); } ; @@ -2950,6 +2962,40 @@ sp_decl: DECLARE_SYM sp_decl_body { $$= $2; } ; + +qualified_column_ident: + 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; + } + ; + +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 { @@ -2983,36 +3029,56 @@ row_field_definition_list: } ; -field_type_row: - ROW_SYM '(' row_field_definition_list ')' { $$= $3; } +row_type_body: + '(' row_field_definition_list ')' { $$= $2; } ; - -sp_decl_body: +sp_decl_idents_init_vars: sp_decl_idents { Lex->sp_variable_declarations_init(thd, $1); } + ; + +sp_decl_variable_list: + sp_decl_idents_init_vars type_with_opt_collate sp_opt_default { if (Lex->sp_variable_declarations_finalize(thd, $1, - &Lex->last_field[0], $4)) + &Lex->last_field[0], $3)) MYSQL_YYABORT; $$.init_using_vars($1); } - | sp_decl_idents + | sp_decl_idents_init_vars + TYPE_SYM OF_SYM optionally_qualified_column_ident + sp_opt_default { - Lex->sp_variable_declarations_init(thd, $1); + if (Lex->sp_variable_declarations_with_ref_finalize(thd, $1, $4, $5)) + MYSQL_YYABORT; + $$.init_using_vars($1); } - field_type_row + | sp_decl_idents_init_vars + ROW_SYM TYPE_SYM OF_SYM optionally_qualified_column_ident + sp_opt_default + { + if (Lex->sp_variable_declarations_rowtype_finalize(thd, $1, $5, $6)) + MYSQL_YYABORT; + $$.init_using_vars($1); + } + | sp_decl_idents_init_vars + ROW_SYM row_type_body sp_opt_default { if (Lex->sp_variable_declarations_row_finalize(thd, $1, $3, $4)) MYSQL_YYABORT; $$.init_using_vars($1); } - | ident CONDITION_SYM FOR_SYM sp_cond + ; + +sp_decl_body: + sp_decl_variable_list + | sp_decl_ident CONDITION_SYM FOR_SYM sp_cond { if (Lex->spcont->declare_condition(thd, $1, $4)) MYSQL_YYABORT; @@ -3031,7 +3097,7 @@ sp_decl_body: $$.vars= $$.conds= $$.curs= 0; $$.hndlrs= 1; } - | ident CURSOR_SYM + | sp_decl_ident CURSOR_SYM { Lex->sp_block_init(thd); } @@ -3448,8 +3514,12 @@ condition_information_item_name: { $$= Condition_information_item::RETURNED_SQLSTATE; } ; +sp_decl_ident: + ident { $$= $1; } + ; + sp_decl_idents: - ident + sp_decl_ident { /* NOTE: field definition is filled in sp_decl section. */ @@ -14445,6 +14515,7 @@ keyword_sp: | NOTFOUND_SYM {} | NUMBER_SYM {} | NVARCHAR_SYM {} + | OF_SYM {} /* SQL-1999-R, Oracle-R */ | OFFSET_SYM {} | OLD_PASSWORD_SYM {} | ONE_SYM {} diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 42fc7e90fc2..791e7a386c9 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -690,6 +690,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %token NUMERIC_SYM /* SQL-2003-R */ %token NTH_VALUE_SYM /* SQL-2011 */ %token NVARCHAR_SYM +%token OF_SYM /* SQL-1992-R, Oracle-R */ %token OFFSET_SYM %token OLD_PASSWORD_SYM %token ON /* SQL-2003-R */ @@ -14769,6 +14770,7 @@ keyword_sp_not_data_type: | NODEGROUP_SYM {} | NONE_SYM {} | NOTFOUND_SYM {} + | OF_SYM {} /* SQL-1999-R, Oracle-R */ | OFFSET_SYM {} | OLD_PASSWORD_SYM {} | ONE_SYM {} |