mariadb/mysql-test/main/metadata.test

#
# Test metadata
#
#View protocol gives slightly different metadata
--source include/no_view_protocol.inc

--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

--echo #
--echo # End of 4.1 tests
--echo #

#
# 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 #
--echo # End of 5.0 tests
--echo #

# 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 @a:=1e0;
SELECT * FROM t1;
DROP TABLE t1;
SET NAMES latin1;
CREATE TABLE t1 AS SELECT @a:=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;

--echo #
--echo # End of 10.3 tests
--echo #