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

4 files changed, 23 insertions, 23 deletions

diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result
index f0c7c4eebe1..d4f8e28fe07 100644
--- a/mysql-test/suite/compat/oracle/r/table_value_constr.result
+++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result
@@ -741,21 +741,21 @@ a	b
 explain extended select * from t1
 where a in (values (1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1"
+Note	1003	select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1
 explain extended select * from t1
 where a in (select * from (values (1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1"
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1
 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place
 select * from t1
 where a in (values (1) union select 2);
@@ -976,21 +976,21 @@ a	b
 explain extended select * from t1 
 where a = any (values (1),(2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1"
+Note	1003	select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1
 explain extended select * from t1 
 where a = any (select * from (values (1),(2)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1"
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1
 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
 select * from t1 
 where a = any (values (1) union select 2);
diff --git a/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result
index bc9bddad690..81194cf7e13 100644
--- a/mysql-test/suite/gcol/r/gcol_select_innodb.result
+++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result
@@ -691,8 +691,8 @@ WHERE t4.c1 < 'o'
 )
 AND t1.i1 <= t3.i2_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.i1	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -746,8 +746,8 @@ WHERE t4.c1 < 'o'
 )
 AND t1.i1 <= t3.i2_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.i1	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -802,8 +802,8 @@ WHERE t4.c1 < 'o'
 )
 AND t1.i1 <= t3.i2_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.i1	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -866,8 +866,8 @@ WHERE t4.c1 < 'o'
 )
 AND t1.i1 <= t3.i2_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.i1	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result
index 280618ed76e..6e3411dc066 100644
--- a/mysql-test/suite/gcol/r/gcol_select_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result
@@ -1313,8 +1313,8 @@ WHERE t4.c1 < 'o'
 )
 AND t1.i1 <= t3.i2_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	eq_ref	PRIMARY,v_idx	PRIMARY	4	test.t4.i1	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -1369,8 +1369,8 @@ WHERE t4.c1 < 'o'
 )
 AND t1.i1 <= t3.i2_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	eq_ref	PRIMARY,v_idx,v_idx2	PRIMARY	4	test.t4.i1	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -1427,8 +1427,8 @@ WHERE t4.c1 < 'o'
 )
 AND t1.i1 <= t3.i2_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	eq_ref	PRIMARY,v_idx2	PRIMARY	4	test.t4.i1	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -1494,8 +1494,8 @@ WHERE t4.c1 < 'o'
 )
 AND t1.i1 <= t3.i2_key;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	eq_ref	PRIMARY,v_idx	PRIMARY	4	test.t4.i1	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	3	Using where
diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result
index fc070a70120..e35ab3e84d6 100644
--- a/mysql-test/suite/versioning/r/cte.result
+++ b/mysql-test/suite/versioning/r/cte.result
@@ -137,15 +137,15 @@ where e.mgr = a.emp_id
 )
 select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	4	100.00	
-1	PRIMARY	emp	ALL	PRIMARY	NULL	NULL	NULL	4	75.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	emp	ALL	PRIMARY	NULL	NULL	NULL	4	75.00	Using where
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 4	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	
 2	DERIVED	e	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 3	RECURSIVE UNION	e	ALL	mgr-fk	NULL	NULL	NULL	4	100.00	Using where
 3	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.e.mgr	2	100.00	
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`emp_id` = `ancestors`.`emp_id` and `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999'
+Note	1003	with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999'
 with recursive
 ancestors
 as