diff options
Diffstat (limited to 'mysql-test/main/sp-anchor-type.result')
-rw-r--r-- | mysql-test/main/sp-anchor-type.result | 1073 |
1 files changed, 1073 insertions, 0 deletions
diff --git a/mysql-test/main/sp-anchor-type.result b/mysql-test/main/sp-anchor-type.result new file mode 100644 index 00000000000..b2de14bca1c --- /dev/null +++ b/mysql-test/main/sp-anchor-type.result @@ -0,0 +1,1073 @@ +# +# 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 +# +# +# MDEV-14139 Anchored data types for variables +# +BEGIN NOT ATOMIC +DECLARE a TYPE OF a; +END; +$$ +ERROR 42000: Undeclared variable: a +BEGIN NOT ATOMIC +DECLARE int11 INT; +DECLARE dec103 DECIMAL(10,3); +DECLARE flt0 FLOAT; +DECLARE dbl0 DOUBLE; +DECLARE enum0 ENUM('a','b'); +DECLARE bit3 BIT(3); +DECLARE varchar10 VARCHAR(10); +DECLARE text1 TEXT; +DECLARE tinytext1 TINYTEXT; +DECLARE mediumtext1 MEDIUMTEXT; +DECLARE longtext1 LONGTEXT; +DECLARE time3 TIME(3); +DECLARE datetime4 DATETIME(4); +DECLARE timestamp5 TIMESTAMP(5); +DECLARE date0 DATE; +DECLARE a_int11 TYPE OF int11; +DECLARE a_dec103 TYPE OF dec103; +DECLARE a_flt0 TYPE OF flt0; +DECLARE a_dbl0 TYPE OF dbl0; +DECLARE a_bit3 TYPE OF bit3; +DECLARE a_enum0 TYPE OF enum0; +DECLARE a_varchar10 TYPE OF varchar10; +DECLARE a_text1 TYPE OF text1; +DECLARE a_tinytext1 TYPE OF tinytext1; +DECLARE a_mediumtext1 TYPE OF mediumtext1; +DECLARE a_longtext1 TYPE OF longtext1; +DECLARE a_time3 TYPE OF time3; +DECLARE a_datetime4 TYPE OF datetime4; +DECLARE a_timestamp5 TYPE OF timestamp5; +DECLARE a_date0 TYPE OF date0; +DECLARE aa_int11 TYPE OF a_int11; +DECLARE aa_dec103 TYPE OF a_dec103; +DECLARE aa_flt0 TYPE OF a_flt0; +DECLARE aa_dbl0 TYPE OF a_dbl0; +DECLARE aa_bit3 TYPE OF a_bit3; +DECLARE aa_enum0 TYPE OF a_enum0; +DECLARE aa_varchar10 TYPE OF a_varchar10; +DECLARE aa_text1 TYPE OF a_text1; +DECLARE aa_tinytext1 TYPE OF a_tinytext1; +DECLARE aa_mediumtext1 TYPE OF a_mediumtext1; +DECLARE aa_longtext1 TYPE OF a_longtext1; +DECLARE aa_time3 TYPE OF a_time3; +DECLARE aa_datetime4 TYPE OF a_datetime4; +DECLARE aa_timestamp5 TYPE OF a_timestamp5; +DECLARE aa_date0 TYPE OF a_date0; +CREATE TABLE t1 AS +SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3, +a_enum0,a_varchar10, +a_text1,a_tinytext1,a_mediumtext1,a_longtext1, +a_time3,a_datetime4,a_timestamp5,a_date0; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 AS +SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3, +aa_enum0,aa_varchar10, +aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1, +aa_time3,aa_datetime4,aa_timestamp5,aa_date0; +SHOW CREATE TABLE t1; +DROP TABLE t1; +END; +$$ +Table Create Table +t1 CREATE TABLE `t1` ( + `a_int11` int(11) DEFAULT NULL, + `a_dec103` decimal(10,3) DEFAULT NULL, + `a_flt0` float DEFAULT NULL, + `a_dbl0` double DEFAULT NULL, + `a_bit3` bit(3) DEFAULT NULL, + `a_enum0` varchar(1) DEFAULT NULL, + `a_varchar10` varchar(10) DEFAULT NULL, + `a_text1` text DEFAULT NULL, + `a_tinytext1` tinytext DEFAULT NULL, + `a_mediumtext1` mediumtext DEFAULT NULL, + `a_longtext1` longtext DEFAULT NULL, + `a_time3` time(3) DEFAULT NULL, + `a_datetime4` datetime(4) DEFAULT NULL, + `a_timestamp5` timestamp(5) NULL DEFAULT NULL, + `a_date0` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +Table Create Table +t1 CREATE TABLE `t1` ( + `aa_int11` int(11) DEFAULT NULL, + `aa_dec103` decimal(10,3) DEFAULT NULL, + `aa_flt0` float DEFAULT NULL, + `aa_dbl0` double DEFAULT NULL, + `aa_bit3` bit(3) DEFAULT NULL, + `aa_enum0` varchar(1) DEFAULT NULL, + `aa_varchar10` varchar(10) DEFAULT NULL, + `aa_text1` text DEFAULT NULL, + `aa_tinytext1` tinytext DEFAULT NULL, + `aa_mediumtext1` mediumtext DEFAULT NULL, + `aa_longtext1` longtext DEFAULT NULL, + `aa_time3` time(3) DEFAULT NULL, + `aa_datetime4` datetime(4) DEFAULT NULL, + `aa_timestamp5` timestamp(5) NULL DEFAULT NULL, + `aa_date0` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 |