Merge branch '5.5' into 10.1

9 files changed, 228 insertions, 160 deletions

diff --git a/sql/my_json_writer.cc b/sql/my_json_writer.cc
index 72b000df639..22c66ba7a34 100644
--- a/sql/my_json_writer.cc
+++ b/sql/my_json_writer.cc
@@ -129,6 +129,13 @@ void Json_writer::add_ll(longlong val)
   add_unquoted_str(buf);
 }
 
+void Json_writer::add_ull(ulonglong val)
+{
+  char buf[64];
+  my_snprintf(buf, sizeof(buf), "%llu", val);
+  add_unquoted_str(buf);
+}
+
 
 /* Add a memory size, printing in Kb, Kb, Gb if necessary */
 void Json_writer::add_size(longlong val)
diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h
index 349a1f380da..ffee6db4c03 100644
--- a/sql/my_json_writer.h
+++ b/sql/my_json_writer.h
@@ -108,6 +108,7 @@ public:
   void add_str(const String &str);
 
   void add_ll(longlong val);
+  void add_ull(ulonglong val);
   void add_size(longlong val);
   void add_double(double val);
   void add_bool(bool val);
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 043be9b9a96..d49fbcddbf9 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4531,8 +4531,7 @@ open_tables_check_upgradable_mdl(THD *thd, TABLE_LIST *tables_start,
 */
 
 bool open_tables(THD *thd, const DDL_options_st &options,
-                 TABLE_LIST **start, uint *counter,
-                 Sroutine_hash_entry **sroutine_to_open_list, uint flags,
+                 TABLE_LIST **start, uint *counter, uint flags,
                  Prelocking_strategy *prelocking_strategy)
 {
   /*
@@ -4575,9 +4574,10 @@ restart:
 
   has_prelocking_list= thd->lex->requires_prelocking();
   table_to_open= start;
-  sroutine_to_open= sroutine_to_open_list;
+  sroutine_to_open= &thd->lex->sroutines_list.first;
   *counter= 0;
   THD_STAGE_INFO(thd, stage_opening_tables);
+  prelocking_strategy->reset(thd);
 
   /*
     If we are executing LOCK TABLES statement or a DDL statement
@@ -4635,8 +4635,7 @@ restart:
     elements in prelocking list/set.
   */
   while (*table_to_open  ||
-         (thd->locked_tables_mode <= LTM_LOCK_TABLES &&
-          *sroutine_to_open))
+         (thd->locked_tables_mode <= LTM_LOCK_TABLES && *sroutine_to_open))
   {
     /*
       For every table in the list of tables to open, try to find or open
@@ -4756,6 +4755,8 @@ restart:
         }
       }
     }
+    if ((error= prelocking_strategy->handle_end(thd)))
+      goto error;
   }
 
   /*
diff --git a/sql/sql_base.h b/sql/sql_base.h
index 445d537ba36..91eef7369ac 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -252,17 +252,7 @@ lock_table_names(THD *thd, TABLE_LIST *table_list,
 }
 bool open_tables(THD *thd, const DDL_options_st &options,
                  TABLE_LIST **tables, uint *counter,
-                 Sroutine_hash_entry **sroutine_to_open, uint flags,
-                 Prelocking_strategy *prelocking_strategy);
-
-static inline bool
-open_tables(THD *thd, const DDL_options_st &options, TABLE_LIST **tables,
-            uint *counter, uint flags, Prelocking_strategy *prelocking_strategy)
-{
-  return open_tables(thd, options, tables, counter,
-                     &thd->lex->sroutines_list.first, flags,
-                     prelocking_strategy);
-}
+                 uint flags, Prelocking_strategy *prelocking_strategy);
 
 static inline bool
 open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags,
@@ -442,6 +432,7 @@ class Prelocking_strategy
 public:
   virtual ~Prelocking_strategy() { }
 
+  virtual void reset(THD *thd) { };
   virtual bool handle_routine(THD *thd, Query_tables_list *prelocking_ctx,
                               Sroutine_hash_entry *rt, sp_head *sp,
                               bool *need_prelocking) = 0;
@@ -449,6 +440,7 @@ public:
                             TABLE_LIST *table_list, bool *need_prelocking) = 0;
   virtual bool handle_view(THD *thd, Query_tables_list *prelocking_ctx,
                            TABLE_LIST *table_list, bool *need_prelocking)= 0;
+  virtual bool handle_end(THD *thd) { return 0; };
 };
 
 
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index c8576136069..2fa4b2cb3ae 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -1223,7 +1223,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai
   if (rows_set)
   {
     item_list.push_back(new (mem_root)
-                        Item_int(thd, (longlong) (ulonglong) rows,
+                        Item_int(thd, (ulonglong) rows,
                                  MY_INT64_NUM_DECIMAL_DIGITS),
                         mem_root);
   }
@@ -1601,7 +1601,7 @@ void Explain_table_access::print_explain_json(Explain_query *query,
   
   /* `rows` */
   if (rows_set)
-    writer->add_member("rows").add_ll(rows);
+    writer->add_member("rows").add_ull(rows);
 
   /* `r_rows` */
   if (is_analyze)
@@ -2239,7 +2239,7 @@ void Explain_update::print_explain_json(Explain_query *query,
   }
   
   /* `rows` */
-  writer->add_member("rows").add_ll(rows);
+  writer->add_member("rows").add_ull(rows);
 
 
   if (mrr_type.length() != 0)
@@ -2268,7 +2268,7 @@ void Explain_update::print_explain_json(Explain_query *query,
           r_rows= 0;
         r_filtered= buf_tracker.get_filtered_after_where() * 100.0;
       }
-      writer->add_member("r_rows").add_ll(r_rows);
+      writer->add_member("r_rows").add_ull(r_rows);
       writer->add_member("r_filtered").add_double(r_filtered);
     }
     else /* Not doing buffering */
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index 336b1800042..f7366a64e60 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -2156,7 +2156,8 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last)
   }
 
 finish:
