diff options
Diffstat (limited to 'mysql-test/main/statistics.test')
| -rw-r--r-- | mysql-test/main/statistics.test | 82 |
1 files changed, 54 insertions, 28 deletions
diff --git a/mysql-test/main/statistics.test b/mysql-test/main/statistics.test index 8d9ef79b69f..525836abba8 100644 --- a/mysql-test/main/statistics.test +++ b/mysql-test/main/statistics.test @@ -1,15 +1,22 @@ --source include/no_valgrind_without_big.inc --source include/have_stat_tables.inc --source include/have_innodb.inc ---disable_warnings -drop table if exists t1,t2; ---enable_warnings + +set @SINGLE_PREC_TYPE='single_prec_hb'; +set @DOUBLE_PREC_TYPE='double_prec_hb'; +set @DEFAULT_HIST_TYPE='double_prec_hb'; + +if ($histogram_type_override) { + eval set @SINGLE_PREC_TYPE=$histogram_type_override; + eval set @DOUBLE_PREC_TYPE=$histogram_type_override; + eval set @DEFAULT_HIST_TYPE=$histogram_type_override; +} set @save_use_stat_tables=@@use_stat_tables; set @save_histogram_size=@@global.histogram_size; set @@global.histogram_size=0,@@local.histogram_size=0; -set @save_hist_type=@@histogram_type; -set histogram_type='single_prec_hb'; +set @save_hist_type=@DEFAULT_HIST_TYPE; +set histogram_type=@SINGLE_PREC_TYPE; DELETE FROM mysql.table_stats; --sorted_result @@ -82,6 +89,7 @@ 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), @@ -178,30 +186,32 @@ DELETE FROM mysql.column_stats; set histogram_size=4; ANALYZE TABLE t1; ---sorted_result +--source include/histogram_replaces.inc 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; + hist_size, hist_type, decode_histogram(hist_type,histogram) +FROM mysql.column_stats +ORDER BY db_name, table_name, column_name; DELETE FROM mysql.column_stats; set histogram_size=8; -set histogram_type='DOUBLE_PREC_HB'; +set histogram_type=@DOUBLE_PREC_TYPE; ANALYZE TABLE t1; ---sorted_result +--source include/histogram_replaces.inc 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; + hist_size, hist_type, decode_histogram(hist_type,histogram) +FROM mysql.column_stats +ORDER BY db_name, table_name, column_name; DELETE FROM mysql.column_stats; set histogram_size= 0; -set histogram_type='single_prec_hb'; +set histogram_type=@SINGLE_PREC_TYPE; ANALYZE TABLE t1; @@ -653,20 +663,22 @@ use world; set use_stat_tables='preferably'; --disable_result_log set histogram_size=100; -set histogram_type='SINGLE_PREC_HB'; +set histogram_type=@SINGLE_PREC_TYPE; ANALYZE TABLE CountryLanguage; set histogram_size=254; -set histogram_type='DOUBLE_PREC_HB'; +set histogram_type=@DOUBLE_PREC_TYPE; ANALYZE TABLE City; FLUSH TABLES; --enable_result_log --disable_view_protocol ---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'; +--source include/histogram_replaces.inc +--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,decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='COUNTRYLANGUAGE' and UPPER(column_name) = 'PERCENTAGE'; +--source include/histogram_replaces.inc +--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,decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='CITY' and UPPER(column_name) = 'POPULATION'; --enable_view_protocol -set histogram_type='single_prec_hb'; +set histogram_type=@SINGLE_PREC_TYPE; set histogram_size=0; use test; @@ -710,11 +722,13 @@ set histogram_size=10; analyze table t1 persistent for all; +--source include/histogram_replaces.inc 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; + hist_size, hist_type, decode_histogram(hist_type,histogram) +FROM mysql.column_stats +ORDER BY db_name, table_name, column_name; set histogram_size=default; @@ -729,20 +743,22 @@ create table t1 ( a int); insert into t1 values (1),(2),(3),(4),(5); set histogram_size=10; -set histogram_type='double_prec_hb'; +set histogram_type=@DOUBLE_PREC_TYPE; show variables like 'histogram%'; analyze table t1 persistent for all; +--source include/histogram_replaces.inc 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; + hist_size, hist_type, decode_histogram(hist_type,histogram) +FROM mysql.column_stats +ORDER BY db_name, table_name, column_name; set histogram_size=0; -set histogram_type='single_prec_hb'; +set histogram_type=@SINGLE_PREC_TYPE; drop table t1; @@ -779,10 +795,11 @@ set histogram_size=63; analyze table t2 persistent for all; +--source include/histogram_replaces.inc select db_name, table_name, column_name, min_value, max_value, nulls_ratio, avg_frequency, - hist_size, hist_type, HEX(histogram) + hist_size, hist_type, decode_histogram(hist_type,histogram) FROM mysql.column_stats; set histogram_size=0; @@ -800,10 +817,11 @@ insert into t1 values(unhex('D879626AF872675F73E662F8')); analyze table t1 persistent for all; show warnings; +--source include/histogram_replaces.inc select db_name, table_name, column_name, HEX(min_value), HEX(max_value), nulls_ratio, avg_frequency, - hist_size, hist_type, HEX(histogram) + hist_size, hist_type, decode_histogram(hist_type,histogram) FROM mysql.column_stats; drop table t1; @@ -913,11 +931,14 @@ set use_stat_tables=@save_use_stat_tables; --echo # MDEV-10134 Add full support for DEFAULT --echo # -CREATE TABLE t1 (a BLOB, b TEXT DEFAULT DECODE_HISTOGRAM('SINGLE_PREC_HB',a)); +# disable for JSON histograms +if (!$histogram_type_override) { +CREATE TABLE t1 (a BLOB, b TEXT DEFAULT DECODE_HISTOGRAM(@SINGLE_PREC_TYPE,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 @@ -964,6 +985,7 @@ INSERT INTO t1 SELECT id+9192 FROM t1; --echo # This query will should show a full table scan analysis. --echo # ANALYZE TABLE t1; +--source include/histogram_replaces.inc select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; @@ -974,6 +996,7 @@ set analyze_sample_percentage=0.1; --echo # This query will show an innacurate avg_frequency value. --echo # ANALYZE TABLE t1; +--source include/histogram_replaces.inc select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; @@ -983,6 +1006,7 @@ from mysql.column_stats; --echo # set analyze_sample_percentage=25; ANALYZE TABLE t1; +--source include/histogram_replaces.inc select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; @@ -993,6 +1017,7 @@ set analyze_sample_percentage=0; --echo # Test self adjusting sampling level. --echo # ANALYZE TABLE t1; +--source include/histogram_replaces.inc select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; @@ -1004,12 +1029,13 @@ explain select * from t1; set analyze_sample_percentage=100; ANALYZE TABLE t1; +--source include/histogram_replaces.inc select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; explain select * from t1; - +drop table t0; drop table t1; set analyze_sample_percentage=@save_analyze_sample_percentage; set histogram_size=@save_histogram_size; |