MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:May 31 2008 7:26am
Subject:bzr push into mysql-6.0 branch (sergefp:2649) Bug#35468
View as plain text  
 2649 Sergey Petrunia	2008-05-31
      BUG#35468: Slowdown and wrong result for uncorrelated subquery w/o where
      - Moved LooseScan functionality from READ_RECORD functions to join runtime.
        (This enables use of insideout together with range access)
      - Let best_access_path() save the number of the index to be used by the
        insideout scan and #keyparts (just keeping a flag wasn't enough for 
        full-index insideout scans).
      - Made setup_semijoin_dups_elimination() define and use an enum instead 
        of numeric constants.
      - Addressed review feedback
modified:
  mysql-test/r/subselect_sj2.result
  mysql-test/t/subselect_sj2.test
  sql/handler.cc
  sql/handler.h
  sql/mysql_priv.h
  sql/mysqld.cc
  sql/sql_select.cc
  sql/sql_select.h
  sql/structs.h

=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result	2008-05-03 08:27:47 +0000
+++ b/mysql-test/r/subselect_sj2.result	2008-05-31 07:14:57 +0000
@@ -654,3 +654,30 @@ call p1();
 a
 drop procedure p1;
 drop table t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a  from t0 A, t0 B, t0 C;
+create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a  from t1;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `id` int(11) NOT NULL DEFAULT '0',
+  `a` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+set @a=0;
+create table t3 as select * from t2 limit 0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+alter table t3 add primary key(id), add key(a);
+The following must use loose index scan over t3, key a:
+explain select count(a) from t2 where a in ( SELECT  a FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	index	a	a	5	NULL	30000	Using index; LooseScan
+1	PRIMARY	t2	ref	a	a	5	test.t3.a	1	Using index
+select count(a) from t2 where a in ( SELECT  a FROM t3);
+count(a)
+1000
+drop table t0,t1,t2,t3;

=== modified file 'mysql-test/t/subselect_sj2.test'
--- a/mysql-test/t/subselect_sj2.test	2008-05-03 08:27:47 +0000
+++ b/mysql-test/t/subselect_sj2.test	2008-05-31 07:14:57 +0000
@@ -837,3 +837,25 @@ delimiter ;|
 call p1();
 drop procedure p1;
 drop table t1;
+
+#
+# BUG#35468 "Slowdown and wrong result for uncorrelated subquery w/o where"
+#
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a  from t0 A, t0 B, t0 C;
+create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a  from t1;
+show create table t2;
+set @a=0;
+create table t3 as select * from t2 limit 0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+insert into t3 select @a:=@a+1, t2.a from t2, t0;
+
+alter table t3 add primary key(id), add key(a);
+--echo The following must use loose index scan over t3, key a:
+explain select count(a) from t2 where a in ( SELECT  a FROM t3);
+select count(a) from t2 where a in ( SELECT  a FROM t3);
+
+drop table t0,t1,t2,t3;

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2008-05-18 08:52:30 +0000
+++ b/sql/handler.cc	2008-05-31 07:14:57 +0000
@@ -4586,7 +4586,7 @@ ha_rows DsMrr_impl::dsmrr_info_const(uin
   @retval FALSE  No
 */
 
-bool DsMrr_impl::key_uses_partial_cols(uint keyno)
+bool key_uses_partial_cols(TABLE *table, uint keyno)
 {
   KEY_PART_INFO *kp= table->key_info[keyno].key_part;
   KEY_PART_INFO *kp_end= kp + table->key_info[keyno].key_parts;
@@ -4598,7 +4598,6 @@ bool DsMrr_impl::key_uses_partial_cols(u
   return FALSE;
 }
 
-
 /**
   DS-MRR Internals: Choose between Default MRR implementation and DS-MRR
 
@@ -4632,7 +4631,7 @@ bool DsMrr_impl::choose_mrr_impl(uint ke
       (*flags & HA_MRR_INDEX_ONLY) || (*flags & HA_MRR_SORTED) ||
       (keyno == table->s->primary_key && 
        h->primary_key_is_clustered()) || 
-       key_uses_partial_cols(keyno))
+       key_uses_partial_cols(table, keyno))
   {
     /* Use the default implementation */
     *flags |= HA_MRR_USE_DEFAULT_IMPL;

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2008-05-08 13:01:30 +0000
+++ b/sql/handler.h	2008-05-31 07:14:57 +0000
@@ -2319,6 +2319,7 @@ private:
 };
 
 
+bool key_uses_partial_cols(TABLE *table, uint keyno);
 
 /**
   A Disk-Sweep MRR interface implementation
@@ -2379,7 +2380,6 @@ public:
                             void *seq_init_param, uint n_ranges, uint *bufsz,
                             uint *flags, COST_VECT *cost);
 private:
-  bool key_uses_partial_cols(uint keyno);
   bool choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, uint *bufsz, 
                        COST_VECT *cost);
   bool get_disk_sweep_mrr_cost(uint keynr, ha_rows rows, uint flags, 

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2008-05-21 10:17:29 +0000
+++ b/sql/mysql_priv.h	2008-05-31 07:14:57 +0000
@@ -568,6 +568,7 @@ enum open_table_mode
 /* @@optimizer_switch flags */
 #define OPTIMIZER_SWITCH_NO_MATERIALIZATION 1
 #define OPTIMIZER_SWITCH_NO_SEMIJOIN 2
+#define OPTIMIZER_SWITCH_NO_LOOSE_SCAN 4
 
 
 /*

=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc	2008-05-22 18:40:15 +0000
+++ b/sql/mysqld.cc	2008-05-31 07:14:57 +0000
@@ -321,7 +321,7 @@ TYPELIB sql_mode_typelib= { array_elemen
 
 static const char *optimizer_switch_names[]=
 {
-  "no_materialization", "no_semijoin",
+  "no_materialization", "no_semijoin", "no_loosescan",
   NullS
 };
 
@@ -329,7 +329,8 @@ static const char *optimizer_switch_name
 static const unsigned int optimizer_switch_names_len[]=
 {
   /*no_materialization*/          19,
-  /*no_semijoin*/                 11
+  /*no_semijoin*/                 11,
+  /*no_loosescan*/                12,
 };
 
 TYPELIB optimizer_switch_typelib= { array_elements(optimizer_switch_names)-1,"",

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-05-23 05:03:07 +0000
+++ b/sql/sql_select.cc	2008-05-31 07:14:57 +0000
@@ -156,13 +156,11 @@ static int join_read_always_key(JOIN_TAB
 static int join_read_last_key(JOIN_TAB *tab);
 static int join_no_more_records(READ_RECORD *info);
 static int join_read_next(READ_RECORD *info);
-static int join_read_next_different(READ_RECORD *info);
 static int join_init_quick_read_record(JOIN_TAB *tab);
 static int test_if_quick_select(JOIN_TAB *tab);
 static int join_init_read_record(JOIN_TAB *tab);
 static int join_read_first(JOIN_TAB *tab);
 static int join_read_next_same(READ_RECORD *info);
-static int join_read_next_same_diff(READ_RECORD *info);
 static int join_read_last(JOIN_TAB *tab);
 static int join_read_prev_same(READ_RECORD *info);
 static int join_read_prev(READ_RECORD *info);
@@ -1042,6 +1040,12 @@ static
 int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, uint no_jbuf_after)
 {
   table_map cur_map= join->const_table_map | PSEUDO_TABLE_BITS;
+  enum sj_strategy_enum { 
+    SJ_STRATEGY_INVALID,
+    SJ_STRATEGY_INSIDEOUT,
+    SJ_STRATEGY_DUPS_WEEDOUT,
+    SJ_STRATEGY_DUPS_WEEDOUT_JBUF
+  };
   struct {
     /* 
       0 - invalid (EOF marker), 
@@ -1049,7 +1053,7 @@ int setup_semijoin_dups_elimination(JOIN
       2 - Temptable (maybe confluent),
       3 - Temptable with join buffering
     */
-    uint strategy;
+    enum sj_strategy_enum strategy;
     uint start_idx; /* Left range bound */
     uint end_idx;   /* Right range bound */
     /* 
@@ -1073,7 +1077,7 @@ int setup_semijoin_dups_elimination(JOIN
   /*
     First pass: locate the duplicate-generating ranges and pick the strategies.
   */
-  for (i=join->const_tables ; i < join->tables ; i++)
+  for (i= join->const_tables ; i < join->tables ; i++)
   {
     JOIN_TAB *tab=join->join_tab+i;
     TABLE *table=tab->table;
@@ -1091,7 +1095,7 @@ int setup_semijoin_dups_elimination(JOIN
           be that it overlaps with anther semi-join's range. we don't
           support InsideOut for joined ranges)
         */
-        if (join->best_positions[i].use_insideout_scan)
+        if (join->best_positions[i].insideout_key != MAX_KEY)
           emb_insideout_nest= tab->emb_sj_nest;
       }
 
@@ -1161,7 +1165,7 @@ int setup_semijoin_dups_elimination(JOIN
           bitmap_covers(cur_map, emb_insideout_nest->sj_inner_tables))
       {
         /* Save that this range is handled with InsideOut: */
-        dups_ranges[cur_range].strategy= 1;
+        dups_ranges[cur_range].strategy= SJ_STRATEGY_INSIDEOUT;
         end_of_range= TRUE;
       }
       else if (bitmap_covers(cur_map, emb_outer_tables | emb_sj_map))
@@ -1170,7 +1174,9 @@ int setup_semijoin_dups_elimination(JOIN
           This is a complete range to be handled with either DuplicateWeedout 
           or FirstMatch
         */
-        dups_ranges[cur_range].strategy= dealing_with_jbuf? 3 : 2;
+        dups_ranges[cur_range].strategy= 
+          dealing_with_jbuf? SJ_STRATEGY_DUPS_WEEDOUT_JBUF: 
+                             SJ_STRATEGY_DUPS_WEEDOUT;
         /* 
           This will hold tables from within the range that need to be put 
           into the join buffer before we can use the FirstMatch on its tail.
@@ -1186,7 +1192,7 @@ int setup_semijoin_dups_elimination(JOIN
         emb_sj_map= emb_outer_tables= 0;
         emb_insideout_nest= NULL;
         dealing_with_jbuf= FALSE;
-        dups_ranges[++cur_range].strategy= 0;
+        dups_ranges[++cur_range].strategy= SJ_STRATEGY_INVALID;
       }
       else
       {
@@ -1200,18 +1206,27 @@ int setup_semijoin_dups_elimination(JOIN
   THD *thd= join->thd;
   SJ_TMP_TABLE **next_sjtbl_ptr= &join->sj_tmp_tables;
   /*
-    Second pass: setup the chosen strategies    
+    The second pass: setup the chosen strategies    
   */
   for (int j= 0; j < cur_range; j++)
   {
     JOIN_TAB *tab=join->join_tab + dups_ranges[j].start_idx;
     JOIN_TAB *jump_to;
-    if (dups_ranges[j].strategy == 1)  // InsideOut strategy
+    if (dups_ranges[j].strategy == SJ_STRATEGY_INSIDEOUT)
     {
+      /* We jump from the last table to the first one */
       tab->insideout_match_tab= join->join_tab + dups_ranges[j].end_idx - 1;
+      
+      /* Calculate key length */
+      uint nparts= join->positions[dups_ranges[j].start_idx].insideout_parts;
+      uint keyno= join->positions[dups_ranges[j].start_idx].insideout_key;
+      uint keylen= 0;
+      for (uint kp=0; kp < nparts; kp++)
+        keylen += tab->table->key_info[keyno].key_part[kp].store_length;
+      tab->insideout_key_len= keylen;
       jump_to= tab++;
     }
-    else // DuplicateWeedout strategy
+    else // DuplicateWeedout or FirstMatch
     {
       SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
       table_map cur_map= join->const_table_map | PSEUDO_TABLE_BITS;
@@ -5389,7 +5404,12 @@ best_access_path(JOIN      *join,
   table_map best_ref_depends_map= 0;
   double tmp;
   ha_rows rec;
-  uint best_is_sj_inside_out=    0;
+  uint best_is_sj_inside_out= MAX_KEY;
+  uint best_sj_keyparts;
+  bool try_sj_inside_out= FALSE;
+  uint sj_insideout_quick_select= FALSE;
+  uint sj_insideout_quick_max_sj_keypart;
+  uint sj_inside_out_scan= MAX_KEY;
   DBUG_ENTER("best_access_path");
 
   if (s->keyuse)
@@ -5399,14 +5419,13 @@ best_access_path(JOIN      *join,
     double best_records= DBL_MAX;
     uint max_key_part=0;
     ulonglong bound_sj_equalities= 0;
-    bool try_sj_inside_out= FALSE;
     /*
       Discover the bound equalites. We need to do this, if
         1. The next table is an SJ-inner table, and
         2. It is the first table from that semijoin, and
         3. We're not within a semi-join range (i.e. all semi-joins either have
            all or none of their tables in join_table_map), except
-           s->emb_sj_nest (which we've just entered).
+           s->emb_sj_nest (which we've just entered, see #2).
         4. All non-IN-equality correlation references from this sj-nest are 
            bound
         5. But some of the IN-equalities aren't (so this can't be handled by 
@@ -5419,12 +5438,15 @@ best_access_path(JOIN      *join,
         join->cur_emb_sj_nests == s->emb_sj_nest->sj_inner_tables &&    // (3)
         !(remaining_tables & 
           s->emb_sj_nest->nested_join->sj_corr_tables) &&               // (4)
-        remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on)  // (5)
+        remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on &&// (5)
+        !test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_NO_LOOSE_SCAN))
     {
       /* This table is an InsideOut scan candidate */
       bound_sj_equalities= get_bound_sj_equalities(s->emb_sj_nest, 
                                                    remaining_tables);
       try_sj_inside_out= TRUE;
+      DBUG_PRINT("info", ("Will try InsideOut scan, bound_map=%llx",
+                          (longlong)bound_sj_equalities));
     }
 
     /* Test how we can use keys */
@@ -5445,6 +5467,9 @@ best_access_path(JOIN      *join,
       start_key= keyuse;
       ulonglong handled_sj_equalities=0;
       key_part_map sj_insideout_map= 0;
+      uint max_sj_keypart= 0;
+      DBUG_PRINT("info", ("Considering ref access on key %s",
+                          keyuse->table->key_info[keyuse->key].name));
 
       do /* For each keypart */
       {
@@ -5492,6 +5517,7 @@ best_access_path(JOIN      *join,
             {
               handled_sj_equalities |= 1ULL << keyuse->sj_pred_no;
               sj_insideout_map |= ((key_part_map)1) << keyuse->keypart;
+              set_if_bigger(max_sj_keypart, keyuse->keypart);
             }
           }
 
@@ -5510,7 +5536,7 @@ best_access_path(JOIN      *join,
       if (rec < MATCHING_ROWS_IN_OTHER_TABLE)
         rec= MATCHING_ROWS_IN_OTHER_TABLE;      // Fix for small tables
 
-      bool sj_inside_out_scan= FALSE;
+      sj_inside_out_scan= MAX_KEY;
       /*
         ft-keys require special treatment
       */
@@ -5527,49 +5553,66 @@ best_access_path(JOIN      *join,
       {
         found_constraint= test(found_part);
         /*
-          Check if InsideOut scan is applicable:
-          1. All IN-equalities are either "bound" or "handled"
-          2. Index keyparts are 
-             ...
+          Check if we can use InsideOut semi-join strategy. We can if
+          1. This is the right table at right location
+          2. All IN-equalities are either
+             - "bound", ie. the outer_expr part refers to the preceding tables
+             - "handled", ie. covered by the index we're considering
+          3. Index order allows to enumerate subquery's duplicate groups in
+             order. This happens when the index definition matches this
+             pattern:
+
+               (handled_col|bound_col)* (other_col|bound_col)
+
         */
-        if (try_sj_inside_out && 
-            table->covering_keys.is_set(key) &&
-            (handled_sj_equalities | bound_sj_equalities) ==     // (1)
-            PREV_BITS(ulonglong, s->emb_sj_nest->sj_in_exprs)) // (1)
+        if (try_sj_inside_out &&                                    // (1)
+            (handled_sj_equalities | bound_sj_equalities) ==      // (2)
+            PREV_BITS(ulonglong, s->emb_sj_nest->sj_in_exprs) &&  // (2)
+            (PREV_BITS(key_part_map, max_sj_keypart+1) &           // (3)
+             (found_part | sj_insideout_map)) ==                     // (3)
+             (found_part | sj_insideout_map) &&                     // (3)
+            !key_uses_partial_cols(s->table, key))
         {
-          uint n_fixed_parts= max_part_bit(found_part);
-          if (n_fixed_parts != keyinfo->key_parts &&
-              (PREV_BITS(uint, n_fixed_parts) | sj_insideout_map) ==
-               PREV_BITS(uint, keyinfo->key_parts))
+          /* Ok, can use the strategy */
+          sj_inside_out_scan= key;
+          if (s->quick && s->quick->index == key && 
+              s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
           {
-            /*
-              Not all parts are fixed. Produce bitmap of remaining bits and
-              check if all of them are covered.
-            */
-            sj_inside_out_scan= TRUE;
-            DBUG_PRINT("info", ("Using sj InsideOut scan"));
-            if (!n_fixed_parts)
-            {
-              /*
-                It's a confluent ref scan.
+            sj_insideout_quick_select= TRUE;
+            sj_insideout_quick_max_sj_keypart= max_sj_keypart;
+          }
+          DBUG_PRINT("info", ("Can use InsideOut scan"));
 
-                That is, all found KEYUSE elements refer to IN-equalities,
-                and there is really no ref access because there is no
-                  t.keypart0 = {bound expression}
+          /* 
+            Check if this is a confluent where there are no usable bound
+            IN-equalities, e.g. we have
 
-                Calculate the cost of complete loose index scan.
-              */
-              records= (double)s->table->file->stats.records;
+              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
+          */
+          if (!(found_part & 1 ) && /* no usable ref access for 1st key part */
+              table->covering_keys.is_set(key))
+          {
+            DBUG_PRINT("info", ("Can use full index scan for InsideOut"));
+            /* Calculate the cost of complete loose index scan.  */
+            records= rows2double(s->table->file->stats.records);
 
-              /* The cost is entire index scan cost (divided by 2) */
-              best_time= s->table->file->index_only_read_time(key, records);
+            /* The cost is entire index scan cost (divided by 2) */
+            best_time= s->table->file->index_only_read_time(key, records);
 
-              /* Now figure how many different keys we will get */
-              ulong rpc;
-              if ((rpc= keyinfo->rec_per_key[keyinfo->key_parts-1]))
-                records= records / rpc;
-              start_key= NULL;
-            }
+            /*
+              Now find out how many different keys we will get (for now we
+              ignore the fact that we have "keypart_i=const" restriction for
+              some key components, that may make us think think that loose
+              scan will produce more distinct records than it actually will)
+            */
+            ulong rpc;
+            if ((rpc= keyinfo->rec_per_key[max_sj_keypart]))
+              records= records / rpc;
+            start_key= NULL;
+            /* Fall through */
           }
         }
 
@@ -5824,7 +5867,7 @@ best_access_path(JOIN      *join,
             tmp= best_time;                    // Do nothing
         }
 
-        if (sj_inside_out_scan && !start_key)
+        if ((sj_inside_out_scan != MAX_KEY) && !start_key)
         {
           tmp= tmp/2;
           if (records)
@@ -5841,8 +5884,9 @@ best_access_path(JOIN      *join,
         best_max_key_part= max_key_part;
         best_ref_depends_map= found_ref;
         best_is_sj_inside_out= sj_inside_out_scan;
+        best_sj_keyparts= max_sj_keypart;
       }
-    }
+    } /* for each key */
     records= best_records;
   }
 
@@ -5881,6 +5925,7 @@ best_access_path(JOIN      *join,
         ! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
       !(s->table->force_index && best_key && !s->quick))                 // (4)
   {                                             // Check full join
+    sj_inside_out_scan= MAX_KEY;
     ha_rows rnd_records= s->found_records;
     /*
       If there is a filtering condition on the table (i.e. ref analyzer found
@@ -5920,6 +5965,11 @@ best_access_path(JOIN      *join,
       tmp= record_count *
         (s->quick->read_time +
          (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
+      
+      if (sj_insideout_quick_select && idx == join->const_tables)
+      {
+        sj_inside_out_scan= s->quick->index;
+      }
     }
     else
     {
@@ -5971,7 +6021,8 @@ best_access_path(JOIN      *join,
       best_key= 0;
       /* range/index_merge/ALL/index access method are "independent", so: */
       best_ref_depends_map= 0;
-      best_is_sj_inside_out= FALSE;
+      best_is_sj_inside_out= sj_inside_out_scan;
+      best_sj_keyparts= sj_insideout_quick_max_sj_keypart;
     }
   }
 
@@ -5981,7 +6032,8 @@ best_access_path(JOIN      *join,
   join->positions[idx].key=          best_key;
   join->positions[idx].table=        s;
   join->positions[idx].ref_depend_map= best_ref_depends_map;
-  join->positions[idx].use_insideout_scan= best_is_sj_inside_out;
+  join->positions[idx].insideout_key= best_is_sj_inside_out;
+  join->positions[idx].insideout_parts= best_sj_keyparts + 1;
 
   if (!best_key &&
       idx == join->const_tables &&
@@ -6924,7 +6976,7 @@ get_best_combination(JOIN *join)
     DBUG_PRINT("info",("type: %d", j->type));
     if (j->type == JT_CONST)
       continue;					// Handled in make_join_stat..
-
+    j->insideout_match_tab= NULL;
     j->ref.key = -1;
     j->ref.key_parts=0;
 
@@ -6933,6 +6985,7 @@ get_best_combination(JOIN *join)
     if (j->keys.is_clear_all() || !(keyuse= join->best_positions[tablenr].key))
     {
       j->type=JT_ALL;
+      j->index= join->best_positions[tablenr].insideout_key;
       if (tablenr != join->const_tables)
 	join->full_join=1;
     }
@@ -7227,6 +7280,7 @@ make_simple_join(JOIN *join,TABLE *tmp_t
   join_tab->ref.key_parts= 0;
   join_tab->flush_weedout_table= join_tab->check_weed_out_table= NULL;
   join_tab->do_firstmatch= NULL;
+  join_tab->insideout_match_tab= NULL;
   bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record));
   tmp_table->status=0;
   tmp_table->null_row=0;
@@ -8260,9 +8314,8 @@ make_join_readinfo(JOIN *join, ulonglong
     sorted= 0;                                  // only first must be sorted
     if (tab->insideout_match_tab)
     {
-      if (!(tab->insideout_buf= (uchar*)join->thd->alloc(tab->table->key_info
-                                                         [tab->index].
-                                                         key_length)))
+      if (!(tab->insideout_buf= 
+            (uchar*)join->thd->alloc(tab->insideout_key_len)))
         return TRUE;
     }
     switch (tab->type) {
@@ -8323,8 +8376,7 @@ make_join_readinfo(JOIN *join, ulonglong
       if (tab->type == JT_REF)
       {
 	tab->read_first_record= join_read_always_key;
-	tab->read_record.read_record= tab->insideout_match_tab? 
-           join_read_next_same_diff : join_read_next_same;
+        tab->read_record.read_record= join_read_next_same;
       }
       else
       {
@@ -13442,9 +13494,25 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
     Note: psergey has added the 2nd part of the following condition; the 
     change should probably be made in 5.1, too.
   */
+  bool skip_over= FALSE;
   while (rc == NESTED_LOOP_OK && join->return_tab >= join_tab)
   {
+    if (join_tab->insideout_match_tab && join_tab->insideout_match_tab->found_match)
+    {
+      KEY *key= join_tab->table->key_info + join_tab->index;
+      key_copy(join_tab->insideout_buf, info->record, key, 
+               join_tab->insideout_key_len);
+      skip_over= TRUE;
+    }
     error= info->read_record(info);
+
+    if (skip_over && !error && 
+        !key_cmp(join_tab->table->key_info[join_tab->index].key_part,
+                 join_tab->insideout_buf, join_tab->insideout_key_len))
+    {
+      continue;
+    }
+
     rc= evaluate_join_record(join, join_tab, error);
   }
 
@@ -14171,37 +14239,6 @@ join_no_more_records(READ_RECORD *info _
   return -1;
 }
 
-static int
-join_read_next_same_diff(READ_RECORD *info)
-{
-  TABLE *table= info->table;
-  JOIN_TAB *tab=table->reginfo.join_tab;
-  if (tab->insideout_match_tab->found_match)
-  {
-    KEY *key= tab->table->key_info + tab->index;
-    do 
-    {
-      int error;
-      /* Save index tuple from record to the buffer */
-      key_copy(tab->insideout_buf, info->record, key, 0);
-
-      if ((error=table->file->index_next_same(table->record[0],
-                                              tab->ref.key_buff,
-                                              tab->ref.key_length)))
-      {
-        if (error != HA_ERR_END_OF_FILE)
-          return report_error(table, error);
-        table->status= STATUS_GARBAGE;
-        return -1;
-      }
-    } while (!key_cmp(tab->table->key_info[tab->index].key_part, 
-                      tab->insideout_buf, key->key_length));
-    tab->insideout_match_tab->found_match= 0;
-    return 0;
-  }
-  else
-    return join_read_next_same(info);
-}
 
 static int
 join_read_next_same(READ_RECORD *info)
@@ -14298,17 +14335,7 @@ join_read_first(JOIN_TAB *tab)
   tab->read_record.file=table->file;
   tab->read_record.index=tab->index;
   tab->read_record.record=table->record[0];
-  if (tab->insideout_match_tab)
-  {
-    tab->read_record.do_insideout_scan= tab;
-    tab->read_record.read_record=join_read_next_different;
-    tab->insideout_match_tab->found_match= 0;
-  }
-  else
-  {
-    tab->read_record.read_record=join_read_next;
-    tab->read_record.do_insideout_scan= 0;
-  }
+  tab->read_record.read_record=join_read_next;
 
   if (!table->file->inited)
     table->file->ha_index_init(tab->index, tab->sorted);
@@ -14323,32 +14350,6 @@ join_read_first(JOIN_TAB *tab)
 
 
 static int
-join_read_next_different(READ_RECORD *info)
-{
-  JOIN_TAB *tab= info->do_insideout_scan;
-  if (tab->insideout_match_tab->found_match)
-  {
-    KEY *key= tab->table->key_info + tab->index;
-    do 
-    {
-      int error;
-      /* Save index tuple from record to the buffer */
-      key_copy(tab->insideout_buf, info->record, key, 0);
-
-      if ((error=info->file->index_next(info->record)))
-        return report_error(info->table, error);
-      
-    } while (!key_cmp(tab->table->key_info[tab->index].key_part, 
-                      tab->insideout_buf, key->key_length));
-    tab->insideout_match_tab->found_match= 0;
-    return 0;
-  }
-  else
-    return join_read_next(info);
-}
-
-
-static int
 join_read_next(READ_RECORD *info)
 {
   int error;

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2007-12-21 20:10:05 +0000
+++ b/sql/sql_select.h	2008-05-31 07:14:57 +0000
@@ -273,13 +273,18 @@ typedef struct st_join_table {
   struct st_join_table  *do_firstmatch;
  
   /* 
-     ptr  - this join tab should do an InsideOut scan. Points 
-            to the tab for which we'll need to check tab->found_match.
-
+     ptr  - We're doing and InsideOut scan, and this is the first (aka
+            "driving" join tab). ptr to the last tab, for which we'll need 
+            to check tab->found_match to see if the current value group had
+            a match.
      NULL - Not an insideout scan.
   */
   struct st_join_table *insideout_match_tab;
-  uchar *insideout_buf; // Buffer to save index tuple to be able to skip dups
+  /* Buffer to save index tuple to be able to skip duplicates */
+  uchar *insideout_buf;
+  
+  /* How many key components to store in the above */
+  uint insideout_key_len;
 
   /* Used by InsideOut scan. Just set to true when have found a row. */
   bool found_match;
@@ -349,8 +354,16 @@ typedef struct st_position
 
   /* If ref-based access is used: bitmap of tables this table depends on  */
   table_map ref_depend_map;
-
-  bool use_insideout_scan;
+  
+  /* 
+    keyno  - This is an insideout scan on this key. If keyuse is NULL then
+              this is a full index scan, otherwise this is a ref + insideout
+              scan (and keyno matches the KEUSE's)
+    MAX_KEY - This is not an InsideOut scan
+  */
+  uint insideout_key;
+  /* Number of key parts to be used by insideout */
+  uint insideout_parts;
 } POSITION;
 
 

=== modified file 'sql/structs.h'
--- a/sql/structs.h	2008-04-01 15:13:57 +0000
+++ b/sql/structs.h	2008-05-31 07:14:57 +0000
@@ -137,7 +137,6 @@ typedef struct st_read_record {			/* Par
   uchar	*cache,*cache_pos,*cache_end,*read_positions;
   IO_CACHE *io_cache;
   bool print_error, ignore_not_found_rows;
-  struct st_join_table *do_insideout_scan;
 } READ_RECORD;
 
 

Thread
bzr push into mysql-6.0 branch (sergefp:2649) Bug#35468Sergey Petrunia31 May