Added histogams for table columns.

1 files changed, 110 insertions, 1 deletions

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 3db8da46ea2..af3f6fdda2d 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -10,6 +10,7 @@ DROP DATABASE IF EXISTS dbt3_s001;
 CREATE DATABASE dbt3_s001;
 use dbt3_s001;
 set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_histogram_size=@@histogram_size;
 EXPLAIN EXTENDED select sql_calc_found_rows
 s_name, s_address
 from supplier, nation
@@ -84,7 +85,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	part	ALL	PRIMARY	NULL	NULL	NULL	200	4.17	Using where
 2	MATERIALIZED	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	4	dbt3_s001.part.p_partkey	3	100.00	Using where
-4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	100.00	Using where
+4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	15.14	Using where
 Warnings:
 Note	1276	Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
 Note	1276	Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2
@@ -109,6 +110,114 @@ order by s_name
 limit 10;
 s_name	s_address
 Supplier#000000010	Saygah3gYWMp72i PY
+set histogram_size=15;
+flush table part;
+ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
+Table	Op	Msg_type	Msg_text
+dbt3_s001.part	analyze	status	Table is already up to date
+set optimizer_use_condition_selectivity=4;
+EXPLAIN EXTENDED select sql_calc_found_rows
+s_name, s_address
+from supplier, nation
+where s_suppkey in (select ps_suppkey from partsupp
+where ps_partkey in (select p_partkey from part
+where p_name like 'g%')
+and ps_availqty >
+(select 0.5 * sum(l_quantity)
+from lineitem
+where l_partkey = ps_partkey
+and l_suppkey = ps_suppkey
+and l_shipdate >= date('1993-01-01')
+and l_shipdate < date('1993-01-01') +
+interval '1' year ))
+and s_nationkey = n_nationkey
+and n_name = 'UNITED STATES'
+order by s_name
+limit 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	6.25	Using where
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_partkey	4	dbt3_s001.part.p_partkey	3	100.00	Using where; FirstMatch(supplier)
+4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	15.14	Using where
+Warnings:
+Note	1276	Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
+Note	1276	Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2
+Note	1003	select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where ((`dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey`) and (`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`nation`.`n_name` = 'UNITED STATES') and (`dbt3_s001`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey`) and (`dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((select (0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`)) from `dbt3_s001`.`lineitem` where ((`dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`) and (`dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey`) and (`dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date))) and (`dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>((cast('1993-01-01' as date) + interval '1' year))))))) and (`dbt3_s001`.`part`.`p_name` like 'g%')) order by `dbt3_s001`.`supplier`.`s_name` limit 10
+select sql_calc_found_rows
+s_name, s_address
+from supplier, nation
+where s_suppkey in (select ps_suppkey from partsupp
+where ps_partkey in (select p_partkey from part
+where p_name like 'g%')
+and ps_availqty >
+(select 0.5 * sum(l_quantity)
+from lineitem
+where l_partkey = ps_partkey
+and l_suppkey = ps_suppkey
+and l_shipdate >= date('1993-01-01')
+and l_shipdate < date('1993-01-01') +
+interval '1' year ))
+and s_nationkey = n_nationkey
+and n_name = 'UNITED STATES'
+order by s_name
+limit 10;
+s_name	s_address
+Supplier#000000010	Saygah3gYWMp72i PY
+set histogram_size=24;
+flush table nation;
+ANALYZE TABLE nation PERSISTENT FOR COLUMNS(n_name) INDEXES();
+Table	Op	Msg_type	Msg_text
+dbt3_s001.nation	analyze	status	Table is already up to date
+EXPLAIN EXTENDED select sql_calc_found_rows
+s_name, s_address
+from supplier, nation
+where s_suppkey in (select ps_suppkey from partsupp
+where ps_partkey in (select p_partkey from part
+where p_name like 'g%')
+and ps_availqty >
+(select 0.5 * sum(l_quantity)
+from lineitem
+where l_partkey = ps_partkey
+and l_suppkey = ps_suppkey
+and l_shipdate >= date('1993-01-01')
+and l_shipdate < date('1993-01-01') +
+interval '1' year ))
+and s_nationkey = n_nationkey
+and n_name = 'UNITED STATES'
+order by s_name
+limit 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	6.25	Using where
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_partkey	4	dbt3_s001.part.p_partkey	3	100.00	Using where; FirstMatch(supplier)
+4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	15.14	Using where
+Warnings:
+Note	1276	Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
+Note	1276	Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2
+Note	1003	select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where ((`dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey`) and (`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`nation`.`n_name` = 'UNITED STATES') and (`dbt3_s001`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey`) and (`dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((select (0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`)) from `dbt3_s001`.`lineitem` where ((`dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`) and (`dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey`) and (`dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date))) and (`dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>((cast('1993-01-01' as date) + interval '1' year))))))) and (`dbt3_s001`.`part`.`p_name` like 'g%')) order by `dbt3_s001`.`supplier`.`s_name` limit 10
+select sql_calc_found_rows
+s_name, s_address
+from supplier, nation
+where s_suppkey in (select ps_suppkey from partsupp
+where ps_partkey in (select p_partkey from part
+where p_name like 'g%')
+and ps_availqty >
+(select 0.5 * sum(l_quantity)
+from lineitem
+where l_partkey = ps_partkey
+and l_suppkey = ps_suppkey
+and l_shipdate >= date('1993-01-01')
+and l_shipdate < date('1993-01-01') +
+interval '1' year ))
+and s_nationkey = n_nationkey
+and n_name = 'UNITED STATES'
+order by s_name
+limit 10;
+s_name	s_address
+Supplier#000000010	Saygah3gYWMp72i PY
 DROP DATABASE dbt3_s001;
+set histogram_size=@save_histogram_size;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;