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

statistical tables instead of the statistics provided by engine.

1 files changed, 229 insertions, 0 deletions

diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
new file mode 100644
index 00000000000..089c7871506
--- /dev/null
+++ b/mysql-test/r/stat_tables.result
@@ -0,0 +1,229 @@
+set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables;
+set optimizer_use_stat_tables='preferably';
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+select * from mysql.table_stat;
+db_name	table_name	cardinality
+dbt3_s001	customer	150
+dbt3_s001	lineitem	6005
+dbt3_s001	nation	25
+dbt3_s001	orders	1500
+dbt3_s001	part	200
+dbt3_s001	partsupp	700
+dbt3_s001	region	5
+dbt3_s001	supplier	10
+select * from mysql.index_stat;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	customer	PRIMARY	1	1
+dbt3_s001	customer	i_c_nationkey	1	6
+dbt3_s001	lineitem	PRIMARY	1	4.003333333333333
+dbt3_s001	lineitem	PRIMARY	2	1
+dbt3_s001	lineitem	i_l_shipdate	1	2.650044130626655
+dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.025
+dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.57857142857143
+dbt3_s001	lineitem	i_l_partkey	1	30.025
+dbt3_s001	lineitem	i_l_suppkey	1	600.5
+dbt3_s001	lineitem	i_l_receiptdate	1	2.6477072310405645
+dbt3_s001	lineitem	i_l_orderkey	1	4.003333333333333
+dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.003333333333333
+dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0403672903672905
+dbt3_s001	lineitem	i_l_commitdate	1	2.7159656264133876
+dbt3_s001	nation	PRIMARY	1	1
+dbt3_s001	nation	i_n_regionkey	1	5
+dbt3_s001	orders	PRIMARY	1	1
+dbt3_s001	orders	i_o_orderdate	1	1.3321492007104796
+dbt3_s001	orders	i_o_custkey	1	15
+dbt3_s001	part	PRIMARY	1	1
+dbt3_s001	part	i_p_retailprice	1	1
+dbt3_s001	partsupp	PRIMARY	1	3.5
+dbt3_s001	partsupp	PRIMARY	2	1
+dbt3_s001	partsupp	i_ps_partkey	1	3.5
+dbt3_s001	partsupp	i_ps_suppkey	1	70
+dbt3_s001	region	PRIMARY	1	1
+dbt3_s001	supplier	PRIMARY	1	1
+dbt3_s001	supplier	i_s_nationkey	1	1.1111111111111112
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=off';
+EXPLAIN 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	range	PRIMARY,i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	179	Using where; Using temporary; Using filesort
+1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	SIMPLE	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.lineitem.l_suppkey	1	Using where
+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;
+n_name	revenue
+PERU	321915.8715
+ARGENTINA	69817.1451
+set optimizer_switch=@save_optimizer_switch;
+EXPLAIN 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;
+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,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	SIMPLE	n1	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+1	SIMPLE	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.lineitem.l_suppkey	1	Using where
+1	SIMPLE	n2	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
+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;
+o_year	mkt_share
+1995	0.4495521838895718
+1996	0.024585468215352495
+EXPLAIN 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	Using where; Using temporary; Using filesort
+1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
+1	SIMPLE	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	70	
+1	SIMPLE	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.supplier.s_suppkey	8	
+1	SIMPLE	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	
+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;
+nation	o_year	sum_profit
+ARGENTINA	1997	18247.873399999993
+ARGENTINA	1996	7731.089399999995
+ARGENTINA	1995	134490.5697
+ARGENTINA	1994	36767.101500000004
+ARGENTINA	1993	35857.08
+ARGENTINA	1992	35740
+ETHIOPIA	1998	2758.7801999999992
+ETHIOPIA	1997	19419.294599999997
+ETHIOPIA	1995	51231.87439999999
+ETHIOPIA	1994	3578.9478999999974
+ETHIOPIA	1992	1525.8234999999986
+IRAN	1998	37817.229600000006
+IRAN	1997	52643.77359999999
+IRAN	1996	70143.7761
+IRAN	1995	84094.58260000001
+IRAN	1994	18140.925599999995
+IRAN	1993	78655.1676
+IRAN	1992	87142.23960000002
+IRAQ	1998	22860.8082
+IRAQ	1997	93676.24359999999
+IRAQ	1996	45103.3242
+IRAQ	1994	36010.728599999995
+IRAQ	1993	33221.9399
+IRAQ	1992	47755.05900000001
+KENYA	1998	44194.831999999995
+KENYA	1997	57578.36259999999
+KENYA	1996	59195.90210000001
+KENYA	1995	79262.6278
+KENYA	1994	102360.66609999999
+KENYA	1993	128422.0196
+KENYA	1992	181517.2089
+MOROCCO	1998	41797.823199999984
+MOROCCO	1997	23685.801799999994
+MOROCCO	1996	62115.19579999998
+MOROCCO	1995	42442.64300000001
+MOROCCO	1994	48655.878000000004
+MOROCCO	1993	22926.744400000003
+MOROCCO	1992	32239.8088
+PERU	1998	86999.36459999997
+PERU	1997	121110.41070000001
+PERU	1996	177040.40759999995
+PERU	1995	122247.94520000002
+PERU	1994	88046.25329999998
+PERU	1993	49379.813799999996
+PERU	1992	80646.86050000001
+UNITED KINGDOM	1998	50577.25560000001
+UNITED KINGDOM	1997	114288.8605
+UNITED KINGDOM	1996	147684.46480000002
+UNITED KINGDOM	1995	225267.65759999998
+UNITED KINGDOM	1994	140595.5864
+UNITED KINGDOM	1993	322548.49210000003
+UNITED KINGDOM	1992	67747.88279999999
+UNITED STATES	1998	3957.0431999999996
+UNITED STATES	1997	94729.5704
+UNITED STATES	1996	79297.85670000002
+UNITED STATES	1995	62201.23360000001
+UNITED STATES	1994	43075.629899999985
+UNITED STATES	1993	27168.486199999996
+UNITED STATES	1992	34092.366
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+EXPLAIN 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate	i_o_orderdate	4	const	1	
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	part	eq_ref	PRIMARY,i_p_retailprice	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+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;
+o_orderkey	p_partkey
+5895	200
+set optimizer_switch=@save_optimizer_switch;
+DROP DATABASE dbt3_s001;
+use test;
+set optimizer_use_stat_tables=@save_optimizer_use_stat_tables;