32 files changed, 443 insertions, 127 deletions

diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc
index f04c33ce90b..36d5da94b11 100644
--- a/sql/ha_partition.cc
+++ b/sql/ha_partition.cc
@@ -4069,6 +4069,8 @@ int ha_partition::rnd_next(uchar *buf)
   int result= HA_ERR_END_OF_FILE;
   uint part_id= m_part_spec.start_part;
   DBUG_ENTER("ha_partition::rnd_next");
+
+  /* upper level will increment this once again at end of call */
   decrement_statistics(&SSV::ha_read_rnd_next_count);
 
   if (NO_CURRENT_PART_ID == part_id)
diff --git a/sql/item.cc b/sql/item.cc
index 02cbe9b7f49..de4716350c2 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5378,7 +5378,8 @@ bool Item_field::set_no_const_sub(uchar *arg)
 
 Item *Item_field::replace_equal_field(uchar *arg)
 {
-  if (item_equal && item_equal == (Item_equal *) arg)
+  REPLACE_EQUAL_FIELD_ARG* param= (REPLACE_EQUAL_FIELD_ARG*)arg;
+  if (item_equal && item_equal == param->item_equal)
   {
     Item *const_item= item_equal->get_const();
     if (const_item)
@@ -5387,7 +5388,8 @@ Item *Item_field::replace_equal_field(uchar *arg)
         return this;
       return const_item;
     }
-    Item_field *subst= (Item_field *)(item_equal->get_first(this));
+    Item_field *subst= 
+      (Item_field *)(item_equal->get_first(param->context_tab, this));
     if (subst)
       subst= (Item_field *) (subst->real_item());
     if (subst && !field->eq(subst->field))
@@ -9467,13 +9469,13 @@ void Item_type_holder::get_full_info(Item *item)
     DBUG_ASSERT((enum_set_typelib &&
                  get_real_type(item) == MYSQL_TYPE_NULL) ||
                 (!enum_set_typelib &&
-                 item->type() == Item::FIELD_ITEM &&
-                 (get_real_type(item) == MYSQL_TYPE_ENUM ||
-                  get_real_type(item) == MYSQL_TYPE_SET) &&
-                 ((Field_enum*)((Item_field *) item)->field)->typelib));
+                 item->real_item()->type() == Item::FIELD_ITEM &&
+                 (get_real_type(item->real_item()) == MYSQL_TYPE_ENUM ||
+                  get_real_type(item->real_item()) == MYSQL_TYPE_SET) &&
+                 ((Field_enum*)((Item_field *) item->real_item())->field)->typelib));
     if (!enum_set_typelib)
     {
-      enum_set_typelib= ((Field_enum*)((Item_field *) item)->field)->typelib;
+      enum_set_typelib= ((Field_enum*)((Item_field *) item->real_item())->field)->typelib;
     }
   }
 }
@@ -9558,18 +9560,22 @@ void Item_ref::update_used_tables()
 }
 
 
-table_map Item_direct_view_ref::used_tables() const		
+table_map Item_direct_view_ref::used_tables() const
 {
-  return get_depended_from() ? 
+  return get_depended_from() ?
          OUTER_REF_TABLE_BIT :
-         (view->merged ? (*ref)->used_tables() : view->table->map); 
+         ((view->merged || !view->table) ?
+          (*ref)->used_tables() :
+          view->table->map);
 }
 
-table_map Item_direct_view_ref::not_null_tables() const		
+table_map Item_direct_view_ref::not_null_tables() const
 {
-  return get_depended_from() ? 
+  return get_depended_from() ?
          0 :
-         (view->merged ? (*ref)->not_null_tables() : view->table->map); 
+         ((view->merged || !view->table) ?
+          (*ref)->not_null_tables() :
+          view->table->map);
 }
 
 /*
@@ -9583,6 +9589,8 @@ table_map Item_ref_null_helper::used_tables() const
 }
 
 
+#ifndef DBUG_OFF
+
 /* Debugger help function */
 static char dbug_item_print_buf[256];
 
@@ -9599,6 +9607,9 @@ const char *dbug_print_item(Item *item)
   else
     return "Couldn't fit into buffer";
 }
+
+#endif /*DBUG_OFF*/
+
 /*****************************************************************************
 ** Instantiate templates
 *****************************************************************************/
diff --git a/sql/item.h b/sql/item.h
index 4732a1a4657..6c0d3138b62 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -471,6 +471,16 @@ typedef enum monotonicity_info
 class sp_rcontext;
 
 
+class Item_equal;
+
+struct st_join_table* const NO_PARTICULAR_TAB= (struct st_join_table*)0x1;
+
+typedef struct replace_equal_field_arg 
+{
+  Item_equal *item_equal;
+  struct st_join_table *context_tab;
+} REPLACE_EQUAL_FIELD_ARG;
+
 class Settable_routine_parameter
 {
 public:
@@ -1283,6 +1293,7 @@ public:
 
   virtual Item *equal_fields_propagator(uchar * arg) { return this; }
   virtual bool set_no_const_sub(uchar *arg) { return FALSE; }
+  /* arg points to REPLACE_EQUAL_FIELD_ARG object */
   virtual Item *replace_equal_field(uchar * arg) { return this; }
   /*
     Check if an expression value has allowed arguments, like DATE/DATETIME
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index b3954c55394..4ea5e9534a8 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -5804,7 +5804,7 @@ longlong Item_equal::val_int()
 
 void Item_equal::fix_length_and_dec()
 {
-  Item *item= get_first(NULL);
+  Item *item= get_first(NO_PARTICULAR_TAB, NULL);
   eval_item= cmp_item::get_comparator(item->cmp_type(), item,
                                       item->collation.collation);
 }
@@ -5904,7 +5904,7 @@ CHARSET_INFO *Item_equal::compare_collation()
   @retval 0 if no field found.
 */
 
-Item* Item_equal::get_first(Item *field_item)
+Item* Item_equal::get_first(JOIN_TAB *context, Item *field_item)
 {
   Item_equal_fields_iterator it(*this);
   Item *item;
@@ -5932,18 +5932,24 @@ Item* Item_equal::get_first(Item *field_item)
     in presense of SJM nests.
   */
 
-  TABLE_LIST *emb_nest= field->table->pos_in_table_list->embedding;
+  TABLE_LIST *emb_nest;
+  if (context != NO_PARTICULAR_TAB)
+    emb_nest= context->emb_sj_nest;
+  else
+    emb_nest= field->table->pos_in_table_list->embedding;
 
   if (emb_nest && emb_nest->sj_mat_info && emb_nest->sj_mat_info->is_used)
   {
     /*
-      It's a field from an materialized semi-join. We can substitute it only
-      for a field from the same semi-join. Find the first of such items.
+      It's a field from an materialized semi-join. We can substitute it for
+       - a constant item 
+       - a field from the same semi-join
+       Find the first of such items:
     */
-
     while ((item= it++))
     {
-      if (it.get_curr_field()->table->pos_in_table_list->embedding == emb_nest)
+      if (item->const_item() || 
+          it.get_curr_field()->table->pos_in_table_list->embedding == emb_nest)
       {
         /*
           If we found given field then return NULL to avoid unnecessary
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 7b2193b1acd..384c91d5d2e 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1705,7 +1705,7 @@ public:
   /** Add a non-constant item to the multiple equality */
   void add(Item *f) { equal_items.push_back(f); }
   bool contains(Field *field);
-  Item* get_first(Item *field);
+  Item* get_first(struct st_join_table *context, Item *field);
   /** Get number of field items / references to field items in this object */   
   uint n_field_items() { return equal_items.elements-test(with_const); }
   void merge(Item_equal *item);
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 5f78cdf167b..50625286348 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1271,7 +1271,8 @@ void Item_exists_subselect::fix_length_and_dec()
     We need only 1 row to determine existence (i.e. any EXISTS that is not
     an IN always requires LIMIT 1)
   */
-  unit->global_parameters->select_limit= new Item_int((int32) 1);
+  thd->change_item_tree(&unit->global_parameters->select_limit,
+                        new Item_int((int32) 1));
   DBUG_PRINT("info", ("Set limit to 1"));
   DBUG_VOID_RETURN;
 }
@@ -1597,7 +1598,6 @@ Item_in_subselect::single_value_transformer(JOIN *join)
                               (Item**)optimizer->get_cache(),
 			      (char *)"<no matter>",
 			      (char *)in_left_expr_name);
-
   }
 
   DBUG_RETURN(false);
@@ -1684,10 +1684,6 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join)
 
     DBUG_EXECUTE("where",
                  print_where(item, "rewrite with MIN/MAX", QT_ORDINARY););
-    if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY)
-    {
-      select_lex->set_non_agg_field_used(false);
-    }
 
     save_allow_sum_func= thd->lex->allow_sum_func;
     thd->lex->allow_sum_func|= 1 << thd->lex->current_select->nest_level;
