summaryrefslogtreecommitdiff
path: root/mysql-test/main/statistics.test
diff options
context:
space:
mode:
authorMichael Widenius <monty@mariadb.org>2018-03-09 14:05:35 +0200
committerMonty <monty@mariadb.org>2018-03-29 13:59:44 +0300
commita7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch)
tree70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/main/statistics.test
parentab1941266c59a19703a74b5593cf3f508a5752d7 (diff)
downloadmariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/main/statistics.test')
-rw-r--r--mysql-test/main/statistics.test855
1 files changed, 855 insertions, 0 deletions
diff --git a/mysql-test/main/statistics.test b/mysql-test/main/statistics.test
new file mode 100644
index 00000000000..e9ecb56a1cb
--- /dev/null
+++ b/mysql-test/main/statistics.test
@@ -0,0 +1,855 @@
+--source include/have_stat_tables.inc
+--source include/have_innodb.inc
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+set @save_use_stat_tables=@@use_stat_tables;
+
+DELETE FROM mysql.table_stats;
+--sorted_result
+DELETE FROM mysql.column_stats;
+--sorted_result
+DELETE FROM mysql.index_stats;
+
+set use_stat_tables='preferably';
+
+CREATE TABLE t1 (
+ a int NOT NULL PRIMARY KEY,
+ b varchar(32),
+ c char(16),
+ d date,
+ e double,
+ f bit(3),
+ INDEX idx1 (b, e),
+ INDEX idx2 (c, d),
+ INDEX idx3 (d),
+ INDEX idx4 (e, b, d)
+) ENGINE= MYISAM;
+
+INSERT INTO t1 VALUES
+ (0, NULL, NULL, NULL, NULL, NULL),
+ (7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'),
+ (17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'),
+ (1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'),
+ (12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'),
+ (23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'),
+ (8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'),
+ (22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'),
+ (31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'),
+ (10, NULL, 'aaaa', NULL, 0.01, b'010'),
+ (5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'),
+ (15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'),
+ (30, NULL, 'bbbbbb', NULL, NULL, b'100'),
+ (38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL),
+ (18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'),
+ (9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL),
+ (29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'),
+ (3, 'yyy', 'dddddddd', '1990-05-15', 0.112, b'010'),
+ (39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'),
+ (14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'),
+ (40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL),
+ (44, NULL, 'aaaa', '1989-03-12', NULL, b'010'),
+ (19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'),
+ (21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'),
+ (45, NULL, NULL, '1989-03-12', NULL, b'011'),
+ (2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'),
+ (35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'),
+ (4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'),
+ (47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'),
+ (42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'),
+ (32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'),
+ (49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL),
+ (43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'),
+ (37, 'yyy', NULL, '1989-03-12', 0.05, b'011'),
+ (41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL),
+ (34, 'yyy', NULL, NULL, NULL, NULL),
+ (33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'),
+ (24, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01, b'101'),
+ (11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL),
+ (25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01, b'101');
+
+ANALYZE TABLE t1;
+
+SELECT * FROM mysql.table_stats;
+SELECT * FROM mysql.column_stats;
+SELECT * FROM mysql.index_stats;
+
+SELECT COUNT(*) FROM t1;
+
+SELECT * FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='a';
+SELECT MIN(t1.a), MAX(t1.a),
+ (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) /
+ (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.a)",
+ (SELECT COUNT(t1.a) FROM t1) /
+ (SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)"
+FROM t1;
+
+SELECT * FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='b';
+SELECT MIN(t1.b), MAX(t1.b),
+ (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) /
+ (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.b)",
+ (SELECT COUNT(t1.b) FROM t1) /
+ (SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)"
+FROM t1;
+
+SELECT * FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='c';
+SELECT MIN(t1.c), MAX(t1.c),
+ (SELECT COUNT(*) FROM t1 WHERE t1.c IS NULL) /
+ (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.c)",
+ (SELECT COUNT(t1.c) FROM t1) /
+ (SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)"
+FROM t1;
+
+SELECT * FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='d';
+SELECT MIN(t1.d), MAX(t1.d),
+ (SELECT COUNT(*) FROM t1 WHERE t1.d IS NULL) /
+ (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.d)",
+ (SELECT COUNT(t1.d) FROM t1) /
+ (SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)"
+FROM t1;
+
+SELECT * FROM mysql.column_stats
+ WHERE db_name='test' AND table_name='t1' AND column_name='e';
+SELECT MIN(t1.e), MAX(t1.e),
+ (SELECT COUNT(*) FROM t1 WHERE t1.e IS NULL) /
+ (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.e)",
+ (SELECT COUNT(t1.e) FROM t1) /
+ (SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)"
+FROM t1;
+
+SELECT * FROM mysql.index_stats
+ WHERE db_name='test' AND table_name='t1' AND index_name='idx1';
+SELECT
+ (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) /
+ (SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL)
+ AS 'ARITY 1',
+ (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) /
+ (SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1
+ WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL)
+ AS 'ARITY 2';
+
+SELECT * FROM mysql.index_stats
+ WHERE db_name='test' AND table_name='t1' AND index_name='idx2';
+SELECT
+ (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) /
+ (SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL)
+ AS 'ARITY 1',
+ (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) /
+ (SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1
+ WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL)
+ AS 'ARITY 2';
+
+SELECT * FROM mysql.index_stats
+ WHERE db_name='test' AND table_name='t1' AND index_name='idx3';
+SELECT
+ (SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) /
+ (SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL)
+ AS 'ARITY 1';
+
+SELECT * FROM mysql.index_stats
+ WHERE db_name='test' AND table_name='t1' AND index_name='idx4';
+SELECT
+ (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) /
+ (SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL)
+ AS 'ARITY 1',
+ (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) /
+ (SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1
+ WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL)
+ AS 'ARITY 2',
+ (SELECT COUNT(*) FROM t1
+ WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) /
+ (SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1
+ WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL)
+ AS 'ARITY 3';
+
+
+DELETE FROM mysql.column_stats;
+
+set histogram_size=4;
+ANALYZE TABLE t1;
+
+--sorted_result
+SELECT db_name, table_name, column_name,
+ min_value, max_value,
+ nulls_ratio, avg_frequency,
+ hist_size, hist_type, HEX(histogram)
+ FROM mysql.column_stats;
+
+DELETE FROM mysql.column_stats;
+
+set histogram_size=8;
+set histogram_type='DOUBLE_PREC_HB';
+ANALYZE TABLE t1;
+
+--sorted_result
+SELECT db_name, table_name, column_name,
+ min_value, max_value,
+ nulls_ratio, avg_frequency,
+ hist_size, hist_type, HEX(histogram)
+ FROM mysql.column_stats;
+
+DELETE FROM mysql.column_stats;
+
+set histogram_size= 0;
+set histogram_type=default;
+ANALYZE TABLE t1;
+
+
+CREATE TABLE t3 (
+ a int NOT NULL PRIMARY KEY,
+ b varchar(32),
+ c char(16),
+ INDEX idx (c)
+) ENGINE=MYISAM;
+
+INSERT INTO t3 VALUES
+ (0, NULL, NULL),
+ (7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd'),
+ (17, 'vvvvvvvvvvvvv', 'aaaa'),
+ (1, 'vvvvvvvvvvvvv', NULL),
+ (12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd'),
+ (23, 'vvvvvvvvvvvvv', 'dddddddd'),
+ (8, 'vvvvvvvvvvvvv', 'aaaa'),
+ (22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa'),
+ (31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa'),
+ (10, NULL, 'aaaa'),
+ (5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd'),
+ (15, 'vvvvvvvvvvvvv', 'ccccccccc'),
+ (30, NULL, 'bbbbbb'),
+ (38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb'),
+ (18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc'),
+ (9, 'yyy', 'bbbbbb'),
+ (29, 'vvvvvvvvvvvvv', 'dddddddd');
+
+ANALYZE TABLE t3;
+
+--sorted_result
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ALTER TABLE t1 RENAME TO s1;
+--sorted_result
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+RENAME TABLE s1 TO t1;
+--sorted_result
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+DROP TABLE t3;
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+
+CREATE TEMPORARY TABLE t0 (
+ a int NOT NULL PRIMARY KEY,
+ b varchar(32)
+);
+INSERT INTO t0 SELECT a,b FROM t1;
+
+ALTER TABLE t1 CHANGE COLUMN b x varchar(32),
+ CHANGE COLUMN e y double;
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+
+ALTER TABLE t1 CHANGE COLUMN x b varchar(32),
+ CHANGE COLUMN y e double;
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+
+ALTER TABLE t1 RENAME TO s1, CHANGE COLUMN b x varchar(32);
+SHOW CREATE TABLE s1;
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ALTER TABLE s1 RENAME TO t1, CHANGE COLUMN x b varchar(32);
+SHOW CREATE TABLE t1;
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ALTER TABLE t1 CHANGE COLUMN b x varchar(30);
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ALTER TABLE t1 CHANGE COLUMN x b varchar(32);
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx4);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval
+SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/save_column_stats'
+ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
+ FROM mysql.column_stats WHERE column_name='b';
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval
+SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/save_index_stats'
+ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
+ FROM mysql.index_stats WHERE index_name IN ('idx1', 'idx4');
+
+ALTER TABLE t1 CHANGE COLUMN b x varchar(30);
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ALTER TABLE t1 CHANGE COLUMN x b varchar(32);
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval
+LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/save_column_stats'
+ INTO TABLE mysql.column_stats
+ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval
+LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/save_index_stats'
+ INTO TABLE mysql.index_stats
+ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+remove_file $MYSQLTEST_VARDIR/tmp/save_column_stats;
+remove_file $MYSQLTEST_VARDIR/tmp/save_index_stats;
+
+
+ALTER TABLE t1 DROP COLUMN b;
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+DROP INDEX idx2 ON t1;
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+DROP INDEX idx1 ON t1;
+DROP INDEX idx4 ON t1;
+SHOW CREATE TABLE t1;
+
+ALTER TABLE t1 ADD COLUMN b varchar(32);
+CREATE INDEX idx1 ON t1(b, e);
+CREATE INDEX idx2 ON t1(c, d);
+CREATE INDEX idx4 ON t1(e, b, d);
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+UPDATE t1 SET b=(SELECT b FROM t0 WHERE t0.a= t1.a);
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ALTER TABLE t1 DROP COLUMN b,
+ DROP INDEX idx1, DROP INDEX idx2, DROP INDEX idx4;
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ALTER TABLE t1 ADD COLUMN b varchar(32);
+ALTER TABLE t1
+ ADD INDEX idx1 (b, e), ADD INDEX idx2 (c, d), ADD INDEX idx4 (e, b, d);
+UPDATE t1 SET b=(SELECT b FROM t0 WHERE t0.a= t1.a);
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4);
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
+
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS() INDEXES();
+SELECT * FROM mysql.table_stats;
+SELECT * FROM mysql.column_stats;
+SELECT * FROM mysql.index_stats;
+
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(c,e,b) INDEXES(idx2,idx4);
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+DELETE FROM mysql.index_stats WHERE table_name='t1' AND index_name='primary';
+--sorted_result
+SELECT * FROM mysql.index_stats;
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS() INDEXES(primary);
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
+
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS ALL INDEXES ALL;
+
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+CREATE TABLE t2 LIKE t1;
+ALTER TABLE t2 ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+
+set optimizer_switch='extended_keys=off';
+
+ANALYZE TABLE t2;
+
+--sorted_result
+SELECT * FROM mysql.table_stats;
+SELECT * FROM mysql.column_stats ORDER BY column_name, table_name;
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
+
+set optimizer_switch='extended_keys=on';
+
+ANALYZE TABLE t2;
+
+SELECT * FROM mysql.table_stats;
+SELECT * FROM mysql.column_stats ORDER BY column_name;
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+ALTER TABLE t2 DROP PRIMARY KEY, DROP INDEX idx1;
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+UPDATE t2 SET b=0 WHERE b IS NULL;
+ALTER TABLE t2 ADD PRIMARY KEY (a,b);
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL;
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+ALTER TABLE t2 CHANGE COLUMN b b varchar(30);
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL;
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+ALTER TABLE t2 CHANGE COLUMN b b varchar(32);
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL;
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+ALTER TABLE t2 DROP COLUMN b, DROP PRIMARY KEY, ADD PRIMARY KEY(a);
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL;
+SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name;
+
+set optimizer_switch='extended_keys=off';
+
+ALTER TABLE t1
+ DROP INDEX idx1,
+ DROP INDEX idx4;
+ALTER TABLE t1
+ MODIFY COLUMN b text,
+ ADD INDEX idx1 (b(4), e),
+ ADD INDEX idx4 (e, b(4), d);
+
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+ANALYZE TABLE t1;
+
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
+
+ANALYZE TABLE mysql.column_stats PERSISTENT FOR ALL;
+
+ANALYZE TABLE mysql.column_stats;
+
+SELECT * FROM mysql.table_stats;
+SELECT * FROM mysql.column_stats;
+SELECT * FROM mysql.index_stats;
+
+set use_stat_tables='never';
+
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+
+SELECT * FROM mysql.table_stats;
+--sorted_result
+SELECT * FROM mysql.column_stats;
+--sorted_result
+SELECT * FROM mysql.index_stats;
+
+
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
+
+
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES();
+ANALYZE TABLE t1 PERSISTENT FOR columns(a,b) INDEXES();
+ANALYZE TABLE t1 PERSISTENT FOR columns(b) indexes(idx2);
+ANALYZE TABLE t1 PERSISTENT FOR columns() indexes(idx2);
+
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
+
+
+DROP TABLE t1,t2;
+
+set names utf8;
+
+CREATE DATABASE world;
+
+use world;
+
+--source include/world_schema_utf8.inc
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/world.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+set use_stat_tables='preferably';
+
+--disable_result_log
+ANALYZE TABLE Country, City, CountryLanguage;
+--enable_result_log
+
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name), cardinality
+ FROM mysql.table_stats;
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name),
+ column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
+ FROM mysql.column_stats;
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name),
+ index_name, prefix_arity, avg_frequency
+ FROM mysql.index_stats;
+
+use test;
+
+set use_stat_tables='never';
+
+CREATE DATABASE world_innodb;
+
+use world_innodb;
+
+--source include/world_schema_utf8.inc
+
+ALTER TABLE Country ENGINE=InnoDB;
+ALTER TABLE City ENGINE=InnoDB;
+ALTER TABLE CountryLanguage ENGINE=InnoDB;
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/world.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+set use_stat_tables='preferably';
+
+--disable_result_log
+ANALYZE TABLE Country, City, CountryLanguage;
+--enable_result_log
+
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name), cardinality
+ FROM mysql.table_stats;
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name),
+ column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
+ FROM mysql.column_stats;
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name),
+ index_name, prefix_arity, avg_frequency
+ FROM mysql.index_stats;
+
+use world;
+set use_stat_tables='preferably';
+--disable_result_log
+set histogram_size=100;
+set histogram_type='SINGLE_PREC_HB';
+ANALYZE TABLE CountryLanguage;
+set histogram_size=254;
+set histogram_type='DOUBLE_PREC_HB';
+ANALYZE TABLE City;
+FLUSH TABLES;
+--enable_result_log
+
+--query_vertical select UPPER(db_name),UPPER(table_name),UPPER(column_name),min_value,max_value,nulls_ratio,avg_length,avg_frequency,hist_size,hist_type,hex(histogram),decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='COUNTRYLANGUAGE' and UPPER(column_name) = 'PERCENTAGE';
+--query_vertical select UPPER(db_name),UPPER(table_name),UPPER(column_name),min_value,max_value,nulls_ratio,avg_length,avg_frequency,hist_size,hist_type,hex(histogram),decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='CITY' and UPPER(column_name) = 'POPULATION';
+
+set histogram_type=default;
+set histogram_size=default;
+
+use test;
+DROP DATABASE world;
+
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name), cardinality
+ FROM mysql.table_stats;
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name),
+ column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
+ FROM mysql.column_stats;
+--sorted_result
+SELECT UPPER(db_name), UPPER(table_name),
+ index_name, prefix_arity, avg_frequency
+ FROM mysql.index_stats;
+
+DROP DATABASE world_innodb;
+SELECT UPPER(db_name), UPPER(table_name), cardinality
+ FROM mysql.table_stats;
+SELECT UPPER(db_name), UPPER(table_name),
+ column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
+ FROM mysql.column_stats;
+SELECT UPPER(db_name), UPPER(table_name),
+ index_name, prefix_arity, avg_frequency
+ FROM mysql.index_stats;
+
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
+
+--echo #
+--echo # Bug mdev-4357: empty string as a value of the HIST_SIZE column
+--echo # from mysql.column_stats
+--echo #
+
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+
+set histogram_size=10;
+
+analyze table t1 persistent for all;
+
+select db_name, table_name, column_name,
+ min_value, max_value,
+ nulls_ratio, avg_frequency,
+ hist_size, hist_type, HEX(histogram)
+ FROM mysql.column_stats;
+
+set histogram_size=default;
+
+drop table t1;
+
+--echo #
+--echo # Bug mdev-4359: wrong setting of the HIST_SIZE column
+--echo # (see also mdev-4357) from mysql.column_stats
+--echo #
+
+create table t1 ( a int);
+insert into t1 values (1),(2),(3),(4),(5);
+
+set histogram_size=10;
+set histogram_type='double_prec_hb';
+
+show variables like 'histogram%';
+
+analyze table t1 persistent for all;
+
+select db_name, table_name, column_name,
+ min_value, max_value,
+ nulls_ratio, avg_frequency,
+ hist_size, hist_type, HEX(histogram)
+ FROM mysql.column_stats;
+
+set histogram_size=default;
+set histogram_type=default;
+
+drop table t1;
+
+--echo #
+--echo # Bug mdev-4369: histogram for a column with many distinct values
+--echo #
+
+
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int);
+
+INSERT INTO t1 (id) VALUES (1), (1), (1),(1);
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 SELECT id+1 FROM t1;
+INSERT INTO t1 SELECT id+2 FROM t1;
+INSERT INTO t1 SELECT id+4 FROM t1;
+INSERT INTO t1 SELECT id+8 FROM t1;
+INSERT INTO t1 SELECT id+16 FROM t1;
+INSERT INTO t1 SELECT id+32 FROM t1;
+INSERT INTO t1 SELECT id+64 FROM t1;
+INSERT INTO t1 SELECT id+128 FROM t1;
+INSERT INTO t1 SELECT id+256 FROM t1;
+INSERT INTO t1 SELECT id+512 FROM t1;
+
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+
+SELECT COUNT(*) FROM t2;
+SELECT COUNT(DISTINCT id) FROM t2;
+
+set @@tmp_table_size=1024*16;
+set @@max_heap_table_size=1024*16;
+
+set histogram_size=63;
+
+analyze table t2 persistent for all;
+
+select db_name, table_name, column_name,
+ min_value, max_value,
+ nulls_ratio, avg_frequency,
+ hist_size, hist_type, HEX(histogram)
+ FROM mysql.column_stats;
+
+set histogram_size=default;
+
+drop table t1, t2;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug MDEV-7383: min/max value for a column not utf8 compatible
+--echo #
+
+create table t1 (a varchar(100)) engine=MyISAM;
+insert into t1 values(unhex('D879626AF872675F73E662F8'));
+analyze table t1 persistent for all;
+show warnings;
+
+select db_name, table_name, column_name,
+ HEX(min_value), HEX(max_value),
+ nulls_ratio, avg_frequency,
+ hist_size, hist_type, HEX(histogram)
+ FROM mysql.column_stats;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-9590: Always print "Engine-independent statistic" warnings and
+--echo # might be filtering columns unintentionally from engines
+--echo #
+
+set use_stat_tables='NEVER';
+create table t1 (test blob);
+show variables like 'use_stat_tables';
+analyze table t1;
+drop table t1;
+
+--echo #
+--echo # MDEV-10435 crash with bad stat tables
+--echo #
+
+set use_stat_tables='preferably';
+call mtr.add_suppression("Column count of mysql.table_stats is wrong. Expected 3, found 1. The table is probably corrupted");
+
+rename table mysql.table_stats to test.table_stats;
+flush tables;
+create table t1 (a int);
+--error ER_NO_SUCH_TABLE
+rename table t1 to t2, t3 to t4;
+drop table t1;
+rename table test.table_stats to mysql.table_stats;
+
+rename table mysql.table_stats to test.table_stats;
+create table mysql.table_stats (a int);
+flush tables;
+create table t1 (a int);
+--error ER_NO_SUCH_TABLE
+rename table t1 to t2, t3 to t4;
+drop table t1, mysql.table_stats;
+rename table test.table_stats to mysql.table_stats;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-10134 Add full support for DEFAULT
+--echo #
+
+CREATE TABLE t1 (a BLOB, b TEXT DEFAULT DECODE_HISTOGRAM('SINGLE_PREC_HB',a));
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (a) VALUES (0x0000000000000000000000000101010101010101010202020303030304040404050505050606070707080809090A0A0B0C0D0D0E0E0F10111213131415161718191B1C1E202224292A2E33373B4850575F6A76818C9AA7B9C4CFDADFE5EBF0F4F8FAFCFF);
+SELECT b FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #