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, 5 insertions, 2 deletions

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 2a491688c58..6dd7de63612 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -2387,7 +2387,7 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map)
                                          &subjoin_out_rows);
 
         sjm->materialization_cost.convert_from_cost(subjoin_read_time);
-        sjm->rows= subjoin_out_rows;
+        sjm->rows_with_duplicates= sjm->rows= subjoin_out_rows;
         
         // Don't use the following list because it has "stale" items. use
         // ref_pointer_array instead:
@@ -3011,11 +3011,14 @@ bool Sj_materialization_picker::check_qep(JOIN *join,
                        disable_jbuf, prefix_rec_count, &curpos, &dummy);
       prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read);
       prefix_cost= COST_ADD(prefix_cost, curpos.read_time);
+      prefix_cost= COST_ADD(prefix_cost,
+                            prefix_rec_count / (double) TIME_FOR_COMPARE);
+      //TODO: take into account join condition selectivity here
     }
 
     *strategy= SJ_OPT_MATERIALIZE_SCAN;
     *read_time=    prefix_cost;
-    *record_count= prefix_rec_count;
+    *record_count= prefix_rec_count / mat_info->rows_with_duplicates;
     *handled_fanout= mat_nest->sj_inner_tables;
     return TRUE;
   }