Supported extended keys when collecting and using persistent statistics.

4 files changed, 210 insertions, 1 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
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
index 005e4c4828e..99e3a2ab083 100644
--- a/mysql-test/r/stat_tables_innodb.result
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -1,9 +1,86 @@
 SET SESSION STORAGE_ENGINE='InnoDB';
+set @save_optimizer_switch_for_stat_tables_test=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
 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;
+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	211	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	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.customer.c_nationkey	1	Using index
+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
+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
@@ -18,32 +95,54 @@ 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	customer	i_c_nationkey	2	1
 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_shipdate	2	1.014872401554842
+dbt3_s001	lineitem	i_l_shipdate	3	1
 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_suppkey_partkey	3	1.0030065141139135
+dbt3_s001	lineitem	i_l_suppkey_partkey	4	1
 dbt3_s001	lineitem	i_l_partkey	1	30.025
+dbt3_s001	lineitem	i_l_partkey	2	1.008904569892473
+dbt3_s001	lineitem	i_l_partkey	3	1
 dbt3_s001	lineitem	i_l_suppkey	1	600.5
+dbt3_s001	lineitem	i_l_suppkey	2	1.207277844792923
+dbt3_s001	lineitem	i_l_suppkey	3	1
 dbt3_s001	lineitem	i_l_receiptdate	1	2.6477072310405645
+dbt3_s001	lineitem	i_l_receiptdate	2	1.0152155536770922
+dbt3_s001	lineitem	i_l_receiptdate	3	1
 dbt3_s001	lineitem	i_l_orderkey	1	4.003333333333333
+dbt3_s001	lineitem	i_l_orderkey	2	1
 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_orderkey_quantity	3	1
 dbt3_s001	lineitem	i_l_commitdate	1	2.7159656264133876
+dbt3_s001	lineitem	i_l_commitdate	2	1.036416983085951
+dbt3_s001	lineitem	i_l_commitdate	3	1
 dbt3_s001	nation	PRIMARY	1	1
 dbt3_s001	nation	i_n_regionkey	1	5
+dbt3_s001	nation	i_n_regionkey	2	1
 dbt3_s001	orders	PRIMARY	1	1
 dbt3_s001	orders	i_o_orderdate	1	1.3321492007104796
+dbt3_s001	orders	i_o_orderdate	2	1
 dbt3_s001	orders	i_o_custkey	1	15
+dbt3_s001	orders	i_o_custkey	2	1
 dbt3_s001	part	PRIMARY	1	1
 dbt3_s001	part	i_p_retailprice	1	1
+dbt3_s001	part	i_p_retailprice	2	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_partkey	2	1
 dbt3_s001	partsupp	i_ps_suppkey	1	70
+dbt3_s001	partsupp	i_ps_suppkey	2	1
 dbt3_s001	region	PRIMARY	1	1
 dbt3_s001	supplier	PRIMARY	1	1
 dbt3_s001	supplier	i_s_nationkey	1	1.1111111111111112
+dbt3_s001	supplier	i_s_nationkey	2	1
 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
@@ -228,4 +327,5 @@ set optimizer_switch=@save_optimizer_switch;
 DROP DATABASE dbt3_s001;
 use test;
 set optimizer_use_stat_tables=@save_optimizer_use_stat_tables;
+set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
 SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
index cd896abebe1..aecb43fe763 100644
--- a/mysql-test/t/stat_tables.test
+++ b/mysql-test/t/stat_tables.test
@@ -10,15 +10,21 @@ 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';
  
 --disable_query_log
 --disable_result_log
 --disable_warnings
 --source include/dbt3_s001.inc
 create index i_p_retailprice on part(p_retailprice);
+delete from mysql.table_stat;
+delete from mysql.column_stat;
+delete from mysql.index_stat;
 ANALYZE TABLE
 customer, lineitem, nation, orders, part, partsupp, region, supplier;
-FLUSH TABLES customer, lineitem, nation, orders, part, partsupp, region, supplier;
+FLUSH TABLE mysql.table_stat, mysql.index_stat;
 --enable_warnings
 --enable_result_log
 --enable_query_log
@@ -26,6 +32,7 @@ FLUSH TABLES customer, lineitem, nation, orders, part, partsupp, region, supplie
 select * from mysql.table_stat;
 select * from mysql.index_stat;
 
+set optimizer_switch=@save_optimizer_switch; 
 
 set @save_optimizer_switch=@@optimizer_switch;
 set optimizer_switch='index_condition_pushdown=off';
@@ -46,6 +53,28 @@ eval $Q5;
 
 set optimizer_switch=@save_optimizer_switch;
 
+delete from mysql.index_stat;
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+ANALYZE TABLE
+customer, lineitem, nation, orders, part, partsupp, region, supplier;
+FLUSH TABLE mysql.table_stat, mysql.index_stat;
+--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';
+
+eval EXPLAIN $Q5;
+eval $Q5;
+
+set optimizer_switch=@save_optimizer_switch;
 
 let $Q8=
 select o_year,
diff --git a/mysql-test/t/stat_tables_innodb.test b/mysql-test/t/stat_tables_innodb.test
index e2ed647f49f..04e81de8f9d 100644
--- a/mysql-test/t/stat_tables_innodb.test
+++ b/mysql-test/t/stat_tables_innodb.test
@@ -2,6 +2,11 @@
 
 SET SESSION STORAGE_ENGINE='InnoDB';
 
+set @save_optimizer_switch_for_stat_tables_test=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+
 --source stat_tables.test
 
+set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
+
 SET SESSION STORAGE_ENGINE=DEFAULT;