- Backport @@optimizer_switch support from 6.0

- Add support for setting it as a server commandline argument
- Add support for those switches:
  = no_index_merge
  = no_index_merge_union
  = no_index_merge_sort_union
  = no_index_merge_intersection

mysql-test/r/index_merge_myisam.result:
  Testcases for index_merge related @@optimizer_switch flags.
mysql-test/t/index_merge_myisam.test:
  Testcases for index_merge related @@optimizer_switch flags.
sql/set_var.cc:
  - Backport @@optimizer_switch support from 6.0
  - Add support for setting it as a server commandline argument
sql/sql_class.h:
  - Backport @@optimizer_switch support from 6.0
sql/sql_select.h:
  - Backport @@optimizer_switch support from 6.0

7 files changed, 158 insertions, 23 deletions

diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 3fba9248940..21741bc2eaf 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -523,6 +523,12 @@ protected:
 #define MODE_NO_ENGINE_SUBSTITUTION     (MODE_HIGH_NOT_PRECEDENCE*2)
 #define MODE_PAD_CHAR_TO_FULL_LENGTH    (ULL(1) << 31)
 
+/* @@optimizer_switch flags */
+#define OPTIMIZER_SWITCH_NO_INDEX_MERGE 1
+#define OPTIMIZER_SWITCH_NO_INDEX_MERGE_UNION 2
+#define OPTIMIZER_SWITCH_NO_INDEX_MERGE_SORT_UNION 4
+#define OPTIMIZER_SWITCH_NO_INDEX_MERGE_INTERSECT 8
+
 /*
   Replication uses 8 bytes to store SQL_MODE in the binary log. The day you
   use strictly more than 64 bits by adding one more define above, you should
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 88b923244e7..959a1e21fac 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -297,6 +297,24 @@ static const unsigned int sql_mode_names_len[]=
 TYPELIB sql_mode_typelib= { array_elements(sql_mode_names)-1,"",
 			    sql_mode_names,
                             (unsigned int *)sql_mode_names_len };
+
+static const char *optimizer_switch_names[]=
+{
+  "no_index_merge","no_index_merge_union","no_index_merge_sort_union", 
+  "no_index_merge_intersection", NullS
+};
+/* Corresponding defines are named OPTIMIZER_SWITCH_XXX */
+static const unsigned int optimizer_switch_names_len[]=
+{
+  sizeof("no_index_merge") - 1,
+  sizeof("no_index_merge_union") - 1,
+  sizeof("ni_index_merge_sort_union") - 1,
+  sizeof("no_index_merge_intersection") - 1
+};
+TYPELIB optimizer_switch_typelib= { array_elements(optimizer_switch_names)-1,"",
+                                    optimizer_switch_names,
+                                    (unsigned int *)optimizer_switch_names_len };
+
 static const char *tc_heuristic_recover_names[]=
 {
   "COMMIT", "ROLLBACK", NullS
@@ -362,6 +380,7 @@ static ulong max_used_connections;
 static ulong my_bind_addr;			/**< the address we bind to */
 static volatile ulong cached_thread_count= 0;
 static const char *sql_mode_str= "OFF";
+static const char *optimizer_switch_str="";
 static char *mysqld_user, *mysqld_chroot, *log_error_file_ptr;
 static char *opt_init_slave, *language_ptr, *opt_init_connect;
 static char *default_character_set_name;
@@ -5565,6 +5584,7 @@ enum options_mysqld
   OPT_SYSDATE_IS_NOW,
   OPT_OPTIMIZER_SEARCH_DEPTH,
   OPT_OPTIMIZER_PRUNE_LEVEL,
+  OPT_OPTIMIZER_SWITCH,
   OPT_UPDATABLE_VIEWS_WITH_LIMIT,
   OPT_SP_AUTOMATIC_PRIVILEGES,
   OPT_MAX_SP_RECURSION_DEPTH,
@@ -6717,6 +6737,10 @@ The minimum value for this variable is 4096.",
    (uchar**) &global_system_variables.optimizer_search_depth,
    (uchar**) &max_system_variables.optimizer_search_depth,
    0, GET_ULONG, OPT_ARG, MAX_TABLES+1, 0, MAX_TABLES+2, 0, 1, 0},
