MDEV-20109: Optimizer ignores distinct key created for materialized...

(Backported to 10.3, addressed review input)

Sj_materialization_picker::check_qep(): fix error in cost/fanout
calculations:
- for each join prefix, add #prefix_rows / TIME_FOR_COMPARE to the cost,
  like best_extension_by_limited_search does
- Remove the fanout produced by the subquery tables.
- Also take into account join condition selectivity

optimize_wo_join_buffering() (used by LooseScan and FirstMatch)
- also add #prefix_rows / TIME_FOR_COMPARE to the cost of each prefix.
- Also take into account join condition selectivity

1 files changed, 12 insertions, 10 deletions

diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result
index 2955307eb86..23c54ab7734 100644
--- a/mysql-test/main/subselect_sj2_jcl6.result
+++ b/mysql-test/main/subselect_sj2_jcl6.result
@@ -158,9 +158,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	hash_ALL	NULL	#hash#$hj	5	test.it.a	32	Using where; Using join buffer (flat, BNLH join)
-2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	Using where
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -226,9 +226,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	hash_ALL	NULL	#hash#$hj	5	test.it.a	52	Using where; Using join buffer (flat, BNLH join)
-2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	Using where
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -1367,9 +1367,10 @@ SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t3); Using join buffer (incremental, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t1	ref	b	b	4	test.t3.b	1	Using index
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	
+2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 b	c
@@ -1395,9 +1396,10 @@ EXPLAIN
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t2)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+2	MATERIALIZED	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 pk	a	b
 1	6	8