MDEV-30218: Incorrect optimization for rowid_filtering, correctionbb-10.4-mdev30218-part2

Enable use of Rowid Filter optimization with eq_ref access.
Use the following assumptions:
- Assume index-only access cost is 50% of non-index-only access cost.
- Take into account that "Eq_ref access cache" reduces the number of
  lookups eq_ref access will make.
  = This means the number of Rowid Filter checks is reduced also
  = Eq_ref access cost is computed using that assumption (see
    prev_record_reads() call), so we should use it in all cost '
    computations.

1 files changed, 22 insertions, 6 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index eb54484fa51..217d02bfa8d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7891,7 +7891,27 @@ best_access_path(JOIN      *join,
           (s->table->file->index_flags(start_key->key,0,1) &
            HA_DO_RANGE_FILTER_PUSHDOWN))
       {
-        double rows= record_count * records;
+        double rows;
+        if (type == JT_EQ_REF)
+        {
+          /*
+            Treat EQ_REF access in a special way:
+            1. We have no cost for index-only read. Assume its cost is 50% of
+               the cost of the full read.
+
+            2. A regular ref access will do #record_count lookups, but eq_ref
+               has "lookup cache" which reduces the number of lookups made.
+               The estimation code uses prev_record_reads() call to estimate:
+
+                tmp = prev_record_reads(join_positions, idx, found_ref);
+
+               Set the effective number of rows from "tmp" here.
+          */
+          keyread_tmp= tmp/ 2;
+          rows= tmp;
+        }
+        else
+          rows= record_count * records;
 
         /*
           If we use filter F with selectivity s the the cost of fetching data
@@ -7934,10 +7954,6 @@ best_access_path(JOIN      *join,
           we cannot use filters as the cost calculation below would cause
           tmp to become negative.  The future resultion is to not limit
           cost with worst_seek.
-
-          We cannot use filter with JT_EQ_REF as in this case 'tmp' is
-          number of rows from prev_record_read() and keyread_tmp is 0. These
-          numbers are not usable with rowid filter code.
 	*/
         double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0);
         if (!(records < s->worst_seeks &&
@@ -7945,7 +7961,7 @@ best_access_path(JOIN      *join,
           trace_access_idx.add("rowid_filter_skipped", "worst/max seeks clipping");
         else if (access_cost_factor <= 0.0)
           trace_access_idx.add("rowid_filter_skipped", "cost_factor <= 0");
-        else if (type != JT_EQ_REF)
+        else
         {
           filter=
             table->best_range_rowid_filter_for_partial_join(start_key->key,