diff options
Diffstat (limited to 'mysql-test/main/stat_tables_disabled.result')
| -rw-r--r-- | mysql-test/main/stat_tables_disabled.result | 70 |
1 files changed, 70 insertions, 0 deletions
diff --git a/mysql-test/main/stat_tables_disabled.result b/mysql-test/main/stat_tables_disabled.result new file mode 100644 index 00000000000..f57abc34e0c --- /dev/null +++ b/mysql-test/main/stat_tables_disabled.result @@ -0,0 +1,70 @@ +SET SESSION STORAGE_ENGINE='InnoDB'; +select @@global.use_stat_tables; +@@global.use_stat_tables +NEVER +select @@session.use_stat_tables; +@@session.use_stat_tables +NEVER +set @save_use_stat_tables=@@use_stat_tables; +set use_stat_tables='preferably'; +DROP DATABASE IF EXISTS dbt3_s001; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +set @save_optimizer_switch=@@optimizer_switch; +set optimizer_switch='extended_keys=off'; +# +# Bug mdev-503: optimizer ignores setting use_stat_tables='preferably' +# +flush tables +customer, lineitem, nation, orders, part, partsupp, region, supplier; +set use_stat_tables='never'; +EXPLAIN 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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL # Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey # Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey # Using where +set use_stat_tables='preferably'; +EXPLAIN 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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +flush tables customer, orders, lineitem; +EXPLAIN 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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +# 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 STORAGE_ENGINE=DEFAULT; |