Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1045 insertions, 0 deletions

diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result
new file mode 100644
index 00000000..bce9e884
--- /dev/null
+++ b/mysql-test/main/stat_tables_innodb.result
@@ -0,0 +1,1045 @@
+SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB';
+set @save_optimizer_switch_for_stat_tables_test=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+set @innodb_stats_persistent_save= @@innodb_stats_persistent;
+set @innodb_stats_persistent_sample_pages_save=
+@@innodb_stats_persistent_sample_pages;
+set global innodb_stats_persistent= 1;
+set global innodb_stats_persistent_sample_pages=100;
+select @@global.use_stat_tables;
+@@global.use_stat_tables
+COMPLEMENTARY
+select @@session.use_stat_tables;
+@@session.use_stat_tables
+COMPLEMENTARY
+set @save_use_stat_tables=@@use_stat_tables;
+set @save_histogram_size=@@global.histogram_size;
+set @@global.histogram_size=0,@@local.histogram_size=0;
+set optimizer_use_condition_selectivity=4;
+set use_stat_tables='preferably';
+set @save_histogram_type=@@histogram_type;
+set histogram_type='single_prec_hb';
+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_stats;
+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_stats;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	customer	PRIMARY	1	1.0000
+dbt3_s001	customer	i_c_nationkey	1	6.0000
+dbt3_s001	lineitem	PRIMARY	1	4.0033
+dbt3_s001	lineitem	PRIMARY	2	1.0000
+dbt3_s001	lineitem	i_l_shipdate	1	2.6500
+dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
+dbt3_s001	lineitem	i_l_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey	1	600.5000
+dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
+dbt3_s001	lineitem	i_l_orderkey	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
+dbt3_s001	lineitem	i_l_commitdate	1	2.7160
+dbt3_s001	nation	PRIMARY	1	1.0000
+dbt3_s001	nation	i_n_regionkey	1	5.0000
+dbt3_s001	orders	PRIMARY	1	1.0000
+dbt3_s001	orders	i_o_orderdate	1	1.3321
+dbt3_s001	orders	i_o_custkey	1	15.0000
+dbt3_s001	part	PRIMARY	1	1.0000
+dbt3_s001	part	i_p_retailprice	1	1.0000
+dbt3_s001	partsupp	PRIMARY	1	3.5000
+dbt3_s001	partsupp	PRIMARY	2	1.0000
+dbt3_s001	partsupp	i_ps_partkey	1	3.5000
+dbt3_s001	partsupp	i_ps_suppkey	1	70.0000
+dbt3_s001	region	PRIMARY	1	1.0000
+dbt3_s001	supplier	PRIMARY	1	1.0000
+dbt3_s001	supplier	i_s_nationkey	1	1.1111
+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	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	nation	ref	PRIMARY,i_n_regionkey	i_n_regionkey	5	dbt3_s001.region.r_regionkey	5	
+1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	Using index
+1	SIMPLE	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	Using index
+1	SIMPLE	orders	ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (14%)	Using where; Using rowid filter
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	9	dbt3_s001.supplier.s_suppkey,dbt3_s001.orders.o_orderkey	1	
+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.87150000007
+ARGENTINA	69817.1451
+set optimizer_switch=@save_optimizer_switch;
+delete from mysql.index_stats;
+select * from mysql.table_stats;
+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_stats;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	customer	PRIMARY	1	1.0000
+dbt3_s001	customer	i_c_nationkey	1	6.0000
+dbt3_s001	customer	i_c_nationkey	2	1.0000
+dbt3_s001	lineitem	PRIMARY	1	4.0033
+dbt3_s001	lineitem	PRIMARY	2	1.0000
+dbt3_s001	lineitem	i_l_shipdate	1	2.6500
+dbt3_s001	lineitem	i_l_shipdate	2	1.0149
+dbt3_s001	lineitem	i_l_shipdate	3	1.0000
+dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
+dbt3_s001	lineitem	i_l_suppkey_partkey	3	1.0030
+dbt3_s001	lineitem	i_l_suppkey_partkey	4	1.0000
+dbt3_s001	lineitem	i_l_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_partkey	2	1.0089
+dbt3_s001	lineitem	i_l_partkey	3	1.0000
+dbt3_s001	lineitem	i_l_suppkey	1	600.5000
+dbt3_s001	lineitem	i_l_suppkey	2	1.2073
+dbt3_s001	lineitem	i_l_suppkey	3	1.0000
+dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
+dbt3_s001	lineitem	i_l_receiptdate	2	1.0152
+dbt3_s001	lineitem	i_l_receiptdate	3	1.0000
+dbt3_s001	lineitem	i_l_orderkey	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey	2	1.0000
+dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
+dbt3_s001	lineitem	i_l_orderkey_quantity	3	1.0000
+dbt3_s001	lineitem	i_l_commitdate	1	2.7160
+dbt3_s001	lineitem	i_l_commitdate	2	1.0364
+dbt3_s001	lineitem	i_l_commitdate	3	1.0000
+dbt3_s001	nation	PRIMARY	1	1.0000
+dbt3_s001	nation	i_n_regionkey	1	5.0000
+dbt3_s001	nation	i_n_regionkey	2	1.0000
+dbt3_s001	orders	PRIMARY	1	1.0000
+dbt3_s001	orders	i_o_orderdate	1	1.3321
+dbt3_s001	orders	i_o_orderdate	2	1.0000
+dbt3_s001	orders	i_o_custkey	1	15.0000
+dbt3_s001	orders	i_o_custkey	2	1.0000
+dbt3_s001	part	PRIMARY	1	1.0000
+dbt3_s001	part	i_p_retailprice	1	1.0000
+dbt3_s001	part	i_p_retailprice	2	1.0000
+dbt3_s001	partsupp	PRIMARY	1	3.5000
+dbt3_s001	partsupp	PRIMARY	2	1.0000
+dbt3_s001	partsupp	i_ps_partkey	1	3.5000
+dbt3_s001	partsupp	i_ps_partkey	2	1.0000
+dbt3_s001	partsupp	i_ps_suppkey	1	70.0000
+dbt3_s001	partsupp	i_ps_suppkey	2	1.0000
+dbt3_s001	region	PRIMARY	1	1.0000
+dbt3_s001	supplier	PRIMARY	1	1.0000
+dbt3_s001	supplier	i_s_nationkey	1	1.1111
+dbt3_s001	supplier	i_s_nationkey	2	1.0000
+select * from mysql.table_stats where table_name='orders';
+db_name	table_name	cardinality
+dbt3_s001	orders	1500
+select * from mysql.index_stats where table_name='orders';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	orders	PRIMARY	1	1.0000
+dbt3_s001	orders	i_o_orderdate	1	1.3321
+dbt3_s001	orders	i_o_orderdate	2	1.0000
+dbt3_s001	orders	i_o_custkey	1	15.0000
+dbt3_s001	orders	i_o_custkey	2	1.0000
+select (select cardinality from mysql.table_stats where table_name='orders') /
+(select avg_frequency from mysql.index_stats 
+where index_name='i_o_orderdate' and prefix_arity=1) as n_distinct;
+n_distinct
+1126.0416
+select count(distinct o_orderdate) from orders;
+count(distinct o_orderdate)
+1126
+select (select cardinality from mysql.table_stats where table_name='orders') /
+(select avg_frequency from mysql.index_stats 
+where index_name='i_o_custkey' and prefix_arity=1) as n_distinct;
+n_distinct
+100.0000
+select count(distinct o_custkey) from orders;
+count(distinct o_custkey)
+100
+show index from orders;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+orders	0	PRIMARY	1	o_orderkey	A	1500	NULL	NULL		BTREE			NO
+orders	1	i_o_orderdate	1	o_orderDATE	A	1126	NULL	NULL	YES	BTREE			NO
+orders	1	i_o_custkey	1	o_custkey	A	100	NULL	NULL	YES	BTREE			NO
+select index_name, column_name, cardinality from information_schema.statistics
+where table_name='orders';
+index_name	column_name	cardinality
+PRIMARY	o_orderkey	1500
+i_o_orderdate	o_orderDATE	1126
+i_o_custkey	o_custkey	100
+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	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	nation	ref	PRIMARY,i_n_regionkey	i_n_regionkey	5	dbt3_s001.region.r_regionkey	5	
+1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	Using index
+1	SIMPLE	customer	ref	PRIMARY,i_c_nationkey	i_c_nationkey	5	dbt3_s001.nation.n_nationkey	6	Using index
+1	SIMPLE	orders	ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	i_o_custkey|i_o_orderdate	5|4	dbt3_s001.customer.c_custkey	15 (14%)	Using where; Using rowid filter
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey	9	dbt3_s001.supplier.s_suppkey,dbt3_s001.orders.o_orderkey	1	
+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.87150000007
+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	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+1	SIMPLE	part	ALL	PRIMARY	NULL	NULL	NULL	200	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	i_l_partkey	5	dbt3_s001.part.p_partkey	30	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	
+1	SIMPLE	orders	eq_ref	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
+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	Using where
+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	index	PRIMARY,i_s_nationkey	i_s_nationkey	5	NULL	10	Using where; Using index; 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	part	range	PRIMARY,i_p_retailprice	i_p_retailprice	9	NULL	1	Using where; Using index
+1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate	i_o_orderdate	4	const	1	Using index
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity	i_l_partkey	9	dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey	1	Using index
+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;
+#
+# Bug mdev-473: ANALYZE table locked for write
+# 
+set use_stat_tables='complementary';
+create table t1 (i int);
+lock table t1 write;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+alter table t1 add column a varchar(8);
+drop table t1;
+#
+# Bug mdev-487: memory leak in ANALYZE with stat tables
+# 
+SET  use_stat_tables = 'preferably';
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+DELETE FROM t1 WHERE a=1;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+DROP TABLE t1;
+#
+# Bug mdev-518: corrupted/missing statistical tables
+# 
+CREATE TABLE t1 (i int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+FLUSH TABLE t1;
+SET use_stat_tables='never';
+EXPLAIN SELECT * FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+FLUSH TABLES;
+SET use_stat_tables='preferably';
+EXPLAIN SELECT * FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-5204: invalid impossible where after reading const tables  
+#                when use_stat_tables = 'preferably'
+#
+set use_stat_tables = 'preferably';
+CREATE TABLE t1 (id int PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+CREATE TABLE t2 (name char(3)) ENGINE=MyISAM;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+INSERT INTO t2 VALUES ('USA'),('AUS');
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	name
+1	AUS
+EXPLAIN
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	Using where
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	name
+1	AUS
+EXPLAIN
+SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+DROP TABLE t1,t2;
+#
+# MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists
+#
+drop database if exists db1;
+drop database if exists db1;
+create database db1;
+create database db2;
+use db1;
+#
+# First, run the original testcase:
+#
+create table t1 (i int);
+insert into t1 values (10),(20);
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+db1.t1	analyze	status	Engine-independent statistics collected
+db1.t1	analyze	status	OK
+rename table t1 to db2.t1;
+# Verify that stats in the old database are gone:
+select * from mysql.column_stats where db_name='db1' and table_name='t1';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+select * from mysql.table_stats where db_name='db1' and table_name='t1';
+db_name	table_name	cardinality
+# Verify that stats are present in the new database:
+select * from mysql.column_stats where db_name='db2' and table_name='t1';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+db2	t1	i	10	20	0.0000	4.0000	1.0000	0	NULL	NULL
+select * from mysql.table_stats where db_name='db2' and table_name='t1';
+db_name	table_name	cardinality
+db2	t1	2
+#
+# Now, try with more than one column and with indexes:
+#
+use test;
+create table t1(a int primary key);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+use db1;
+create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b));
+insert into t2 select a/10, a/2, a from test.t1;
+analyze table t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+db1.t2	analyze	status	Engine-independent statistics collected
+db1.t2	analyze	status	OK
+alter table t2 rename db2.t2;
+# Verify that stats in the old database are gone:
+select * from mysql.table_stats where db_name='db1' and table_name='t2';
+db_name	table_name	cardinality
+select * from mysql.column_stats where db_name='db1' and table_name='t2';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+select * from mysql.index_stats where db_name='db1' and table_name='t2';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+# Verify that stats are present in the new database:
+select * from mysql.table_stats where db_name='db2' and table_name='t2';
+db_name	table_name	cardinality
+db2	t2	10
+select * from mysql.column_stats where db_name='db2' and table_name='t2';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+db2	t2	a	0	1	0.0000	4.0000	5.0000	0	NULL	NULL
+db2	t2	b	0	5	0.0000	4.0000	1.6667	0	NULL	NULL
+db2	t2	c	0	9	0.0000	4.0000	1.0000	0	NULL	NULL
+select * from mysql.index_stats where db_name='db2' and table_name='t2';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+db2	t2	IDX1	1	5.0000
+db2	t2	IDX2	1	5.0000
+db2	t2	IDX2	2	1.6667
+use db2;
+#
+# Now, rename within the same database and verify:
+#
+rename table t2 to t3;
+# No stats under old name:
+select * from mysql.table_stats where db_name='db2' and table_name='t2';
+db_name	table_name	cardinality
+select * from mysql.column_stats where db_name='db2' and table_name='t2';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+select * from mysql.index_stats where db_name='db2' and table_name='t2';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+# Stats under the new name:
+select * from mysql.table_stats where db_name='db2' and table_name='t3';
+db_name	table_name	cardinality
+db2	t3	10
+select * from mysql.column_stats where db_name='db2' and table_name='t3';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+db2	t3	a	0	1	0.0000	4.0000	5.0000	0	NULL	NULL
+db2	t3	b	0	5	0.0000	4.0000	1.6667	0	NULL	NULL
+db2	t3	c	0	9	0.0000	4.0000	1.0000	0	NULL	NULL
+select * from mysql.index_stats where db_name='db2' and table_name='t3';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+db2	t3	IDX1	1	5.0000
+db2	t3	IDX2	1	5.0000
+db2	t3	IDX2	2	1.6667
+use test;
+drop database db1;
+drop database db2;
+drop table t1;
+#
+# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
+#
+SET use_stat_tables = PREFERABLY;
+SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
+CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' )
+NULL
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16757: manual addition of min/max statistics for BLOB
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 't'
+test.t1	analyze	status	OK
+SELECT * FROM mysql.column_stats;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+test	t1	pk	1	2	0.0000	4.0000	1.0000	NULL	NULL	NULL
+DELETE FROM mysql.column_stats
+WHERE db_name='test' AND table_name='t1' AND column_name='t';
+INSERT INTO mysql.column_stats VALUES
+('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
+SELECT * FROM mysql.column_stats;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+test	t1	pk	1	2	0.0000	4.0000	1.0000	NULL	NULL	NULL
+test	t1	t	bar	foo	0.0000	3.0000	1.0000	0	NULL	NULL
+SELECT pk FROM t1;
+pk
+1
+2
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SELECT * FROM t1;
+pk	c
+1	foo
+2	bar
+SELECT * FROM mysql.column_stats;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+test	t1	pk	1	2	0.0000	4.0000	1.0000	NULL	NULL	NULL
+test	t1	c	bar	foo	0.0000	3.0000	1.0000	0	NULL	NULL
+CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
+SELECT * FROM t1;
+pk	a
+SELECT * FROM mysql.column_stats;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4
+#
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @@optimizer_use_condition_selectivity=4;
+set @@use_stat_tables= PREFERABLY;
+explain
+SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	PROFILING	ALL	NULL	NULL	NULL	NULL	NULL	
+1	SIMPLE	global_priv	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-17734: AddressSanitizer: use-after-poison in create_key_parts_for_pseudo_indexes
+#
+set @@use_stat_tables= PREFERABLY;
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set @@optimizer_use_condition_selectivity=4;
+set @save_use_stat_tables= @@use_stat_tables;
+create table t1 (a int, b int);
+insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10);
+analyze table t1 persistent for columns (a) indexes ();
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select * from t1 where a=1 and b=3;
+a	b
+1	3
+set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set use_stat_tables=@save_use_stat_tables;
+drop table t1;
+#
+# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
+#
+SET use_stat_tables= PREFERABLY;
+CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
+INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
+SELECT MAX(pk) FROM t1;
+MAX(pk)
+NULL
+DROP TABLE t1;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-17605: SHOW INDEXES with use_stat_tables='preferably'
+#
+set use_stat_tables='preferably';
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=off';
+select * from mysql.table_stats;
+db_name	table_name	cardinality
+dbt3_s001	lineitem	6005
+select * from mysql.index_stats;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	lineitem	PRIMARY	1	4.0033
+dbt3_s001	lineitem	PRIMARY	2	1.0000
+dbt3_s001	lineitem	i_l_shipdate	1	2.6500
+dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
+dbt3_s001	lineitem	i_l_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey	1	600.5000
+dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
+dbt3_s001	lineitem	i_l_orderkey	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
+dbt3_s001	lineitem	i_l_commitdate	1	2.7160
+SHOW INDEXES FROM lineitem;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+lineitem	0	PRIMARY	1	l_orderkey	A	1500	NULL	NULL		BTREE			NO
+lineitem	0	PRIMARY	2	l_linenumber	A	6005	NULL	NULL		BTREE			NO
+lineitem	1	i_l_shipdate	1	l_shipDATE	A	2266	NULL	NULL	YES	BTREE			NO
+lineitem	1	i_l_suppkey_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE			NO
+lineitem	1	i_l_suppkey_partkey	2	l_suppkey	A	699	NULL	NULL	YES	BTREE			NO
+lineitem	1	i_l_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE			NO
+lineitem	1	i_l_suppkey	1	l_suppkey	A	10	NULL	NULL	YES	BTREE			NO
+lineitem	1	i_l_receiptdate	1	l_receiptDATE	A	2268	NULL	NULL	YES	BTREE			NO
+lineitem	1	i_l_orderkey	1	l_orderkey	A	1500	NULL	NULL		BTREE			NO
+lineitem	1	i_l_orderkey_quantity	1	l_orderkey	A	1500	NULL	NULL		BTREE			NO
+lineitem	1	i_l_orderkey_quantity	2	l_quantity	A	5771	NULL	NULL	YES	BTREE			NO
+lineitem	1	i_l_commitdate	1	l_commitDATE	A	2210	NULL	NULL	YES	BTREE			NO
+SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	INDEX_COMMENT	IGNORED
+def	dbt3_s001	lineitem	0	dbt3_s001	PRIMARY	1	l_orderkey	A	1500	NULL	NULL		BTREE			NO
+def	dbt3_s001	lineitem	0	dbt3_s001	PRIMARY	2	l_linenumber	A	6005	NULL	NULL		BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_shipdate	1	l_shipDATE	A	2266	NULL	NULL	YES	BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey_partkey	2	l_suppkey	A	699	NULL	NULL	YES	BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey	1	l_suppkey	A	10	NULL	NULL	YES	BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_receiptdate	1	l_receiptDATE	A	2268	NULL	NULL	YES	BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey	1	l_orderkey	A	1500	NULL	NULL		BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey_quantity	1	l_orderkey	A	1500	NULL	NULL		BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey_quantity	2	l_quantity	A	5771	NULL	NULL	YES	BTREE			NO
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_commitdate	1	l_commitDATE	A	2210	NULL	NULL	YES	BTREE			NO
+SELECT
+COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber),
+COUNT(DISTINCT l_shipDATE),
+COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey),
+COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE),
+COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE)
+FROM lineitem;
+COUNT(DISTINCT l_orderkey)	COUNT(DISTINCT l_orderkey,l_linenumber)	COUNT(DISTINCT l_shipDATE)	COUNT(DISTINCT l_partkey)	COUNT(DISTINCT l_partkey,l_suppkey)	COUNT(DISTINCT l_suppkey)	COUNT(DISTINCT l_receiptDATE)	COUNT(DISTINCT l_orderkey, l_quantity)	COUNT(DISTINCT l_commitDATE)
+1500	6005	2266	200	700	10	2268	5772	2211
+set optimizer_switch=@save_optimizer_switch;
+DROP DATABASE dbt3_s001;
+USE test;
+delete from mysql.table_stats;
+delete from mysql.column_stats;
+delete from mysql.index_stats;
+#
+# MDEV-19352: Server crash in alloc_histograms_for_table_share upon query from information schema
+#
+use test;
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set @@optimizer_use_condition_selectivity= 4;
+set use_stat_tables='preferably';
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT);
+CREATE VIEW v AS SELECT * FROM t1 JOIN t2;
+INSERT INTO t2 SELECT * FROM x;
+ERROR 42S02: Table 'test.x' doesn't exist
+select * from information_schema.tables where table_name='v';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT	MAX_INDEX_LENGTH	TEMPORARY
+def	test	v	VIEW	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	VIEW	NULL	NULL
+set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+drop table t1,t2;
+drop view v;
+#
+# MDEV-19407: Assertion `field->table->stats_is_read' failed in is_eits_usable
+#
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set @@optimizer_use_condition_selectivity= 1;
+set @@use_stat_tables='never';
+create table t1(pk int);
+insert into t1 values (4),(3);
+set @@optimizer_use_condition_selectivity= 4;
+set use_stat_tables='preferably';
+INSERT INTO t1 SELECT * FROM x;
+ERROR 42S02: Table 'test.x' doesn't exist
+CREATE TABLE t2 SELECT pk FROM t1 WHERE pk>2;
+select * from t2;
+pk
+4
+3
+drop table t1,t2;
+create table t1(a int,b int, key k1(a) );
+insert into t1 values(1,1),(2,2),(3,3);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select * from mysql.index_stats, t1 where index_name='k1' and t1.a > 1 and t1.b > 1;
+db_name	table_name	index_name	prefix_arity	avg_frequency	a	b
+test	t1	k1	1	1.0000	2	2
+test	t1	k1	1	1.0000	3	3
+drop table t1;
+set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set @save_optimizer_switch=@@optimizer_switch;
+set use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-18899: Server crashes in Field::set_warning_truncated_wrong_value
+#
+set names utf8;
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set optimizer_use_condition_selectivity=4;
+set use_stat_tables=preferably;
+set histogram_size=255;
+create table t1 ( a varchar(255) character set utf8);
+insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255));
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select HEX(RIGHT(min_value, 1)), length(min_value) from mysql.column_stats where db_name='test' and table_name='t1';
+HEX(RIGHT(min_value, 1))	length(min_value)
+A7	254
+select HEX(RIGHT(max_value, 1)), length(max_value) from mysql.column_stats where db_name='test' and table_name='t1';
+HEX(RIGHT(max_value, 1))	length(max_value)
+A5	254
+analyze select * from t1 where a  >= 'ӥ';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	2.00	50.00	50.00	Using where
+set @save_sql_mode= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+update mysql.column_stats set min_value= REPEAT('ӥ',255) where db_name='test' and table_name='t1';
+Warnings:
+Warning	1265	Data truncated for column 'min_value' at row 1
+select HEX(RIGHT(min_value, 1)), length(min_value) from mysql.column_stats where db_name='test' and table_name='t1';
+HEX(RIGHT(min_value, 1))	length(min_value)
+D3	255
+analyze select * from t1 where a  >= 'ӥ';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	2.00	50.00	50.00	Using where
+set names latin1;
+drop table t1;
+CREATE TABLE t1 (col1 date);
+INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');
+INSERT INTO t1 VALUES('0000-10-31');
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+update mysql.column_stats set min_value='2004-0-31123' where db_name='test' and table_name='t1';
+select min_value from mysql.column_stats where db_name='test' and table_name='t1';
+min_value
+2004-0-31123
+select * from t1;
+col1
+2004-01-01
+2004-02-29
+0000-10-31
+set @@sql_mode= @save_sql_mode;
+set @@use_stat_tables=@save_use_stat_tables;
+set @@histogram_size= @save_histogram_size;
+set @@histogram_type=@save_histogram_type;
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+drop table t1;
+#
+# MDEV-20589: Server still crashes in Field::set_warning_truncated_wrong_value
+#
+set names utf8;
+create table t1 ( a varchar(255) character set utf8);
+insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255));
+set use_stat_tables='preferably';
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+set @save_sql_mode= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
+update mysql.column_stats set min_value= REPEAT('ӥ',256) where db_name='test' and table_name='t1';
+Warnings:
+Warning	1265	Data truncated for column 'min_value' at row 1
+set @@sql_mode= @save_sql_mode;
+select length(a) from t1 where a=REPEAT('ӥ',255);
+length(a)
+510
+set names latin1;
+set @@use_stat_tables=@save_use_stat_tables;
+drop table t1;
+#
+# MDEV-23753: SIGSEGV in Column_stat::store_stat_fields
+#
+CREATE TABLE t1 (a INT, b INT) PARTITION BY HASH (b) PARTITIONS 2;
+LOCK TABLES t1 WRITE;
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES ();
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (nonexisting) INDEXES (nonexisting);
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	error	Invalid argument
+DROP TABLE t1;
+# please keep this at the last
+set @@global.histogram_size=@save_histogram_size;
+# Start of 10.4 tests
+set histogram_size=0;
+#
+# MDEV-17255: New optimizer defaults and ANALYZE TABLE
+#
+create table t1 (a int, b int);
+insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10);
+set use_stat_tables= preferably_for_queries;
+#
+# with use_stat_tables= PREFERABLY_FOR_QUERIES
+# analyze table t1 will not collect statistics
+#
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select * from mysql.column_stats;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+analyze
+select * from t1 where a = 1 and b=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	10.00	Using where
+#
+# with use_stat_tables= PREFERABLY_FOR_QUERIES
+# analyze table t1 will  collect statistics if we use PERSISTENT
+# for columns, indexes or everything
+#
+analyze table t1 persistent for columns (a) indexes ();
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select * from mysql.column_stats;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+test	t1	a	1	4	0.0000	4.0000	2.5000	0	NULL	NULL
+# filtered shows that we used the data from stat tables
+analyze
+select * from t1 where a = 1 and b=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	25.00	10.00	Using where
+#
+# with use_stat_tables= PREFERABLY
+# analyze table t1 will collect statistics
+#
+set use_stat_tables=PREFERABLY;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+select * from mysql.column_stats;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+test	t1	a	1	4	0.0000	4.0000	2.5000	0	NULL	NULL
+test	t1	b	2	10	0.0000	4.0000	1.1111	0	NULL	NULL
+# filtered shows that we used the data from stat tables
+analyze
+select * from t1 where a=1 and b=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	10.00	2.78	10.00	Using where
+drop table t1;
+set @@global.histogram_size=@save_histogram_size;
+#
+# End of 10.4 tests
+#
+#
+# MDEV-29693 ANALYZE TABLE still flushes table definition cache
+# when engine-independent statistics is used
+#
+create table t1 (a int);
+insert into t1 select seq from seq_0_to_99;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+explain extended select count(*) from t1 where a < 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	50.51	Using where
+Warnings:
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
+connect  con1, localhost, root,,;
+connection con1;
+explain extended select count(*) from t1 where a < 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	50.51	Using where
+Warnings:
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
+connection default;
+update t1 set a= a +100;
+# Explain shows outdated statistics:
+explain extended select count(*) from t1 where a < 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	50.51	Using where
+Warnings:
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
+connection con1;
+explain extended select count(*) from t1 where a < 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	50.51	Using where
+Warnings:
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
+connection default;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+# Now explain shows updated statistics:
+explain extended select count(*) from t1 where a < 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	1.00	Using where
+Warnings:
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
+connection con1;
+explain extended select count(*) from t1 where a < 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	1.00	Using where
+Warnings:
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
+connection con1;
+# Run update and analyze in con1:
+update t1 set a= a - 150;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+connection default;
+# Explain shows updated statistics:
+explain extended select count(*) from t1 where a < 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where
+Warnings:
+Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
+disconnect con1;
+drop table t1;
+#
+# End of 10.6 tests
+#
+set global innodb_stats_persistent= @innodb_stats_persistent_save;
+set global innodb_stats_persistent_sample_pages=
+@innodb_stats_persistent_sample_pages_save;
+set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
+#
+# MDEV-22851: Engine independent index statistics are incorrect for large tables on Windows.
+#
+CREATE TABLE t1(a INT) ENGINE=INNODB;
+INSERT INTO t1 SELECT 1 FROM seq_1_to_60000;
+SET @save_use_stat_tables= @@use_stat_tables;
+SET use_stat_tables= preferably;
+SELECT count(*) FROM t1;
+count(*)
+60000
+CREATE INDEX idx ON t1(a);
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES (idx);
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SELECT * FROM mysql.index_stats where table_name='t1';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	idx	1	60000.0000
+SELECT * FROM mysql.column_stats where table_name='t1';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency	hist_size	hist_type	histogram
+test	t1	a	1	1	0.0000	4.0000	60000.0000	0	NULL	NULL
+SET use_stat_tables= @save_use_stat_tables;
+DROP TABLE t1;
+# end of 10.1 tests
+SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT;