MDEV-8289: Semijoin inflates number of rows in query result

- Make semi-join optimizer not to choose LooseScan
  when 1) the index is not covered and 2) full index
  scan will be required.

- Make sure that the code in make_join_select() that may change
  full index scan into a range scan is not invoked when the table
  uses full scan.

1 files changed, 10 insertions, 3 deletions

diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 3da94d05521..0fb1a931e36 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -194,8 +194,6 @@ public:
         PREV_BITS(key_part_map, max_loose_keypart+1) &&        // (3)
         !key_uses_partial_cols(s->table->s, key))
     {
-      /* Ok, can use the strategy */
-      part1_conds_met= TRUE;
       if (s->quick && s->quick->index == key && 
           s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
       {
@@ -204,6 +202,12 @@ public:
       }
       DBUG_PRINT("info", ("Can use LooseScan scan"));
 
+      if (found_part & 1)
+      {
+        /* Can use LooseScan on ref access if the first key part is bound */
+        part1_conds_met= TRUE;
+      }
+
       /* 
         Check if this is a special case where there are no usable bound
         IN-equalities, i.e. we have
@@ -211,11 +215,13 @@ public:
           outer_expr IN (SELECT innertbl.key FROM ...) 
         
         and outer_expr cannot be evaluated yet, so it's actually full
-        index scan and not a ref access
+        index scan and not a ref access.
+        We can do full index scan if it uses index-only.
       */
       if (!(found_part & 1 ) && /* no usable ref access for 1st key part */
           s->table->covering_keys.is_set(key))
       {
+        part1_conds_met= TRUE;
         DBUG_PRINT("info", ("Can use full index scan for LooseScan"));
         
         /* Calculate the cost of complete loose index scan.  */
@@ -383,6 +389,7 @@ public:
   bool create_sj_weedout_tmp_table(THD *thd);
 };
 
+int setup_semijoin_loosescan(JOIN *join);
 int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, 
                                     uint no_jbuf_after);
 void destroy_sj_tmp_tables(JOIN *join);