bk commit into 5.1 tree (mhansson:1.2562)

[mhansson]

Below is the list of changes that have just been committed into a local
5.1 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-10-11 13:02:43+02:00, mhansson@stripped +5 -0
  Worklog#3724

  sql/mysqld.cc@stripped, 2007-10-11 13:02:35+02:00, mhansson@stripped +1 -0
    Worklog#3724

  sql/set_var.cc@stripped, 2007-10-11 13:02:35+02:00, mhansson@stripped +1 -0
    Worklog#3724

  sql/sql_class.h@stripped, 2007-10-11 13:02:35+02:00, mhansson@stripped +2 -1
    Worklog#3724

  sql/sql_select.cc@stripped, 2007-10-11 13:02:35+02:00, mhansson@stripped +108 -5
    Worklog#3724

  sql/sql_select.h@stripped, 2007-10-11 13:02:35+02:00, mhansson@stripped +36 -0
    Worklog#3724

diff -Nrup a/sql/mysqld.cc b/sql/mysqld.cc
--- a/sql/mysqld.cc	2007-08-28 09:02:53 +02:00
+++ b/sql/mysqld.cc	2007-10-11 13:02:35 +02:00
@@ -7171,6 +7171,7 @@ static void mysql_init_variables(void)
   max_system_variables.ndb_index_stat_cache_entries=~0L;
   global_system_variables.ndb_index_stat_update_freq=20;
   max_system_variables.ndb_index_stat_update_freq=~0L;
+  max_system_variables.explain_shortcuts=1;
 #endif
 #ifdef HAVE_OPENSSL
   have_ssl=SHOW_OPTION_YES;
diff -Nrup a/sql/set_var.cc b/sql/set_var.cc
--- a/sql/set_var.cc	2007-08-23 15:15:30 +02:00
+++ b/sql/set_var.cc	2007-10-11 13:02:35 +02:00
@@ -211,6 +211,7 @@ static sys_var_long_ptr	sys_delayed_queu
 static sys_var_event_scheduler sys_event_scheduler(&vars, "event_scheduler");
 static sys_var_long_ptr	sys_expire_logs_days(&vars, "expire_logs_days",
 					     &expire_logs_days);
+static sys_var_thd_ulong sys_explain_shortcuts(&vars, "explain_shortcuts",
&SV::explain_shortcuts);
 static sys_var_bool_ptr	sys_flush(&vars, "flush", &myisam_flush);
 static sys_var_long_ptr	sys_flush_time(&vars, "flush_time", &flush_time);
 static sys_var_str             sys_ft_boolean_syntax(&vars, "ft_boolean_syntax",
diff -Nrup a/sql/sql_class.h b/sql/sql_class.h
--- a/sql/sql_class.h	2007-08-31 20:13:22 +02:00
+++ b/sql/sql_class.h	2007-10-11 13:02:35 +02:00
@@ -347,7 +347,7 @@ struct system_variables
   DATE_TIME_FORMAT *datetime_format;
   DATE_TIME_FORMAT *time_format;
   my_bool sysdate_is_now;
-
+  ulong explain_shortcuts;
 };
 
 
@@ -413,6 +413,7 @@ typedef struct system_status_var
     global status variable counter
   */
   double last_query_cost;
