diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/metadata.test | |
parent | Initial commit. (diff) | |
download | mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/metadata.test')
-rw-r--r-- | mysql-test/main/metadata.test | 492 |
1 files changed, 492 insertions, 0 deletions
diff --git a/mysql-test/main/metadata.test b/mysql-test/main/metadata.test new file mode 100644 index 00000000..7cfdef5e --- /dev/null +++ b/mysql-test/main/metadata.test @@ -0,0 +1,492 @@ +# +# 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 # |