1drop table if exists t0,t1,t2,t3; 2select @@global.use_stat_tables; 3@@global.use_stat_tables 4COMPLEMENTARY 5select @@session.use_stat_tables; 6@@session.use_stat_tables 7COMPLEMENTARY 8set @save_use_stat_tables=@@use_stat_tables; 9set use_stat_tables='preferably'; 10set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; 11set @save_histogram_size=@@histogram_size; 12set @save_histogram_type=@@histogram_type; 13set join_cache_level=2; 14set @@global.histogram_size=0,@@local.histogram_size=0; 15set histogram_type='single_prec_hb'; 16set optimizer_use_condition_selectivity=3; 17create table t1 (a int); 18insert into t1 values 19(9), (3), (2), (NULL), (NULL), (2), (NULL), (1), (5), (NULL); 20analyze table t1; 21Table Op Msg_type Msg_text 22test.t1 analyze status Engine-independent statistics collected 23test.t1 analyze status OK 24select * from mysql.column_stats; 25db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 26test t1 a 1 9 0.4000 4.0000 1.2000 0 NULL NULL 27flush table t1; 28explain extended 29select * from t1 where a is null; 30id select_type table type possible_keys key key_len ref rows filtered Extra 311 SIMPLE t1 ALL NULL NULL NULL NULL 10 40.00 Using where 32Warnings: 33Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is null 34explain extended 35select * from t1 where a is not null; 36id select_type table type possible_keys key key_len ref rows filtered Extra 371 SIMPLE t1 ALL NULL NULL NULL NULL 10 60.00 Using where 38Warnings: 39Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is not null 40drop table t1; 41set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 42DROP DATABASE IF EXISTS dbt3_s001; 43CREATE DATABASE dbt3_s001; 44use dbt3_s001; 45=== Q2 === 46set optimizer_use_condition_selectivity=5; 47explain extended 48select 49s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment 50from 51part, supplier, partsupp, nation, region 52where 53p_partkey = ps_partkey 54and s_suppkey = ps_suppkey 55and p_size = 9 56and p_type like '%TIN' 57 and s_nationkey = n_nationkey 58and n_regionkey = r_regionkey 59and r_name = 'ASIA' 60 and ps_supplycost = ( 61select 62min(ps_supplycost) 63from 64partsupp, supplier, nation, region 65where 66p_partkey = ps_partkey 67and s_suppkey = ps_suppkey 68and s_nationkey = n_nationkey 69and n_regionkey = r_regionkey 70and r_name = 'ASIA' 71 ) 72order by 73s_acctbal desc, n_name, s_name, p_partkey; 74id select_type table type possible_keys key key_len ref rows filtered Extra 751 PRIMARY part ALL PRIMARY NULL NULL NULL 200 0.31 Using where; Using temporary; Using filesort 761 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where 771 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 781 PRIMARY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 791 PRIMARY region eq_ref PRIMARY PRIMARY 4 dbt3_s001.nation.n_regionkey 1 20.00 Using where 802 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where 812 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 822 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 832 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 84Warnings: 85Note 1276 Field or reference 'dbt3_s001.part.p_partkey' of SELECT #2 was resolved in SELECT #1 86Note 1003 /* select#1 */ 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`.`region`.`r_regionkey` = `dbt3_s001`.`nation`.`n_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#2 */ 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` 87set optimizer_use_condition_selectivity=4; 88explain extended 89select 90s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment 91from 92part, supplier, partsupp, nation, region 93where 94p_partkey = ps_partkey 95and s_suppkey = ps_suppkey 96and p_size = 9 97and p_type like '%TIN' 98 and s_nationkey = n_nationkey 99and n_regionkey = r_regionkey 100and r_name = 'ASIA' 101 and ps_supplycost = ( 102select 103min(ps_supplycost) 104from 105partsupp, supplier, nation, region 106where 107p_partkey = ps_partkey 108and s_suppkey = ps_suppkey 109and s_nationkey = n_nationkey 110and n_regionkey = r_regionkey 111and r_name = 'ASIA' 112 ) 113order by 114s_acctbal desc, n_name, s_name, p_partkey; 115id select_type table type possible_keys key key_len ref rows filtered Extra 1161 PRIMARY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where; Using temporary; Using filesort 1171 PRIMARY nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 100.00 1181 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 1191 PRIMARY part ALL PRIMARY NULL NULL NULL 200 2.08 Using where; Using join buffer (flat, BNL join) 1201 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where 1212 DEPENDENT SUBQUERY region ALL PRIMARY NULL NULL NULL 5 20.00 Using where 1222 DEPENDENT SUBQUERY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 1232 DEPENDENT SUBQUERY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.partsupp.ps_suppkey 1 100.00 Using where 1242 DEPENDENT SUBQUERY nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 125Warnings: 126Note 1276 Field or reference 'dbt3_s001.part.p_partkey' of SELECT #2 was resolved in SELECT #1 127Note 1003 /* select#1 */ 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`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`part`.`p_size` = 9 and `dbt3_s001`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_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#2 */ 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` 128=== Q15 === 129create view revenue0 (supplier_no, total_revenue) as 130select l_suppkey, sum(l_extendedprice * (1 - l_discount)) 131from lineitem 132where 133l_shipdate >= '1995-08-01' 134 and l_shipdate < date_add('1995-08-01', interval 90 day) 135group by l_suppkey; 136set @save_optimizer_switch=@@optimizer_switch; 137set optimizer_switch='index_condition_pushdown=off'; 138set optimizer_use_condition_selectivity=1; 139EXPLAIN EXTENDED select s_suppkey, s_name, s_address, s_phone, total_revenue 140from supplier, revenue0 141where s_suppkey = supplier_no 142and total_revenue = (select max(total_revenue) from revenue0) 143order by s_suppkey; 144id select_type table type possible_keys key key_len ref rows filtered Extra 1451 PRIMARY supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using filesort 1461 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where 1473 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 269 100.00 Using where; Using temporary; Using filesort 1482 SUBQUERY <derived4> ALL NULL NULL NULL NULL 269 100.00 1494 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 269 100.00 Using where; Using temporary; Using filesort 150Warnings: 151Note 1003 /* select#1 */ select `dbt3_s001`.`supplier`.`s_suppkey` AS `s_suppkey`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`revenue0`.`total_revenue` AS `total_revenue` from `dbt3_s001`.`supplier` join `dbt3_s001`.`revenue0` where `revenue0`.`supplier_no` = `dbt3_s001`.`supplier`.`s_suppkey` and `revenue0`.`total_revenue` = (/* select#2 */ select max(`revenue0`.`total_revenue`) from `dbt3_s001`.`revenue0`) order by `dbt3_s001`.`supplier`.`s_suppkey` 152select s_suppkey, s_name, s_address, s_phone, total_revenue 153from supplier, revenue0 154where s_suppkey = supplier_no 155and total_revenue = (select max(total_revenue) from revenue0) 156order by s_suppkey; 157s_suppkey s_name s_address s_phone total_revenue 1581 Supplier#000000001 N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ 27-918-335-1736 729084.7773 159set optimizer_use_condition_selectivity=3; 160EXPLAIN EXTENDED select s_suppkey, s_name, s_address, s_phone, total_revenue 161from supplier, revenue0 162where s_suppkey = supplier_no 163and total_revenue = (select max(total_revenue) from revenue0) 164order by s_suppkey; 165id select_type table type possible_keys key key_len ref rows filtered Extra 1661 PRIMARY supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using filesort 1671 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where 1683 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 269 100.00 Using where; Using temporary; Using filesort 1692 SUBQUERY <derived4> ALL NULL NULL NULL NULL 269 100.00 1704 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 269 100.00 Using where; Using temporary; Using filesort 171Warnings: 172Note 1003 /* select#1 */ select `dbt3_s001`.`supplier`.`s_suppkey` AS `s_suppkey`,`dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address`,`dbt3_s001`.`supplier`.`s_phone` AS `s_phone`,`revenue0`.`total_revenue` AS `total_revenue` from `dbt3_s001`.`supplier` join `dbt3_s001`.`revenue0` where `revenue0`.`supplier_no` = `dbt3_s001`.`supplier`.`s_suppkey` and `revenue0`.`total_revenue` = (/* select#2 */ select max(`revenue0`.`total_revenue`) from `dbt3_s001`.`revenue0`) order by `dbt3_s001`.`supplier`.`s_suppkey` 173select s_suppkey, s_name, s_address, s_phone, total_revenue 174from supplier, revenue0 175where s_suppkey = supplier_no 176and total_revenue = (select max(total_revenue) from revenue0) 177order by s_suppkey; 178s_suppkey s_name s_address s_phone total_revenue 1791 Supplier#000000001 N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ 27-918-335-1736 729084.7773 180set optimizer_switch=@save_optimizer_switch; 181drop view revenue0; 182=== Q16 === 183set optimizer_use_condition_selectivity=1; 184EXPLAIN EXTENDED select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt 185from partsupp, part 186where p_partkey = ps_partkey 187and p_brand <> 'Brand#11' 188 and p_type not like 'SMALL POLISHED%' 189 and p_size in (49, 37, 27, 5, 40, 6, 22, 8) 190and ps_suppkey not in (select s_suppkey from supplier 191where s_comment like '%Customer%Complaints%') 192group by p_brand, p_type, p_size 193order by supplier_cnt desc, p_brand, p_type, p_size; 194id select_type table type possible_keys key key_len ref rows filtered Extra 1951 PRIMARY part ALL PRIMARY NULL NULL NULL 200 100.00 Using where; Using temporary; Using filesort 1961 PRIMARY partsupp ref PRIMARY,i_ps_partkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index 1972 MATERIALIZED supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using where 198Warnings: 199Note 1003 /* select#1 */ select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`part`.`p_brand` <> 'Brand#11' and `dbt3_s001`.`part`.`p_type` not like 'SMALL POLISHED%' and `dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8) and !<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */ select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where `dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%' ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where `dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_suppkey`)))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` 200select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt 201from partsupp, part 202where p_partkey = ps_partkey 203and p_brand <> 'Brand#11' 204 and p_type not like 'SMALL POLISHED%' 205 and p_size in (49, 37, 27, 5, 40, 6, 22, 8) 206and ps_suppkey not in (select s_suppkey from supplier 207where s_comment like '%Customer%Complaints%') 208group by p_brand, p_type, p_size 209order by supplier_cnt desc, p_brand, p_type, p_size; 210p_brand p_type p_size supplier_cnt 211Brand#21 MEDIUM ANODIZED TIN 8 4 212Brand#22 PROMO PLATED TIN 5 4 213Brand#24 MEDIUM BURNISHED NICKEL 6 4 214Brand#24 SMALL ANODIZED STEEL 40 4 215Brand#32 MEDIUM BURNISHED BRASS 49 4 216Brand#33 MEDIUM POLISHED BRASS 49 4 217Brand#41 STANDARD BRUSHED NICKEL 40 4 218Brand#44 PROMO POLISHED STEEL 5 4 219Brand#45 PROMO ANODIZED BRASS 22 4 220Brand#53 STANDARD BRUSHED STEEL 27 4 221Brand#54 MEDIUM POLISHED BRASS 22 4 222Brand#54 STANDARD ANODIZED BRASS 22 4 223Brand#13 LARGE BRUSHED STEEL 8 2 224Brand#25 ECONOMY BURNISHED COPPER 27 2 225Brand#44 STANDARD PLATED TIN 37 1 226Brand#51 ECONOMY POLISHED STEEL 49 1 227set optimizer_use_condition_selectivity=3; 228EXPLAIN EXTENDED select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt 229from partsupp, part 230where p_partkey = ps_partkey 231and p_brand <> 'Brand#11' 232 and p_type not like 'SMALL POLISHED%' 233 and p_size in (49, 37, 27, 5, 40, 6, 22, 8) 234and ps_suppkey not in (select s_suppkey from supplier 235where s_comment like '%Customer%Complaints%') 236group by p_brand, p_type, p_size 237order by supplier_cnt desc, p_brand, p_type, p_size; 238id select_type table type possible_keys key key_len ref rows filtered Extra 2391 PRIMARY part ALL PRIMARY NULL NULL NULL 200 16.67 Using where; Using temporary; Using filesort 2401 PRIMARY partsupp ref PRIMARY,i_ps_partkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index 2412 MATERIALIZED supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using where 242Warnings: 243Note 1003 /* select#1 */ select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`part`.`p_brand` <> 'Brand#11' and `dbt3_s001`.`part`.`p_type` not like 'SMALL POLISHED%' and `dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8) and !<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */ select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where `dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%' ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where `dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_suppkey`)))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` 244select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt 245from partsupp, part 246where p_partkey = ps_partkey 247and p_brand <> 'Brand#11' 248 and p_type not like 'SMALL POLISHED%' 249 and p_size in (49, 37, 27, 5, 40, 6, 22, 8) 250and ps_suppkey not in (select s_suppkey from supplier 251where s_comment like '%Customer%Complaints%') 252group by p_brand, p_type, p_size 253order by supplier_cnt desc, p_brand, p_type, p_size; 254p_brand p_type p_size supplier_cnt 255Brand#21 MEDIUM ANODIZED TIN 8 4 256Brand#22 PROMO PLATED TIN 5 4 257Brand#24 MEDIUM BURNISHED NICKEL 6 4 258Brand#24 SMALL ANODIZED STEEL 40 4 259Brand#32 MEDIUM BURNISHED BRASS 49 4 260Brand#33 MEDIUM POLISHED BRASS 49 4 261Brand#41 STANDARD BRUSHED NICKEL 40 4 262Brand#44 PROMO POLISHED STEEL 5 4 263Brand#45 PROMO ANODIZED BRASS 22 4 264Brand#53 STANDARD BRUSHED STEEL 27 4 265Brand#54 MEDIUM POLISHED BRASS 22 4 266Brand#54 STANDARD ANODIZED BRASS 22 4 267Brand#13 LARGE BRUSHED STEEL 8 2 268Brand#25 ECONOMY BURNISHED COPPER 27 2 269Brand#44 STANDARD PLATED TIN 37 1 270Brand#51 ECONOMY POLISHED STEEL 49 1 271set optimizer_use_condition_selectivity=4; 272EXPLAIN EXTENDED select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt 273from partsupp, part 274where p_partkey = ps_partkey 275and p_brand <> 'Brand#11' 276 and p_type not like 'SMALL POLISHED%' 277 and p_size in (49, 37, 27, 5, 40, 6, 22, 8) 278and ps_suppkey not in (select s_suppkey from supplier 279where s_comment like '%Customer%Complaints%') 280group by p_brand, p_type, p_size 281order by supplier_cnt desc, p_brand, p_type, p_size; 282id select_type table type possible_keys key key_len ref rows filtered Extra 2831 PRIMARY part ALL PRIMARY NULL NULL NULL 200 16.67 Using where; Using temporary; Using filesort 2841 PRIMARY partsupp ref PRIMARY,i_ps_partkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where; Using index 2852 MATERIALIZED supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using where 286Warnings: 287Note 1003 /* select#1 */ select `dbt3_s001`.`part`.`p_brand` AS `p_brand`,`dbt3_s001`.`part`.`p_type` AS `p_type`,`dbt3_s001`.`part`.`p_size` AS `p_size`,count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) AS `supplier_cnt` from `dbt3_s001`.`partsupp` join `dbt3_s001`.`part` where `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`part`.`p_brand` <> 'Brand#11' and `dbt3_s001`.`part`.`p_type` not like 'SMALL POLISHED%' and `dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8) and !<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (/* select#2 */ select `dbt3_s001`.`supplier`.`s_suppkey` from `dbt3_s001`.`supplier` where `dbt3_s001`.`supplier`.`s_comment` like '%Customer%Complaints%' ), <primary_index_lookup>(`dbt3_s001`.`partsupp`.`ps_suppkey` in <temporary table> on distinct_key where `dbt3_s001`.`partsupp`.`ps_suppkey` = `<subquery2>`.`s_suppkey`)))) group by `dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` order by count(distinct `dbt3_s001`.`partsupp`.`ps_suppkey`) desc,`dbt3_s001`.`part`.`p_brand`,`dbt3_s001`.`part`.`p_type`,`dbt3_s001`.`part`.`p_size` 288select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt 289from partsupp, part 290where p_partkey = ps_partkey 291and p_brand <> 'Brand#11' 292 and p_type not like 'SMALL POLISHED%' 293 and p_size in (49, 37, 27, 5, 40, 6, 22, 8) 294and ps_suppkey not in (select s_suppkey from supplier 295where s_comment like '%Customer%Complaints%') 296group by p_brand, p_type, p_size 297order by supplier_cnt desc, p_brand, p_type, p_size; 298p_brand p_type p_size supplier_cnt 299Brand#21 MEDIUM ANODIZED TIN 8 4 300Brand#22 PROMO PLATED TIN 5 4 301Brand#24 MEDIUM BURNISHED NICKEL 6 4 302Brand#24 SMALL ANODIZED STEEL 40 4 303Brand#32 MEDIUM BURNISHED BRASS 49 4 304Brand#33 MEDIUM POLISHED BRASS 49 4 305Brand#41 STANDARD BRUSHED NICKEL 40 4 306Brand#44 PROMO POLISHED STEEL 5 4 307Brand#45 PROMO ANODIZED BRASS 22 4 308Brand#53 STANDARD BRUSHED STEEL 27 4 309Brand#54 MEDIUM POLISHED BRASS 22 4 310Brand#54 STANDARD ANODIZED BRASS 22 4 311Brand#13 LARGE BRUSHED STEEL 8 2 312Brand#25 ECONOMY BURNISHED COPPER 27 2 313Brand#44 STANDARD PLATED TIN 37 1 314Brand#51 ECONOMY POLISHED STEEL 49 1 315=== Q18 === 316set optimizer_use_condition_selectivity=1; 317EXPLAIN EXTENDED select 318c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) 319from customer, orders, lineitem 320where 321o_orderkey in (select l_orderkey from lineitem 322group by l_orderkey having sum(l_quantity) > 250) 323and c_custkey = o_custkey 324and o_orderkey = l_orderkey 325group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice 326order by o_totalprice desc, o_orderdate; 327id select_type table type possible_keys key key_len ref rows filtered Extra 3281 PRIMARY orders ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort 3291 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 3301 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 3311 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index 3322 MATERIALIZED lineitem index NULL i_l_orderkey_quantity 13 NULL 6005 100.00 Using index 333Warnings: 334Note 1003 /* select#1 */ select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (/* select#2 */ select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where `dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey` and `<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` and `dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`.`orders`.`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` 335select 336c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) 337from customer, orders, lineitem 338where 339o_orderkey in (select l_orderkey from lineitem 340group by l_orderkey having sum(l_quantity) > 250) 341and c_custkey = o_custkey 342and o_orderkey = l_orderkey 343group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice 344order by o_totalprice desc, o_orderdate; 345c_name c_custkey o_orderkey o_orderdate o_totalprice sum(l_quantity) 346Customer#000000070 70 2567 1998-02-27 263411.29 266 347Customer#000000010 10 4421 1997-04-04 258779.02 255 348Customer#000000082 82 3460 1995-10-03 245976.74 254 349Customer#000000068 68 2208 1995-05-01 245388.06 256 350set optimizer_use_condition_selectivity=3; 351EXPLAIN EXTENDED select 352c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) 353from customer, orders, lineitem 354where 355o_orderkey in (select l_orderkey from lineitem 356group by l_orderkey having sum(l_quantity) > 250) 357and c_custkey = o_custkey 358and o_orderkey = l_orderkey 359group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice 360order by o_totalprice desc, o_orderdate; 361id select_type table type possible_keys key key_len ref rows filtered Extra 3621 PRIMARY orders ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort 3631 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 3641 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 3651 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index 3662 MATERIALIZED lineitem index NULL i_l_orderkey_quantity 13 NULL 6005 100.00 Using index 367Warnings: 368Note 1003 /* select#1 */ select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (/* select#2 */ select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where `dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey` and `<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` and `dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`.`orders`.`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` 369select 370c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) 371from customer, orders, lineitem 372where 373o_orderkey in (select l_orderkey from lineitem 374group by l_orderkey having sum(l_quantity) > 250) 375and c_custkey = o_custkey 376and o_orderkey = l_orderkey 377group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice 378order by o_totalprice desc, o_orderdate; 379c_name c_custkey o_orderkey o_orderdate o_totalprice sum(l_quantity) 380Customer#000000070 70 2567 1998-02-27 263411.29 266 381Customer#000000010 10 4421 1997-04-04 258779.02 255 382Customer#000000082 82 3460 1995-10-03 245976.74 254 383Customer#000000068 68 2208 1995-05-01 245388.06 256 384=== Q22 === 385set optimizer_use_condition_selectivity=1; 386EXPLAIN EXTENDED select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal 387from ( 388select substr(c_phone, 1, 2) as cntrycode, c_acctbal 389from customer 390where 391substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25') 392and c_acctbal > (select avg(c_acctbal) from customer 393where c_acctbal > 0.00 394and substr(c_phone, 1, 2) in 395('10', '20', '14', '19', '11', '28', '25')) 396and not exists (select * from orders where o_custkey = c_custkey) 397) as vip 398group by cntrycode 399order by cntrycode; 400id select_type table type possible_keys key key_len ref rows filtered Extra 4011 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort 4024 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index 4033 SUBQUERY customer ALL NULL NULL NULL NULL 150 100.00 Using where 404Warnings: 405Note 1276 Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2 406Note 1003 /* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,<expr_cache><`dbt3_s001`.`customer`.`c_custkey`>(exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey` limit 1))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) 407select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal 408from ( 409select substr(c_phone, 1, 2) as cntrycode, c_acctbal 410from customer 411where 412substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25') 413and c_acctbal > (select avg(c_acctbal) from customer 414where c_acctbal > 0.00 415and substr(c_phone, 1, 2) in 416('10', '20', '14', '19', '11', '28', '25')) 417and not exists (select * from orders where o_custkey = c_custkey) 418) as vip 419group by cntrycode 420order by cntrycode; 421cntrycode numcust totacctbal 42211 4 29942.58 42319 2 17120.35 42420 1 9091.82 42528 2 14755.5 426set optimizer_use_condition_selectivity=3; 427EXPLAIN EXTENDED select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal 428from ( 429select substr(c_phone, 1, 2) as cntrycode, c_acctbal 430from customer 431where 432substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25') 433and c_acctbal > (select avg(c_acctbal) from customer 434where c_acctbal > 0.00 435and substr(c_phone, 1, 2) in 436('10', '20', '14', '19', '11', '28', '25')) 437and not exists (select * from orders where o_custkey = c_custkey) 438) as vip 439group by cntrycode 440order by cntrycode; 441id select_type table type possible_keys key key_len ref rows filtered Extra 4421 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort 4434 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index 4443 SUBQUERY customer ALL NULL NULL NULL NULL 150 91.00 Using where 445Warnings: 446Note 1276 Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2 447Note 1003 /* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,<expr_cache><`dbt3_s001`.`customer`.`c_custkey`>(exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey` limit 1))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) 448select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal 449from ( 450select substr(c_phone, 1, 2) as cntrycode, c_acctbal 451from customer 452where 453substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25') 454and c_acctbal > (select avg(c_acctbal) from customer 455where c_acctbal > 0.00 456and substr(c_phone, 1, 2) in 457('10', '20', '14', '19', '11', '28', '25')) 458and not exists (select * from orders where o_custkey = c_custkey) 459) as vip 460group by cntrycode 461order by cntrycode; 462cntrycode numcust totacctbal 46311 4 29942.58 46419 2 17120.35 46520 1 9091.82 46628 2 14755.5 467=== Q20 === 468set optimizer_use_condition_selectivity=1; 469EXPLAIN EXTENDED select sql_calc_found_rows 470s_name, s_address 471from supplier, nation 472where s_suppkey in (select ps_suppkey from partsupp 473where ps_partkey in (select p_partkey from part 474where p_name like 'g%') 475and ps_availqty > 476(select 0.5 * sum(l_quantity) 477from lineitem 478where l_partkey = ps_partkey 479and l_suppkey = ps_suppkey 480and l_shipdate >= date('1993-01-01') 481and l_shipdate < date('1993-01-01') + 482interval '1' year )) 483and s_nationkey = n_nationkey 484and n_name = 'UNITED STATES' 485order by s_name 486limit 10; 487id select_type table type possible_keys key key_len ref rows filtered Extra 4881 PRIMARY supplier ALL PRIMARY,i_s_nationkey NULL NULL NULL 10 100.00 Using where; Using filesort 4891 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 4901 PRIMARY nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where 4912 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 100.00 Using where 4922 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where 4934 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 494Warnings: 495Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 496Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 497Note 1003 /* select#1 */ 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`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ 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 498select sql_calc_found_rows 499s_name, s_address 500from supplier, nation 501where s_suppkey in (select ps_suppkey from partsupp 502where ps_partkey in (select p_partkey from part 503where p_name like 'g%') 504and ps_availqty > 505(select 0.5 * sum(l_quantity) 506from lineitem 507where l_partkey = ps_partkey 508and l_suppkey = ps_suppkey 509and l_shipdate >= date('1993-01-01') 510and l_shipdate < date('1993-01-01') + 511interval '1' year )) 512and s_nationkey = n_nationkey 513and n_name = 'UNITED STATES' 514order by s_name 515limit 10; 516s_name s_address 517Supplier#000000010 Saygah3gYWMp72i PY 518SELECT ((SELECT COUNT(*) FROM part WHERE p_name LIKE 'g%') / 519(SELECT COUNT(*) FROM part)) AS sel; 520sel 5210.0600 522set optimizer_use_condition_selectivity=3; 523EXPLAIN EXTENDED select sql_calc_found_rows 524s_name, s_address 525from supplier, nation 526where s_suppkey in (select ps_suppkey from partsupp 527where ps_partkey in (select p_partkey from part 528where p_name like 'g%') 529and ps_availqty > 530(select 0.5 * sum(l_quantity) 531from lineitem 532where l_partkey = ps_partkey 533and l_suppkey = ps_suppkey 534and l_shipdate >= date('1993-01-01') 535and l_shipdate < date('1993-01-01') + 536interval '1' year )) 537and s_nationkey = n_nationkey 538and n_name = 'UNITED STATES' 539order by s_name 540limit 10; 541id select_type table type possible_keys key key_len ref rows filtered Extra 5421 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort 5431 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 5441 PRIMARY part ALL PRIMARY NULL NULL NULL 200 4.17 Using where; Start temporary; Using join buffer (flat, BNL join) 5451 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where; End temporary 5464 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 547Warnings: 548Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 549Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 550Note 1003 /* select#1 */ 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`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ 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 551select sql_calc_found_rows 552s_name, s_address 553from supplier, nation 554where s_suppkey in (select ps_suppkey from partsupp 555where ps_partkey in (select p_partkey from part 556where p_name like 'g%') 557and ps_availqty > 558(select 0.5 * sum(l_quantity) 559from lineitem 560where l_partkey = ps_partkey 561and l_suppkey = ps_suppkey 562and l_shipdate >= date('1993-01-01') 563and l_shipdate < date('1993-01-01') + 564interval '1' year )) 565and s_nationkey = n_nationkey 566and n_name = 'UNITED STATES' 567order by s_name 568limit 10; 569s_name s_address 570Supplier#000000010 Saygah3gYWMp72i PY 571set histogram_size=127; 572ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES(); 573Table Op Msg_type Msg_text 574dbt3_s001.part analyze status Engine-independent statistics collected 575dbt3_s001.part analyze status Table is already up to date 576flush table part; 577set optimizer_use_condition_selectivity=4; 578EXPLAIN EXTENDED select sql_calc_found_rows 579s_name, s_address 580from supplier, nation 581where s_suppkey in (select ps_suppkey from partsupp 582where ps_partkey in (select p_partkey from part 583where p_name like 'g%') 584and ps_availqty > 585(select 0.5 * sum(l_quantity) 586from lineitem 587where l_partkey = ps_partkey 588and l_suppkey = ps_suppkey 589and l_shipdate >= date('1993-01-01') 590and l_shipdate < date('1993-01-01') + 591interval '1' year )) 592and s_nationkey = n_nationkey 593and n_name = 'UNITED STATES' 594order by s_name 595limit 10; 596id select_type table type possible_keys key key_len ref rows filtered Extra 5971 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort 5981 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 5991 PRIMARY part ALL PRIMARY NULL NULL NULL 200 7.03 Using where 6001 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where; FirstMatch(supplier) 6014 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 602Warnings: 603Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 604Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 605Note 1003 /* select#1 */ 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`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ 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 606select sql_calc_found_rows 607s_name, s_address 608from supplier, nation 609where s_suppkey in (select ps_suppkey from partsupp 610where ps_partkey in (select p_partkey from part 611where p_name like 'g%') 612and ps_availqty > 613(select 0.5 * sum(l_quantity) 614from lineitem 615where l_partkey = ps_partkey 616and l_suppkey = ps_suppkey 617and l_shipdate >= date('1993-01-01') 618and l_shipdate < date('1993-01-01') + 619interval '1' year )) 620and s_nationkey = n_nationkey 621and n_name = 'UNITED STATES' 622order by s_name 623limit 10; 624s_name s_address 625Supplier#000000010 Saygah3gYWMp72i PY 626set histogram_type='DOUBLE_PREC_HB'; 627set histogram_size=126; 628ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES(); 629Table Op Msg_type Msg_text 630dbt3_s001.part analyze status Engine-independent statistics collected 631dbt3_s001.part analyze status Table is already up to date 632flush table part; 633EXPLAIN EXTENDED select sql_calc_found_rows 634s_name, s_address 635from supplier, nation 636where s_suppkey in (select ps_suppkey from partsupp 637where ps_partkey in (select p_partkey from part 638where p_name like 'g%') 639and ps_availqty > 640(select 0.5 * sum(l_quantity) 641from lineitem 642where l_partkey = ps_partkey 643and l_suppkey = ps_suppkey 644and l_shipdate >= date('1993-01-01') 645and l_shipdate < date('1993-01-01') + 646interval '1' year )) 647and s_nationkey = n_nationkey 648and n_name = 'UNITED STATES' 649order by s_name 650limit 10; 651id select_type table type possible_keys key key_len ref rows filtered Extra 6521 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort 6531 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 6541 PRIMARY part ALL PRIMARY NULL NULL NULL 200 7.81 Using where 6551 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where; FirstMatch(supplier) 6564 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 657Warnings: 658Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 659Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 660Note 1003 /* select#1 */ 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`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ 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 661select sql_calc_found_rows 662s_name, s_address 663from supplier, nation 664where s_suppkey in (select ps_suppkey from partsupp 665where ps_partkey in (select p_partkey from part 666where p_name like 'g%') 667and ps_availqty > 668(select 0.5 * sum(l_quantity) 669from lineitem 670where l_partkey = ps_partkey 671and l_suppkey = ps_suppkey 672and l_shipdate >= date('1993-01-01') 673and l_shipdate < date('1993-01-01') + 674interval '1' year )) 675and s_nationkey = n_nationkey 676and n_name = 'UNITED STATES' 677order by s_name 678limit 10; 679s_name s_address 680Supplier#000000010 Saygah3gYWMp72i PY 681set histogram_type='SINGLE_PREC_HB'; 682set histogram_size=24; 683ANALYZE TABLE nation PERSISTENT FOR COLUMNS(n_name) INDEXES(); 684Table Op Msg_type Msg_text 685dbt3_s001.nation analyze status Engine-independent statistics collected 686dbt3_s001.nation analyze status Table is already up to date 687flush table nation; 688EXPLAIN EXTENDED select sql_calc_found_rows 689s_name, s_address 690from supplier, nation 691where s_suppkey in (select ps_suppkey from partsupp 692where ps_partkey in (select p_partkey from part 693where p_name like 'g%') 694and ps_availqty > 695(select 0.5 * sum(l_quantity) 696from lineitem 697where l_partkey = ps_partkey 698and l_suppkey = ps_suppkey 699and l_shipdate >= date('1993-01-01') 700and l_shipdate < date('1993-01-01') + 701interval '1' year )) 702and s_nationkey = n_nationkey 703and n_name = 'UNITED STATES' 704order by s_name 705limit 10; 706id select_type table type possible_keys key key_len ref rows filtered Extra 7071 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort 7081 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 7091 PRIMARY part ALL PRIMARY NULL NULL NULL 200 7.81 Using where 7101 PRIMARY partsupp eq_ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 8 dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey 1 100.00 Using where; FirstMatch(supplier) 7114 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 712Warnings: 713Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 714Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 715Note 1003 /* select#1 */ 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`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ 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 716select sql_calc_found_rows 717s_name, s_address 718from supplier, nation 719where s_suppkey in (select ps_suppkey from partsupp 720where ps_partkey in (select p_partkey from part 721where p_name like 'g%') 722and ps_availqty > 723(select 0.5 * sum(l_quantity) 724from lineitem 725where l_partkey = ps_partkey 726and l_suppkey = ps_suppkey 727and l_shipdate >= date('1993-01-01') 728and l_shipdate < date('1993-01-01') + 729interval '1' year )) 730and s_nationkey = n_nationkey 731and n_name = 'UNITED STATES' 732order by s_name 733limit 10; 734s_name s_address 735Supplier#000000010 Saygah3gYWMp72i PY 736DROP DATABASE dbt3_s001; 737set histogram_type=@save_histogram_type; 738set histogram_size=@save_histogram_size; 739set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 740use test; 741# 742# Bug mdev-4348: using view with use_condition_selectivity > 1 743# 744set @tmp_use_stat_tables=@@use_stat_tables; 745set use_stat_tables='never'; 746set optimizer_use_condition_selectivity=3; 747CREATE TABLE t1 (a int, b int); 748INSERT t1 VALUES (7,1), (0,7); 749CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; 750CREATE TABLE t2 (c int, d int, index idx(d)); 751INSERT INTO t2 VALUES 752(0,4), (8,6), (1,3), (8,5), (9,3), (2,2), (6,2), 753(1,9), (6,3), (2,8), (4,1), (0,7), (4,8), (4,5); 754EXPLAIN EXTENDED 755SELECT * FROM v1 INNER JOIN t2 ON ( a = c AND b = d ); 756id select_type table type possible_keys key key_len ref rows filtered Extra 7571 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 7581 SIMPLE t2 ref idx idx 5 test.t1.b 2 100.00 Using where 759Warnings: 760Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t1`.`b` 761SELECT * FROM v1 INNER JOIN t2 ON ( a = c AND b = d ); 762a b c d 7630 7 0 7 764DROP VIEW v1; 765DROP TABLE t1,t2; 766set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 767set use_stat_tables=@tmp_use_stat_tables; 768# 769# Bug mdev-4349: impossible range for non-indexed column 770# 771set optimizer_use_condition_selectivity=3; 772create table t1 (a int); 773insert into t1 values 774(3), (7), (2), (5), (7), (1), (2), (2); 775set optimizer_use_condition_selectivity=1; 776explain extended 777select * from t1 where a < 1 and a > 7; 778id select_type table type possible_keys key key_len ref rows filtered Extra 7791 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where 780Warnings: 781Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1 and `test`.`t1`.`a` > 7 782select * from t1 where a < 1 and a > 7; 783a 784set optimizer_use_condition_selectivity=3; 785explain extended 786select * from t1 where a < 1 and a > 7; 787id select_type table type possible_keys key key_len ref rows filtered Extra 7881 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where 789Warnings: 790Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1 and `test`.`t1`.`a` > 7 791select * from t1 where a < 1 and a > 7; 792a 793drop table t1; 794create table t1 (a int); 795insert into t1 values (1); 796create table t2 (b int); 797insert into t2 values (2),(3); 798explain extended 799select * from t1 where a in ( select b from t2 ) AND ( a > 3 ); 800id select_type table type possible_keys key key_len ref rows filtered Extra 8011 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 802Warnings: 803Note 1003 select 1 AS `a` from (`test`.`t2`) where 0 804select * from t1 where a in ( select b from t2 ) AND ( a > 3 ); 805a 806drop table t1,t2; 807set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 808# 809# Bug mdev-4350: erroneous negative selectivity 810# 811create table t1 (a int); 812insert into t1 values (1), (1); 813insert into t1 select * from t1; 814insert into t1 select * from t1; 815insert into t1 select * from t1; 816insert into t1 select * from t1; 817insert into t1 select * from t1; 818insert into t1 select * from t1; 819insert into t1 select * from t1; 820insert into t1 select * from t1; 821insert into t1 select * from t1; 822insert into t1 values (0); 823select count(*) from t1; 824count(*) 8251025 826set use_stat_tables='preferably'; 827set histogram_size=127; 828set histogram_type='SINGLE_PREC_HB'; 829analyze table t1; 830Table Op Msg_type Msg_text 831test.t1 analyze status Engine-independent statistics collected 832test.t1 analyze status OK 833flush table t1; 834set optimizer_use_condition_selectivity=4; 835explain extended select * from t1 where a=0; 836id select_type table type possible_keys key key_len ref rows filtered Extra 8371 SIMPLE t1 ALL NULL NULL NULL NULL 1025 0.39 Using where 838Warnings: 839Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 0 840drop table t1; 841set histogram_size=@save_histogram_size; 842set histogram_type=@save_histogram_type; 843set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 844# 845# Bug mdev-4367: 2-way join with an empty table 846# when optimizer_use_condition_selectivity=3 847# 848set optimizer_use_condition_selectivity=3; 849CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM; 850INSERT INTO t1 VALUES ('j'),('k'); 851CREATE TABLE t2 (b varchar(1)) ENGINE=MyISAM; 852INSERT INTO t2 VALUES ('x'),('y'); 853CREATE TABLE t3 (c varchar(1), KEY(c)) ENGINE=MyISAM; 854SELECT * FROM t1 STRAIGHT_JOIN (t2 JOIN t3 ON c = b AND b > 'z'); 855a b c 856DROP TABLE t1,t2,t3; 857set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 858# 859# Bug mdev-4366: impossible condition on an indexed column discovered after 860# substitution of constant tables 861# with optimizer_use_condition_selectivity=3 862# 863CREATE TABLE t1 (pk int PRIMARY KEY, a int); 864INSERT INTO t1 VALUES 865(1,4), (2,6), (3,3), (4,5); 866CREATE TABLE t2 (b int); 867INSERT INTO t2 VALUES (1), (7); 868set optimizer_use_condition_selectivity=1; 869EXPLAIN EXTENDED 870SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; 871id select_type table type possible_keys key key_len ref rows filtered Extra 8721 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 873Warnings: 874Note 1003 select 1 AS `1` from `test`.`t1` join `test`.`t2` where 0 875SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; 8761 877set optimizer_use_condition_selectivity=3; 878EXPLAIN EXTENDED 879SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; 880id select_type table type possible_keys key key_len ref rows filtered Extra 8811 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 882Warnings: 883Note 1003 select 1 AS `1` from `test`.`t1` join `test`.`t2` where 0 884SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10; 8851 886DROP TABLE t1,t2; 887set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 888# 889# Bug mdev-4370: Histograms have bean created, but the query is run after 890# FLUSH TABLES with optimizer_use_condition_selectivity=3 891# 892set use_stat_tables=PREFERABLY; 893set histogram_size=10; 894set histogram_type='SINGLE_PREC_HB'; 895CREATE TABLE t1 (a int); 896INSERT INTO t1 VALUES (9), (1); 897ANALYZE TABLE t1; 898Table Op Msg_type Msg_text 899test.t1 analyze status Engine-independent statistics collected 900test.t1 analyze status OK 901FLUSH TABLES; 902set optimizer_use_condition_selectivity=3; 903EXPLAIN EXTENDED 904SELECT * FROM t1 WHERE a > 3; 905id select_type table type possible_keys key key_len ref rows filtered Extra 9061 SIMPLE t1 ALL NULL NULL NULL NULL 2 75.00 Using where 907Warnings: 908Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3 909SELECT * FROM t1 WHERE a > 3; 910a 9119 912set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 913DROP TABLE t1; 914set histogram_size=@save_histogram_size; 915set histogram_type=@save_histogram_type; 916set use_stat_tables=@save_use_stat_tables; 917# 918# Bug mdev-4371: Join with condition supported by index on an empty table 919# with optimizer_use_condition_selectivity=3 920# 921set use_stat_tables=PREFERABLY; 922CREATE TABLE t1 (a int, b int, INDEX(a)); 923CREATE TABLE t2 (c int); 924INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 925ANALYZE TABLE t1, t2; 926Table Op Msg_type Msg_text 927test.t1 analyze status Engine-independent statistics collected 928test.t1 analyze status Table is already up to date 929test.t2 analyze status Engine-independent statistics collected 930test.t2 analyze status OK 931FLUSH TABLES; 932set optimizer_use_condition_selectivity=3; 933set @save_optimizer_switch=@@optimizer_switch; 934set optimizer_switch='index_condition_pushdown=off'; 935EXPLAIN EXTENDED 936SELECT * FROM t1, t2 WHERE a > 9; 937id select_type table type possible_keys key key_len ref rows filtered Extra 9381 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 939Warnings: 940Note 1003 select NULL AS `a`,NULL AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where 0 941SELECT * FROM t1, t2 WHERE a > 9; 942a b c 943set optimizer_switch=@save_optimizer_switch; 944set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 945DROP TABLE t1,t2; 946set use_stat_tables=@save_use_stat_tables; 947# 948# Bug mdev-4373: condition on a short varchar column 949# with optimizer_use_condition_selectivity=3 950# 951set use_stat_tables=PREFERABLY; 952CREATE TABLE t1 (a varchar(1)); 953INSERT INTO t1 VALUES ('x'), ('y'); 954ANALYZE TABLE t1; 955Table Op Msg_type Msg_text 956test.t1 analyze status Engine-independent statistics collected 957test.t1 analyze status OK 958FLUSH TABLES; 959set optimizer_use_condition_selectivity=3; 960SELECT * FROM t1 WHERE a <= 'w'; 961a 962set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 963DROP TABLE t1; 964set use_stat_tables=@save_use_stat_tables; 965# 966# Bug mdev-4372: exists subquery in WHERE 967# with optimizer_use_condition_selectivity=3 968# 969set use_stat_tables = PREFERABLY; 970CREATE TABLE t1 (a int); 971INSERT INTO t1 VALUES 972(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),(1),(3),(8),(8); 973CREATE TABLE t2 (b int); 974INSERT INTO t2 VALUES (4),(5),(2),(5),(1),(1),(2); 975ANALYZE TABLE t1, t2; 976Table Op Msg_type Msg_text 977test.t1 analyze status Engine-independent statistics collected 978test.t1 analyze status OK 979test.t2 analyze status Engine-independent statistics collected 980test.t2 analyze status OK 981FLUSH TABLES; 982set optimizer_use_condition_selectivity=3; 983EXPLAIN EXTENDED 984SELECT * FROM t1, t2 WHERE EXISTS ( SELECT 1 FROM t1, t2 ) AND a != b OR a <= 4; 985id select_type table type possible_keys key key_len ref rows filtered Extra 9861 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00 9871 PRIMARY t1 ALL NULL NULL NULL NULL 14 100.00 Using where; Using join buffer (flat, BNL join) 9882 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 9892 SUBQUERY t1 ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) 990Warnings: 991Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where <cache>(<in_optimizer>(1,exists(/* select#2 */ select 1 from `test`.`t1` join `test`.`t2` limit 1))) and `test`.`t1`.`a` <> `test`.`t2`.`b` or `test`.`t1`.`a` <= 4 992set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 993DROP TABLE t1,t2; 994set use_stat_tables=@save_use_stat_tables; 995# 996# Bug mdev-4363: selectivity of the condition a IS NULL OR IS NOT NULL 997# with optimizer_use_condition_selectivity=3 998# 999set use_stat_tables = PREFERABLY; 1000CREATE TABLE t1 (a int); 1001INSERT INTO t1 VALUES 1002(1),(7),(4),(7),(NULL),(2),(NULL),(4),(NULL),(NULL),(1),(3),(8),(8); 1003ANALYZE TABLE t1; 1004Table Op Msg_type Msg_text 1005test.t1 analyze status Engine-independent statistics collected 1006test.t1 analyze status OK 1007FLUSH TABLE t1; 1008set optimizer_use_condition_selectivity=3; 1009EXPLAIN EXTENDED 1010SELECT * FROM t1 WHERE a IS NULL; 1011id select_type table type possible_keys key key_len ref rows filtered Extra 10121 SIMPLE t1 ALL NULL NULL NULL NULL 14 28.57 Using where 1013Warnings: 1014Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is null 1015EXPLAIN EXTENDED 1016SELECT * FROM t1 WHERE a IS NOT NULL; 1017id select_type table type possible_keys key key_len ref rows filtered Extra 10181 SIMPLE t1 ALL NULL NULL NULL NULL 14 71.43 Using where 1019Warnings: 1020Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is not null 1021EXPLAIN EXTENDED 1022SELECT * FROM t1 WHERE a IS NULL OR a IS NOT NULL; 1023id select_type table type possible_keys key key_len ref rows filtered Extra 10241 SIMPLE t1 ALL NULL NULL NULL NULL 14 100.00 Using where 1025Warnings: 1026Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` is not null 1027EXPLAIN EXTENDED 1028SELECT * FROM t1 WHERE a IS NULL OR a < 5; 1029id select_type table type possible_keys key key_len ref rows filtered Extra 10301 SIMPLE t1 ALL NULL NULL NULL NULL 14 69.39 Using where 1031Warnings: 1032Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` is null or `test`.`t1`.`a` < 5 1033set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1034DROP TABLE t1; 1035set use_stat_tables=@save_use_stat_tables; 1036# 1037# Bug mdev-4378: 2-way join with a materialized IN subquery in WHERE 1038# when optimizer_use_condition_selectivity=4 1039# 1040set use_stat_tables=PREFERABLY; 1041set histogram_size=50; 1042set histogram_type=SINGLE_PREC_HB; 1043CREATE TABLE t1 (a INT) ENGINE=MyISAM; 1044INSERT INTO t1 VALUES (8),(9),(6); 1045CREATE TABLE t2 (b INT) ENGINE=MyISAM; 1046INSERT INTO t2 VALUES (8),(1),(8),(9),(24),(6),(1),(6),(2),(4); 1047CREATE TABLE t3 (ln VARCHAR(16)) ENGINE=MyISAM; 1048INSERT INTO t3 VALUES 1049('smith'),('black'),('white'),('jones'), 1050('brown'),('taylor'),('anderson'),('taylor'); 1051ANALYZE TABLE t1, t2, t3; 1052Table Op Msg_type Msg_text 1053test.t1 analyze status Engine-independent statistics collected 1054test.t1 analyze status OK 1055test.t2 analyze status Engine-independent statistics collected 1056test.t2 analyze status OK 1057test.t3 analyze status Engine-independent statistics collected 1058test.t3 analyze status OK 1059FLUSH TABLES; 1060set optimizer_use_condition_selectivity=4; 1061SELECT * FROM t1, t2 WHERE 'garcia' IN ( SELECT MIN( ln ) FROM t3 WHERE ln = 'sun' ); 1062a b 1063set histogram_size=@save_histogram_size; 1064set histogram_type=@save_histogram_type; 1065set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1066DROP TABLE t1,t2,t3; 1067set use_stat_tables=@save_use_stat_tables; 1068# 1069# Bug mdev-4380: 2-way join with a materialized IN subquery in WHERE 1070# when optimizer_use_condition_selectivity=3 1071# 1072set use_stat_tables=PREFERABLY; 1073CREATE TABLE t1 (a INT); 1074INSERT INTO t1 VALUES (5),(9); 1075CREATE TABLE t2 (b VARCHAR(8)); 1076INSERT INTO t2 VALUES ('red'),('blue'); 1077CREATE TABLE t3 (c VARCHAR(8), d VARCHAR(8)); 1078INSERT INTO t3 VALUES ('white','black'),('cyan','yellow'); 1079ANALYZE TABLE t1, t2, t3; 1080Table Op Msg_type Msg_text 1081test.t1 analyze status Engine-independent statistics collected 1082test.t1 analyze status OK 1083test.t2 analyze status Engine-independent statistics collected 1084test.t2 analyze status OK 1085test.t3 analyze status Engine-independent statistics collected 1086test.t3 analyze status OK 1087FLUSH TABLES; 1088set optimizer_use_condition_selectivity=3; 1089SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( 1090SELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c 1091); 1092a b 1093set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1094DROP TABLE t1,t2,t3; 1095set use_stat_tables=@save_use_stat_tables; 1096# 1097# Bug mdev-4389: join with degenerated range condition in WHERE 1098# when optimizer_use_condition_selectivity=3 1099# 1100set use_stat_tables=PREFERABLY; 1101CREATE TABLE t1 (f1 VARCHAR(1)); 1102INSERT t1 VALUES ('p'),('q'); 1103CREATE TABLE t2 (f2 VARCHAR(1)); 1104INSERT INTO t2 VALUES 1105('o'),('v'),('f'),('f'),('e'),('l'),('j'),('p'),('r'),('j'), 1106('j'),('u'),('i'),('r'),('x'),('a'),('x'),('s'); 1107ANALYZE TABLE t1, t2; 1108Table Op Msg_type Msg_text 1109test.t1 analyze status Engine-independent statistics collected 1110test.t1 analyze status OK 1111test.t2 analyze status Engine-independent statistics collected 1112test.t2 analyze status OK 1113FLUSH TABLES; 1114SET optimizer_use_condition_selectivity=3; 1115SELECT * FROM t1, t2 AS t2a, t2 AS t2b WHERE f1 <= 'a' AND t2a.f2 = f1; 1116f1 f2 f2 1117set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1118DROP TABLE t1,t2; 1119set use_stat_tables=@save_use_stat_tables; 1120# 1121# Bug mdev-4406: range condition for non-nullable column 1122# when optimizer_use_condition_selectivity=3 1123# 1124create table t1 (a int not null); 1125insert into t1 values 1126(7), (6), (4), (9), (1), (5), (2), (1), (3), (8); 1127set use_stat_tables='preferably'; 1128analyze table t1; 1129Table Op Msg_type Msg_text 1130test.t1 analyze status Engine-independent statistics collected 1131test.t1 analyze status OK 1132flush table t1; 1133set optimizer_use_condition_selectivity=3; 1134select count(*) from t1 where a between 5 and 7; 1135count(*) 11363 1137explain extended select * from t1 where a between 5 and 7; 1138id select_type table type possible_keys key key_len ref rows filtered Extra 11391 SIMPLE t1 ALL NULL NULL NULL NULL 10 25.00 Using where 1140Warnings: 1141Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` between 5 and 7 1142alter table t1 change column a a int; 1143analyze table t1; 1144Table Op Msg_type Msg_text 1145test.t1 analyze status Engine-independent statistics collected 1146test.t1 analyze status OK 1147flush table t1; 1148explain extended select * from t1 where a between 5 and 7; 1149id select_type table type possible_keys key key_len ref rows filtered Extra 11501 SIMPLE t1 ALL NULL NULL NULL NULL 10 25.00 Using where 1151Warnings: 1152Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` between 5 and 7 1153set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1154drop table t1; 1155set use_stat_tables=@save_use_stat_tables; 1156# 1157# Bug mdev-5200: impossible where with a semijoin subquery 1158# when optimizer_use_condition_selectivity=2 1159# 1160set use_stat_tables = 'preferably'; 1161set optimizer_use_condition_selectivity = 2; 1162CREATE TABLE t1 (i1 int) ENGINE=MyISAM; 1163INSERT INTO t1 VALUES (0), (1); 1164CREATE TABLE t2 (pk2 int, i2 int, c2 char(1), PRIMARY KEY(pk2)) ENGINE=MyISAM; 1165INSERT INTO t2 VALUES (1,8,'m'), (2,9,'b'); 1166CREATE TABLE t3 (c3 char(1), INDEX(c3)) ENGINE=MyISAM; 1167INSERT INTO t3 VALUES ('v'), ('c'); 1168ANALYZE TABLE t1,t2,t3; 1169Table Op Msg_type Msg_text 1170test.t1 analyze status Engine-independent statistics collected 1171test.t1 analyze status OK 1172test.t2 analyze status Engine-independent statistics collected 1173test.t2 analyze status OK 1174test.t3 analyze status Engine-independent statistics collected 1175test.t3 analyze status OK 1176SELECT * FROM t1 1177WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 ); 1178i1 1179EXPLAIN EXTENDED 1180SELECT * FROM t1 1181WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 ); 1182id select_type table type possible_keys key key_len ref rows filtered Extra 11831 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1184Warnings: 1185Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` left join `test`.`t3` on(`test`.`t3`.`c3` = 'b')) where 0 1186set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1187DROP TABLE t1,t2,t3; 1188# 1189# Bug mdev-5415: query over an information schema table 1190# when optimizer_use_condition_selectivity=3 1191# 1192set optimizer_use_condition_selectivity = 3; 1193SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != ''; 1194TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION 1195set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1196# 1197# Bug mdev-5630: always true conjunctive condition 1198# when optimizer_use_condition_selectivity=3 1199# 1200set use_stat_tables = 'preferably'; 1201set optimizer_use_condition_selectivity = 3; 1202CREATE TABLE t1 (a int) ENGINE=MyISAM; 1203INSERT INTO t1 VALUES (10); 1204CREATE TABLE t2 (id int, flag char(1), INDEX(id)) ENGINE=MyISAM; 1205INSERT INTO t2 VALUES (100,'0'),(101,'1'); 1206ANALYZE TABLE t1, t2; 1207Table Op Msg_type Msg_text 1208test.t1 analyze status Engine-independent statistics collected 1209test.t1 analyze status OK 1210test.t2 analyze status Engine-independent statistics collected 1211test.t2 analyze status OK 1212SELECT * FROM t1, t2 WHERE id = a AND ( a = 16 OR flag AND a != 6 ); 1213a id flag 1214DROP TABLE t1,t2; 1215# 1216# Bug mdev-4429: join with range condition whose selectivity == 0 1217# when optimizer_use_condition_selectivity=3 1218# 1219CREATE TABLE language (lang_group INT, lang VARCHAR(16) PRIMARY KEY); 1220INSERT INTO language VALUES 1221(1,'Chinese'),(6,'English'),(1,'French'), 1222(1,'German'),(1,'Italian'),(0,'Japanese'); 1223CREATE TABLE country (code varchar(3) PRIMARY KEY, 1224country_group INT DEFAULT NULL); 1225INSERT INTO country VALUES ('USA',3),('FRA',5); 1226CREATE TABLE continent (cont_group INT, cont varchar(16) PRIMARY KEY); 1227INSERT INTO continent VALUES 1228(1,'N.America'),(1,'S.America'),(3,'Australia'), 1229(4,'Africa'),(5,'Antarctica'),(6,'Eurasia'); 1230SET use_stat_tables=PREFERABLY; 1231ANALYZE TABLE country, language, continent; 1232Table Op Msg_type Msg_text 1233test.country analyze status Engine-independent statistics collected 1234test.country analyze status OK 1235test.language analyze status Engine-independent statistics collected 1236test.language analyze status OK 1237test.continent analyze status Engine-independent statistics collected 1238test.continent analyze status OK 1239FLUSH TABLES; 1240SET optimizer_use_condition_selectivity=3; 1241SELECT * FROM language, country, continent 1242WHERE country_group = lang_group AND lang_group IS NULL; 1243lang_group lang code country_group cont_group cont 1244EXPLAIN EXTENDED 1245SELECT * FROM language, country, continent 1246WHERE country_group = lang_group AND lang_group IS NULL; 1247id select_type table type possible_keys key key_len ref rows filtered Extra 12481 SIMPLE country ALL NULL NULL NULL NULL 2 0.00 Using where 12491 SIMPLE language ALL NULL NULL NULL NULL 6 0.00 Using where; Using join buffer (flat, BNL join) 12501 SIMPLE continent ALL NULL NULL NULL NULL 6 100.00 Using join buffer (incremental, BNL join) 1251Warnings: 1252Note 1003 select `test`.`language`.`lang_group` AS `lang_group`,`test`.`language`.`lang` AS `lang`,`test`.`country`.`code` AS `code`,`test`.`country`.`country_group` AS `country_group`,`test`.`continent`.`cont_group` AS `cont_group`,`test`.`continent`.`cont` AS `cont` from `test`.`language` join `test`.`country` join `test`.`continent` where `test`.`language`.`lang_group` = `test`.`country`.`country_group` and `test`.`country`.`country_group` is null 1253set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1254drop table language, country, continent; 1255set use_stat_tables=@save_use_stat_tables; 1256# 1257# Bug mdev-5191: performance degradation due to a suboptimal chosen plan 1258# when optimizer_use_condition_selectivity=3 1259# 1260set use_stat_tables = 'preferably'; 1261set @@RAND_SEED1=810763568, @@RAND_SEED2=600681772; 1262set TIMESTAMP=1394806993; 1263create table t1 (a int, b int) engine=myisam; 1264insert t1 values (rand()*1e5, rand()*1e5); 1265insert t1 select rand()*1e5, rand()*1e5 from t1; 1266insert t1 select rand()*1e5, rand()*1e5 from t1; 1267insert t1 select rand()*1e5, rand()*1e5 from t1; 1268insert t1 select rand()*1e5, rand()*1e5 from t1; 1269insert t1 select rand()*1e5, rand()*1e5 from t1; 1270insert t1 select rand()*1e5, rand()*1e5 from t1; 1271insert t1 select rand()*1e5, rand()*1e5 from t1; 1272insert t1 select rand()*1e5, rand()*1e5 from t1; 1273insert t1 select rand()*1e5, rand()*1e5 from t1; 1274insert t1 select rand()*1e5, rand()*1e5 from t1; 1275insert t1 select rand()*1e5, rand()*1e5 from t1; 1276insert t1 select rand()*1e5, rand()*1e5 from t1; 1277insert t1 select rand()*1e5, rand()*1e5 from t1; 1278insert t1 select rand()*1e5, rand()*1e5 from t1; 1279insert t1 select rand()*1e5, rand()*1e5 from t1; 1280insert t1 select rand()*1e5, rand()*1e5 from t1; 1281insert t1 select rand()*1e5, rand()*1e5 from t1; 1282insert t1 select rand()*1e5, rand()*1e5 from t1; 1283create table t2 (c int, d int, key(c), key(d)) engine=myisam; 1284insert t2 select floor(rand()*1e5/2)*2, floor(rand()*1e5/3)*3 from t1; 1285analyze table t1,t2; 1286Table Op Msg_type Msg_text 1287test.t1 analyze status Engine-independent statistics collected 1288test.t1 analyze status OK 1289test.t2 analyze status Engine-independent statistics collected 1290test.t2 analyze status Table is already up to date 1291set optimizer_use_condition_selectivity=1; 1292explain extended 1293select * from t1, t2, t1 as t3 1294where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; 1295id select_type table type possible_keys key key_len ref rows filtered Extra 12961 SIMPLE t1 ALL NULL NULL NULL NULL 262144 100.00 Using where 12971 SIMPLE t2 ref c,d c 5 test.t1.b 5 100.00 12981 SIMPLE t3 ALL NULL NULL NULL NULL 262144 100.00 Using where; Using join buffer (flat, BNL join) 1299Warnings: 1300Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where `test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`a` = `test`.`t2`.`d` and `test`.`t3`.`b` < 5 and `test`.`t1`.`a` < 2000 1301select * from t1, t2, t1 as t3 1302where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; 1303a b c d a b 13041063 89366 89366 28296 28296 3 13051495 89366 89366 28296 28296 3 1306221 56120 56120 28296 28296 3 1307961 24512 24512 85239 85239 4 1308set optimizer_use_condition_selectivity=3; 1309explain extended 1310select * from t1, t2, t1 as t3 1311where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; 1312id select_type table type possible_keys key key_len ref rows filtered Extra 13131 SIMPLE t3 ALL NULL NULL NULL NULL 262144 0.00 Using where 13141 SIMPLE t2 ref c,d d 5 test.t3.a 7 100.00 13151 SIMPLE t1 ALL NULL NULL NULL NULL 262144 2.00 Using where; Using join buffer (flat, BNL join) 1316Warnings: 1317Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t3` where `test`.`t1`.`b` = `test`.`t2`.`c` and `test`.`t2`.`d` = `test`.`t3`.`a` and `test`.`t3`.`b` < 5 and `test`.`t1`.`a` < 2000 1318select * from t1, t2, t1 as t3 1319where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; 1320a b c d a b 13211063 89366 89366 28296 28296 3 13221495 89366 89366 28296 28296 3 1323221 56120 56120 28296 28296 3 1324961 24512 24512 85239 85239 4 1325set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1326drop table t1,t2; 1327set histogram_type=@save_histogram_type; 1328set histogram_size=@save_histogram_size; 1329set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1330# 1331# Bug mdev-5931: no where condition after constant table row substitution 1332# with optimizer_use_condition_selectivity=3 1333# 1334CREATE TABLE t1 (a varchar(3), b varchar(3)) ENGINE=MyISAM; 1335INSERT INTO t1 VALUES ('foo', 'foo'); 1336CREATE TABLE t2 (c INT) ENGINE=MyISAM; 1337INSERT INTO t2 VALUES (1), (2); 1338set optimizer_use_condition_selectivity=3; 1339EXPLAIN EXTENDED 1340SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ; 1341id select_type table type possible_keys key key_len ref rows filtered Extra 13421 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 13431 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 1344Warnings: 1345Note 1003 select 'foo' AS `a`,'foo' AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 1 1346SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ; 1347a b c 1348foo foo 1 1349foo foo 2 1350set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1351DROP TABLE t1,t2; 1352# 1353# Bug mdev-6325: wrong selectivity of a column with ref access 1354# 1355create table t0(a int); 1356insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1357create table t1(a int); 1358insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; 1359create table t2 (a int, b int, key(a)); 1360insert into t2 select A.a + 10*B.a, 12345 from t0 A, t0 B, t0 C; 1361set use_stat_tables='preferably'; 1362set histogram_size=100; 1363set optimizer_use_condition_selectivity=4; 1364analyze table t1 persistent for all; 1365Table Op Msg_type Msg_text 1366test.t1 analyze status Engine-independent statistics collected 1367test.t1 analyze status OK 1368analyze table t2 persistent for all; 1369Table Op Msg_type Msg_text 1370test.t2 analyze status Engine-independent statistics collected 1371test.t2 analyze status Table is already up to date 1372explain extended 1373select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10; 1374id select_type table type possible_keys key key_len ref rows filtered Extra 13751 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1.96 Using where 13761 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 1377Warnings: 1378Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` < 10 1379explain extended 1380select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10; 1381id select_type table type possible_keys key key_len ref rows filtered Extra 13821 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1.96 Using where 13831 SIMPLE t2 ref a a 5 test.t1.a 10 100.00 1384Warnings: 1385Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` straight_join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` < 10 1386set histogram_size=@save_histogram_size; 1387set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1388drop table t0,t1,t2; 1389# 1390# Bug mdev-6843: col IS NULL in where condition when col is always NULL 1391# 1392create table t0(a int); 1393insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1394create table t1(a int); 1395insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; 1396create table t2 (a int, b int); 1397insert into t2 select NULL, a from t1; 1398set use_stat_tables='preferably'; 1399set histogram_size=100; 1400set optimizer_use_condition_selectivity=4; 1401analyze table t2 persistent for all; 1402Table Op Msg_type Msg_text 1403test.t2 analyze status Engine-independent statistics collected 1404test.t2 analyze status OK 1405explain extended 1406select * from t2 a straight_join t2 b where a.a is null; 1407id select_type table type possible_keys key key_len ref rows filtered Extra 14081 SIMPLE a ALL NULL NULL NULL NULL 1000 100.00 Using where 14091 SIMPLE b ALL NULL NULL NULL NULL 1000 100.00 Using join buffer (flat, BNL join) 1410Warnings: 1411Note 1003 select `test`.`a`.`a` AS `a`,`test`.`a`.`b` AS `b`,`test`.`b`.`a` AS `a`,`test`.`b`.`b` AS `b` from `test`.`t2` `a` straight_join `test`.`t2` `b` where `test`.`a`.`a` is null 1412set histogram_size=@save_histogram_size; 1413set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1414drop table t0,t1,t2; 1415# 1416# Bug mdev-7316: a conjunct in WHERE with selectivity == 0 1417# 1418CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b)); 1419INSERT INTO t1 VALUES 1420('USAChinese',10), ('USAEnglish',20), ('USAFrench',30); 1421CREATE TABLE t2 (i int); 1422INSERT INTO t2 VALUES 1423(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4); 1424ANALYZE TABLE t1, t2; 1425Table Op Msg_type Msg_text 1426test.t1 analyze status Engine-independent statistics collected 1427test.t1 analyze status OK 1428test.t2 analyze status Engine-independent statistics collected 1429test.t2 analyze status OK 1430set use_stat_tables='preferably'; 1431set optimizer_use_condition_selectivity=3; 1432EXPLAIN EXTENDED 1433SELECT * FROM t1, t2 1434WHERE a <> 'USARussian' AND b IS NULL; 1435id select_type table type possible_keys key key_len ref rows filtered Extra 14361 SIMPLE t1 ref PRIMARY,b b 5 const 1 100.00 Using index condition; Using where 14371 SIMPLE t2 ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) 1438Warnings: 1439Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` <> 'USARussian' and `test`.`t1`.`b` is null 1440SELECT * FROM t1, t2 1441WHERE a <> 'USARussian' AND b IS NULL; 1442a b i 1443set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1444DROP TABLE t1,t2; 1445set use_stat_tables=@save_use_stat_tables; 1446# 1447# Bug mdev-11096: range condition over column without statistical data 1448# 1449set use_stat_tables='preferably'; 1450set optimizer_use_condition_selectivity=3; 1451create table t1(col1 char(32)); 1452insert into t1 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h'); 1453analyze table t1 persistent for columns () indexes (); 1454Table Op Msg_type Msg_text 1455test.t1 analyze status Engine-independent statistics collected 1456test.t1 analyze status OK 1457explain extended 1458select * from t1 where col1 > 'b' and col1 < 'e'; 1459id select_type table type possible_keys key key_len ref rows filtered Extra 14601 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where 1461Warnings: 1462Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where `test`.`t1`.`col1` > 'b' and `test`.`t1`.`col1` < 'e' 1463select * from t1 where col1 > 'b' and col1 < 'e'; 1464col1 1465c 1466d 1467drop table t1; 1468set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1469set use_stat_tables=@save_use_stat_tables; 1470# 1471# Bug mdev-9628: unindexed blob column without min-max statistics 1472# with optimizer_use_condition_selectivity=3 1473# 1474set use_stat_tables='preferably'; 1475set optimizer_use_condition_selectivity=3; 1476create table t1(col1 char(32)); 1477insert into t1 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h'); 1478analyze table t1; 1479Table Op Msg_type Msg_text 1480test.t1 analyze status Engine-independent statistics collected 1481test.t1 analyze status OK 1482create table t2(col1 text); 1483insert into t2 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h'); 1484analyze table t2; 1485Table Op Msg_type Msg_text 1486test.t2 analyze status Engine-independent statistics collected 1487test.t2 analyze Warning Engine-independent statistics are not collected for column 'col1' 1488test.t2 analyze status OK 1489select * from t1 where col1 > 'b' and col1 < 'd'; 1490col1 1491c 1492explain extended 1493select * from t1 where col1 > 'b' and col1 < 'd'; 1494id select_type table type possible_keys key key_len ref rows filtered Extra 14951 SIMPLE t1 ALL NULL NULL NULL NULL 8 28.57 Using where 1496Warnings: 1497Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where `test`.`t1`.`col1` > 'b' and `test`.`t1`.`col1` < 'd' 1498select * from t2 where col1 > 'b' and col1 < 'd'; 1499col1 1500c 1501explain extended 1502select * from t2 where col1 > 'b' and col1 < 'd'; 1503id select_type table type possible_keys key key_len ref rows filtered Extra 15041 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 1505Warnings: 1506Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` > 'b' and `test`.`t2`.`col1` < 'd' 1507select * from t2 where col1 < 'b' and col1 > 'd'; 1508col1 1509explain extended 1510select * from t2 where col1 < 'b' and col1 > 'd'; 1511id select_type table type possible_keys key key_len ref rows filtered Extra 15121 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where 1513Warnings: 1514Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` < 'b' and `test`.`t2`.`col1` > 'd' 1515drop table t1,t2; 1516set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1517set use_stat_tables=@save_use_stat_tables; 1518# 1519# Bug mdev-11364: IS NULL over not nullable datetime column 1520# in mergeable derived 1521# 1522set use_stat_tables='preferably'; 1523set optimizer_use_condition_selectivity=4; 1524set HISTOGRAM_SIZE = 255; 1525CREATE TABLE t1 (t TIME, d DATE NOT NULL); 1526INSERT INTO t1 VALUES ('10:00:00', '0000-00-00'),('11:00:00','0000-00-00'); 1527ANALYZE TABLE t1; 1528Table Op Msg_type Msg_text 1529test.t1 analyze status Engine-independent statistics collected 1530test.t1 analyze status OK 1531SELECT * FROM (SELECT t FROM t1 WHERE d IS NULL) sq; 1532t 153310:00:00 153411:00:00 1535DROP TABLE t1; 1536# 1537# MDEV-16374: filtered shows 0 for materilization scan for a semi join, which makes optimizer 1538# always pick materialization scan over materialization lookup 1539# 1540create table t0(a int); 1541insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1542create table t1 (a int, b int); 1543insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10), 1544(11,11),(12,12),(13,13),(14,14),(15,15); 1545set @@optimizer_use_condition_selectivity=2; 1546explain extended select * from t1 where a in (select max(a) from t1 group by b); 1547id select_type table type possible_keys key key_len ref rows filtered Extra 15481 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where 15491 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 15502 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary 1551Warnings: 1552Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where `<subquery2>`.`max(a)` = `test`.`t1`.`a` 1553select * from t1 where a in (select max(a) from t1 group by b); 1554a b 15550 0 15561 1 15572 2 15583 3 15594 4 15605 5 15616 6 15627 7 15638 8 15649 9 156510 10 156611 11 156712 12 156813 13 156914 14 157015 15 1571set @@optimizer_use_condition_selectivity=1; 1572explain extended select * from t1 where a in (select max(a) from t1 group by b); 1573id select_type table type possible_keys key key_len ref rows filtered Extra 15741 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where 15751 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 15762 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary 1577Warnings: 1578Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where `<subquery2>`.`max(a)` = `test`.`t1`.`a` 1579select * from t1 where a in (select max(a) from t1 group by b); 1580a b 15810 0 15821 1 15832 2 15843 3 15854 4 15865 5 15876 6 15887 7 15898 8 15909 9 159110 10 159211 11 159312 12 159413 13 159514 14 159615 15 1597drop table t1,t0; 1598set histogram_size=@save_histogram_size; 1599set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1600set use_stat_tables=@save_use_stat_tables; 1601# 1602# MDEV-15306: Wrong/Unexpected result with the value 1603# optimizer_use_condition_selectivity set to 4 1604# 1605CREATE TABLE t1 (a INT); 1606INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 1607CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 1608BEGIN 1609SET @cnt := @cnt + 1; 1610RETURN 1; 1611END;| 1612set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; 1613set @save_use_stat_tables= @@use_stat_tables; 1614set @@use_stat_tables='complementary'; 1615set @@optimizer_use_condition_selectivity=4; 1616SET @cnt= 0; 1617SELECT * FROM t1 WHERE a = f1(); 1618a 16191 1620SELECT @cnt; 1621@cnt 16221 1623set @@use_stat_tables='preferably'; 1624analyze table t1 persistent for all; 1625Table Op Msg_type Msg_text 1626test.t1 analyze status Engine-independent statistics collected 1627test.t1 analyze status OK 1628SET @cnt := 0; 1629set @@optimizer_use_condition_selectivity=4; 1630SELECT * FROM t1 WHERE a = f1(); 1631a 16321 1633SELECT @cnt; 1634@cnt 16352 1636alter table t1 force; 1637drop table t1; 1638drop function f1; 1639# 1640# MDEV-19834 Selectivity of an equality condition discounted twice 1641# 1642create table t1 (a int, b int, key (b), key (a)); 1643insert into t1 1644select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; 1645analyze table t1 ; 1646Table Op Msg_type Msg_text 1647test.t1 analyze status Engine-independent statistics collected 1648test.t1 analyze status Table is already up to date 1649# Check what info the optimizer has about selectivities 1650explain extended select * from t1 use index () where a in (17,51,5); 1651id select_type table type possible_keys key key_len ref rows filtered Extra 16521 SIMPLE t1 ALL NULL NULL NULL NULL 1000 3.90 Using where 1653Warnings: 1654Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5) 1655explain extended select * from t1 use index () where b=2; 1656id select_type table type possible_keys key key_len ref rows filtered Extra 16571 SIMPLE t1 ALL NULL NULL NULL NULL 1000 5.47 Using where 1658Warnings: 1659Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`b` = 2 1660# Now, the equality is used for ref access, while the range condition 1661# gives selectivity data 1662explain extended select * from t1 where a in (17,51,5) and b=2; 1663id select_type table type possible_keys key key_len ref rows filtered Extra 16641 SIMPLE t1 ref|filter b,a b|a 5|5 const 58 (3%) 2.90 Using where; Using rowid filter 1665Warnings: 1666Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) 1667drop table t1; 1668set use_stat_tables= @save_use_stat_tables; 1669set @@histogram_size=@save_histogram_size; 1670set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1671# 1672# MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1) 1673# 1674set @@optimizer_use_condition_selectivity=2; 1675set names utf8; 1676CREATE DATABASE world; 1677use world; 1678CREATE TABLE Country ( 1679Code char(3) NOT NULL default '', 1680Name char(52) NOT NULL default '', 1681SurfaceArea float(10,2) NOT NULL default '0.00', 1682Population int(11) NOT NULL default '0', 1683Capital int(11) default NULL, 1684PRIMARY KEY (Code), 1685UNIQUE INDEX (Name) 1686); 1687CREATE TABLE City ( 1688ID int(11) NOT NULL auto_increment, 1689Name char(35) NOT NULL default '', 1690Country char(3) NOT NULL default '', 1691Population int(11) NOT NULL default '0', 1692PRIMARY KEY (ID), 1693INDEX (Population), 1694INDEX (Country) 1695); 1696CREATE TABLE CountryLanguage ( 1697Country char(3) NOT NULL default '', 1698Language char(30) NOT NULL default '', 1699Percentage float(3,1) NOT NULL default '0.0', 1700PRIMARY KEY (Country, Language), 1701INDEX (Percentage) 1702); 1703CREATE INDEX Name ON City(Name); 1704CREATE INDEX CountryPopulation ON City(Country,Population); 1705CREATE INDEX CountryName ON City(Country,Name); 1706set @@optimizer_use_condition_selectivity=2; 1707EXPLAIN 1708SELECT * FROM City WHERE Country='FIN'; 1709id select_type table type possible_keys key key_len ref rows Extra 17101 SIMPLE City ref Country,CountryPopulation,CountryName CountryName 3 const 5 Using index condition 1711DROP DATABASE world; 1712use test; 1713CREATE TABLE t1 ( 1714a INT, 1715b INT NOT NULL, 1716c char(100), 1717KEY (b, c), 1718KEY (b, a, c) 1719) ENGINE=MyISAM 1720DEFAULT CHARSET = utf8; 1721INSERT INTO t1 VALUES 1722(1, 1, 1), 1723(2, 2, 2), 1724(3, 3, 3), 1725(4, 4, 4), 1726(5, 5, 5), 1727(6, 6, 6), 1728(7, 7, 7), 1729(8, 8, 8), 1730(9, 9, 9); 1731INSERT INTO t1 SELECT a + 10, b, c FROM t1; 1732INSERT INTO t1 SELECT a + 20, b, c FROM t1; 1733INSERT INTO t1 SELECT a + 40, b, c FROM t1; 1734INSERT INTO t1 SELECT a + 80, b, c FROM t1; 1735INSERT INTO t1 SELECT a + 160, b, c FROM t1; 1736INSERT INTO t1 SELECT a + 320, b, c FROM t1; 1737INSERT INTO t1 SELECT a + 640, b, c FROM t1; 1738INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; 1739EXPLAIN 1740SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; 1741id select_type table type possible_keys key key_len ref rows Extra 17421 SIMPLE t1 range b,b_2 b 4 NULL 226 Using where 1743SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; 1744a 17452071 17462061 17472051 17482041 17492031 17502021 17512011 17522001 17531991 1754set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 1755DROP TABLE t1; 1756# 1757# MDEV-20424: New default value for optimizer_use_condition-selectivity 1758# leads to bad plan 1759# 1760create table t1(a int, b int, c int, d int, key(a,b)); 1761insert into t1 select 50,seq-1,seq-1,seq from seq_1_to_10; 1762insert into t1 select seq-1,seq-1,seq-1,seq from seq_1_to_100 limit 90; 1763create table t2(a int, b int, c int, primary key(a)); 1764insert into t2 select seq-1,seq-1,seq-1 from seq_1_to_100; 1765create table t3(a int, b int, c int, primary key(a)); 1766insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30; 1767set optimizer_use_condition_selectivity=1; 1768explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 1769where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; 1770id select_type table type possible_keys key key_len ref rows filtered Extra 17711 SIMPLE t1 range a a 10 NULL 9 100.00 Using index condition; Using where 17721 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 17731 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index 1774Warnings: 1775Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 1776select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 1777where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; 1778b a a b 17790 0 1 1 17801 1 2 2 17812 2 3 3 17823 3 4 4 17834 4 5 5 17845 5 6 6 17856 6 7 7 17867 7 8 8 17878 8 9 9 17889 9 10 10 1789set optimizer_use_condition_selectivity=2; 1790explain extended select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 1791where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; 1792id select_type table type possible_keys key key_len ref rows filtered Extra 17931 SIMPLE t1 range a a 10 NULL 9 9.00 Using index condition; Using where 17941 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.d 1 100.00 17951 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 100.00 Using index 1796Warnings: 1797Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t1`.`c` and `test`.`t3`.`a` = `test`.`t1`.`d` and `test`.`t1`.`a` = 50 and `test`.`t1`.`b` <= 100 1798select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 1799where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; 1800b a a b 18010 0 1 1 18021 1 2 2 18032 2 3 3 18043 3 4 4 18054 4 5 5 18065 5 6 6 18076 6 7 7 18087 7 8 8 18098 8 9 9 18109 9 10 10 1811set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; 1812drop table t1,t2,t3; 1813# 1814# MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 1815# 1816create table t1 (id int, a int, PRIMARY KEY(id), key(a)); 1817insert into t1 select seq,seq from seq_1_to_100; 1818create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); 1819insert into t2 select seq,seq,seq from seq_1_to_100; 1820set optimizer_switch='exists_to_in=off'; 1821set optimizer_use_condition_selectivity=2; 1822SELECT * FROM t1 1823WHERE 1824EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id 1825WHERE A.a=t1.a AND t2.b < 20); 1826id a 18271 1 18282 2 18293 3 18304 4 18315 5 18326 6 18337 7 18348 8 18359 9 183610 10 183711 11 183812 12 183913 13 184014 14 184115 15 184216 16 184317 17 184418 18 184519 19 1846explain SELECT * FROM t1 1847WHERE 1848EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id 1849WHERE A.a=t1.a AND t2.b < 20); 1850id select_type table type possible_keys key key_len ref rows Extra 18511 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where 18522 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 18532 DEPENDENT SUBQUERY t2 ref|filter a,b a|b 5|5 test.A.id 1 (10%) Using where; Using rowid filter 1854EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; 1855id select_type table type possible_keys key key_len ref rows Extra 18561 SIMPLE A const PRIMARY,a PRIMARY 4 const 1 18571 SIMPLE B ref a a 5 const 1 1858explain SELECT * FROM t1 1859WHERE 1860EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id 1861WHERE A.a=t1.a AND t2.b < 20); 1862id select_type table type possible_keys key key_len ref rows Extra 18631 PRIMARY t1 ALL NULL NULL NULL NULL 100 Using where 18642 DEPENDENT SUBQUERY A ref PRIMARY,a a 5 test.t1.a 1 18652 DEPENDENT SUBQUERY t2 ref|filter a,b a|b 5|5 test.A.id 1 (10%) Using where; Using rowid filter 1866set optimizer_switch= @save_optimizer_switch; 1867set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 1868drop table t1,t2; 1869# 1870# MDEV-21495: Conditional jump or move depends on uninitialised value in sel_arg_range_seq_next 1871# 1872CREATE TABLE t1(a INT, b INT); 1873INSERT INTO t1 SELECT seq, seq from seq_1_to_100; 1874set optimizer_use_condition_selectivity=4; 1875ANALYZE TABLE t1 PERSISTENT FOR ALL; 1876Table Op Msg_type Msg_text 1877test.t1 analyze status Engine-independent statistics collected 1878test.t1 analyze status OK 1879SELECT * from t1 WHERE a = 5 and b = 5; 1880a b 18815 5 1882set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 1883drop table t1; 1884# End of 10.1 tests 1885# 1886# MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect 1887# 1888SET optimizer_use_condition_selectivity=4; 1889SET histogram_size=255; 1890CREATE TABLE t1 (a BIT(32), b INT); 1891INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82); 1892ANALYZE TABLE t1 PERSISTENT FOR ALL; 1893Table Op Msg_type Msg_text 1894test.t1 analyze status Engine-independent statistics collected 1895test.t1 analyze status OK 1896EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81; 1897id select_type table type possible_keys key key_len ref rows filtered Extra 18981 SIMPLE t1 ALL NULL NULL NULL NULL 3 66.41 Using where 1899Warnings: 1900Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 81 1901SELECT HEX(a), b from t1 where t1.a >= 81; 1902HEX(a) b 190351 81 190452 82 1905set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 1906set histogram_size=@save_histogram_size; 1907DROP TABLE t1; 1908# 1909# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3 1910# 1911CREATE TABLE t1(a int); 1912INSERT INTO t1 values (1),(2),(2),(3),(4); 1913SET optimizer_use_condition_selectivity=4; 1914SET histogram_size= 255; 1915set use_stat_tables='preferably'; 1916ANALYZE TABLE t1 PERSISTENT FOR ALL; 1917Table Op Msg_type Msg_text 1918test.t1 analyze status Engine-independent statistics collected 1919test.t1 analyze status OK 1920EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; 1921id select_type table type possible_keys key key_len ref rows filtered Extra 19221 SIMPLE t1 ALL NULL NULL NULL NULL 5 39.84 Using where 1923Warnings: 1924Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 1925SET optimizer_use_condition_selectivity=3; 1926# filtered should show 25 % 1927EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; 1928id select_type table type possible_keys key key_len ref rows filtered Extra 19291 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where 1930Warnings: 1931Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 1932FLUSH TABLES; 1933EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; 1934id select_type table type possible_keys key key_len ref rows filtered Extra 19351 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where 1936Warnings: 1937Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 1938set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 1939set histogram_size=@save_histogram_size; 1940set use_stat_tables= @save_use_stat_tables; 1941DROP TABLE t1; 1942# End of 10.2 tests 1943set @@global.histogram_size=@save_histogram_size; 1944