+  ulong explain_shortcuts;
 } STATUS_VAR;
 
 /*
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-09-21 10:14:59 +02:00
+++ b/sql/sql_select.cc	2007-10-11 13:02:35 +02:00
@@ -223,7 +223,7 @@ static void select_describe(JOIN *join, 
 			    bool distinct, const char *message=NullS);
 static Item *remove_additional_cond(Item* conds);
 static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
-
+static void initialize_shortcuts(JOIN *join);
 
 /*
   This handles SELECT with and without UNION
@@ -2309,7 +2309,7 @@ mysql_select(THD *thd, Item ***rref_poin
 
   if (thd->net.report_error)
     goto err;
-
+  initialize_shortcuts(join);
   join->exec();
 
   if (thd->cursor && thd->cursor->is_open())
@@ -6510,6 +6510,13 @@ void JOIN_TAB::cleanup()
   end_read_record(&read_record);
 }
 
+  
+int JOIN_TAB::do_read_first_record() {
+  join->last_join_tab_accessed= this;
+  return (*read_first_record)(this);
+}
+
+
 
 /*
   Partially cleanup JOIN after it has executed: close index or rnd read
@@ -10569,6 +10576,72 @@ Next_select_func setup_end_select_func(J
   return end_select;
 }
 
+static table_map get_dependencies_map(JOIN_TAB *join_tab)
+{
+  Item *select_cond= join_tab->select_cond;
+  if(!select_cond)
+    return (table_map)0L;
+
+  return select_cond->used_tables() & ~join_tab->table->map;
+}
+
+static bool crosses_outer_join(JOIN_TAB *first, JOIN_TAB *last) 
+{
+  for (JOIN_TAB *join_tab= first; join_tab < last; ++join_tab)
+    if (join_tab->is_first_inner())
+      return TRUE;
+  return FALSE;
+}
+
+static void initialize_shortcuts(JOIN *join) {
+  /*
+    Yes, it can actually happen that we get this far without JOIN::join_tab
+    being initialized!
+   */
+  if(!join->join_tab)
+    return;
+
+  /* 
+     We start searching for short-cuts on the third table, since the minimum
+     length of a short-cut must be 2 steps.
+   */
+  for (uint i= 2; i < join->tables; i++)
+  {
+    JOIN_TAB *join_tab= &join->join_tab[i];
+    // should be initialized from the start.
+    join_tab->join_shortcut= NULL;
+    /*
+      all_depenencies represents a list of dependency tables, ordered
+      by position within the plan. We consider them in this order.
+     */
+    table_map all_depenencies= get_dependencies_map(join_tab);
+    if (all_depenencies)
+    {
+      table_map mask= 1;
+      uint join_tab_pos= 0;
+      
+      do
+      {
+        table_map prospect_shortcut_map= all_depenencies & mask;
+        if (prospect_shortcut_map) {
+          JOIN_TAB *prospect_shortcut=
join->get_join_tab_by_map(prospect_shortcut_map);
+          /* Make sure the short-cut doesn't cross an outer join boundary. */
+          if (!crosses_outer_join(prospect_shortcut, join_tab))
+            
+            if (!join_tab->join_shortcut ||
+                join_tab->join_shortcut > prospect_shortcut)
+              /* In order to be a short-cut, it must be at least two tables away */
+              if (join_tab - prospect_shortcut > 1)
+              
+                join_tab->join_shortcut= prospect_shortcut;
+        }
+        mask <<=1;
+        ++join_tab_pos;
+      } while (mask <= all_depenencies && !join_tab->join_shortcut);
+    }
+  }
+}
+
 
 /****************************************************************************
   Make a join of all tables and write it on socket or to table
@@ -10865,13 +10938,25 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
     }
     join->thd->row_count= 0;
 
-    error= (*join_tab->read_first_record)(join_tab);
+    error= join_tab->do_read_first_record();
+    uint row_count_before= join->thd->row_count;
     rc= evaluate_join_record(join, join_tab, error, report_error);
+
+    if (join->thd->row_count == row_count_before &&
+        // try without this one when you have some test cases
+        join->last_join_tab_accessed &&
+        join->thd->variables.explain_shortcuts)
+      if (join->last_join_tab_accessed->join_shortcut == join_tab)
+        /* Receiving end of a short-cut */
+        ;
+      else if (join->last_join_tab_accessed->join_shortcut &&
+               join->last_join_tab_accessed->join_shortcut < join_tab)
+        return rc;
   }
 
   while (rc == NESTED_LOOP_OK)
   {
-    error= info->read_record(info);
+    error= join_tab->do_read_record();
     rc= evaluate_join_record(join, join_tab, error, report_error);
   }
 
@@ -10983,7 +11068,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
       enum enum_nested_loop_state rc;
       /* A match from join_tab is found for the current partial join. */
       rc= (*join_tab->next_select)(join, join_tab+1, 0);
-      if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
+        if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
         return rc;
       if (join->return_tab < join_tab)
         return NESTED_LOOP_OK;
@@ -15481,6 +15566,22 @@ void JOIN::clear()
   Send a description about what how the select will be done to stdout
 ****************************************************************************/
 