-  if (outer_join_first_inner)
+  if (outer_join_first_inner &&
+      join_tab->first_inner == join_tab->first_unmatched)
   {
     /* 
       All null complemented rows have been already generated for all
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 45e62733698..64406f54005 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -4372,7 +4372,10 @@ void SELECT_LEX::increase_derived_records(ha_rows records)
   DBUG_ASSERT(unit->derived);
 
   select_union *result= (select_union*)unit->result;
-  result->records+= records;
+  if (HA_ROWS_MAX - records > result->records)
+    result->records+= records;
+  else
+    result->records= HA_ROWS_MAX;
 }
 
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 22ea300a79a..954080cbc11 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -218,7 +218,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond,
                                            table_map used_table,
                                            int join_tab_idx_arg,
                                            bool exclude_expensive_cond,
-                                           bool retain_ref_cond);
+                                           bool retain_ref_cond,
+                                           bool is_top_and_level);
 
 static Item* part_of_refkey(TABLE *form,Field *field);
 uint find_shortest_key(TABLE *table, const key_map *usable_keys);
@@ -4238,7 +4239,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
     DBUG_RETURN(TRUE); /* purecov: inspected */
 
   {
-    ha_rows records= 1;
+    double records= 1;
     SELECT_LEX_UNIT *unit= join->select_lex->master_unit();
 
     /* Find an optimal join order of the non-constant tables. */
@@ -4263,10 +4264,11 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
         table/view.
       */
       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);
+        if (double rr= join->best_positions[i].records_read)
+          records= COST_MULT(records, rr);
+      ha_rows rows= records > HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records;
+      set_if_smaller(rows, unit->select_limit_cnt);
+      join->select_lex->increase_derived_records(rows);
     }
   }
 
@@ -7392,18 +7394,23 @@ double JOIN::get_examined_rows()
 {
   double examined_rows;
   double prev_fanout= 1;
+  double records;
   JOIN_TAB *tab= first_breadth_first_optimization_tab();
   JOIN_TAB *prev_tab= tab;
 
-  examined_rows= tab->get_examined_rows();
+  records= tab->get_examined_rows();
 
   while ((tab= next_breadth_first_tab(first_breadth_first_optimization_tab(),
                                       top_table_access_tabs_count, tab)))
   {
-    prev_fanout *= prev_tab->records_read;
-    examined_rows+= tab->get_examined_rows() * prev_fanout;
+    prev_fanout= COST_MULT(prev_fanout, prev_tab->records_read);
+    records=
+      COST_ADD(records,
+               COST_MULT((double) (tab->get_examined_rows()), prev_fanout));
     prev_tab= tab;
   }
+  examined_rows=
+    records > (double) HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records;
   return examined_rows;
 }
 
@@ -9853,12 +9860,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
                      RAND_TABLE_BIT;
       }
 
