Fixed several bugs for mwl #253.

One of them is quite serious: the function table_cond_selectivity used
the TABLE_REF structure for ref/eq_ref access methods as if they had been
filled. In  fact these structure are filled after the best execution plan
has been chosen.

The other bugs happened due to:
- an erroneous attempt at get statistics on the result of materialization
  of a view
- incorrect handling of ranges with no left/right limits when calculating
  selectivity of range conditions on non-indexed columns
- lack of cleanup for some newly introduced fields
 

1 files changed, 341 insertions, 0 deletions

diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 9ececad3492..6fbff38000d 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -14,6 +14,347 @@ CREATE DATABASE dbt3_s001;
 use dbt3_s001;
 set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
 set @save_histogram_size=@@histogram_size;
+=== Q15 ===
+create view revenue0 (supplier_no, total_revenue) as
+select l_suppkey, sum(l_extendedprice * (1 - l_discount))
+from lineitem
+where
+l_shipdate >= '1995-08-01'
+    and l_shipdate < date_add('1995-08-01', interval 90 day)
+group by l_suppkey;
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=off';
+set optimizer_use_condition_selectivity=1;
+EXPLAIN EXTENDED select s_suppkey, s_name, s_address, s_phone, total_revenue
+from supplier, revenue0
+where s_suppkey = supplier_no
+and total_revenue = (select max(total_revenue) from revenue0)
+order by s_suppkey;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	supplier	index	PRIMARY	PRIMARY	4	NULL	10	100.00	
+1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
+3	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	228	100.00	Using where; Using temporary; Using filesort
+2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	228	100.00	
+4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	228	100.00	Using where; Using temporary; Using filesort
+Warnings:
+Note	1003	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 max(`revenue0`.`total_revenue`) from `dbt3_s001`.`revenue0`))) order by `dbt3_s001`.`supplier`.`s_suppkey`
+select s_suppkey, s_name, s_address, s_phone, total_revenue
+from supplier, revenue0
+where s_suppkey = supplier_no
+and total_revenue = (select max(total_revenue) from revenue0)
+order by s_suppkey;
+s_suppkey	s_name	s_address	s_phone	total_revenue
+1	Supplier#000000001	 N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ	27-918-335-1736	729084.7773
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED select s_suppkey, s_name, s_address, s_phone, total_revenue
+from supplier, revenue0
+where s_suppkey = supplier_no
+and total_revenue = (select max(total_revenue) from revenue0)
+order by s_suppkey;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	supplier	index	PRIMARY	PRIMARY	4	NULL	10	100.00	
+1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
+3	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	228	0.14	Using where; Using temporary; Using filesort
+2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	228	100.00	
+4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	228	0.14	Using where; Using temporary; Using filesort
+Warnings:
+Note	1003	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 max(`revenue0`.`total_revenue`) from `dbt3_s001`.`revenue0`))) order by `dbt3_s001`.`supplier`.`s_suppkey`
+select s_suppkey, s_name, s_address, s_phone, total_revenue
+from supplier, revenue0
+where s_suppkey = supplier_no
+and total_revenue = (select max(total_revenue) from revenue0)
+order by s_suppkey;
+s_suppkey	s_name	s_address	s_phone	total_revenue
+1	Supplier#000000001	 N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ	27-918-335-1736	729084.7773
+set optimizer_switch=@save_optimizer_switch;
+drop view revenue0;
+=== Q16 ===
+set optimizer_use_condition_selectivity=1;
+EXPLAIN EXTENDED select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
+from partsupp, part
+where p_partkey = ps_partkey
+and p_brand <> 'Brand#11'
+  and p_type not like 'SMALL POLISHED%'
+  and p_size in (49, 37, 27, 5, 40, 6, 22, 8)
+and ps_suppkey not in (select s_suppkey from supplier
+where s_comment like '%Customer%Complaints%')
+group by p_brand, p_type, p_size
+order by supplier_cnt desc, p_brand, p_type, p_size;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	100.00	Using where; Using temporary; Using filesort
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey	i_ps_partkey	4	dbt3_s001.part.p_partkey	3	100.00	Using where; Using index
+2	MATERIALIZED	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using where
+Warnings:
+Note	1003	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 (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (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`
+select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
+from partsupp, part
+where p_partkey = ps_partkey
+and p_brand <> 'Brand#11'
+  and p_type not like 'SMALL POLISHED%'
+  and p_size in (49, 37, 27, 5, 40, 6, 22, 8)
+and ps_suppkey not in (select s_suppkey from supplier
+where s_comment like '%Customer%Complaints%')
+group by p_brand, p_type, p_size
+order by supplier_cnt desc, p_brand, p_type, p_size;
+p_brand	p_type	p_size	supplier_cnt
+Brand#21	MEDIUM ANODIZED TIN	8	4
+Brand#22	PROMO PLATED TIN	5	4
+Brand#24	MEDIUM BURNISHED NICKEL	6	4
+Brand#24	SMALL ANODIZED STEEL	40	4
+Brand#32	MEDIUM BURNISHED BRASS	49	4
+Brand#33	MEDIUM POLISHED BRASS	49	4
+Brand#41	STANDARD BRUSHED NICKEL	40	4
+Brand#44	PROMO POLISHED STEEL	5	4
+Brand#45	PROMO ANODIZED BRASS	22	4
+Brand#53	STANDARD BRUSHED STEEL	27	4
+Brand#54	MEDIUM POLISHED BRASS	22	4
+Brand#54	STANDARD ANODIZED BRASS	22	4
+Brand#13	LARGE BRUSHED STEEL	8	2
+Brand#25	ECONOMY BURNISHED COPPER	27	2
+Brand#44	STANDARD PLATED TIN	37	1
+Brand#51	ECONOMY POLISHED STEEL	49	1
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
+from partsupp, part
+where p_partkey = ps_partkey
+and p_brand <> 'Brand#11'
+  and p_type not like 'SMALL POLISHED%'
+  and p_size in (49, 37, 27, 5, 40, 6, 22, 8)
+and ps_suppkey not in (select s_suppkey from supplier
+where s_comment like '%Customer%Complaints%')
+group by p_brand, p_type, p_size
+order by supplier_cnt desc, p_brand, p_type, p_size;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	16.67	Using where; Using temporary; Using filesort
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey	i_ps_partkey	4	dbt3_s001.part.p_partkey	3	100.00	Using where; Using index
+2	MATERIALIZED	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using where
+Warnings:
+Note	1003	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 (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (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`
+select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
+from partsupp, part
+where p_partkey = ps_partkey
+and p_brand <> 'Brand#11'
+  and p_type not like 'SMALL POLISHED%'
+  and p_size in (49, 37, 27, 5, 40, 6, 22, 8)
+and ps_suppkey not in (select s_suppkey from supplier
+where s_comment like '%Customer%Complaints%')
+group by p_brand, p_type, p_size
+order by supplier_cnt desc, p_brand, p_type, p_size;
+p_brand	p_type	p_size	supplier_cnt
+Brand#21	MEDIUM ANODIZED TIN	8	4
+Brand#22	PROMO PLATED TIN	5	4
+Brand#24	MEDIUM BURNISHED NICKEL	6	4
+Brand#24	SMALL ANODIZED STEEL	40	4
+Brand#32	MEDIUM BURNISHED BRASS	49	4
+Brand#33	MEDIUM POLISHED BRASS	49	4
+Brand#41	STANDARD BRUSHED NICKEL	40	4
+Brand#44	PROMO POLISHED STEEL	5	4
+Brand#45	PROMO ANODIZED BRASS	22	4
+Brand#53	STANDARD BRUSHED STEEL	27	4
+Brand#54	MEDIUM POLISHED BRASS	22	4
+Brand#54	STANDARD ANODIZED BRASS	22	4
+Brand#13	LARGE BRUSHED STEEL	8	2
+Brand#25	ECONOMY BURNISHED COPPER	27	2
+Brand#44	STANDARD PLATED TIN	37	1
+Brand#51	ECONOMY POLISHED STEEL	49	1
+set optimizer_use_condition_selectivity=4;
+EXPLAIN EXTENDED select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
+from partsupp, part
+where p_partkey = ps_partkey
+and p_brand <> 'Brand#11'
+  and p_type not like 'SMALL POLISHED%'
+  and p_size in (49, 37, 27, 5, 40, 6, 22, 8)
+and ps_suppkey not in (select s_suppkey from supplier
+where s_comment like '%Customer%Complaints%')
+group by p_brand, p_type, p_size
+order by supplier_cnt desc, p_brand, p_type, p_size;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	16.67	Using where; Using temporary; Using filesort
+1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey	i_ps_partkey	4	dbt3_s001.part.p_partkey	3	100.00	Using where; Using index
+2	MATERIALIZED	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using where
+Warnings:
+Note	1003	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 (not((`dbt3_s001`.`part`.`p_type` like 'SMALL POLISHED%'))) and (`dbt3_s001`.`part`.`p_size` in (49,37,27,5,40,6,22,8)) and (not(<expr_cache><`dbt3_s001`.`partsupp`.`ps_suppkey`>(<in_optimizer>(`dbt3_s001`.`partsupp`.`ps_suppkey`,`dbt3_s001`.`partsupp`.`ps_suppkey` in ( <materialize> (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`
+select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
+from partsupp, part
+where p_partkey = ps_partkey
+and p_brand <> 'Brand#11'
+  and p_type not like 'SMALL POLISHED%'
+  and p_size in (49, 37, 27, 5, 40, 6, 22, 8)
+and ps_suppkey not in (select s_suppkey from supplier
+where s_comment like '%Customer%Complaints%')
+group by p_brand, p_type, p_size
+order by supplier_cnt desc, p_brand, p_type, p_size;
+p_brand	p_type	p_size	supplier_cnt
+Brand#21	MEDIUM ANODIZED TIN	8	4
+Brand#22	PROMO PLATED TIN	5	4
+Brand#24	MEDIUM BURNISHED NICKEL	6	4
+Brand#24	SMALL ANODIZED STEEL	40	4
+Brand#32	MEDIUM BURNISHED BRASS	49	4
+Brand#33	MEDIUM POLISHED BRASS	49	4
+Brand#41	STANDARD BRUSHED NICKEL	40	4
+Brand#44	PROMO POLISHED STEEL	5	4
+Brand#45	PROMO ANODIZED BRASS	22	4
+Brand#53	STANDARD BRUSHED STEEL	27	4
+Brand#54	MEDIUM POLISHED BRASS	22	4
+Brand#54	STANDARD ANODIZED BRASS	22	4
+Brand#13	LARGE BRUSHED STEEL	8	2
+Brand#25	ECONOMY BURNISHED COPPER	27	2
+Brand#44	STANDARD PLATED TIN	37	1
+Brand#51	ECONOMY POLISHED STEEL	49	1
+=== Q18 ===
+set optimizer_use_condition_selectivity=1;
+EXPLAIN EXTENDED select 
+c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
+from customer, orders, lineitem
+where
+o_orderkey in (select l_orderkey from lineitem
+group by l_orderkey having sum(l_quantity) > 250)
+and c_custkey = o_custkey
+and o_orderkey = l_orderkey
+group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
+order by o_totalprice desc, o_orderdate;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	orders	ALL	PRIMARY,i_o_custkey	NULL	NULL	NULL	1500	100.00	Using where; Using temporary; Using filesort
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.orders.o_orderkey	1	100.00	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	100.00	
+1	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
+2	MATERIALIZED	lineitem	index	NULL	PRIMARY	8	NULL	6005	100.00	
+Warnings:
+Note	1003	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 `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 ((`<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey`) and (`dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey`) and (`dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey`)) 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`
+select 
+c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
+from customer, orders, lineitem
+where
+o_orderkey in (select l_orderkey from lineitem
+group by l_orderkey having sum(l_quantity) > 250)
+and c_custkey = o_custkey
+and o_orderkey = l_orderkey
+group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
+order by o_totalprice desc, o_orderdate;
+c_name	c_custkey	o_orderkey	o_orderdate	o_totalprice	sum(l_quantity)
+Customer#000000070	70	2567	1998-02-27	263411.29	266
+Customer#000000010	10	4421	1997-04-04	258779.02	255
+Customer#000000082	82	3460	1995-10-03	245976.74	254
+Customer#000000068	68	2208	1995-05-01	245388.06	256
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED select 
+c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
+from customer, orders, lineitem
+where
+o_orderkey in (select l_orderkey from lineitem
+group by l_orderkey having sum(l_quantity) > 250)
+and c_custkey = o_custkey
+and o_orderkey = l_orderkey
+group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
+order by o_totalprice desc, o_orderdate;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	orders	ALL	PRIMARY,i_o_custkey	NULL	NULL	NULL	1500	100.00	Using where; Using temporary; Using filesort
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	dbt3_s001.orders.o_orderkey	1	100.00	
+1	PRIMARY	customer	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.orders.o_custkey	1	100.00	
+1	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
+2	MATERIALIZED	lineitem	index	NULL	PRIMARY	8	NULL	6005	100.00	
+Warnings:
+Note	1003	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 `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 ((`<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey`) and (`dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey`) and (`dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey`)) 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`
+select 
+c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
+from customer, orders, lineitem
+where
+o_orderkey in (select l_orderkey from lineitem
+group by l_orderkey having sum(l_quantity) > 250)
+and c_custkey = o_custkey
+and o_orderkey = l_orderkey
+group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
+order by o_totalprice desc, o_orderdate;
+c_name	c_custkey	o_orderkey	o_orderdate	o_totalprice	sum(l_quantity)
+Customer#000000070	70	2567	1998-02-27	263411.29	266
+Customer#000000010	10	4421	1997-04-04	258779.02	255
+Customer#000000082	82	3460	1995-10-03	245976.74	254
+Customer#000000068	68	2208	1995-05-01	245388.06	256
+=== Q22 ===
+set optimizer_use_condition_selectivity=1;
+EXPLAIN EXTENDED select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
+from  (
+select substr(c_phone, 1, 2) as cntrycode, c_acctbal
+from customer
+where
+substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25')
+and c_acctbal > (select avg(c_acctbal) from customer
+where c_acctbal > 0.00
+and substr(c_phone, 1, 2) in
+('10', '20', '14', '19', '11', '28', '25'))
+and not exists (select * from orders where o_custkey = c_custkey)
+) as vip
+group by cntrycode
+order by cntrycode;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	customer	ALL	NULL	NULL	NULL	NULL	150	100.00	Using where; Using temporary; Using filesort
+4	DEPENDENT SUBQUERY	orders	ref	i_o_custkey	i_o_custkey	5	dbt3_s001.customer.c_custkey	15	100.00	Using index
+3	SUBQUERY	customer	ALL	NULL	NULL	NULL	NULL	150	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2
+Note	1003	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 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 (not(exists(select 1 from `dbt3_s001`.`orders` where (`dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`))))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
+select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
+from  (
+select substr(c_phone, 1, 2) as cntrycode, c_acctbal
+from customer
+where
+substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25')
+and c_acctbal > (select avg(c_acctbal) from customer
+where c_acctbal > 0.00
+and substr(c_phone, 1, 2) in
+('10', '20', '14', '19', '11', '28', '25'))
+and not exists (select * from orders where o_custkey = c_custkey)
+) as vip
+group by cntrycode
+order by cntrycode;
+cntrycode	numcust	totacctbal
+11	4	29942.58
+19	2	17120.35
+20	1	9091.82
+28	2	14755.5
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
+from  (
+select substr(c_phone, 1, 2) as cntrycode, c_acctbal
+from customer
+where
+substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25')
+and c_acctbal > (select avg(c_acctbal) from customer
+where c_acctbal > 0.00
+and substr(c_phone, 1, 2) in
+('10', '20', '14', '19', '11', '28', '25'))
+and not exists (select * from orders where o_custkey = c_custkey)
+) as vip
+group by cntrycode
+order by cntrycode;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	customer	ALL	NULL	NULL	NULL	NULL	150	100.00	Using where; Using temporary; Using filesort
+4	DEPENDENT SUBQUERY	orders	ref	i_o_custkey	i_o_custkey	5	dbt3_s001.customer.c_custkey	15	100.00	Using index
+3	SUBQUERY	customer	ALL	NULL	NULL	NULL	NULL	150	91.00	Using where
+Warnings:
+Note	1276	Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2
+Note	1003	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 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 (not(exists(select 1 from `dbt3_s001`.`orders` where (`dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`))))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
+select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
+from  (
+select substr(c_phone, 1, 2) as cntrycode, c_acctbal
+from customer
+where
+substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25')
+and c_acctbal > (select avg(c_acctbal) from customer
+where c_acctbal > 0.00
+and substr(c_phone, 1, 2) in
+('10', '20', '14', '19', '11', '28', '25'))
+and not exists (select * from orders where o_custkey = c_custkey)
+) as vip
+group by cntrycode
+order by cntrycode;
+cntrycode	numcust	totacctbal
+11	4	29942.58
+19	2	17120.35
+20	1	9091.82
+28	2	14755.5
+=== Q20 ===
+set optimizer_use_condition_selectivity=1;
 EXPLAIN EXTENDED select sql_calc_found_rows
 s_name, s_address
 from supplier, nation