Create 'main' test directory and move 't' and 'r' there

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 #