-      /*
-	Following force including random expression in last table condition.
-	It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5
-      */
-      if (tab == join->join_tab + last_top_base_tab_idx)
-        current_map|= RAND_TABLE_BIT;
       used_tables|=current_map;
 
       if (tab->type == JT_REF && tab->quick &&
@@ -9900,6 +9901,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
         {
           tmp= make_cond_for_table(thd, cond, used_tables, current_map, i,
                                    FALSE, FALSE);
+          if (tab == join->join_tab + last_top_base_tab_idx)
+          {
+            /*
+              This pushes conjunctive conditions of WHERE condition such that:
+              - their used_tables() contain RAND_TABLE_BIT
+              - the conditions does not refer to any fields
+              (such like rand() > 0.5)
+            */
+            table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+            COND *rand_cond= make_cond_for_table(thd, cond, used_tables,
+                                                 rand_table_bit, -1,
+                                                 FALSE, FALSE);
+            add_cond_and_fix(thd, &tmp, rand_cond);
+          }
         }
         /* Add conditions added by add_not_null_conds(). */
         if (tab->select_cond)
@@ -10235,6 +10250,21 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
                                               current_map,
                                               /*(inner_tab - first_tab)*/ -1,
 					      FALSE, FALSE);
+          if (tab == last_tab)
+          {
+            /*
+              This pushes conjunctive conditions of ON expression of an outer
+              join such that:
+              - their used_tables() contain RAND_TABLE_BIT
+              - the conditions does not refer to any fields
+              (such like rand() > 0.5)
+            */
+            table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+            COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2,
+                                                 rand_table_bit, -1,
+                                                 FALSE, FALSE);
+            add_cond_and_fix(thd, &tmp_cond, rand_cond);
+          }
           bool is_sjm_lookup_tab= FALSE;
           if (inner_tab->bush_children)
           {
@@ -11848,6 +11878,8 @@ ha_rows JOIN_TAB::get_examined_rows()
   else
     examined_rows= records_read;
 
+  if (examined_rows >= (double) HA_ROWS_MAX)
+    return HA_ROWS_MAX;
   return (ha_rows) examined_rows;
 }
 
@@ -20275,7 +20307,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables,
   return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table,
                                        join_tab_idx_arg,
                                        exclude_expensive_cond,
-                                       retain_ref_cond);
+                                       retain_ref_cond, true);
 }
 
 
@@ -20285,9 +20317,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
                               int join_tab_idx_arg,
                               bool exclude_expensive_cond __attribute__
                               ((unused)),
-                              bool retain_ref_cond)
+                              bool retain_ref_cond,
+                              bool is_top_and_level)
 
 {
+  table_map rand_table_bit= (table_map) RAND_TABLE_BIT;
+
   if (used_table && !(cond->used_tables() & used_table))
     return (COND*) 0;				// Already checked
 
@@ -20303,11 +20338,28 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
       Item *item;
       while ((item=li++))
       {
+        /*
+          Special handling of top level conjuncts with RAND_TABLE_BIT:
+          if such a conjunct contains a reference to a field that is not
+          an outer field then it is pushed to the corresponding table by
+          the same rule as all other conjuncts. Otherwise, if the conjunct
+          is used in WHERE is is pushed to the last joined table, if is it
+          is used in ON condition of an outer join it is pushed into the
+          last inner table of the outer join. Such conjuncts are pushed in
+          a call of make_cond_for_table_from_pred() with the
+          parameter 'used_table' equal to PSEUDO_TABLE_BITS.
+        */
+        if (is_top_and_level && used_table == rand_table_bit &&
+            (item->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+        {
+          /* The conjunct with RAND_TABLE_BIT has been allready pushed */
+          continue;
+        }
 	Item *fix=make_cond_for_table_from_pred(thd, root_cond, item, 
                                                 tables, used_table,
-						join_tab_idx_arg,
+                                                join_tab_idx_arg,
                                                 exclude_expensive_cond,
-                                                retain_ref_cond);
+                                                retain_ref_cond, false);
 	if (fix)
 	  new_cond->argument_list()->push_back(fix, thd->mem_root);
       }
@@ -20331,6 +20383,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
     }
     else
     {						// Or list
+      if (is_top_and_level && used_table == rand_table_bit &&
+          (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+      {
+        /* This top level formula with RAND_TABLE_BIT has been already pushed */
+        return (COND*) 0;
+      }
+
       Item_cond_or *new_cond=new (thd->mem_root) Item_cond_or(thd);
       if (!new_cond)
 	return (COND*) 0;			// OOM /* purecov: inspected */
@@ -20342,7 +20401,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
                                                 tables, 0L,
                                                 join_tab_idx_arg,
                                                 exclude_expensive_cond,
-                                                retain_ref_cond);
+                                                retain_ref_cond, false);
 	if (!fix)
 	  return (COND*) 0;			// Always true
 	new_cond->argument_list()->push_back(fix, thd->mem_root);
@@ -20359,6 +20418,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond,
     }
   }
 