+  {"optimizer_switch", OPT_OPTIMIZER_SWITCH,
+   "optimizer_switch=option[,option[,option...]] where option can be one of: no_index_merge, no_index_merge_union, no_index_merge_sort_union, no_index_merge_intersection",
+   (uchar**) &optimizer_switch_str, (uchar**) &optimizer_switch_str, 0, GET_STR, REQUIRED_ARG, 0,
+   0, 0, 0, 0, 0},
   {"plugin_dir", OPT_PLUGIN_DIR,
    "Directory for plugins.",
    (uchar**) &opt_plugin_dir_ptr, (uchar**) &opt_plugin_dir_ptr, 0,
@@ -8206,6 +8230,13 @@ mysqld_get_one_option(int optid,
 						   sql_mode);
     break;
   }
+  case OPT_OPTIMIZER_SWITCH:
+  {
+    optimizer_switch_str= argument;
+    global_system_variables.optimizer_switch=
+      find_bit_type_or_exit(argument, &optimizer_switch_typelib, opt->name);
+    break;
+  }
   case OPT_ONE_THREAD:
     global_system_variables.thread_handling=
       SCHEDULER_ONE_THREAD_PER_CONNECTION;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 9f8f17eb4ba..aaa745bd413 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2386,7 +2386,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
           objects are not allowed so don't use ROR-intersection for
           table deletes.
         */
-        if ((thd->lex->sql_command != SQLCOM_DELETE))
+        if ((thd->lex->sql_command != SQLCOM_DELETE) && 
+             !optimizer_flag(thd, OPTIMIZER_SWITCH_NO_INDEX_MERGE))
         {
           /*
             Get best non-covering ROR-intersection plan and prepare data for
@@ -2410,25 +2411,28 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
       }
       else
       {
-        /* Try creating index_merge/ROR-union scan. */
-        SEL_IMERGE *imerge;
-        TABLE_READ_PLAN *best_conj_trp= NULL, *new_conj_trp;
-        LINT_INIT(new_conj_trp); /* no empty index_merge lists possible */
-        DBUG_PRINT("info",("No range reads possible,"
-                           " trying to construct index_merge"));
-        List_iterator_fast<SEL_IMERGE> it(tree->merges);
-        while ((imerge= it++))
+        if (!optimizer_flag(thd, OPTIMIZER_SWITCH_NO_INDEX_MERGE))
         {
-          new_conj_trp= get_best_disjunct_quick(&param, imerge, best_read_time);
-          if (new_conj_trp)
-            set_if_smaller(param.table->quick_condition_rows, 
-                           new_conj_trp->records);
-          if (!best_conj_trp || (new_conj_trp && new_conj_trp->read_cost <
-                                 best_conj_trp->read_cost))
-            best_conj_trp= new_conj_trp;
+          /* Try creating index_merge/ROR-union scan. */
+          SEL_IMERGE *imerge;
+          TABLE_READ_PLAN *best_conj_trp= NULL, *new_conj_trp;
+          LINT_INIT(new_conj_trp); /* no empty index_merge lists possible */
+          DBUG_PRINT("info",("No range reads possible,"
+                             " trying to construct index_merge"));
+          List_iterator_fast<SEL_IMERGE> it(tree->merges);
+          while ((imerge= it++))
+          {
+            new_conj_trp= get_best_disjunct_quick(&param, imerge, best_read_time);
+            if (new_conj_trp)
+              set_if_smaller(param.table->quick_condition_rows, 
+                             new_conj_trp->records);
+            if (!best_conj_trp || (new_conj_trp && new_conj_trp->read_cost <
+                                   best_conj_trp->read_cost))
+              best_conj_trp= new_conj_trp;
+          }
+          if (best_conj_trp)
+            best_trp= best_conj_trp;
         }
-        if (best_conj_trp)
-          best_trp= best_conj_trp;
       }
     }
 
