--- /dev/null +++ b/mysql-test/include/percona_stats_null.inc @@ -0,0 +1,58 @@ +--disable_warnings +DROP TABLE IF EXISTS t; +--enable_warnings + +eval CREATE TABLE `t` (`id` int(11) DEFAULT NULL, KEY `t$id` (`id`)) ENGINE=$engine; + +--disable_query_log +--disable_result_log +# insert 100 null values +--let i=100 +while($i) +{ + eval INSERT INTO t VALUES(); + dec $i; +} +# insert 6 not null values +--let i=6 +while($i) +{ +eval INSERT INTO t(id) VALUES ($i); +dec $i; +} + +# clone to 100000 +INSERT INTO t SELECT a.* FROM t as a, t as b, t as c LIMIT 99983; + +--enable_result_log +--enable_query_log + +ANALYZE TABLE t; + +--let result_null_count=`SELECT count(*) FROM t WHERE id IS NULL` +--let result_not_null_count=`SELECT count(*) FROM t WHERE id IS NOT NULL` +--let result_cardinality=query_get_value('SHOW INDEXES FROM t', Cardinality, 1) + +--let result_cardinality_near_the_null_count=`SELECT ($result_cardinality > ($result_null_count * 0.8)) AND ($result_cardinality < ($result_null_count * 1.2))` +--let result_cardinality_ok=`SELECT $result_cardinality_near_the_null_count = $expected_near_null_count` + +if (!$result_cardinality_ok) +{ +--echo Failed with '$restart_mysqld_options' + SELECT count(*) FROM t WHERE id IS NULL; + SELECT count(*) FROM t WHERE id IS NOT NULL; + SHOW INDEXES FROM t; + if ($expected_near_null_count) + { +--echo We are expected cardinality approximately equal to null count, but this is false + } + if (!$expected_near_null_count) + { +--echo +--echo We are expected cardinality approximately equal to not null count, but this is false +--echo + } +} + +DROP TABLE t; + --- a/mysql-test/include/restart_mysqld.inc +++ b/mysql-test/include/restart_mysqld.inc @@ -18,8 +18,13 @@ shutdown_server 10; # Write file to make mysql-test-run.pl start up the server again ---exec echo "restart" > $_expect_file_name +if ($restart_mysqld_options) { +--exec echo "restart: $restart_mysqld_options" > $_expect_file_name +} +if (!$restart_mysqld_options) { +--exec echo "restart" > $_expect_file_name +} # Turn on reconnect --enable_reconnect --- /dev/null +++ b/mysql-test/t/percona_stats_null_innodb.test @@ -0,0 +1,26 @@ +--source include/have_innodb.inc +--let engine=InnoDB + +--let innodb_stats_method=nulls_equal +--let expected_near_null_count=0 + +--let restart_mysqld_options=--innodb_stats_on_metadata=0 --innodb_stats_method=$innodb_stats_method --default-storage-engine=$engine +--source include/restart_mysqld.inc + +--source include/percona_stats_null.inc + +--let innodb_stats_method=nulls_unequal +--let expected_near_null_count=1 + +--let restart_mysqld_options=--innodb_stats_on_metadata=0 --innodb_stats_method=$innodb_stats_method --default-storage-engine=$engine +--source include/restart_mysqld.inc + +--source include/percona_stats_null.inc + +--let innodb_stats_method=nulls_ignored +--let expected_near_null_count=0 + +--let restart_mysqld_options=--innodb_stats_on_metadata=0 --innodb_stats_method=$innodb_stats_method --default-storage-engine=$engine +--source include/restart_mysqld.inc + +--source include/percona_stats_null.inc --- /dev/null +++ b/mysql-test/t/percona_stats_null_myisam.test @@ -0,0 +1,25 @@ +--let engine=MyISAM + +--let myisam_stats_method=nulls_equal +--let expected_near_null_count=0 + +--let restart_mysqld_options=--myisam_stats_method=$myisam_stats_method --default-storage-engine=$engine +--source include/restart_mysqld.inc + +--source include/percona_stats_null.inc + +--let myisam_stats_method=nulls_unequal +--let expected_near_null_count=1 + +--let restart_mysqld_options=--myisam_stats_method=$myisam_stats_method --default-storage-engine=$engine +--source include/restart_mysqld.inc + +--source include/percona_stats_null.inc + +--let myisam_stats_method=nulls_ignored +--let expected_near_null_count=0 + +--let restart_mysqld_options=--myisam_stats_method=$myisam_stats_method --default-storage-engine=$engine +--source include/restart_mysqld.inc + +--source include/percona_stats_null.inc --- /dev/null +++ b/mysql-test/r/percona_stats_null_innodb.result @@ -0,0 +1,18 @@ +DROP TABLE IF EXISTS t; +CREATE TABLE `t` (`id` int(11) DEFAULT NULL, KEY `t` (`id`)) ENGINE=InnoDB; +ANALYZE TABLE t; +Table Op Msg_type Msg_text +test.t analyze status OK +DROP TABLE t; +DROP TABLE IF EXISTS t; +CREATE TABLE `t` (`id` int(11) DEFAULT NULL, KEY `t` (`id`)) ENGINE=InnoDB; +ANALYZE TABLE t; +Table Op Msg_type Msg_text +test.t analyze status OK +DROP TABLE t; +DROP TABLE IF EXISTS t; +CREATE TABLE `t` (`id` int(11) DEFAULT NULL, KEY `t` (`id`)) ENGINE=InnoDB; +ANALYZE TABLE t; +Table Op Msg_type Msg_text +test.t analyze status OK +DROP TABLE t; --- /dev/null +++ b/mysql-test/r/percona_stats_null_myisam.result @@ -0,0 +1,18 @@ +DROP TABLE IF EXISTS t; +CREATE TABLE `t` (`id` int(11) DEFAULT NULL, KEY `t` (`id`)) ENGINE=MyISAM; +ANALYZE TABLE t; +Table Op Msg_type Msg_text +test.t analyze status OK +DROP TABLE t; +DROP TABLE IF EXISTS t; +CREATE TABLE `t` (`id` int(11) DEFAULT NULL, KEY `t` (`id`)) ENGINE=MyISAM; +ANALYZE TABLE t; +Table Op Msg_type Msg_text +test.t analyze status OK +DROP TABLE t; +DROP TABLE IF EXISTS t; +CREATE TABLE `t` (`id` int(11) DEFAULT NULL, KEY `t` (`id`)) ENGINE=MyISAM; +ANALYZE TABLE t; +Table Op Msg_type Msg_text +test.t analyze status OK +DROP TABLE t;