+  if (is_top_and_level && used_table == rand_table_bit &&
+      (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit)
+  {
+    /* This top level formula with RAND_TABLE_BIT has been already pushed */
+    return (COND*) 0;
+  }
+
   /*
     Because the following test takes a while and it can be done
     table_count times, we mark each item that we have examined with the result
@@ -24485,10 +24551,10 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta,
   }
   else
   {
-    double examined_rows= get_examined_rows();
+    ha_rows examined_rows= get_examined_rows();
 
     eta->rows_set= true;
-    eta->rows= (ha_rows) examined_rows;
+    eta->rows= examined_rows;
 
     /* "filtered"  */
     float f= 0.0; 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 009d8ae2658..1360bf9fb74 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1364,106 +1364,81 @@ static bool multi_update_check_table_access(THD *thd, TABLE_LIST *table,
 }
 
 
-/*
-  make update specific preparation and checks after opening tables
+class Multiupdate_prelocking_strategy : public DML_prelocking_strategy
+{
+  bool done;
+  bool has_prelocking_list;
+public:
+  void reset(THD *thd);
+  bool handle_end(THD *thd);
+};
+
+void Multiupdate_prelocking_strategy::reset(THD *thd)
+{
+  done= false;
+  has_prelocking_list= thd->lex->requires_prelocking();
+}
 
-  SYNOPSIS
-    mysql_multi_update_prepare()
-    thd         thread handler
+/**
+  Determine what tables could be updated in the multi-update
 
-  RETURN
-    FALSE OK
-    TRUE  Error
+  For these tables we'll need to open triggers and continue prelocking
+  until all is open.
 */
-
-int mysql_multi_update_prepare(THD *thd)
+bool Multiupdate_prelocking_strategy::handle_end(THD *thd)
 {
-  LEX *lex= thd->lex;
-  TABLE_LIST *table_list= lex->query_tables;
-  TABLE_LIST *tl;
-  List<Item> *fields= &lex->select_lex.item_list;
-  table_map tables_for_update;
-  bool update_view= 0;
-  DML_prelocking_strategy prelocking_strategy;
-  bool has_prelocking_list= thd->lex->requires_prelocking();
+  DBUG_ENTER("Multiupdate_prelocking_strategy::handle_end");
+  if (done)
+    DBUG_RETURN(0);
 
-  /*
-    if this multi-update was converted from usual update, here is table
-    counter else junk will be assigned here, but then replaced with real
-    count in open_tables()
-  */
-  uint  table_count= lex->table_count;
-  const bool using_lock_tables= thd->locked_tables_mode != LTM_NONE;
-  bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI);
-  DBUG_ENTER("mysql_multi_update_prepare");
+  LEX *lex= thd->lex;
+  SELECT_LEX *select_lex= &lex->select_lex;
+  TABLE_LIST *table_list= lex->query_tables, *tl;
 
-  /* following need for prepared statements, to run next time multi-update */
-  thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
+  done= true;
 
-  /*
-    Open tables and create derived ones, but do not lock and fill them yet.
+  if (mysql_handle_derived(lex, DT_INIT) ||
+      mysql_handle_derived(lex, DT_MERGE_FOR_INSERT) ||
+      mysql_handle_derived(lex, DT_PREPARE))
+    DBUG_RETURN(1);
 
-    During prepare phase acquire only S metadata locks instead of SW locks to
-    keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE
-    and global read lock.
-  */
-  if ((original_multiupdate && open_tables(thd, &table_list, &table_count,
-                                           thd->stmt_arena->is_stmt_prepare()
-                                           ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
-                                           &prelocking_strategy)) ||
-      mysql_handle_derived(lex, DT_INIT))
-    DBUG_RETURN(TRUE);
   /*
     setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables()
     second time, but this call will do nothing (there are check for second
     call in setup_tables()).
   */
 
-  //We need to merge for insert prior to prepare.
-  if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT))
-    DBUG_RETURN(TRUE);
-  if (mysql_handle_derived(lex, DT_PREPARE))
-    DBUG_RETURN(TRUE);
-
-  if (setup_tables_and_check_access(thd, &lex->select_lex.context,
-                                    &lex->select_lex.top_join_list,
-                                    table_list,
-                                    lex->select_lex.leaf_tables, FALSE,
-                                    UPDATE_ACL, SELECT_ACL, FALSE))
-    DBUG_RETURN(TRUE);
+  if (setup_tables_and_check_access(thd, &select_lex->context,
+        &select_lex->top_join_list, table_list, select_lex->leaf_tables,
+        FALSE, UPDATE_ACL, SELECT_ACL, FALSE))
+    DBUG_RETURN(1);
 
