MDEV-4345

Sampling of selectivity of LIKE predicate.

1 files changed, 83 insertions, 0 deletions

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 79ff506d603..512ef4ffc1b 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -37,6 +37,89 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit
 DROP DATABASE IF EXISTS dbt3_s001;
 CREATE DATABASE dbt3_s001;
 use dbt3_s001;
+=== Q2 ===
+set optimizer_use_condition_selectivity=5;
+explain extended
+select
+s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
+from
+part, supplier, partsupp, nation, region
+where
+p_partkey = ps_partkey
+and s_suppkey = ps_suppkey
+and p_size = 9
+and p_type like '%TIN'
+	and s_nationkey = n_nationkey
+and n_regionkey = r_regionkey
+and r_name = 'ASIA'
+	and ps_supplycost = (
+select
+min(ps_supplycost)
+from
+partsupp, supplier, nation, region
+where
+p_partkey = ps_partkey
+and s_suppkey = ps_suppkey
+and s_nationkey = n_nationkey
+and n_regionkey = r_regionkey
+and r_name = 'ASIA'
+	)
+order by
+s_acctbal desc, n_name, s_name, p_partkey;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	0.31	Using where; Using temporary; Using filesort
+1	PRIMARY	region	ALL	PRIMARY	NULL	NULL	NULL	5	20.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	4	dbt3_s001.part.p_partkey	3	100.00	Using where
+1	PRIMARY	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	80.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	100.00	Using where
+2	DEPENDENT SUBQUERY	region	ALL	PRIMARY	NULL	NULL	NULL	5	20.00	Using where
+2	DEPENDENT SUBQUERY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	4	dbt3_s001.part.p_partkey	3	100.00	
+2	DEPENDENT SUBQUERY	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.partsupp.ps_suppkey	1	100.00	Using where
+2	DEPENDENT SUBQUERY	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'dbt3_s001.part.p_partkey' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `dbt3_s001`.`supplier`.`s_acctbal` AS `s_acctbal`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`nation`.`n_name` AS `n_name`,`dbt3_s001`.`part`.`p_partkey` AS `p_partkey`,`dbt3_s001`.`part`.`p_mfgr` AS `p_mfgr`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`dbt3_s001`.`supplier`.`s_comment` AS `s_comment` from `dbt3_s001`.`part` join `dbt3_s001`.`supplier` join `dbt3_s001`.`partsupp` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey`) and (`dbt3_s001`.`part`.`p_size` = 9) and (`dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey`) and (`dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey`) and (`dbt3_s001`.`region`.`r_name` = 'ASIA') and (`dbt3_s001`.`part`.`p_type` like '%TIN') and (`dbt3_s001`.`partsupp`.`ps_supplycost` = <expr_cache><`dbt3_s001`.`part`.`p_partkey`>((select min(`dbt3_s001`.`partsupp`.`ps_supplycost`) from `dbt3_s001`.`partsupp` join `dbt3_s001`.`supplier` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where ((`dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey`) and (`dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey`) and (`dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey`) and (`dbt3_s001`.`region`.`r_name` = 'ASIA') and (`dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`)))))) order by `dbt3_s001`.`supplier`.`s_acctbal` desc,`dbt3_s001`.`nation`.`n_name`,`dbt3_s001`.`supplier`.`s_name`,`dbt3_s001`.`part`.`p_partkey`
+set optimizer_use_condition_selectivity=4;
+explain extended
+select
+s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
+from
+part, supplier, partsupp, nation, region
+where
+p_partkey = ps_partkey
+and s_suppkey = ps_suppkey
+and p_size = 9
+and p_type like '%TIN'
+	and s_nationkey = n_nationkey
+and n_regionkey = r_regionkey
+and r_name = 'ASIA'
+	and ps_supplycost = (
+select
+min(ps_supplycost)
+from
+partsupp, supplier, nation, region
+where
+p_partkey = ps_partkey
+and s_suppkey = ps_suppkey
+and s_nationkey = n_nationkey
+and n_regionkey = r_regionkey
+and r_name = 'ASIA'
+	)
+order by
+s_acctbal desc, n_name, s_name, p_partkey;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	region	ALL	PRIMARY	NULL	NULL	NULL	5	20.00	Using where; Using temporary; Using filesort
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	2.08	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	4	dbt3_s001.part.p_partkey	3	100.00	Using where
+1	PRIMARY	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	80.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	100.00	Using where
+2	DEPENDENT SUBQUERY	region	ALL	PRIMARY	NULL	NULL	NULL	5	20.00	Using where
+2	DEPENDENT SUBQUERY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	4	dbt3_s001.part.p_partkey	3	100.00	
+2	DEPENDENT SUBQUERY	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.partsupp.ps_suppkey	1	100.00	Using where
+2	DEPENDENT SUBQUERY	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'dbt3_s001.part.p_partkey' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `dbt3_s001`.`supplier`.`s_acctbal` AS `s_acctbal`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`nation`.`n_name` AS `n_name`,`dbt3_s001`.`part`.`p_partkey` AS `p_partkey`,`dbt3_s001`.`part`.`p_mfgr` AS `p_mfgr`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`dbt3_s001`.`supplier`.`s_comment` AS `s_comment` from `dbt3_s001`.`part` join `dbt3_s001`.`supplier` join `dbt3_s001`.`partsupp` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey`) and (`dbt3_s001`.`part`.`p_size` = 9) and (`dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey`) and (`dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey`) and (`dbt3_s001`.`region`.`r_name` = 'ASIA') and (`dbt3_s001`.`part`.`p_type` like '%TIN') and (`dbt3_s001`.`partsupp`.`ps_supplycost` = <expr_cache><`dbt3_s001`.`part`.`p_partkey`>((select min(`dbt3_s001`.`partsupp`.`ps_supplycost`) from `dbt3_s001`.`partsupp` join `dbt3_s001`.`supplier` join `dbt3_s001`.`nation` join `dbt3_s001`.`region` where ((`dbt3_s001`.`supplier`.`s_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey`) and (`dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey`) and (`dbt3_s001`.`nation`.`n_regionkey` = `dbt3_s001`.`region`.`r_regionkey`) and (`dbt3_s001`.`region`.`r_name` = 'ASIA') and (`dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`)))))) order by `dbt3_s001`.`supplier`.`s_acctbal` desc,`dbt3_s001`.`nation`.`n_name`,`dbt3_s001`.`supplier`.`s_name`,`dbt3_s001`.`part`.`p_partkey`
 === Q15 ===
 create view revenue0 (supplier_no, total_revenue) as
 select l_suppkey, sum(l_extendedprice * (1 - l_discount))