@@ -2244,7 +2240,12 @@ bool Item_in_subselect::create_in_to_exists_cond(JOIN *join_arg)
   /*
     The IN=>EXISTS transformation makes non-correlated subqueries correlated.
   */
-  join_arg->select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
+  if (!left_expr->const_item() || left_expr->is_expensive())
+  {
+    join_arg->select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
+    join_arg->select_lex->master_unit()->uncacheable|= 
+                                         UNCACHEABLE_DEPENDENT_INJECTED;
+  }
   /*
     The uncacheable property controls a number of actions, e.g. whether to
     save/restore (via init_save_join_tab/restore_tmp) the original JOIN for
@@ -2510,6 +2511,7 @@ bool Item_in_subselect::fix_fields(THD *thd_arg, Item **ref)
       left_expr && !left_expr->fixed &&
       left_expr->fix_fields(thd_arg, &left_expr))
     return TRUE;
+  else
   if (Item_subselect::fix_fields(thd_arg, ref))
     return TRUE;
   fixed= TRUE;
@@ -3159,6 +3161,8 @@ bool subselect_uniquesubquery_engine::copy_ref_key()
 
   for (store_key **copy= tab->ref.key_copy ; *copy ; copy++)
   {
+    if ((*copy)->store_key_is_const())
+      continue;
     tab->ref.key_err= (*copy)->copy();
 
     /*
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 6e115854f17..afe4f34bd05 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1129,13 +1129,12 @@ Item_sum_num::fix_fields(THD *thd, Item **ref)
     return TRUE;
 
   decimals=0;
-  maybe_null=0;
+  maybe_null= sum_func() != COUNT_FUNC;
   for (uint i=0 ; i < arg_count ; i++)
   {
     if (args[i]->fix_fields(thd, args + i) || args[i]->check_cols(1))
       return TRUE;
     set_if_bigger(decimals, args[i]->decimals);
-    maybe_null |= args[i]->maybe_null;
   }
   result_field=0;
   max_length=float_length(decimals);
diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc
index d8dc45d38c7..29e7faa630d 100644
--- a/sql/multi_range_read.cc
+++ b/sql/multi_range_read.cc
@@ -482,6 +482,10 @@ void Mrr_ordered_index_reader::resume_read()
 
 /**
   Fill the buffer with (lookup_tuple, range_id) pairs and sort
+
+  @return 
+    0                   OK, the buffer is non-empty and sorted
+    HA_ERR_END_OF_FILE  Source exhausted, the buffer is empty.
 */
 
 int Mrr_ordered_index_reader::refill_buffer(bool initial)
@@ -518,6 +522,13 @@ int Mrr_ordered_index_reader::refill_buffer(bool initial)
   if (source_exhausted && key_buffer->is_empty())
     DBUG_RETURN(HA_ERR_END_OF_FILE);
 
+  if (!initial)
+  {
+    /* This is a non-initial buffer fill and we've got a non-empty buffer */
+    THD *thd= current_thd;
+    status_var_increment(thd->status_var.ha_mrr_extra_key_sorts);
+  }
+
   key_buffer->sort((key_buffer->type() == Lifo_buffer::FORWARD)? 
                      (qsort2_cmp)Mrr_ordered_index_reader::compare_keys_reverse : 
                      (qsort2_cmp)Mrr_ordered_index_reader::compare_keys, 
@@ -592,6 +603,7 @@ int Mrr_ordered_rndpos_reader::init(handler *h_arg,
 int Mrr_ordered_rndpos_reader::refill_buffer(bool initial)
 {
   int res;
+  bool first_call= initial;
   DBUG_ENTER("Mrr_ordered_rndpos_reader::refill_buffer");
 
   if (index_reader_exhausted)
@@ -609,6 +621,14 @@ int Mrr_ordered_rndpos_reader::refill_buffer(bool initial)
     initial= FALSE;
     index_reader_needs_refill= FALSE;
   }
+
+  if (!first_call && !index_reader_exhausted)
+  {
+    /* Ok, this was a successful buffer refill operation */
+    THD *thd= current_thd;
+    status_var_increment(thd->status_var.ha_mrr_extra_rowid_sorts);
+  }
+
   DBUG_RETURN(res);
 }
 
@@ -841,8 +861,7 @@ int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF *seq_funcs,
     strategy= disk_strategy= &reader_factory.ordered_rndpos_reader;
   }
 
-  if (is_mrr_assoc)
-    status_var_increment(thd->status_var.ha_multi_range_read_init_count);
+  status_var_increment(thd->status_var.ha_multi_range_read_init_count);
 
   full_buf= buf->buffer;
   full_buf_end= buf->buffer_end;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index cc0ee652b16..bea3d1ef4a4 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -1883,7 +1883,6 @@ static void clean_up_mutexes()
   mysql_mutex_destroy(&LOCK_delayed_insert);
   mysql_mutex_destroy(&LOCK_delayed_status);
   mysql_mutex_destroy(&LOCK_delayed_create);
-  mysql_mutex_destroy(&LOCK_manager);
   mysql_mutex_destroy(&LOCK_crypt);
   mysql_mutex_destroy(&LOCK_user_conn);
   mysql_mutex_destroy(&LOCK_connection_count);
@@ -1914,7 +1913,6 @@ static void clean_up_mutexes()
   mysql_cond_destroy(&COND_thread_count);
   mysql_cond_destroy(&COND_thread_cache);
   mysql_cond_destroy(&COND_flush_thread_cache);
-  mysql_cond_destroy(&COND_manager);
   mysql_mutex_destroy(&LOCK_server_started);
   mysql_cond_destroy(&COND_server_started);
   mysql_mutex_destroy(&LOCK_prepare_ordered);
@@ -3776,8 +3774,6 @@ static int init_thread_environment()
                    &LOCK_delayed_status, MY_MUTEX_INIT_FAST);
   mysql_mutex_init(key_LOCK_delayed_create,
                    &LOCK_delayed_create, MY_MUTEX_INIT_SLOW);
-  mysql_mutex_init(key_LOCK_manager,
-                   &LOCK_manager, MY_MUTEX_INIT_FAST);
   mysql_mutex_init(key_LOCK_crypt, &LOCK_crypt, MY_MUTEX_INIT_FAST);
   mysql_mutex_init(key_LOCK_user_conn, &LOCK_user_conn, MY_MUTEX_INIT_FAST);
   mysql_mutex_init(key_LOCK_active_mi, &LOCK_active_mi, MY_MUTEX_INIT_FAST);
@@ -3826,7 +3822,6 @@ static int init_thread_environment()
   mysql_cond_init(key_COND_thread_count, &COND_thread_count, NULL);
   mysql_cond_init(key_COND_thread_cache, &COND_thread_cache, NULL);
   mysql_cond_init(key_COND_flush_thread_cache, &COND_flush_thread_cache, NULL);
-  mysql_cond_init(key_COND_manager, &COND_manager, NULL);
 #ifdef HAVE_REPLICATION
   mysql_mutex_init(key_LOCK_rpl_status, &LOCK_rpl_status, MY_MUTEX_INIT_FAST);
   mysql_cond_init(key_COND_rpl_status, &COND_rpl_status, NULL);