-  if (lex->select_lex.handle_derived(thd->lex, DT_MERGE))  
-    DBUG_RETURN(TRUE);
+  if (select_lex->handle_derived(thd->lex, DT_MERGE))
+    DBUG_RETURN(1);
 
+  List<Item> *fields= &lex->select_lex.item_list;
   if (setup_fields_with_no_wrap(thd, 0, *fields, MARK_COLUMNS_WRITE, 0, 0))
-    DBUG_RETURN(TRUE);
+    DBUG_RETURN(1);
 
   for (tl= table_list; tl ; tl= tl->next_local)
-  {
     if (tl->view)
     {
-      update_view= 1;
-      break;
+      if (check_fields(thd, *fields))
+        DBUG_RETURN(1);
+      else
+        break;
     }
-  }
-
-  if (update_view && check_fields(thd, *fields))
-  {
-    DBUG_RETURN(TRUE);
-  }
-
-  thd->table_map_for_update= tables_for_update= get_table_map(fields);
 
-  if (unsafe_key_update(lex->select_lex.leaf_tables, tables_for_update))
-    DBUG_RETURN(true);
+  table_map tables_for_update= thd->table_map_for_update= get_table_map(fields);
 
-  TABLE_LIST **new_tables= lex->query_tables_last;
-  DBUG_ASSERT(*new_tables== NULL);
+  if (unsafe_key_update(select_lex->leaf_tables, tables_for_update))
+    DBUG_RETURN(1);
 
   /*
     Setup timestamp handling and locking mode
   */
-  List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables);
+  List_iterator<TABLE_LIST> ti(select_lex->leaf_tables);
+  const bool using_lock_tables= thd->locked_tables_mode != LTM_NONE;
   while ((tl= ti++))
   {
     TABLE *table= tl->table;
@@ -1477,7 +1452,7 @@ int mysql_multi_update_prepare(THD *thd)
       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);
+        DBUG_RETURN(1);
       }
 
       DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
@@ -1488,8 +1463,8 @@ int mysql_multi_update_prepare(THD *thd)
       tl->updating= 1;
       if (tl->belong_to_view)
         tl->belong_to_view->updating= 1;