@@ -3767,11 +3771,19 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge,
                         "full table scan, bailing out"));
     DBUG_RETURN(NULL);
   }
-  if (all_scans_rors)
+
+  /* 
+    If all scans happen to be ROR, proceed to generate a ROR-union plan (it's 
+    guaranteed to be cheaper than non-ROR union), unless ROR-unions are
+    disabled in @@optimizer_switch
+  */
+  if (all_scans_rors && 
+      !optimizer_flag(param->thd, OPTIMIZER_SWITCH_NO_INDEX_MERGE_UNION))
   {
     roru_read_plans= (TABLE_READ_PLAN**)range_scans;
     goto skip_to_ror_scan;
   }
+
   if (cpk_scan)
   {
     /*
@@ -3785,8 +3797,11 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge,
   imerge_cost += get_sweep_read_cost(param, non_cpk_scan_records);
   DBUG_PRINT("info",("index_merge cost with rowid-to-row scan: %g",
                      imerge_cost));
-  if (imerge_cost > read_time)
+  if (imerge_cost > read_time || 
+      optimizer_flag(param->thd, OPTIMIZER_SWITCH_NO_INDEX_MERGE_SORT_UNION))
+  {
     goto build_ror_index_merge;
+  }
 
   /* Add Unique operations cost */
   unique_calc_buff_size=
@@ -3822,7 +3837,9 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge,
   }
 
 build_ror_index_merge:
-  if (!all_scans_ror_able || param->thd->lex->sql_command == SQLCOM_DELETE)
+  if (!all_scans_ror_able || 
+      param->thd->lex->sql_command == SQLCOM_DELETE ||
+      optimizer_flag(param->thd, OPTIMIZER_SWITCH_NO_INDEX_MERGE_UNION))
     DBUG_RETURN(imerge_trp);
 
   /* Ok, it is possible to build a ROR-union, try it. */
@@ -4495,7 +4512,8 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree,
   double min_cost= DBL_MAX;
   DBUG_ENTER("get_best_ror_intersect");
 
-  if ((tree->n_ror_scans < 2) || !param->table->file->stats.records)
+  if ((tree->n_ror_scans < 2) || !param->table->file->stats.records ||
+      optimizer_flag(param->thd, OPTIMIZER_SWITCH_NO_INDEX_MERGE_INTERSECT))
     DBUG_RETURN(NULL);
 
   /*
@@ -4685,6 +4703,9 @@ TRP_ROR_INTERSECT *get_best_covering_ror_intersect(PARAM *param,
   ROR_SCAN_INFO **ror_scans_end= tree->ror_scans_end;
   DBUG_ENTER("get_best_covering_ror_intersect");
 
+  if (optimizer_flag(param->thd, OPTIMIZER_SWITCH_NO_INDEX_MERGE_INTERSECT))
+    DBUG_RETURN(NULL);
+
   for (ROR_SCAN_INFO **scan= tree->ror_scans; scan != ror_scans_end; ++scan)
     (*scan)->key_components=
       param->table->key_info[(*scan)->keynr].key_parts;
diff --git a/sql/set_var.cc b/sql/set_var.cc
index f14068fcfcb..96eaa3c9c2b 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -468,6 +468,8 @@ static sys_var_thd_ulong        sys_optimizer_prune_level(&vars, "optimizer_prun
                                                   &SV::optimizer_prune_level);
 static sys_var_thd_ulong        sys_optimizer_search_depth(&vars, "optimizer_search_depth",
                                                    &SV::optimizer_search_depth);
+static sys_var_thd_optimizer_switch   sys_optimizer_switch(&vars, "optimizer_switch",
+                                     &SV::optimizer_switch);
 static sys_var_const            sys_pid_file(&vars, "pid_file",
                                              OPT_GLOBAL, SHOW_CHAR,
                                              (uchar*) pidfile_name);
@@ -3837,6 +3839,56 @@ ulong fix_sql_mode(ulong sql_mode)
 }
 
 
+bool
+sys_var_thd_optimizer_switch::
+symbolic_mode_representation(THD *thd, ulonglong val, LEX_STRING *rep)
+{
+  char buff[STRING_BUFFER_USUAL_SIZE*8];
+  String tmp(buff, sizeof(buff), &my_charset_latin1);
+
+  tmp.length(0);
+
+  for (uint i= 0; val; val>>= 1, i++)
+  {
+    if (val & 1)
+    {
+      tmp.append(optimizer_switch_typelib.type_names[i],
+                 optimizer_switch_typelib.type_lengths[i]);
+      tmp.append(',');
+    }
+  }
+
+  if (tmp.length())
+    tmp.length(tmp.length() - 1); /* trim the trailing comma */
+
+  rep->str= thd->strmake(tmp.ptr(), tmp.length());
+
+  rep->length= rep->str ? tmp.length() : 0;
+
+  return rep->length != tmp.length();
+}
+
+
+uchar *sys_var_thd_optimizer_switch::value_ptr(THD *thd, enum_var_type type,
+				               LEX_STRING *base)
+{
+  LEX_STRING opts;
+  ulonglong val= ((type == OPT_GLOBAL) ? global_system_variables.*offset :
+                  thd->variables.*offset);
+  (void) symbolic_mode_representation(thd, val, &opts);
+  return (uchar *) opts.str;
+}
+
+
+void sys_var_thd_optimizer_switch::set_default(THD *thd, enum_var_type type)
+{
+  if (type == OPT_GLOBAL)
+    global_system_variables.*offset= 0;
+  else
+    thd->variables.*offset= global_system_variables.*offset;
+}
+
+
 /****************************************************************************
   Named list handling
 ****************************************************************************/