@@ -6874,6 +6869,12 @@ SHOW_VAR status_vars[]= {
   {"Handler_commit",           (char*) offsetof(STATUS_VAR, ha_commit_count), SHOW_LONG_STATUS},
   {"Handler_delete",           (char*) offsetof(STATUS_VAR, ha_delete_count), SHOW_LONG_STATUS},
   {"Handler_discover",         (char*) offsetof(STATUS_VAR, ha_discover_count), SHOW_LONG_STATUS},
+#if 0
+  /* Made 3 counters below temporarily invisible until we agree upon their names */
+  {"Handler_mrr_extra_key_sorts",   (char*) offsetof(STATUS_VAR, ha_mrr_extra_key_sorts), SHOW_LONG_STATUS},
+  {"Handler_mrr_extra_rowid_sorts", (char*) offsetof(STATUS_VAR, ha_mrr_extra_rowid_sorts), SHOW_LONG_STATUS},
+  {"Handler_mrr_init",         (char*) offsetof(STATUS_VAR, ha_multi_range_read_init_count),  SHOW_LONG_STATUS},
+#endif
   {"Handler_prepare",          (char*) offsetof(STATUS_VAR, ha_prepare_count),  SHOW_LONG_STATUS},
   {"Handler_read_first",       (char*) offsetof(STATUS_VAR, ha_read_first_count), SHOW_LONG_STATUS},
   {"Handler_read_key",         (char*) offsetof(STATUS_VAR, ha_read_key_count), SHOW_LONG_STATUS},
@@ -6881,14 +6882,15 @@ SHOW_VAR status_vars[]= {
   {"Handler_read_next",        (char*) offsetof(STATUS_VAR, ha_read_next_count), SHOW_LONG_STATUS},
   {"Handler_read_prev",        (char*) offsetof(STATUS_VAR, ha_read_prev_count), SHOW_LONG_STATUS},
   {"Handler_read_rnd",         (char*) offsetof(STATUS_VAR, ha_read_rnd_count), SHOW_LONG_STATUS},
+  {"Handler_read_rnd_deleted", (char*) offsetof(STATUS_VAR, ha_read_rnd_deleted_count), SHOW_LONG_STATUS},
   {"Handler_read_rnd_next",    (char*) offsetof(STATUS_VAR, ha_read_rnd_next_count), SHOW_LONG_STATUS},
   {"Handler_rollback",         (char*) offsetof(STATUS_VAR, ha_rollback_count), SHOW_LONG_STATUS},
   {"Handler_savepoint",        (char*) offsetof(STATUS_VAR, ha_savepoint_count), SHOW_LONG_STATUS},
   {"Handler_savepoint_rollback",(char*) offsetof(STATUS_VAR, ha_savepoint_rollback_count), SHOW_LONG_STATUS},
-  {"Handler_update",           (char*) offsetof(STATUS_VAR, ha_update_count), SHOW_LONG_STATUS},
-  {"Handler_write",            (char*) offsetof(STATUS_VAR, ha_write_count), SHOW_LONG_STATUS},
   {"Handler_tmp_update",       (char*) offsetof(STATUS_VAR, ha_tmp_update_count), SHOW_LONG_STATUS},
   {"Handler_tmp_write",        (char*) offsetof(STATUS_VAR, ha_tmp_write_count), SHOW_LONG_STATUS},
+  {"Handler_update",           (char*) offsetof(STATUS_VAR, ha_update_count), SHOW_LONG_STATUS},
+  {"Handler_write",            (char*) offsetof(STATUS_VAR, ha_write_count), SHOW_LONG_STATUS},
   {"Key",                      (char*) &show_default_keycache, SHOW_FUNC},
   {"Last_query_cost",          (char*) offsetof(STATUS_VAR, last_query_cost), SHOW_DOUBLE_STATUS},
   {"Max_used_connections",     (char*) &max_used_connections,  SHOW_LONG},
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 3b1991d1686..6376b1ac33f 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -4080,7 +4080,8 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
 {
   uint i;
   DBUG_ENTER("setup_semijoin_dups_elimination");
-
+  
+  join->complex_firstmatch_tables= table_map(0);
 
   POSITION *pos= join->best_positions + join->const_tables;
   for (i= join->const_tables ; i < join->top_join_tab_count; )
@@ -4099,6 +4100,11 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
       {
         /* We jump from the last table to the first one */
         tab->loosescan_match_tab= tab + pos->n_sj_tables - 1;
+        
+        /* LooseScan requires records to be produced in order */
+        if (tab->select && tab->select->quick)
+          tab->select->quick->need_sorted_output();
+
         for (uint j= i; j < i + pos->n_sj_tables; j++)
           join->join_tab[j].inside_loosescan_range= TRUE;
 
@@ -4108,6 +4114,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
         for (uint kp=0; kp < pos->loosescan_picker.loosescan_parts; kp++)
           keylen += tab->table->key_info[keyno].key_part[kp].store_length;
 
+        tab->loosescan_key= keyno;
         tab->loosescan_key_len= keylen;
         if (pos->n_sj_tables > 1) 
           tab[pos->n_sj_tables - 1].do_firstmatch= tab;
@@ -4163,16 +4170,46 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
       }
       case SJ_OPT_FIRST_MATCH:
       {
-        JOIN_TAB *j, *jump_to= tab-1;
+        JOIN_TAB *j;
+        JOIN_TAB *jump_to= tab-1;
+
+        bool complex_range= FALSE;
+        table_map tables_in_range= table_map(0);
+
         for (j= tab; j != tab + pos->n_sj_tables; j++)
         {
-          /*
-            NOTE: this loop probably doesn't do the right thing for the case 
-            where FirstMatch's duplicate-generating range is interleaved with
-            "unrelated" tables (as specified in WL#3750, section 2.2).
-          */
+          tables_in_range |= j->table->map;
           if (!j->emb_sj_nest)
-            jump_to= tab;
+          {
+            /* 
+              Got a table that's not within any semi-join nest. This is a case
+              like this:
+
+              SELECT * FROM ot1, nt1 WHERE ot1.col IN (SELECT expr FROM it1, it2)
+
+              with a join order of 
+
+                   +----- FirstMatch range ----+
+                   |                           |
+              ot1 it1 nt1 nt2 it2 it3 ...
+                   |   ^
+                   |   +-------- 'j' points here
+                   +------------- SJ_OPT_FIRST_MATCH was set for this table as
+                                  it's the first one that produces duplicates
+              
+            */
+            DBUG_ASSERT(j != tab);  /* table ntX must have an itX before it */
+
+            /* 
+              If the table right before us is an inner table (like it1 in the
+              picture), it should be set to jump back to previous outer-table
+            */
+            if (j[-1].emb_sj_nest)
+              j[-1].do_firstmatch= jump_to;
+
+            jump_to= j; /* Jump back to us */
+            complex_range= TRUE;
+          }
           else
           {
             j->first_sj_inner_tab= tab;
@@ -4182,6 +4219,9 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
         j[-1].do_firstmatch= jump_to;
         i+= pos->n_sj_tables;
         pos+= pos->n_sj_tables;
+
+        if (complex_range)
+          join->complex_firstmatch_tables|= tables_in_range;
         break;
       }
       case SJ_OPT_NONE:
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index cda35895a3d..7087a85d13a 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -627,7 +627,12 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo,
   if (!cond)
     DBUG_RETURN(TRUE);
   Field *field= field_part->field;
-  if (!(cond->used_tables() & field->table->map))
+  if (cond->used_tables() & OUTER_REF_TABLE_BIT)
+  { 
+    DBUG_RETURN(FALSE);
+  } 
+  if (!(cond->used_tables() & field->table->map) &&
+      test(cond->used_tables() & ~PSEUDO_TABLE_BITS))
   {
     /* Condition doesn't restrict the used table */
     DBUG_RETURN(!cond->const_item());
diff --git a/sql/slave.cc b/sql/slave.cc
index 98b75bc6dad..2b73cad1d7b 100644
--- a/sql/slave.cc
+++ b/sql/slave.cc
@@ -2927,9 +2927,8 @@ pthread_handler_t handle_slave_io(void *arg)
   if (init_slave_thread(thd, SLAVE_THD_IO))
   {
     mysql_cond_broadcast(&mi->start_cond);
-    mysql_mutex_unlock(&mi->run_lock);
     sql_print_error("Failed during slave I/O thread initialization");
-    goto err;
+    goto err_during_init;
   }
   mysql_mutex_lock(&LOCK_thread_count);
   threads.append(thd);
@@ -3232,6 +3231,7 @@ err:
   thd_proc_info(thd, "Waiting for slave mutex on exit");
   mysql_mutex_lock(&mi->run_lock);
 
+err_during_init:
   /* Forget the relay log's format */
   delete mi->rli.relay_log.description_event_for_queue;
   mi->rli.relay_log.description_event_for_queue= 0;
@@ -3357,10 +3357,9 @@ pthread_handler_t handle_slave_sql(void *arg)
       will be stuck if we fail here
     */
     mysql_cond_broadcast(&rli->start_cond);
-    mysql_mutex_unlock(&rli->run_lock);
     rli->report(ERROR_LEVEL, ER_SLAVE_FATAL_ERROR, 
                 "Failed during slave thread initialization");
-    goto err;
+    goto err_during_init;
   }
   thd->init_for_queries();
   thd->temporary_tables = rli->save_temporary_tables; // restore temp tables
@@ -3613,6 +3612,7 @@ the slave SQL thread with \"SLAVE START\". We stopped at log \
   thd->reset_db(NULL, 0);
   thd_proc_info(thd, "Waiting for slave mutex on exit");
   mysql_mutex_lock(&rli->run_lock);
+err_during_init:
   /* We need data_lock, at least to wake up any waiting master_pos_wait() */
   mysql_mutex_lock(&rli->data_lock);
   DBUG_ASSERT(rli->slave_running == 1); // tracking buffer overrun
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 92747066121..15c3999be54 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -8399,22 +8399,9 @@ static ulong parse_client_handshake_packet(MPVIO_EXT *mpvio,
   ulong client_capabilities= uint2korr(net->read_pos);
   if (client_capabilities & CLIENT_PROTOCOL_41)
   {
-    if (pkt_len < 32)
+    if (pkt_len < 4)
       return packet_error;
     client_capabilities|= ((ulong) uint2korr(net->read_pos+2)) << 16;
-    thd->max_client_packet_length= uint4korr(net->read_pos+4);
-    DBUG_PRINT("info", ("client_character_set: %d", (uint) net->read_pos[8]));
-    if (thd_init_client_charset(thd, (uint) net->read_pos[8]))
-      return packet_error;
-    thd->update_charset();
-    end= (char*) net->read_pos + 32;
-  }
-  else
-  {
-    if (pkt_len < 5)
-      return packet_error;
-    thd->max_client_packet_length= uint3korr(net->read_pos+2);
-    end= (char*) net->read_pos+5;
   }
 
   /* Disable those bits which are not supported by the client. */
@@ -8446,6 +8433,28 @@ static ulong parse_client_handshake_packet(MPVIO_EXT *mpvio,
     }
   }
 
+  if (client_capabilities & CLIENT_PROTOCOL_41)
+  {
+    if (pkt_len < 32)
+      return packet_error;
+    thd->max_client_packet_length= uint4korr(net->read_pos+4);
+    DBUG_PRINT("info", ("client_character_set: %d", (uint) net->read_pos[8]));
+    if (thd_init_client_charset(thd, (uint) net->read_pos[8]))
+      return packet_error;
+    thd->update_charset();
+    end= (char*) net->read_pos+32;
+  }
+  else
+  {
+    if (pkt_len < 5)
+      return packet_error;
+    thd->max_client_packet_length= uint3korr(net->read_pos+2);
+    end= (char*) net->read_pos+5;
+  }
+
+  if (end >= (char*) net->read_pos+ pkt_len +2)
+    return packet_error;
+
   if (thd->client_capabilities & CLIENT_IGNORE_SPACE)
     thd->variables.sql_mode|= MODE_IGNORE_SPACE;
   if (thd->client_capabilities & CLIENT_INTERACTIVE)
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 1d215f5aa40..3167cb7363d 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1923,14 +1923,15 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
   t_name= table->table_name;
   t_alias= table->alias;
 
+retry:
   DBUG_PRINT("info", ("real table: %s.%s", d_name, t_name));
-  for (;;)
+  for (TABLE_LIST *tl= table_list;;)
   {
     /*
       Table is unique if it is present only once in the global list
       of tables and once in the list of table locks.
     */
-    if (! (res= find_table_in_global_list(table_list, d_name, t_name)))
+    if (! (res= find_table_in_global_list(tl, d_name, t_name)))
       break;
 
     /* Skip if same underlying table. */
@@ -1961,10 +1962,23 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list,
       (exclude_from_table_unique_test) or prelocking placeholder.
     */
 next:
-    table_list= res->next_global;
+    tl= res->next_global;
     DBUG_PRINT("info",
                ("found same copy of table or table which we should skip"));
   }
+  if (res && res->belong_to_derived)
+  {
+    /* Try to fix */
+    TABLE_LIST *derived=  res->belong_to_derived;
+    if (derived->is_merged_derived())
+    {
+      DBUG_PRINT("info",
+                 ("convert merged to materialization to resolve the conflict"));
+      derived->change_refs_to_fields();
+      derived->set_materialized_derived();
+    }
+    goto retry;
+  }
   DBUG_RETURN(res);
 }
 
@@ -7363,11 +7377,16 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2,
       if (!(eq_cond= new Item_func_eq(item_ident_1, item_ident_2)))
         goto err;                               /* Out of memory. */
 
+      if (field_1 && field_1->vcol_info)
+        field_1->table->mark_virtual_col(field_1);
+      if (field_2 && field_2->vcol_info)
+        field_2->table->mark_virtual_col(field_2);
+
       /*
         Add the new equi-join condition to the ON clause. Notice that
         fix_fields() is applied to all ON conditions in setup_conds()
         so we don't do it here.
-       */
+      */
       add_join_on((table_ref_1->outer_join & JOIN_TYPE_RIGHT ?
                    table_ref_1 : table_ref_2),
                   eq_cond);
@@ -8085,7 +8104,8 @@ bool setup_tables(THD *thd, Name_resolution_context *context,
     while ((table_list= ti++))
     {
       TABLE *table= table_list->table;
-      table->pos_in_table_list= table_list;
+      if (table)
+        table->pos_in_table_list= table_list;
       if (first_select_table &&
           table_list->top_table() == first_select_table)
       {
@@ -8098,7 +8118,7 @@ bool setup_tables(THD *thd, Name_resolution_context *context,
       {
         table_list->jtbm_table_no= tablenr;
       }
-      else
+      else if (table)
       {
         table->pos_in_table_list= table_list;
         setup_table_map(table, table_list, tablenr);
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 0a1185a9924..9c86b2838c4 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -1463,10 +1463,10 @@ def_week_frmt: %lu, in_trans: %d, autocommit: %d",
       In case the wait time can't be determined there is an upper limit which
       causes try_lock() to abort with a time out.
 
-      The 'TRUE' parameter indicate that the lock is allowed to timeout
+      The 'TIMEOUT' parameter indicate that the lock is allowed to timeout
 
     */
-    if (try_lock(thd, Query_cache::WAIT))
+    if (try_lock(thd, Query_cache::TIMEOUT))
       DBUG_VOID_RETURN;
     if (query_cache_size == 0)
     {
@@ -1674,6 +1674,17 @@ Query_cache::send_result_to_client(THD *thd, char *org_sql, uint query_length)
   if (is_disabled() || thd->locked_tables_mode ||
       thd->variables.query_cache_type == 0)
     goto err;
+
+  /*
+    The following can only happen for prepared statements that was found
+    during parsing or later that the query was not cacheable.
+  */
+  if (!thd->lex->safe_to_cache_query)
+  {
+    DBUG_PRINT("qcache", ("SELECT is non-cacheable"));
+    goto err;
+  }
+
   DBUG_ASSERT(query_cache_size != 0);           // otherwise cache would be disabled
 
   thd->query_cache_is_applicable= 1;
@@ -1959,6 +1970,7 @@ def_week_frmt: %lu, in_trans: %d, autocommit: %d",
           faster.
         */
         thd->query_cache_is_applicable= 0;      // Query can't be cached
+        thd->lex->safe_to_cache_query= 0;       // For prepared statements
         BLOCK_UNLOCK_RD(query_block);
         DBUG_RETURN(-1);
       }
@@ -1975,6 +1987,7 @@ def_week_frmt: %lu, in_trans: %d, autocommit: %d",
 		  table_list.db, table_list.alias));
       unlock();
       thd->query_cache_is_applicable= 0;        // Query can't be cached
+      thd->lex->safe_to_cache_query= 0;         // For prepared statements
       BLOCK_UNLOCK_RD(query_block);
       DBUG_RETURN(-1);				// Privilege error
     }
@@ -1984,6 +1997,7 @@ def_week_frmt: %lu, in_trans: %d, autocommit: %d",
 			    table_list.db, table_list.alias));
       BLOCK_UNLOCK_RD(query_block);
       thd->query_cache_is_applicable= 0;        // Query can't be cached
+      thd->lex->safe_to_cache_query= 0;         // For prepared statements
       goto err_unlock;				// Parse query
     }
 #endif /*!NO_EMBEDDED_ACCESS_CHECKS*/
@@ -2007,7 +2021,13 @@ def_week_frmt: %lu, in_trans: %d, autocommit: %d",
                                   table->key_length());
       }
       else
+      {
+        /*
+          As this can change from call to call, don't reset set
+          thd->lex->safe_to_cache_query
+        */
         thd->query_cache_is_applicable= 0;      // Query can't be cached
+      }
       /* End the statement transaction potentially started by engine. */
       trans_rollback_stmt(thd);
       goto err_unlock;				// Parse query
@@ -3901,6 +3921,7 @@ Query_cache::process_and_count_tables(THD *thd, TABLE_LIST *tables_used,
       DBUG_PRINT("qcache", ("Don't cache statement as it refers to "
                             "tables with column privileges."));
       thd->query_cache_is_applicable= 0;        // Query can't be cached
+      thd->lex->safe_to_cache_query= 0;         // For prepared statements
       DBUG_RETURN(0);
     }
 #endif
@@ -4037,6 +4058,10 @@ my_bool Query_cache::ask_handler_allowance(THD *thd,
     {
       DBUG_PRINT("qcache", ("Handler does not allow caching for %s.%s",
 			    tables_used->db, tables_used->alias));
+      /*
+        As this can change from call to call, don't reset set
+        thd->lex->safe_to_cache_query
+      */
       thd->query_cache_is_applicable= 0;        // Query can't be cached
       DBUG_RETURN(1);
     }
diff --git a/sql/sql_cache.h b/sql/sql_cache.h
index 9d713d012f5..134fd055110 100644
--- a/sql/sql_cache.h
+++ b/sql/sql_cache.h
@@ -443,8 +443,8 @@ protected:
 	      uint def_query_hash_size = QUERY_CACHE_DEF_QUERY_HASH_SIZE,
 	      uint def_table_hash_size = QUERY_CACHE_DEF_TABLE_HASH_SIZE);
 
-  bool is_disabled(void) { return m_cache_status != OK; }
-  bool is_disable_in_progress(void)
+  inline bool is_disabled(void) { return m_cache_status != OK; }
+  inline bool is_disable_in_progress(void)
   { return m_cache_status == DISABLE_REQUEST; }
 
   /* initialize cache (mutex) */
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 2538e73572a..581e35aa137 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -585,12 +585,15 @@ typedef struct system_status_var
   ulong ha_read_prev_count;
   ulong ha_read_rnd_count;
   ulong ha_read_rnd_next_count;
+  ulong ha_read_rnd_deleted_count;
   /*
     This number doesn't include calls to the default implementation and
     calls made by range access. The intent is to count only calls made by
     BatchedKeyAccess.
   */
   ulong ha_multi_range_read_init_count;
+  ulong ha_mrr_extra_key_sorts;
+  ulong ha_mrr_extra_rowid_sorts;
 
   ulong ha_rollback_count;
   ulong ha_update_count;
@@ -4217,10 +4220,17 @@ inline int handler::ha_ft_read(uchar *buf)
 inline int handler::ha_rnd_next(uchar *buf)
 {
   MYSQL_READ_ROW_START(table_share->db.str, table_share->table_name.str, TRUE);
-  increment_statistics(&SSV::ha_read_rnd_next_count);
   int error= rnd_next(buf);
   if (!error)
+  {
     update_rows_read();
+    increment_statistics(&SSV::ha_read_rnd_next_count);
+  }
+  else if (error == HA_ERR_RECORD_DELETED)
+    increment_statistics(&SSV::ha_read_rnd_deleted_count);
+  else
+    increment_statistics(&SSV::ha_read_rnd_next_count);
+
   table->status=error ? STATUS_NOT_FOUND: 0;
   MYSQL_READ_ROW_DONE(error);
   return error;
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index f48724236d4..ff88bf7c0f8 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -76,7 +76,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   if (mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE))
     DBUG_RETURN(TRUE);
 
-  if (!table_list->updatable)
+  if (!table_list->single_table_updatable())
   {
      my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
      DBUG_RETURN(TRUE);
@@ -476,7 +476,8 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
       setup_conds(thd, table_list, select_lex->leaf_tables, conds) ||
       setup_ftfuncs(select_lex))
     DBUG_RETURN(TRUE);
-  if (!table_list->updatable || check_key_in_view(thd, table_list))
+  if (!table_list->single_table_updatable() ||
+      check_key_in_view(thd, table_list))
   {
     my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
     DBUG_RETURN(TRUE);
@@ -572,7 +573,7 @@ int mysql_multi_delete_prepare(THD *thd)
        DBUG_RETURN(TRUE);
     }
 
-    if (!target_tbl->correspondent_table->updatable ||
+    if (!target_tbl->correspondent_table->single_table_updatable() ||
         check_key_in_view(thd, target_tbl->correspondent_table))
     {
       my_error(ER_NON_UPDATABLE_TABLE, MYF(0),
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 63c5bb30474..9f06c99c472 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -483,7 +483,7 @@ bool mysql_derived_merge_for_insert(THD *thd, LEX *lex, TABLE_LIST *derived)
     return mysql_derived_prepare(thd, lex, derived);
   if (!derived->is_multitable())
   {
-    if (!derived->updatable)
+    if (!derived->single_table_updatable())
       return derived->create_field_translation(thd);
     if (derived->merge_underlying_list)
     {
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index c7423d9bada..2209a62d7ed 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -118,7 +118,8 @@ static bool check_view_insertability(THD *thd, TABLE_LIST *view);
 */
 
 bool check_view_single_update(List<Item> &fields, List<Item> *values,
-                              TABLE_LIST *view, table_map *map)
+                              TABLE_LIST *view, table_map *map,
+                              bool insert)
 {
   /* it is join view => we need to find the table for update */
   List_iterator_fast<Item> it(fields);
@@ -156,6 +157,14 @@ bool check_view_single_update(List<Item> &fields, List<Item> *values,
   */
   tbl->table->insert_values= view->table->insert_values;
   view->table= tbl->table;
+  if (!tbl->single_table_updatable())
+  {
+    if (insert)
+      my_error(ER_NON_INSERTABLE_TABLE, MYF(0), view->alias, "INSERT");
+    else
+      my_error(ER_NON_UPDATABLE_TABLE, MYF(0), view->alias, "UPDATE");
+    return TRUE;
+  }
   *map= tables;
 
   return FALSE;
@@ -200,7 +209,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
 {
   TABLE *table= table_list->table;
 
-  if (!table_list->updatable)
+  if (!table_list->single_table_updatable())
   {
     my_error(ER_NON_INSERTABLE_TABLE, MYF(0), table_list->alias, "INSERT");
     return -1;
@@ -276,7 +285,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
       if (check_view_single_update(fields,
                                    fields_and_values_from_different_maps ?
                                    (List<Item>*) 0 : &values,
-                                   table_list, map))
+                                   table_list, map, true))
         return -1;
       table= table_list->table;
     }
@@ -363,7 +372,7 @@ static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list,
   if (insert_table_list->is_view() &&
       insert_table_list->is_merged_derived() &&
       check_view_single_update(update_fields, &update_values,
-                               insert_table_list, map))
+                               insert_table_list, map, false))
     return -1;
 
   if (table->timestamp_field)
@@ -1255,7 +1264,7 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list,
   bool insert_into_view= (table_list->view != 0);
   DBUG_ENTER("mysql_prepare_insert_check_table");
 
-  if (!table_list->updatable)
+  if (!table_list->single_table_updatable())
   {
     my_error(ER_NON_INSERTABLE_TABLE, MYF(0), table_list->alias, "INSERT");
     DBUG_RETURN(TRUE);
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index 3600bbbb65f..f0362585e39 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -899,6 +899,8 @@ int JOIN_CACHE::alloc_buffer()
       curr_buff_space_sz+= cache->get_join_buffer_size();
     }
   }
+  curr_min_buff_space_sz+= min_buff_size;
+  curr_buff_space_sz+= buff_size;
 
   if (curr_min_buff_space_sz > join_buff_space_limit ||
       (curr_buff_space_sz > join_buff_space_limit &&
@@ -2110,16 +2112,6 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last)
     if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
       goto finish;
   }
-  if (outer_join_first_inner)
-  {
-    /* 
-      All null complemented rows have been already generated for all
-      outer records from join buffer. Restore the state of the
-      first_unmatched values to 0 to avoid another null complementing.
-    */
-    for (tab= join_tab->first_inner; tab <= join_tab->last_inner; tab++)
-      tab->first_unmatched= 0;
-  } 
  
   if (skip_last)
   {
@@ -2132,6 +2124,16 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last)
   }
 
 finish:
+  if (outer_join_first_inner)
+  {
+    /* 
+      All null complemented rows have been already generated for all
+      outer records from join buffer. Restore the state of the
+      first_unmatched values to 0 to avoid another null complementing.
+    */
+    for (tab= join_tab->first_inner; tab <= join_tab->last_inner; tab++)
+      tab->first_unmatched= 0;
+  } 
   restore_last_record();
   reset(TRUE);
   DBUG_PRINT("exit", ("rc: %d", rc));
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 9b5c1c0de37..56e05c3dcad 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3167,6 +3167,7 @@ void LEX::cleanup_after_one_table_open()
   if (all_selects_list != &select_lex)
   {
     derived_tables= 0;
+    select_lex.exclude_from_table_unique_test= false;
     /* cleunup underlying units (units of VIEW) */
     for (SELECT_LEX_UNIT *un= select_lex.first_inner_unit();
          un;
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index aab9ea76499..33d1741b5ec 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -235,7 +235,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
                                     INSERT_ACL | UPDATE_ACL, FALSE))
      DBUG_RETURN(-1);
   if (!table_list->table ||               // do not suport join view
-      !table_list->updatable ||           // and derived tables
+      !table_list->single_table_updatable() || // and derived tables
       check_key_in_view(thd, table_list))
   {
     my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "LOAD");
diff --git a/sql/sql_manager.cc b/sql/sql_manager.cc
index 221ba56130a..e9ac55730d3 100644
--- a/sql/sql_manager.cc
+++ b/sql/sql_manager.cc
@@ -43,6 +43,7 @@ static struct handler_cb * volatile cb_list;
 bool mysql_manager_submit(void (*action)())
 {
   bool result= FALSE;
+  DBUG_ASSERT(manager_thread_in_use);
   struct handler_cb * volatile *cb;
   mysql_mutex_lock(&LOCK_manager);
   cb= &cb_list;
@@ -74,8 +75,9 @@ pthread_handler_t handle_manager(void *arg __attribute__((unused)))
 
   pthread_detach_this_thread();
   manager_thread = pthread_self();
+  mysql_cond_init(key_COND_manager, &COND_manager,NULL);
+  mysql_mutex_init(key_LOCK_manager, &LOCK_manager, NULL);
   manager_thread_in_use = 1;
-
   for (;;)
   {
     mysql_mutex_lock(&LOCK_manager);
@@ -122,6 +124,8 @@ pthread_handler_t handle_manager(void *arg __attribute__((unused)))
     }
   }
   manager_thread_in_use = 0;
+  mysql_mutex_destroy(&LOCK_manager);
+  mysql_cond_destroy(&COND_manager);
   DBUG_LEAVE; // Can't use DBUG_RETURN after my_thread_end
   my_thread_end();
   return (NULL);
@@ -149,14 +153,14 @@ void stop_handle_manager()
 {
   DBUG_ENTER("stop_handle_manager");
   abort_manager = true;
-  mysql_mutex_lock(&LOCK_manager);
   if (manager_thread_in_use)
   {
+    mysql_mutex_lock(&LOCK_manager);
     DBUG_PRINT("quit", ("initiate shutdown of handle manager thread: 0x%lx",
                         (ulong)manager_thread));
     mysql_cond_signal(&COND_manager);
+    mysql_mutex_unlock(&LOCK_manager);
   }
-  mysql_mutex_unlock(&LOCK_manager);
   DBUG_VOID_RETURN;
 }
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index f5a2f409dfc..da9dcc053cf 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1365,7 +1365,7 @@ static int mysql_test_update(Prepared_statement *stmt,
   if (table_list->handle_derived(thd->lex, DT_PREPARE))
     goto error;
 
-  if (!table_list->updatable)
+  if (!table_list->single_table_updatable())
   {
     my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
     goto error;
@@ -1439,7 +1439,7 @@ static bool mysql_test_delete(Prepared_statement *stmt,
   if (mysql_handle_derived(thd->lex, DT_PREPARE))
     goto error;
 
-  if (!table_list->updatable)
+  if (!table_list->single_table_updatable())
   {
     my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
     goto error;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index f22510bb2b7..5e483c2d359 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -131,7 +131,8 @@ static COND *build_equal_items(THD *thd, COND *cond,
                                COND_EQUAL *inherited,
                                List<TABLE_LIST> *join_list,
                                COND_EQUAL **cond_equal_ref);
-static COND* substitute_for_best_equal_field(COND *cond,
+static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab,
+                                             COND *cond,
                                              COND_EQUAL *cond_equal,
                                              void *table_join_idx);
 static COND *simplify_joins(JOIN *join, List<TABLE_LIST> *join_list,
@@ -1254,7 +1255,8 @@ JOIN::optimize()
   */
   if (conds)
   {
-    conds= substitute_for_best_equal_field(conds, cond_equal, map2table);
+    conds= substitute_for_best_equal_field(NO_PARTICULAR_TAB, conds, 
+                                           cond_equal, map2table);
     conds->update_used_tables();
     DBUG_EXECUTE("where",
                  print_where(conds,
@@ -1271,7 +1273,8 @@ JOIN::optimize()
   {
     if (*tab->on_expr_ref)
     {
-      *tab->on_expr_ref= substitute_for_best_equal_field(*tab->on_expr_ref,
+      *tab->on_expr_ref= substitute_for_best_equal_field(NO_PARTICULAR_TAB,
+                                                         *tab->on_expr_ref,
                                                          tab->cond_equal,
                                                          map2table);
       (*tab->on_expr_ref)->update_used_tables();
@@ -1294,7 +1297,7 @@ JOIN::optimize()
         continue;
       COND_EQUAL *equals= tab->first_inner ? tab->first_inner->cond_equal : 
                                              cond_equal;
-      ref_item= substitute_for_best_equal_field(ref_item, equals, map2table);
+      ref_item= substitute_for_best_equal_field(tab, ref_item, equals, map2table);
       ref_item->update_used_tables();
       if (*ref_item_ptr != ref_item)
       {
@@ -3596,6 +3599,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
       for (i= 0; i < join->table_count ; i++)
         records*= join->best_positions[i].records_read ?
                   (ha_rows)join->best_positions[i].records_read : 1;
+      set_if_smaller(records, unit->select_limit_cnt);
       join->select_lex->increase_derived_records(records);
     }
   }
@@ -8997,7 +9001,7 @@ void revise_cache_usage(JOIN_TAB *join_tab)
          first_inner;
          first_inner= first_inner->first_upper)           
     {
-      for (tab= end_tab-1; tab >= first_inner; tab--)
+      for (tab= end_tab; tab >= first_inner; tab--)
         set_join_cache_denial(tab);
       end_tab= first_inner;
     }
@@ -9005,7 +9009,7 @@ void revise_cache_usage(JOIN_TAB *join_tab)
   else if (join_tab->first_sj_inner_tab)
   {
     first_inner= join_tab->first_sj_inner_tab;
-    for (tab= join_tab-1; tab >= first_inner; tab--)
+    for (tab= join_tab; tab >= first_inner; tab--)
     {
       set_join_cache_denial(tab);
     }
@@ -9247,6 +9251,9 @@ uint check_join_cache_usage(JOIN_TAB *tab,
 
   if (tab->use_quick == 2)
     goto no_join_cache;
+
+  if (tab->table->map & join->complex_firstmatch_tables)
+    goto no_join_cache;
   
   /*
     Don't use join cache if we're inside a join tab range covered by LooseScan
@@ -9457,7 +9464,7 @@ void check_join_cache_usage_for_tables(JOIN *join, ulonglong options,
   {
     tab->used_join_cache_level= join->max_allowed_join_cache_level;  
   }
-  
+
   uint idx= join->const_tables;
   for (tab= first_linear_tab(join, WITHOUT_CONST_TABLES); 
        tab; 
@@ -9542,6 +9549,8 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
   bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
   bool sorted= 1;
 
+  join->complex_firstmatch_tables= table_map(0);
+
   if (!join->select_lex->sj_nests.is_empty() &&
       setup_semijoin_dups_elimination(join, options, no_jbuf_after))
     DBUG_RETURN(TRUE); /* purecov: inspected */
@@ -10422,6 +10431,15 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond,
   }
   
 
+  /*
+    Cleanup to avoid interference of calls of this function for
+    ORDER BY and GROUP BY
+  */
+  for (JOIN_TAB *tab= join->join_tab + join->const_tables;
+       tab < join->join_tab + join->table_count;
+       tab++)
+    tab->cached_eq_ref_table= FALSE;
+
   prev_ptr= &first_order;
   *simple_order= *join->join_tab[join->const_tables].on_expr_ref ? 0 : 1;
 
@@ -11582,7 +11600,7 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels,
   else
   {
     TABLE_LIST *emb_nest;
-    head= item_equal->get_first(NULL);
+    head= item_equal->get_first(NO_PARTICULAR_TAB, NULL);
     it++;
     if ((emb_nest= embedding_sjm(head)))
     {
@@ -11609,7 +11627,7 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels,
     }      
 
     /* 
-      Check if "item_field=head" equality is already guaranteed to be true 
+      Check if "field_item=head" equality is already guaranteed to be true 
       on upper AND-levels.
     */
     if (upper)
@@ -11621,7 +11639,8 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels,
         Item_equal_fields_iterator li(*item_equal);
         while ((item= li++) != field_item)
         {
-          if (item->find_item_equal(upper_levels) == upper)
+          if (embedding_sjm(item) == field_sjm && 
+              item->find_item_equal(upper_levels) == upper)
             break;
         }
       }
@@ -11691,6 +11710,7 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels,
   return cond;
 }
 
+
 /**
   Substitute every field reference in a condition by the best equal field
   and eliminate all multiple equality predicates.
@@ -11705,6 +11725,9 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels,
     After this the function retrieves all other conjuncted
     predicates substitute every field reference by the field reference
     to the first equal field or equal constant if there are any.
+
+  @param context_tab     Join tab that 'cond' will be attached to, or 
+                         NO_PARTICULAR_TAB. See notes above.
   @param cond            condition to process
   @param cond_equal      multiple equalities to take into consideration
   @param table_join_idx  index to tables determining field preference
@@ -11715,11 +11738,37 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels,
     new fields in multiple equality item of lower levels. We want
     the order in them to comply with the order of upper levels.
 
+    context_tab may be used to specify which join tab `cond` will be
+    attached to. There are two possible cases:
+
+    1. context_tab != NO_PARTICULAR_TAB
+       We're doing substitution for an Item which will be evaluated in the 
+       context of a particular item. For example, if the optimizer does a 
+       ref access on "tbl1.key= expr" then
+        = equality substitution will be perfomed on 'expr'
+        = it is known in advance that 'expr' will be evaluated when 
+          table t1 is accessed.
+       Note that in this kind of substution we never have to replace Item_equal
+       objects. For example, for
+
+        t.key= func(col1=col2 AND col2=const)
+       
+       we will not build Item_equal or do equality substution (if we decide to,
+       this function will need to be fixed to handle it)
+
+    2. context_tab == NO_PARTICULAR_TAB
+       We're doing substitution in WHERE/ON condition, which is not yet 
+       attached to any particular join_tab. We will use information about the
+       chosen join order to make "optimal" substitions, i.e. those that allow
+       to apply filtering as soon as possible. See eliminate_item_equal() and 
+       Item_equal::get_first() for details.
+
   @return
     The transformed condition
 */
 
-static COND* substitute_for_best_equal_field(COND *cond,
+static COND* substitute_for_best_equal_field(JOIN_TAB *context_tab,
+                                             COND *cond,
                                              COND_EQUAL *cond_equal,
                                              void *table_join_idx)
 {
@@ -11735,7 +11784,7 @@ static COND* substitute_for_best_equal_field(COND *cond,
     if (and_level)
     {
       cond_equal= &((Item_cond_and *) cond)->cond_equal;
-      cond_list->disjoin((List<Item> *) &cond_equal->current_level);
+      cond_list->disjoin((List<Item> *) &cond_equal->current_level);/* remove Item_equal objects from the AND. */
 
       List_iterator_fast<Item_equal> it(cond_equal->current_level);      
       while ((item_equal= it++))
@@ -11748,7 +11797,8 @@ static COND* substitute_for_best_equal_field(COND *cond,
     Item *item;
     while ((item= li++))
     {
-      Item *new_item= substitute_for_best_equal_field(item, cond_equal,
+      Item *new_item= substitute_for_best_equal_field(context_tab,
+                                                      item, cond_equal,
                                                       table_join_idx);
       /*
         This works OK with PS/SP re-execution as changes are made to
@@ -11795,7 +11845,8 @@ static COND* substitute_for_best_equal_field(COND *cond,
       List_iterator_fast<Item_equal> it(cond_equal->current_level);
       while((item_equal= it++))
       {
-        cond= cond->transform(&Item::replace_equal_field, (uchar *) item_equal);
+        REPLACE_EQUAL_FIELD_ARG arg= {item_equal, context_tab};
+        cond= cond->transform(&Item::replace_equal_field, (uchar *) &arg);
       }
       cond_equal= cond_equal->upper_levels;
     }
@@ -15589,7 +15640,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
     if (join_tab->loosescan_match_tab && 
         join_tab->loosescan_match_tab->found_match)
     {
-      KEY *key= join_tab->table->key_info + join_tab->index;
+      KEY *key= join_tab->table->key_info + join_tab->loosescan_key;
       key_copy(join_tab->loosescan_buf, join_tab->table->record[0], key, 
                join_tab->loosescan_key_len);
       skip_over= TRUE;
@@ -15599,7 +15650,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
 
     if (skip_over && !error) 
     {
-      if(!key_cmp(join_tab->table->key_info[join_tab->index].key_part,
+      if(!key_cmp(join_tab->table->key_info[join_tab->loosescan_key].key_part,
                   join_tab->loosescan_buf, join_tab->loosescan_key_len))
       {
         /* 
@@ -18395,7 +18446,6 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
     table->sort.io_cache= NULL;
 
     select->cleanup();				// filesort did select
-    tab->select= 0;
     table->quick_keys.clear_all();  // as far as we cleanup select->quick
     table->intersect_keys.clear_all();
     table->sort.io_cache= tablesort_result_cache;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 46e7b7fe95f..2f7d31de404 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -374,6 +374,12 @@ typedef struct st_join_table {
   /* Buffer to save index tuple to be able to skip duplicates */
   uchar *loosescan_buf;
   
+  /* 
+    Index used by LooseScan (we store it here separately because ref access
+    stores it in tab->ref.key, while range scan stores it in tab->index, etc)
+  */
+  uint loosescan_key;
+
   /* Length of key tuple (depends on #keyparts used) to store in the above */
   uint loosescan_key_len;
 
@@ -986,6 +992,13 @@ public:
   
   /* We also maintain a stack of join optimization states in * join->positions[] */
 /******* Join optimization state members end *******/
+
+  /*
+    Tables within complex firstmatch ranges (i.e. those where inner tables are
+    interleaved with outer tables). Join buffering cannot be used for these.
+  */
+  table_map complex_firstmatch_tables;
+
   /*
     The cost of best complete join plan found so far during optimization,
     after optimization phase - cost of picked join order (not taking into
@@ -1440,6 +1453,7 @@ public:
   virtual ~store_key() {}			/** Not actually needed */
   virtual enum Type type() const=0;
   virtual const char *name() const=0;
+  virtual bool store_key_is_const() { return false; }
 
   /**
     @brief sets ignore truncation warnings mode and calls the real copy method
@@ -1593,6 +1607,7 @@ public:
 
   enum Type type() const { return CONST_ITEM_STORE_KEY; }
   const char *name() const { return "const"; }
+  bool store_key_is_const() { return true; }
 
 protected:  
   enum store_key_result copy_inner()
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 584ebd904a8..9b6d3a8dda5 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -964,6 +964,7 @@ bool st_select_lex::cleanup()
   }
   non_agg_fields.empty();
   inner_refs_list.empty();
+  exclude_from_table_unique_test= FALSE;
   DBUG_RETURN(error);
 }
 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 99cac9f40e7..5fa30c91417 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -304,7 +304,7 @@ int mysql_update(THD *thd,
   thd_proc_info(thd, "init");
   table= table_list->table;
 
-  if (!table_list->updatable)
+  if (!table_list->single_table_updatable())
   {
     my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
     DBUG_RETURN(1);
@@ -1246,7 +1246,7 @@ int mysql_multi_update_prepare(THD *thd)
     /* if table will be updated then check that it is unique */
     if (table->map & tables_for_update)
     {
-      if (!tl->updatable || check_key_in_view(thd, tl))
+      if (!tl->single_table_updatable() || check_key_in_view(thd, tl))
       {
         my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE");
         DBUG_RETURN(TRUE);
diff --git a/sql/table.cc b/sql/table.cc
index 53e5872bfe4..8f4f034bf2a 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4053,6 +4053,28 @@ bool TABLE_LIST::prep_where(THD *thd, Item **conds,
   DBUG_RETURN(FALSE);
 }
 
+/**
+  Check that table/view is updatable and if it has single
+  underlying tables/views it is also updatable
+
+  @return Result of the check.
+*/
+
+bool TABLE_LIST::single_table_updatable()
+{
+  if (!updatable)
+    return false;
+  if (view_tables && view_tables->elements == 1)
+  {
+    /*
+      We need to check deeply only single table views. Multi-table views
+      will be turned to multi-table updates and then checked by leaf tables
+    */
+    return view_tables->head()->single_table_updatable();
+  }
+  return true;
+}
+
 
 /*
   Merge ON expressions for a view
@@ -4702,6 +4724,36 @@ bool TABLE_LIST::prepare_security(THD *thd)
   DBUG_RETURN(FALSE);
 }
 
+#ifndef DBUG_OFF
+void TABLE_LIST::set_check_merged()
+{
+  DBUG_ASSERT(derived);
+  /*
+    It is not simple to check all, but at least this should be checked:
+    this select is not excluded or the exclusion came from above.
+  */
+  DBUG_ASSERT(!derived->first_select()->exclude_from_table_unique_test ||
+              derived->outer_select()->
+              exclude_from_table_unique_test);
+}
+#endif
+
+void TABLE_LIST::set_check_materialized()
+{
+  DBUG_ASSERT(derived);
+  if (!derived->first_select()->exclude_from_table_unique_test)
+    derived->set_unique_exclude();
+  else
+  {
+    /*
+      The subtree should be already excluded
+    */
+    DBUG_ASSERT(!derived->first_select()->first_inner_unit() ||
+                derived->first_select()->first_inner_unit()->first_select()->
+                exclude_from_table_unique_test);
+  }
+}
+
 
 Natural_join_column::Natural_join_column(Field_translator *field_param,
                                          TABLE_LIST *tab)
@@ -4828,6 +4880,7 @@ Item *Field_iterator_table::create_item(THD *thd)
   {
     select->non_agg_fields.push_back(item);
     item->marker= select->cur_pos_in_select_list;
+    select->set_non_agg_field_used(true);
   }
   return item;
 }
@@ -5532,6 +5585,12 @@ void TABLE::mark_virtual_columns_for_write(bool insert_fl)
   Field **vfield_ptr, *tmp_vfield;
   bool bitmap_updated= FALSE;
 
+  if (!vfield)
+    return;
+
+  if (!vfield)
+    return;
+
   for (vfield_ptr= vfield; *vfield_ptr; vfield_ptr++)
   {
     tmp_vfield= *vfield_ptr;
@@ -6293,8 +6352,9 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
   */
   if (is_materialized_derived())
   {
-    unit->master_unit()->set_unique_exclude();
+    set_check_materialized();
   }
+
   /*
     Create field translation for mergeable derived tables/views.
     For derived tables field translation can be created only after
diff --git a/sql/table.h b/sql/table.h
index a327d625387..77a7f8cf6a9 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1983,16 +1983,18 @@ struct TABLE_LIST
   inline void set_merged_derived()
   {
     derived_type= ((derived_type & DTYPE_MASK) |
-                    DTYPE_TABLE | DTYPE_MERGE);
+                   DTYPE_TABLE | DTYPE_MERGE);
+    set_check_merged();
   }
   inline bool is_materialized_derived()
   {
     return (derived_type & DTYPE_MATERIALIZE);
   }
-  inline void set_materialized_derived()
+  void set_materialized_derived()
   {
     derived_type= ((derived_type & DTYPE_MASK) |
-                    DTYPE_TABLE | DTYPE_MATERIALIZE);
+                   DTYPE_TABLE | DTYPE_MATERIALIZE);
+    set_check_materialized();
   }
   inline bool is_multitable()
   {
@@ -2033,9 +2035,17 @@ struct TABLE_LIST
   int fetch_number_of_rows();
   bool change_refs_to_fields();
 
+  bool single_table_updatable();
+
 private:
   bool prep_check_option(THD *thd, uint8 check_opt_type);
   bool prep_where(THD *thd, Item **conds, bool no_where_clause);
+  void set_check_materialized();
+#ifndef DBUG_OFF
+  void set_check_merged();
+#else
+  inline void set_check_merged() {}
+#endif
   /** See comments for set_metadata_id() */
   enum enum_table_ref_type m_table_ref_type;
   /** See comments for set_metadata_id() */
diff --git a/sql/winservice.c b/sql/winservice.c
index 51a5a07a820..e4e0a3bd596 100644
--- a/sql/winservice.c
+++ b/sql/winservice.c
@@ -116,7 +116,7 @@ int get_mysql_service_properties(const wchar_t *bin_path,
     wcscat(mysqld_path, L".exe");
 
   if(wcsicmp(file_part, L"mysqld.exe") != 0 && 
-    wcsicmp(file_part, L"mysqld.exe") != 0 &&
+    wcsicmp(file_part, L"mysqld-debug.exe") != 0 &&
     wcsicmp(file_part, L"mysqld-nt.exe") != 0)
   {
     /* The service executable is not mysqld. */
@@ -244,4 +244,4 @@ int get_mysql_service_properties(const wchar_t *bin_path,
 end:
   LocalFree((HLOCAL)args);
   return retval;
-}
\ No newline at end of file
+}