-      if (extend_table_list(thd, tl, &prelocking_strategy, has_prelocking_list))
-        DBUG_RETURN(TRUE);
+      if (extend_table_list(thd, tl, this, has_prelocking_list))
+        DBUG_RETURN(1);
     }
     else
     {
@@ -1521,19 +1496,6 @@ int mysql_multi_update_prepare(THD *thd)
     through all leaf tables but also through all view hierarchy.
   */
 
-  uint addon_table_count= 0;
-  if (*new_tables)
-  {
-    Sroutine_hash_entry **new_routines= thd->lex->sroutines_list.next;
-    DBUG_ASSERT(*new_routines == NULL);
-    if (open_tables(thd, thd->lex->create_info, new_tables,
-                    &addon_table_count, new_routines,
-                    thd->stmt_arena->is_stmt_prepare()
-                    ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
-                    &prelocking_strategy))
-      DBUG_RETURN(TRUE);
-  }
-
   for (tl= table_list; tl; tl= tl->next_local)
   {
     bool not_used= false;
@@ -1546,23 +1508,67 @@ int mysql_multi_update_prepare(THD *thd)
   /* check single table update for view compound from several tables */
   for (tl= table_list; tl; tl= tl->next_local)
   {
+    TABLE_LIST *for_update= 0;
     if (tl->is_jtbm())
       continue;
-    if (tl->is_merged_derived())
+    if (tl->is_merged_derived() &&
+        tl->check_single_table(&for_update, tables_for_update, tl))
     {
-      TABLE_LIST *for_update= 0;
-      if (tl->check_single_table(&for_update, tables_for_update, tl))
-      {
-	my_error(ER_VIEW_MULTIUPDATE, MYF(0),
-		 tl->view_db.str, tl->view_name.str);
-	DBUG_RETURN(-1);
-      }
+      my_error(ER_VIEW_MULTIUPDATE, MYF(0), tl->view_db.str, tl->view_name.str);
+      DBUG_RETURN(-1);
     }
   }
 
+  DBUG_RETURN(0);
+}
+
+/*
+  make update specific preparation and checks after opening tables
+
+  SYNOPSIS
+    mysql_multi_update_prepare()
+    thd         thread handler
+
+  RETURN
+    FALSE OK
+    TRUE  Error
+*/
+
+int mysql_multi_update_prepare(THD *thd)
+{
+  LEX *lex= thd->lex;
+  TABLE_LIST *table_list= lex->query_tables;
+  TABLE_LIST *tl;
+  Multiupdate_prelocking_strategy prelocking_strategy;
+  uint table_count= lex->table_count;
+  DBUG_ENTER("mysql_multi_update_prepare");
+
+  /*
+    Open tables and create derived ones, but do not lock and fill them yet.
+
+    During prepare phase acquire only S metadata locks instead of SW locks to
+    keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE
+    and global read lock.
+  */
+  if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI)
+  {
+    if (open_tables(thd, &table_list, &table_count,
+        thd->stmt_arena->is_stmt_prepare() ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
+        &prelocking_strategy))
+      DBUG_RETURN(TRUE);
+  }
+  else
+  {
+    /* following need for prepared statements, to run next time multi-update */
+    thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
+    prelocking_strategy.reset(thd);
+    if (prelocking_strategy.handle_end(thd))
+      DBUG_RETURN(TRUE);
+  }
+
   /* now lock and fill tables */
   if (!thd->stmt_arena->is_stmt_prepare() &&
-      lock_tables(thd, table_list, table_count + addon_table_count, 0))
+      lock_tables(thd, table_list, table_count, 0))
   {
     DBUG_RETURN(TRUE);
   }
@@ -1575,7 +1581,7 @@ int mysql_multi_update_prepare(THD *thd)
   */
   lex->select_lex.exclude_from_table_unique_test= TRUE;
   /* We only need SELECT privilege for columns in the values list */
-  ti.rewind();
+  List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables);
   while ((tl= ti++))
   {
     if (tl->is_jtbm())
@@ -1618,25 +1624,18 @@ int mysql_multi_update_prepare(THD *thd)
   Setup multi-update handling and call SELECT to do the join
 */
 
-bool mysql_multi_update(THD *thd,
-                        TABLE_LIST *table_list,
-                        List<Item> *fields,
-                        List<Item> *values,
-                        COND *conds,
-                        ulonglong options,
+bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields,
+                        List<Item> *values, COND *conds, ulonglong options,
                         enum enum_duplicates handle_duplicates,
-                        bool ignore,
-                        SELECT_LEX_UNIT *unit,
-                        SELECT_LEX *select_lex,
-                        multi_update **result)
+                        bool ignore, SELECT_LEX_UNIT *unit,
+                        SELECT_LEX *select_lex, multi_update **result)
 {
   bool res;
   DBUG_ENTER("mysql_multi_update");
-  
+
   if (!(*result= new (thd->mem_root) multi_update(thd, table_list,
                                  &thd->lex->select_lex.leaf_tables,
-                                 fields, values,
-                                 handle_duplicates, ignore)))
+                                 fields, values, handle_duplicates, ignore)))
   {
     DBUG_RETURN(TRUE);
   }
@@ -1644,11 +1643,9 @@ bool mysql_multi_update(THD *thd,
   thd->abort_on_warning= !ignore && thd->is_strict_mode();
   List<Item> total_list;
 
-  res= mysql_select(thd, &select_lex->ref_pointer_array,
-                    table_list, select_lex->with_wild,
-                    total_list,
-                    conds, 0, (ORDER *) NULL, (ORDER *)NULL, (Item *) NULL,
-                    (ORDER *)NULL,
+  res= mysql_select(thd, &select_lex->ref_pointer_array, table_list,
+                    select_lex->with_wild, total_list, conds, 0, NULL, NULL,
+                    NULL, NULL,
                     options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
                     OPTION_SETUP_TABLES_DONE,
                     *result, unit, select_lex);