diff --git a/sql/set_var.h b/sql/set_var.h
index b6c67d1ab4a..f07facde0b0 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -31,7 +31,7 @@ typedef struct system_variables SV;
 typedef struct my_locale_st MY_LOCALE;
 
 extern TYPELIB bool_typelib, delay_key_write_typelib, sql_mode_typelib,
-  slave_exec_mode_typelib;
+  optimizer_switch_typelib, slave_exec_mode_typelib;
 
 typedef int (*sys_check_func)(THD *,  set_var *);
 typedef bool (*sys_update_func)(THD *, set_var *);
@@ -532,6 +532,23 @@ public:
 };
 
 
+class sys_var_thd_optimizer_switch :public sys_var_thd_enum
+{
+public:
+  sys_var_thd_optimizer_switch(sys_var_chain *chain, const char *name_arg, 
+                               ulong SV::*offset_arg)
+    :sys_var_thd_enum(chain, name_arg, offset_arg, &optimizer_switch_typelib)
+  {}
+  bool check(THD *thd, set_var *var)
+  {
+    return check_set(thd, var, enum_names);
+  }
+  void set_default(THD *thd, enum_var_type type);
+  uchar *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+  static bool symbolic_mode_representation(THD *thd, ulonglong sql_mode,
+                                           LEX_STRING *rep);
+};
+
 extern void fix_sql_mode_var(THD *thd, enum_var_type type);
 
 class sys_var_thd_sql_mode :public sys_var_thd_enum
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 3439e5b4f74..dd285eb0645 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -321,6 +321,8 @@ struct system_variables
   ulong net_write_timeout;
   ulong optimizer_prune_level;
   ulong optimizer_search_depth;
+  /* A bitmap for switching optimizations on/off */
+  ulong optimizer_switch;
   ulong preload_buff_size;
   ulong profiling_history_size;
   ulong query_cache_type;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 7d794b71f4d..353c2b1d610 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -729,3 +729,9 @@ bool error_if_full_join(JOIN *join);
 int report_error(TABLE *table, int error);
 int safe_index_read(JOIN_TAB *tab);
 COND *remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value);
+
+inline bool optimizer_flag(THD *thd, uint flag)
+{ 
+  return (thd->variables.optimizer_switch & flag);
+}
+