The pilot implementation of mwl#250: Use the statistics from persistent

statistical tables instead of the statistics provided by engine.

1 files changed, 108 insertions, 0 deletions

diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
new file mode 100644
index 00000000000..cd896abebe1
--- /dev/null
+++ b/mysql-test/t/stat_tables.test
@@ -0,0 +1,108 @@
+
+set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables;
+
+set optimizer_use_stat_tables='preferably';
+
+--disable_warnings
+DROP DATABASE IF EXISTS dbt3_s001;
+--enable_warnings
+
+CREATE DATABASE dbt3_s001;
+
+use dbt3_s001;
+ 
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/dbt3_s001.inc
+create index i_p_retailprice on part(p_retailprice);
+ANALYZE TABLE
+customer, lineitem, nation, orders, part, partsupp, region, supplier;
+FLUSH TABLES customer, lineitem, nation, orders, part, partsupp, region, supplier;
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+select * from mysql.table_stat;
+select * from mysql.index_stat;
+
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=off';
+
+let $Q5=
+select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
+from customer, orders, lineitem, supplier, nation, region
+where c_custkey = o_custkey and l_orderkey = o_orderkey
+      and l_suppkey = s_suppkey and c_nationkey = s_nationkey
+      and s_nationkey = n_nationkey and n_regionkey = r_regionkey
+      and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
+      and o_orderdate < date '1995-01-01' + interval '1' year
+group by n_name
+order by revenue desc;
+
+eval EXPLAIN $Q5;
+eval $Q5;
+
+set optimizer_switch=@save_optimizer_switch;
+
+
+let $Q8=
+select o_year,
+       sum(case when nation = 'UNITED STATES' then volume else 0 end) /
+       sum(volume) as mkt_share
+from (select extract(year from o_orderdate) as o_year,
+             l_extendedprice * (1-l_discount) as volume, 
+             n2.n_name as nation
+      from part, supplier, lineitem, orders, customer, 
+           nation n1, nation n2, region
+      where p_partkey = l_partkey and s_suppkey = l_suppkey
+            and l_orderkey = o_orderkey and o_custkey = c_custkey
+            and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
+            and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
+            and o_orderdate between date '1995-01-01' and date '1996-12-31'
+            and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
+group by o_year
+order by o_year;
+
+eval EXPLAIN $Q8;
+eval $Q8;
+
+
+let $Q9=
+select nation, o_year, sum(amount) as sum_profit
+from (select n_name as nation, 
+      extract(year from o_orderdate) as o_year,
+      l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+      from part, supplier, lineitem, partsupp, orders, nation
+      where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
+            and ps_partkey = l_partkey and p_partkey = l_partkey
+            and o_orderkey = l_orderkey and s_nationkey = n_nationkey
+            and p_name like '%green%') as profit
+group by nation, o_year
+order by nation, o_year desc;
+
+eval EXPLAIN $Q9;
+eval $Q9;
+
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+
+let $QQ1=
+select o_orderkey, p_partkey
+  from part, lineitem, orders
+  where p_retailprice > 1100 and o_orderdate='1997-01-01' 
+        and o_orderkey=l_orderkey and p_partkey=l_partkey;
+
+eval EXPLAIN $QQ1;
+eval $QQ1;
+
+set optimizer_switch=@save_optimizer_switch;
+
+
+DROP DATABASE dbt3_s001;
+
+use test;
+
+set optimizer_use_stat_tables=@save_optimizer_use_stat_tables;