+
+// to do: make it so there's only one call to this
+static void append_shortcut_to_explain(JOIN* join, JOIN_TAB *tab, String *extra) 
+{
+  JOIN_TAB *next_tab= tab + 1;
+  if (next_tab >= (join->join_tab + join->tables)) // i.e. there is no next tab
+    return;
+  if (next_tab->join_shortcut && join->thd->variables.explain_shortcuts)
+  {
+    extra->append(STRING_WITH_LEN("; Shortcut("));
+    extra->append(next_tab->join_shortcut->table->alias);
+    extra->append(STRING_WITH_LEN(")")); 
+  }
+}
+
+
 static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
 			    bool distinct,const char *message)
 {
@@ -15811,6 +15912,7 @@ static void select_describe(JOIN *join, 
           extra.append(STRING_WITH_LEN("; Using where"));
         if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
           extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+        append_shortcut_to_explain(join, tab, &extra);
         /* Skip initial "; "*/
         const char *str= extra.ptr();
         uint32 len= extra.length();
@@ -15908,6 +16010,7 @@ static void select_describe(JOIN *join, 
         }
         if (i > 0 && tab[-1].next_select == sub_select_cache)
           extra.append(STRING_WITH_LEN("; Using join buffer"));
+        append_shortcut_to_explain(join, tab, &extra);
         
         /* Skip initial "; "*/
         const char *str= extra.ptr();
diff -Nrup a/sql/sql_select.h b/sql/sql_select.h
--- a/sql/sql_select.h	2007-08-05 07:11:26 +02:00
+++ b/sql/sql_select.h	2007-10-11 13:02:35 +02:00
@@ -138,6 +138,13 @@ typedef struct st_join_table {
   TABLE		*table;
   KEYUSE	*keyuse;			/* pointer to first used key */
   SQL_SELECT	*select;
+  /*
+    This field corresponds somewhat to a 'pushdown condition'. It contains
+    more than just those conditions pushed to the actual table, however.  
+
+    Note that for the first table in the plan there is usually a select_cond
+    attached but it is not initialized. 
+   */
   COND		*select_cond;
   QUICK_SELECT_I *quick;
   Item	       **on_expr_ref;   /* pointer to the associated on expression   */
@@ -148,6 +155,7 @@ typedef struct st_join_table {
   st_join_table *last_inner;    /* last table table for embedding outer join */
   st_join_table *first_upper;  /* first inner table for embedding outer join */
   st_join_table *first_unmatched; /* used for optimization purposes only     */
+  st_join_table *join_shortcut;
   
   /* Special content for EXPLAIN 'Extra' column or NULL if none */
   const char	*info;
@@ -157,6 +165,7 @@ typedef struct st_join_table {
   */
   uint          packed_info;
 
+  // to do: make the following 2 guys private so we can find all callers
   Read_record_func read_first_record;
   Next_select_func next_select;
   READ_RECORD	read_record;
@@ -213,6 +222,21 @@ typedef struct st_join_table {
             (select->quick->get_type() ==
              QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX));
   }
+  
+  int do_read_first_record();
+
+  int do_read_record() {
+    /*
+      let this be a lesson never to give function pointers
+      and struct members the same name.
+    */
+    return read_record.read_record(&read_record);
+  }
+
+  bool is_first_inner() {
+    return test(last_inner);
+  }
+
 } JOIN_TAB;
 
 enum_nested_loop_state sub_select_cache(JOIN *join, JOIN_TAB *join_tab, bool
@@ -270,6 +294,7 @@ public:
   JOIN_TAB *join_tab,**best_ref;
   JOIN_TAB **map2table;    // mapping between table indexes and JOIN_TABs
   JOIN_TAB *join_tab_save; // saved join_tab for subquery reexecution
+  JOIN_TAB *last_join_tab_accessed;
   TABLE    **table,**all_tables,*sort_by_table;
   uint	   tables,const_tables;
   uint	   send_group_parts;
@@ -516,6 +541,17 @@ public:
   {
     return (unit == &thd->lex->unit && (unit->fake_select_lex == 0
||
                                         select_lex == unit->fake_select_lex));
+  }
+  JOIN_TAB *get_join_tab_by_map(table_map map) {
+    if (!map)
+      return NULL;
+    uint index= 0;
+
+    while (!(map & 1)) {
+      map >>= 1;
+      ++index;
+    }
+    return map2table[index];
   }
 };