1 files changed, 485 insertions, 0 deletions
diff --git a/mysql-test/main/metadata.test b/mysql-test/main/metadata.test
new file mode 100644
index 00000000000..96189a9750a
--- /dev/null
+++ b/mysql-test/main/metadata.test
@@ -0,0 +1,485 @@
+#
+# Test metadata
+#
+
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+--enable_metadata
+# PS protocol gives slightly different metadata
+--disable_ps_protocol
+
+#
+# First some simple tests
+#
+
+select 1, 1.0, -1, "hello", NULL;
+SELECT
+  1 AS c1,
+  11 AS c2,
+  111 AS c3,
+  1111 AS c4,
+  11111 AS c5,
+  111111 AS c6,
+  1111111 AS c7,
+  11111111 AS c8,
+  111111111 AS c9,
+  1111111111 AS c10,
+  11111111111 AS c11,
+  111111111111 AS c12,
+  1111111111111 AS c13,
+  11111111111111 AS c14,
+  111111111111111 AS c15,
+  1111111111111111 AS c16,
+  11111111111111111 AS c17,
+  111111111111111111 AS c18,
+  1111111111111111111 AS c19,
+  11111111111111111111 AS c20,
+  111111111111111111111 AS c21;
+
+SELECT
+  -1 AS c1,
+  -11 AS c2,
+  -111 AS c3,
+  -1111 AS c4,
+  -11111 AS c5,
+  -111111 AS c6,
+  -1111111 AS c7,
+  -11111111 AS c8,
+  -111111111 AS c9,
+  -1111111111 AS c10,
+  -11111111111 AS c11,
+  -111111111111 AS c12,
+  -1111111111111 AS c13,
+  -11111111111111 AS c14,
+  -111111111111111 AS c15,
+  -1111111111111111 AS c16,
+  -11111111111111111 AS c17,
+  -111111111111111111 AS c18,
+  -1111111111111111111 AS c19,
+  -11111111111111111111 AS c20,
+  -111111111111111111111 AS c21;
+
+create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10));
+select * from t1;
+select a b, b c from t1 as t2;
+drop table t1;
+
+#
+# Test metadata from ORDER BY (Bug #2654)
+#
+
+CREATE TABLE t1 (id tinyint(3) default NULL, data varchar(255) default NULL);
+INSERT INTO t1 VALUES (1,'male'),(2,'female');
+CREATE TABLE t2 (id tinyint(3) unsigned default NULL, data char(3) default '0');
+INSERT INTO t2 VALUES (1,'yes'),(2,'no');
+
+select t1.id, t1.data, t2.data from t1, t2 where t1.id = t2.id;
+select t1.id, t1.data, t2.data from t1, t2 where t1.id = t2.id order by t1.id;
+select t1.id from t1 union select t2.id from t2;
+drop table t1,t2;
+
+#
+# variables union and derived tables metadata test
+#
+create table t1 ( a int, b varchar(30), primary key(a));
+insert into t1 values (1,'one');
+insert into t1 values (2,'two');
+set @arg00=1 ;
+select @arg00 FROM t1 where a=1 union distinct select 1 FROM t1 where a=1;
+select * from (select @arg00) aaa;
+select 1 union select 1;
+select * from (select 1 union select 1) aaa;
+drop table t1;
+
+--disable_metadata
+
+#
+# Bug #11688: Bad mysql_info() results in multi-results
+#
+--enable_info
+delimiter //;
+create table t1 (i int);
+insert into t1 values (1),(2),(3);
+select * from t1 where i = 2;
+drop table t1;//
+delimiter ;//
+--disable_info
+
+#
+# Bug #20191: getTableName gives wrong or inconsistent result when using VIEWs
+#
+--enable_metadata
+create table t1 (id int(10));
+insert into t1 values (1);
+CREATE  VIEW v1 AS select t1.id as id from t1;
+CREATE  VIEW v2 AS select t1.id as renamed from t1;
+CREATE  VIEW v3 AS select t1.id + 12 as renamed from t1;
+select * from v1 group by id limit 1;
+select * from v1 group by id limit 0;
+select * from v1 where id=1000 group by id;
+select * from v1 where id=1 group by id;
+select * from v2 where renamed=1 group by renamed;
+select * from v3 where renamed=1 group by renamed;
+drop table t1;
+drop view v1,v2,v3;
+--disable_metadata
+
+# End of 4.1 tests
+
+#
+# Bug #28492: subselect returns LONG in >5.0.24a and LONGLONG in <=5.0.24a
+#
+--enable_metadata
+select a.* from (select 2147483648 as v_large) a;
+select a.* from (select 214748364 as v_small) a;
+--disable_metadata
+
+#
+# Bug #28898: table alias and database name of VIEW columns is empty in the
+# metadata of # SELECT statement where join is executed via temporary table.
+#
+
+CREATE TABLE t1 (c1 CHAR(1));
+CREATE TABLE t2 (c2 CHAR(1));
+CREATE VIEW v1 AS SELECT t1.c1 FROM t1;
+CREATE VIEW v2 AS SELECT t2.c2 FROM t2;
+INSERT INTO t1 VALUES ('1'), ('2'), ('3');
+INSERT INTO t2 VALUES ('1'), ('2'), ('3'), ('2');
+
+--enable_metadata
+SELECT v1.c1 FROM v1 JOIN t2 ON c1=c2 ORDER BY 1;
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2;
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2 GROUP BY v1.c1;
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2 GROUP BY v1.c1 ORDER BY v2.c2;
+--disable_metadata
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2;
+
+#
+# Bug #39283: Date returned as VARBINARY to client for queries
+#             with COALESCE and JOIN
+#
+
+CREATE TABLE t1 (i INT, d DATE);
+INSERT INTO t1 VALUES (1, '2008-01-01'), (2, '2008-01-02'), (3, '2008-01-03');
+
+--enable_metadata
+--sorted_result
+SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d),
+       CASE i WHEN i THEN d ELSE d END, GREATEST(d, d), LEAST(d, d)
+  FROM t1 ORDER BY RAND(); # force filesort
+--disable_metadata
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#41788 mysql_fetch_field returns org_table == table by a view
+--echo #
+
+CREATE TABLE t1 (f1 INT);
+CREATE VIEW v1 AS SELECT f1 FROM t1;
+--enable_metadata
+SELECT f1 FROM v1 va;
+--disable_metadata
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo End of 5.0 tests
+
+# Verify that column metadata is correct for all possible data types.
+# Originally about BUG#42980 "Client doesn't set NUM_FLAG for DECIMAL"
+
+create table t1(
+# numeric types
+bool_col bool,
+boolean_col boolean,
+bit_col bit(5),
+tiny tinyint,
+tiny_uns tinyint unsigned,
+small smallint,
+small_uns smallint unsigned,
+medium mediumint,
+medium_uns mediumint unsigned,
+int_col int,
+int_col_uns int unsigned,
+big bigint,
+big_uns bigint unsigned,
+decimal_col decimal(10,5),
+# synonyms of DECIMAL
+numeric_col numeric(10),
+fixed_col fixed(10),
+dec_col dec(10),
+decimal_col_uns decimal(10,5) unsigned,
+fcol float,
+fcol_uns float unsigned,
+dcol double,
+double_precision_col double precision,
+dcol_uns double unsigned,
+# date/time types
+date_col date,
+time_col time,
+timestamp_col timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+year_col year,
+datetime_col datetime,
+# string types
+char_col char(5),
+varchar_col varchar(10),
+binary_col binary(10),
+varbinary_col varbinary(10),
+tinyblob_col tinyblob,
+blob_col blob,
+mediumblob_col mediumblob,
+longblob_col longblob,
+text_col text,
+mediumtext_col mediumtext,
+longtext_col longtext,
+enum_col enum("A","B","C"),
+set_col set("F","E","D")
+);
+
+--enable_metadata
+select * from t1;
+--disable_metadata
+
+drop table t1;
+
+#
+# lp:740173 5.1-micro reports incorrect Length metadata for TIME expressions
+#
+--enable_metadata
+select cast('01:01:01' as time), cast('01:01:01' as time(2));
+--disable_metadata
+
+
+--echo #
+--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
+--echo #
+
+--enable_metadata
+SELECT
+  STRCMP('a','b'),
+  OCTET_LENGTH('a'),
+  CHAR_LENGTH('a'),
+  COERCIBILITY('a'),
+  ASCII('a'),
+  ORD('a'),
+  CRC32('a'),
+  UNCOMPRESSED_LENGTH(COMPRESS('a'));
+
+SELECT
+  INTERVAL(2,1,2,3),
+  REGEXP_INSTR('a','a'),
+  LOCATE('a','a'),
+  FIND_IN_SET('b','a,b,c,d'),
+  FIELD('a','a','b');
+
+SELECT
+  SIGN(1),
+  BIT_COUNT(1);
+
+
+SELECT
+  BENCHMARK(0,0),
+  SLEEP(0);
+
+SELECT
+  GET_LOCK('metadata',0),
+  IS_FREE_LOCK('metadata'),
+  RELEASE_LOCK('metadata');
+
+# Metadata the following functions is not deterministic
+#SELECT CONNECTION_ID();
+#SELECT IS_FREE_LOCK('metadata');
+#SELECT UUID_SHORT();
+
+
+SELECT
+  PERIOD_ADD(200801,2),
+  PERIOD_DIFF(200802,200703),
+  TO_DAYS('2007-10-07'),
+  DAYOFMONTH('2007-02-03'),
+  DAYOFWEEK('2007-02-03'),
+  TO_SECONDS('2013-06-13');
+
+SELECT
+  YEAR('2001-02-03 04:05:06.000007'),
+  DAY('2001-02-03 04:05:06.000007'),
+  HOUR('2001-02-03 04:05:06.000007'),
+  MINUTE('2001-02-03 04:05:06.000007'),
+  SECOND('2001-02-03 04:05:06.000007'),
+  MICROSECOND('2001-02-03 04:05:06.000007');
+
+SELECT
+  WEEK('2001-02-03 04:05:06.000007'),
+  QUARTER('2001-02-03 04:05:06.000007'),
+  YEARWEEK('2001-02-03 04:05:06.000007');
+
+--disable_metadata
+
+--enable_metadata
+SELECT BIT_LENGTH(10);
+SELECT 1|2, 1&2, 1<<2, 1>>2, ~0, 1^2;
+SELECT LAST_INSERT_ID();
+SELECT ROW_COUNT(), FOUND_ROWS();
+SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+--disable_metadata
+
+
+--echo #
+--echo # MDEV-12856 Wrong result set metadata for DIV
+--echo #
+
+--enable_metadata
+SELECT
+  2 DIV 1 AS d0l,
+  222222222 DIV 1 AS d09,
+  2222222222 DIV 1 AS d10;
+--disable_metadata
+
+
+--echo #
+--echo # MDEV-12862 Data type of @a:=1e0 depends on the session character set
+--echo #
+--enable_metadata
+SET NAMES utf8;
+CREATE TABLE t1 AS SELECT @:=1e0;
+SELECT * FROM t1;
+DROP TABLE t1;
+SET NAMES latin1;
+CREATE TABLE t1 AS SELECT @:=1e0;
+SELECT * FROM t1;
+DROP TABLE t1;
+--disable_metadata
+
+--echo #
+--echo # MDEV-12869 Wrong metadata for integer additive and multiplicative operators
+--echo #
+
+--enable_metadata
+SELECT
+  1+1,
+  11+1,
+  111+1,
+  1111+1,
+  11111+1,
+  111111+1,
+  1111111+1,
+  11111111+1,
+  111111111+1 LIMIT 0;
+
+SELECT
+  1-1,
+  11-1,
+  111-1,
+  1111-1,
+  11111-1,
+  111111-1,
+  1111111-1,
+  11111111-1,
+  111111111-1 LIMIT 0;
+
+SELECT
+  1*1,
+  11*1,
+  111*1,
+  1111*1,
+  11111*1,
+  111111*1,
+  1111111*1,
+  11111111*1,
+  111111111*1 LIMIT 0;
+
+SELECT
+  1 MOD 1,
+  11 MOD 1,
+  111 MOD 1,
+  1111 MOD 1,
+  11111 MOD 1,
+  111111 MOD 1,
+  1111111 MOD 1,
+  11111111 MOD 1,
+  111111111 MOD 1,
+  1111111111 MOD 1,
+  11111111111 MOD 1 LIMIT 0;
+
+SELECT
+  -(1),
+  -(11),
+  -(111),
+  -(1111),
+  -(11111),
+  -(111111),
+  -(1111111),
+  -(11111111),
+  -(111111111) LIMIT 0;
+
+SELECT
+  ABS(1),
+  ABS(11),
+  ABS(111),
+  ABS(1111),
+  ABS(11111),
+  ABS(111111),
+  ABS(1111111),
+  ABS(11111111),
+  ABS(111111111),
+  ABS(1111111111) LIMIT 0;
+
+SELECT
+  CEILING(1),
+  CEILING(11),
+  CEILING(111),
+  CEILING(1111),
+  CEILING(11111),
+  CEILING(111111),
+  CEILING(1111111),
+  CEILING(11111111),
+  CEILING(111111111),
+  CEILING(1111111111) LIMIT 0;
+
+SELECT
+  FLOOR(1),
+  FLOOR(11),
+  FLOOR(111),
+  FLOOR(1111),
+  FLOOR(11111),
+  FLOOR(111111),
+  FLOOR(1111111),
+  FLOOR(11111111),
+  FLOOR(111111111),
+  FLOOR(1111111111) LIMIT 0;
+
+SELECT
+  ROUND(1),
+  ROUND(11),
+  ROUND(111),
+  ROUND(1111),
+  ROUND(11111),
+  ROUND(111111),
+  ROUND(1111111),
+  ROUND(11111111),
+  ROUND(111111111),
+  ROUND(1111111111) LIMIT 0;
+
+--disable_metadata
+
+--echo #
+--echo # MDEV-12546 Wrong metadata or data type for string user variables
+--echo #
+SET @a='test';
+--enable_metadata
+SELECT @a;
+--disable_metadata
+CREATE TABLE t1 AS SELECT @a;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--enable_metadata
+SELECT @b1:=10, @b2:=@b2:=111111111111;
+--disable_metadata
+CREATE TABLE t1 AS SELECT @b1:=10, @b2:=111111111111;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;