diff options
Diffstat (limited to 'mysql-test/main/statistics.test')
| -rw-r--r-- | mysql-test/main/statistics.test | 855 |
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 # |