diff options
Diffstat (limited to 'mysql-test/main/stat_tables_disabled.test')
-rw-r--r-- | mysql-test/main/stat_tables_disabled.test | 89 |
1 files changed, 89 insertions, 0 deletions
diff --git a/mysql-test/main/stat_tables_disabled.test b/mysql-test/main/stat_tables_disabled.test new file mode 100644 index 00000000..e6b965af --- /dev/null +++ b/mysql-test/main/stat_tables_disabled.test @@ -0,0 +1,89 @@ +set @innodb_stats_persistent_save= @@innodb_stats_persistent; +set @innodb_stats_persistent_sample_pages_save= + @@innodb_stats_persistent_sample_pages; + +set global innodb_stats_persistent= 1; +set global innodb_stats_persistent_sample_pages=100; +--source include/have_innodb.inc + +SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB'; + +select @@global.use_stat_tables; +select @@session.use_stat_tables; + +set @save_use_stat_tables=@@use_stat_tables; + +set use_stat_tables='preferably'; + +--disable_warnings +DROP DATABASE IF EXISTS dbt3_s001; +--enable_warnings + +CREATE DATABASE dbt3_s001; + +use dbt3_s001; + +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; +set optimizer_switch='rowid_filter=off'; + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/dbt3_s001.inc +delete from mysql.table_stats; +delete from mysql.column_stats; +delete from mysql.index_stats; +ANALYZE TABLE +customer, lineitem, nation, orders, part, partsupp, region, supplier; +--enable_warnings +--enable_result_log +--enable_query_log + +--echo # +--echo # Bug mdev-503: optimizer ignores setting use_stat_tables='preferably' +--echo # + +flush tables +customer, lineitem, nation, orders, part, partsupp, region, supplier; + +let $Q3S= +select sql_calc_found_rows straight_join + l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, + o_orderdate, o_shippriority +from orders, customer, lineitem +where c_mktsegment = 'BUILDING' and c_custkey = o_custkey + and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15' + and l_shipdate > date '1995-03-15' +group by l_orderkey, o_orderdate, o_shippriority +order by revenue desc, o_orderdate +limit 10; + +set use_stat_tables='never'; +--replace_column 9 # +eval EXPLAIN $Q3S; + +set use_stat_tables='preferably'; +--replace_result 2 1 +eval EXPLAIN $Q3S; + +flush tables customer, orders, lineitem; +eval EXPLAIN $Q3S; + +--echo # End of the test case for mdev-503 + +set optimizer_switch=@save_optimizer_switch; + + +DROP DATABASE dbt3_s001; + +use test; + +set use_stat_tables=@save_use_stat_tables; + + +SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT; + +set global innodb_stats_persistent= @innodb_stats_persistent_save; +set global innodb_stats_persistent_sample_pages= + @innodb_stats_persistent_sample_pages_save; |