Supported extended keys when collecting and using persistent statistics.

1 files changed, 75 insertions, 0 deletions

diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
index 089c7871506..08fb5ee825e 100644
--- a/mysql-test/r/stat_tables.result
+++ b/mysql-test/r/stat_tables.result
@@ -3,6 +3,81 @@ set optimizer_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';
+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 optimizer_switch=@save_optimizer_switch;
+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;
+delete from mysql.index_stat;
 select * from mysql.table_stat;
 db_name	table_name	cardinality
 dbt3_s001	customer	150