EXPLAIN DELETE for MariaDB

- Backported the code to 10.0-base
- Removed incorrect assert

13 files changed, 551 insertions, 84 deletions

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index da132a102e2..8baf4d40e7c 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -192,16 +192,24 @@ ERROR HY000: Target is not running an EXPLAINable command
 drop table t2;
 set debug_dbug=@old_debug;
 #
-# Attempt SHOW EXPLAIN for a DELETE
+# Attempt SHOW EXPLAIN for a DELETE (UPD: now works)
 # 
 create table t2 as select a as a, a as dummy from t0 limit 2;
 set @show_explain_probe_select_id=2;
 set debug_dbug='+d,show_explain_probe_join_exec_start';
 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
 show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
 show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Query plan already deleted
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
 drop table t2;
 set debug_dbug=@old_debug;
 #
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 57d86ece4f4..d87bf2f14d4 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -249,7 +249,7 @@ drop table t2;
 set debug_dbug=@old_debug;
 
 --echo #
---echo # Attempt SHOW EXPLAIN for a DELETE
+--echo # Attempt SHOW EXPLAIN for a DELETE (UPD: now works)
 --echo # 
 create table t2 as select a as a, a as dummy from t0 limit 2;
 set @show_explain_probe_select_id=2;
@@ -257,10 +257,10 @@ set debug_dbug='+d,show_explain_probe_join_exec_start';
 send delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
 connection default;
 --source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+#--error ER_TARGET_NOT_EXPLAINABLE
 evalp show explain for $thr2;
 --source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+#--error ER_TARGET_NOT_EXPLAINABLE
 evalp show explain for $thr2;
 connection con1;
 reap;
diff --git a/sql/my_apc.h b/sql/my_apc.h
index 7f19809c082..ee94595e78f 100644
--- a/sql/my_apc.h
+++ b/sql/my_apc.h
@@ -64,6 +64,8 @@ public:
   {
     return test(apc_calls);
   }
+
+  inline bool is_enabled() { return enabled; }
   
   /* Functor class for calls you can schedule */
   class Apc_call
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 4bcb62ef764..d7a612f3d56 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -40,6 +40,129 @@
 #include "records.h"                            // init_read_record,
 #include "sql_derived.h"                        // mysql_handle_list_of_derived
                                                 // end_read_record
+
+
+/*
+  @brief
+    Print query plan of a single-table DELETE command
+  
+  @detail
+    This function is used by EXPLAIN DELETE and by SHOW EXPLAIN when it is
+    invoked on a running DELETE statement.
+*/
+
+int Delete_plan::print_explain(select_result_sink *output, uint8 explain_flags, 
+                               bool *printed_anything)
+{
+  if (deleting_all_rows || impossible_where)
+  {
+    const char *msg= deleting_all_rows? "Deleting all rows": "Impossible where";
+    if (print_explain_message_line(output, explain_flags, 1/*select number*/, 
+                                   "SIMPLE", msg))
+    {
+      return 1;
+    }
+    *printed_anything= true;
+    return 0;
+  }
+
+  select_lex->set_explain_type(FALSE);
+  /* 
+    Print an EXPLAIN line. We dont have join, so we can't directly use
+    JOIN::print_explain.
+    We do have a SELECT_LEX (TODO but how is it useful? it has select_type..
+                             and that's it?)
+  */
+
+  enum join_type jtype;
+  if (select && select->quick)
+  {
+    int quick_type= select->quick->get_type();
+    if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
+        (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) ||
+        (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
+        (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
+      jtype= JT_INDEX_MERGE;
+    else
+      jtype= JT_RANGE;
+  }
+  else
+  {
+    if (index == MAX_KEY)
+      jtype= JT_ALL;
+    else
+      jtype= JT_NEXT;
+  }
+
+  StringBuffer<128> possible_keys_line;
+  make_possible_keys_line(table, possible_keys, &possible_keys_line);
+
+  const char *key_name;
+  const char *key_len;
+
+  StringBuffer<128> key_str;
+  StringBuffer<128> key_len_str;
+  StringBuffer<128> extra_str;
+
+  /* Calculate key_len */
+  if (select && select->quick)
+  {
+    select->quick->add_keys_and_lengths(&key_str, &key_len_str);
+    key_name= key_str.c_ptr();
+    key_len= key_len_str.c_ptr();
+  }
+  else
+  {
+    key_name= (index == MAX_KEY)? NULL : table->key_info[index].name;
+    key_len= NULL;
+  }
+ 
+  if (select && select->cond)
+    extra_str.append(STRING_WITH_LEN("Using where"));
+  if (select && select->quick && 
+      select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
+  {
+    explain_append_mrr_info((QUICK_RANGE_SELECT*)select->quick, &extra_str);
+  }
+
+  if (using_filesort)
+  {
+    if (extra_str.length() !=0)
+      extra_str.append(STRING_WITH_LEN("; "));
+    extra_str.append(STRING_WITH_LEN("Using filesort"));
+  }
+
+  /* 
+    Single-table DELETE commands do not do "Using temporary".
+    "Using index condition" is also not possible (which is an unjustified limitation)
+  */
+
+  print_explain_row(output, explain_flags, 
+                    1, /* id */
+                    select_lex->type,
+                    table->pos_in_table_list->alias, 
+                    // partitions,
+                    jtype,
+                    possible_keys_line.length()? possible_keys_line.c_ptr(): NULL,
+                    key_name,
+                    key_len,
+                    NULL, /* 'ref' is always NULL in single-table EXPLAIN DELETE */
+                    select ? select->records : table_rows,
+                    extra_str.c_ptr());
+
+  *printed_anything= true;
+ 
+  for (SELECT_LEX_UNIT *unit= select_lex->first_inner_unit();
+       unit;
+       unit= unit->next_unit())
+  {
+    if (unit->print_explain(output, explain_flags, printed_anything))
+      return 1;
+  }
+  return 0;
+}
+
+
 /**
   Implement DELETE SQL word.
 
@@ -61,12 +184,16 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   bool          const_cond_result;
   ha_rows	deleted= 0;
   bool          reverse= FALSE;
+  bool          err= true;
   ORDER *order= (ORDER *) ((order_list && order_list->elements) ?
                            order_list->first : NULL);
-  uint usable_index= MAX_KEY;
   SELECT_LEX   *select_lex= &thd->lex->select_lex;
   killed_state killed_status= NOT_KILLED;
   THD::enum_binlog_query_type query_type= THD::ROW_QUERY_TYPE;
+
+  Delete_plan query_plan;
+  query_plan.index= MAX_KEY;
+  query_plan.using_filesort= FALSE;
   DBUG_ENTER("mysql_delete");
 
   if (open_and_lock_tables(thd, table_list, TRUE, 0))
@@ -90,6 +217,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   }
   thd_proc_info(thd, "init");
   table->map=1;
+  query_plan.select_lex= &thd->lex->select_lex;
+  query_plan.table= table;
 
   if (mysql_prepare_delete(thd, table_list, &conds))
     DBUG_RETURN(TRUE);
@@ -163,6 +292,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
     table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
     ha_rows const maybe_deleted= table->file->stats.records;
     DBUG_PRINT("debug", ("Trying to use delete_all_rows()"));
+
+    query_plan.set_delete_all_rows(maybe_deleted);
+    if (thd->lex->describe)
+      goto exit_without_my_ok;
+
     if (!(error=table->file->ha_delete_all_rows()))
     {
       /*
@@ -187,7 +321,12 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
     Item::cond_result result;
     conds= remove_eq_conds(thd, conds, &result);
     if (result == Item::COND_FALSE)             // Impossible where
+    {
       limit= 0;
+      query_plan.set_impossible_where();
+      if (thd->lex->describe)
+        goto exit_without_my_ok;
+    }
   }
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
@@ -195,6 +334,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   {
     free_underlaid_joins(thd, select_lex);
     // No matching record
+    //psergey-explain-todo: No-partitions used EXPLAIN here..
     my_ok(thd, 0);
     DBUG_RETURN(0);
   }
@@ -211,6 +351,10 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
     DBUG_RETURN(TRUE);
   if ((select && select->check_quick(thd, safe_update, limit)) || !limit)
   {
+    query_plan.set_impossible_where();
+    if (thd->lex->describe)
+      goto exit_without_my_ok;
+
     delete select;
     free_underlaid_joins(thd, select_lex);
     /* 
@@ -243,26 +387,46 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
 
   if (order)
   {
-    uint         length= 0;
-    SORT_FIELD  *sortorder;
-    ha_rows examined_rows;
-    ha_rows found_rows;
-    
     table->update_const_key_parts(conds);
     order= simple_remove_const(order, conds);
 
-    bool need_sort;
     if (select && select->quick && select->quick->unique_key_range())
     { // Single row select (always "ordered")
-      need_sort= FALSE;
-      usable_index= MAX_KEY;
+      query_plan.using_filesort= FALSE;
+      query_plan.index= MAX_KEY;
     }
     else
-      usable_index= get_index_for_order(order, table, select, limit,
-                                        &need_sort, &reverse);
-    if (need_sort)
+      query_plan.index= get_index_for_order(order, table, select, limit,
+                                            &query_plan.using_filesort, 
+                                            &reverse);
+  }
+
+  query_plan.select= select;
+  query_plan.possible_keys= table->quick_keys;
+  query_plan.table_rows= table->file->stats.records;
+  thd->lex->delete_plan= &query_plan;
+  
+  /*
+    Ok, we have generated a query plan for the DELETE.
+     - if we're running EXPLAIN DELETE, goto produce explain output 
+     - otherwise, execute the query plan
+  */
+  if (thd->lex->describe)
+    goto exit_without_my_ok;
+
+  thd->apc_target.enable();
+  DBUG_EXECUTE_IF("show_explain_probe_delete_exec_start", 
+                  dbug_serve_apcs(thd, 1););
+
+  if (query_plan.using_filesort)
+  {
+    ha_rows examined_rows;
+    ha_rows found_rows;
+    uint         length= 0;
+    SORT_FIELD  *sortorder;
+
     {
-      DBUG_ASSERT(usable_index == MAX_KEY);
+      DBUG_ASSERT(query_plan.index == MAX_KEY);
       table->sort.io_cache= (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
                                                    MYF(MY_FAE | MY_ZEROFILL |
                                                        MY_THREAD_SPECIFIC));
@@ -276,6 +440,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
       {
         delete select;
         free_underlaid_joins(thd, &thd->lex->select_lex);
+        thd->apc_target.disable();
         DBUG_RETURN(TRUE);
       }
       thd->examined_row_count+= examined_rows;
@@ -294,19 +459,21 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   {
     delete select;
     free_underlaid_joins(thd, select_lex);
+    thd->apc_target.disable();
     DBUG_RETURN(TRUE);
   }
-  if (usable_index == MAX_KEY || (select && select->quick))
+  if (query_plan.index == MAX_KEY || (select && select->quick))
   {
     if (init_read_record(&info, thd, table, select, 1, 1, FALSE))
     {
       delete select;
       free_underlaid_joins(thd, select_lex);
+      thd->apc_target.disable();
       DBUG_RETURN(TRUE);
     }
   }
   else
-    init_read_record_idx(&info, thd, table, 1, usable_index, reverse);
+    init_read_record_idx(&info, thd, table, 1, query_plan.index, reverse);
 
   init_ftfuncs(thd, select_lex, 1);
   thd_proc_info(thd, "updating");
@@ -398,6 +565,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   if (options & OPTION_QUICK)
     (void) table->file->extra(HA_EXTRA_NORMAL);
 
+  thd->apc_target.disable();
 cleanup:
   /*
     Invalidate the table in the query cache if something changed. This must
@@ -458,6 +626,29 @@ cleanup:
     DBUG_PRINT("info",("%ld records deleted",(long) deleted));
   }
   DBUG_RETURN(error >= 0 || thd->is_error());
+  
+  /* Special exits */
+exit_without_my_ok:
+  thd->lex->delete_plan= &query_plan;
+  
+  select_send *result;
+  bool printed_anything;
+  if (!(result= new select_send()))
+    return 1;                               /* purecov: inspected */
+  List<Item> dummy; /* note: looked in 5.6 and they too use a dummy list like this */
+  result->prepare(dummy, &thd->lex->unit);
+  thd->send_explain_fields(result);
+  int err2= thd->lex->print_explain(result, 0 /* explain flags*/, &printed_anything);
+
+  if (err2)
+    result->abort_result_set();
+  else
+    result->send_eof();
+
+  delete select;
+  free_underlaid_joins(thd, select_lex);
+  //table->set_keyread(false);
+  DBUG_RETURN((err || thd->is_error() || thd->killed) ? 1 : 0);
 }
 
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b8ce3b6720e..74e4b3e1162 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -448,6 +448,7 @@ void lex_start(THD *thd)
 
   lex->thd= lex->unit.thd= thd;
 
+  lex->delete_plan= NULL;
   lex->context_stack.empty();
   lex->unit.init_query();
   lex->unit.init_select();
@@ -2557,6 +2558,7 @@ LEX::LEX()
                          INITIAL_LEX_PLUGIN_LIST_SIZE, 0);
   reset_query_tables_list(TRUE);
   mi.init();
+  delete_plan= NULL;
 }
 
 
@@ -4166,12 +4168,17 @@ bool st_select_lex::is_merged_child_of(st_select_lex *ancestor)
   return all_merged;
 }
 
-
-int print_explain_message_line(select_result_sink *result, 
-                               SELECT_LEX *select_lex,
-                               bool on_the_fly,
-                               uint8 options,
-                               const char *message);
+int LEX::print_explain(select_result_sink *output, uint8 explain_flags,
+                       bool *printed_anything)
+{
+  if (delete_plan)
+  {
+    delete_plan->print_explain(output, explain_flags, printed_anything);
+    return 0;
+  }
+  int res= unit.print_explain(output, explain_flags, printed_anything);
+  return res;
+}
 
 
 int st_select_lex::print_explain(select_result_sink *output, 
@@ -4235,8 +4242,9 @@ int st_select_lex::print_explain(select_result_sink *output,
       DBUG_ASSERT(join->have_query_plan == JOIN::QEP_DELETED);
       msg= "Query plan already deleted";
     }
-    res= print_explain_message_line(output, this, TRUE /* on_the_fly */,
-                                    0, msg);
+    set_explain_type(TRUE/* on_the_fly */);
+    res= print_explain_message_line(output, 0/*options*/, select_number, type,
+                                    msg);
   }
 err:
   return res;
@@ -4256,9 +4264,10 @@ int st_select_lex_unit::print_explain(select_result_sink *output,
       EXPLAIN state" error.
     */
     const char *msg="Query plan already deleted";
-    res= print_explain_message_line(output, first, TRUE /* on_the_fly */,
-                                    0, msg);
-    return 0;
+    first->set_explain_type(TRUE/* on_the_fly */);
+    res= print_explain_message_line(output, 0/*options*/, first->select_number,
+                                    first->type, msg);
+    return res;
   }
 
   for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index be7cdd8ca1d..af35ccb01b0 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2344,6 +2344,48 @@ protected:
   LEX *m_lex;
 };
 
+class Delete_plan;
+class SQL_SELECT;
+
+/* Query plan of a single-table DELETE */
+class Delete_plan
+{
+  bool deleting_all_rows;
+  bool impossible_where;
+public:
+
+  TABLE *table;
+  SQL_SELECT *select;
+  uint index;
+  ha_rows table_rows; /* Use if select==NULL */
+  bool using_filesort;
+  key_map possible_keys;
+  
+  /* 
+    Top-level select_lex. Most of its fields are not used, we need it only to
+    get to the subqueries. 
+  */
+  SELECT_LEX *select_lex;
+
+  /* Construction functions */
+  Delete_plan() : 
+    deleting_all_rows(false), impossible_where(false), using_filesort(false) {}
+
+  /* Set this query plan to be a plan to make a call to h->delete_all_rows() */
+  void set_delete_all_rows(ha_rows rows_arg) 
+  { 
+    deleting_all_rows= true;
+    table_rows= rows_arg;
+  }
+
+  /* Set this plan to be a plan to do nothing because of impossible WHRE*/
+  void set_impossible_where() { impossible_where= true; }
+
+  int print_explain(select_result_sink *output, uint8 explain_flags, 
+                    bool *printed_anything);
+};
+
+
 /* The state of the lex parsing. This is saved in the THD struct */
 
 struct LEX: public Query_tables_list
@@ -2355,6 +2397,9 @@ struct LEX: public Query_tables_list
   /* list of all SELECT_LEX */
   SELECT_LEX *all_selects_list;
 
+  /* For single-table DELETE: its query plan */
+  Delete_plan *delete_plan;
+
   char *length,*dec,*change;
   LEX_STRING name;
   char *help_arg;
@@ -2769,6 +2814,9 @@ struct LEX: public Query_tables_list
     }
     return FALSE;
   }
+
+  int print_explain(select_result_sink *output, uint8 explain_flags,
+                    bool *printed_anything);
 };
 
 
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 0abb249d97b..d5fd5c1b32d 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3212,7 +3212,8 @@ end_with_restore_list:
   {
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first;
-    multi_delete *del_result;
+    bool explain= test(lex->describe);
+    select_result *result;
 
     if ((res= multi_delete_precheck(thd, all_tables)))
       break;
@@ -3227,37 +3228,72 @@ end_with_restore_list:
     if ((res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
       break;
 
-    MYSQL_MULTI_DELETE_START(thd->query());
+    if (!explain)
+    {
+      MYSQL_MULTI_DELETE_START(thd->query());
+    }
+
     if ((res= mysql_multi_delete_prepare(thd)))
     {
-      MYSQL_MULTI_DELETE_DONE(1, 0);
+      if (!explain)
+      {
+        MYSQL_MULTI_DELETE_DONE(1, 0);
+      }
       goto error;
     }
 
-    if (!thd->is_fatal_error &&
-        (del_result= new multi_delete(aux_tables, lex->table_count)))
-    {
-      res= mysql_select(thd, &select_lex->ref_pointer_array,
-			select_lex->get_table_list(),
-			select_lex->with_wild,
-			select_lex->item_list,
-			select_lex->where,
-			0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL,
-			(ORDER *)NULL,
-			(select_lex->options | thd->variables.option_bits |
-			SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
-                        OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT,
-			del_result, unit, select_lex);
-      res|= thd->is_error();
-      MYSQL_MULTI_DELETE_DONE(res, del_result->num_deleted());
-      if (res)
-        del_result->abort_result_set();
-      delete del_result;
+    if (!thd->is_fatal_error)
+    {
+      if (explain)
+      {
+        result= new select_send();
+        if (thd->send_explain_fields(result))
+        {
+          delete result;
+          result= NULL;
+        }
+        select_lex->set_explain_type(FALSE);
+      }
+      else
+        result= new multi_delete(aux_tables, lex->table_count);
+
+      if (result)
+      {
+        res= mysql_select(thd, &select_lex->ref_pointer_array,
+                          select_lex->get_table_list(),
+                          select_lex->with_wild,
+                          select_lex->item_list,
+                          select_lex->where,
+                          0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL,
+                          (ORDER *)NULL,
+                          (select_lex->options | thd->variables.option_bits |
+                          SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
+                          OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT,
+                          result, unit, select_lex);
+        res|= thd->is_error();
+
+        if (!explain)
+        {
+          MYSQL_MULTI_DELETE_DONE(res, del_result->num_deleted());
+        }
+      
+        if (res)
+          result->abort_result_set(); /* for both DELETE and EXPLAIN DELETE */
+        else
+        {
+          if (explain)
+            result->send_eof(); 
+        }
+        delete result;
+      }
     }
     else
     {
       res= TRUE;                                // Error
-      MYSQL_MULTI_DELETE_DONE(1, 0);
+      if (!explain)
+      {
+        MYSQL_MULTI_DELETE_DONE(1, 0);
+      }
     }
     break;
   }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 41c0b07bc49..230355c550e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -21594,23 +21594,20 @@ void JOIN::clear()
 /*
   Print an EXPLAIN line with all NULLs and given message in the 'Extra' column
 */
+
 int print_explain_message_line(select_result_sink *result, 
-                               SELECT_LEX *select_lex,
-                               bool on_the_fly,
                                uint8 options,
+                               uint select_number,
+                               const char *select_type,
                                const char *message)
 {
   const CHARSET_INFO *cs= system_charset_info;
   Item *item_null= new Item_null();
   List<Item> item_list;
 
-  if (on_the_fly)
-    select_lex->set_explain_type(on_the_fly);
-
-  item_list.push_back(new Item_int((int32)
-                                   select_lex->select_number));
-  item_list.push_back(new Item_string(select_lex->type,
-                                      strlen(select_lex->type), cs));
+  item_list.push_back(new Item_int((int32) select_number));
+  item_list.push_back(new Item_string(select_type,
+                                      strlen(select_type), cs));
   for (uint i=0 ; i < 7; i++)
     item_list.push_back(item_null);
   if (options & DESCRIBE_PARTITIONS)
@@ -21626,6 +21623,104 @@ int print_explain_message_line(select_result_sink *result,
 }
 
 
+/*
+  Make a comma-separated list of possible_keys names and add it into the string
+*/ 
+
+void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line)
+{
+  if (!possible_keys.is_clear_all())
+  {
+    uint j;
+    for (j=0 ; j < table->s->keys ; j++)
+    {
+      if (possible_keys.is_set(j))
+      {
+        if (line->length())
+          line->append(',');
+        line->append(table->key_info[j].name, 
+                     strlen(table->key_info[j].name),
+                     system_charset_info);
+      }
+    }
+  }
+}
+
+/*
+  Print an EXPLAIN output row, based on information provided in the parameters
+
+  @note
+    Parameters that may have NULL value in EXPLAIN output, should be passed
+    (char*)NULL.
+
+  @return 
+    0  - OK
+    1  - OOM Error
+*/
+
+int print_explain_row(select_result_sink *result,
+                      uint8 options,
+                      uint select_number,
+                      const char *select_type,
+                      const char *table_name,
+                      //const char *partitions, (todo)
+                      enum join_type jtype,
+                      const char *possible_keys,
+                      const char *index,
+                      const char *key_len,
+                      const char *ref,
+                      ha_rows rows,
+                      const char *extra)
+{
+  const CHARSET_INFO *cs= system_charset_info;
+  Item *item_null= new Item_null();
+  List<Item> item_list;
+  Item *item;
+
+  item_list.push_back(new Item_int((int32) select_number));
+  item_list.push_back(new Item_string(select_type,
+                                      strlen(select_type), cs));
+  item_list.push_back(new Item_string(table_name,
+                                      strlen(table_name), cs));
+  if (options & DESCRIBE_PARTITIONS)
+    item_list.push_back(item_null); // psergey-todo: produce proper value
+  
+  const char *jtype_str= join_type_str[jtype];
+  item_list.push_back(new Item_string(jtype_str,
+                                      strlen(jtype_str), cs));
+  
+  item= possible_keys? new Item_string(possible_keys, strlen(possible_keys),
+                                      cs) : item_null;
+  item_list.push_back(item);
+  
+  /* 'index */
+  item= index ? new Item_string(index, strlen(index), cs) : item_null;
+  item_list.push_back(item);
+  
+  /* 'key_len */
+  item= key_len ? new Item_string(key_len, strlen(key_len), cs) : item_null;
+  item_list.push_back(item);
+  
+  /* 'ref' */
+  item= ref ? new Item_string(ref, strlen(ref), cs) : item_null;
+  item_list.push_back(item);
+
+  /* 'rows' */
+  item_list.push_back(new Item_int(rows, 
+                                   MY_INT64_NUM_DECIMAL_DIGITS));
+  /* 'filtered' */
+  if (options & DESCRIBE_EXTENDED)
+    item_list.push_back(item_null);
+  
+  /* 'Extra' */
+  item_list.push_back(new Item_string(extra, strlen(extra), cs));
+
+  if (result->send_data(item_list))
+    return 1;
+  return 0;
+}
+
+
 int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
                                SELECT_LEX *select_lex, uint8 explain_flags)
 {
@@ -21705,6 +21800,26 @@ int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
 }
 
 
+/*
+  Append MRR information from quick select to the given string
+*/
+
+void explain_append_mrr_info(QUICK_RANGE_SELECT *quick, String *res)
+{
+  char mrr_str_buf[128];
+  mrr_str_buf[0]=0;
+  int len;
+  handler *h= quick->head->file;
+  len= h->multi_range_read_explain_info(quick->mrr_flags, mrr_str_buf,
+                                        sizeof(mrr_str_buf));
+  if (len > 0)
+  {
+    res->append(STRING_WITH_LEN("; "));
+    res->append(mrr_str_buf, len);
+  }
+}
+
+
 /**
   EXPLAIN handling.
 
@@ -21746,8 +21861,12 @@ int JOIN::print_explain(select_result_sink *result, uint8 explain_flags,
   */
   if (message)
   {
-    if (print_explain_message_line(result, join->select_lex, on_the_fly, 
-                                   explain_flags, message))
+    if (on_the_fly)
+      join->select_lex->set_explain_type(on_the_fly);
+
+    if (print_explain_message_line(result, explain_flags,
+                                   join->select_lex->select_number,
+                                   join->select_lex->type, message))
       error= 1;
 
   }
@@ -21762,7 +21881,7 @@ int JOIN::print_explain(select_result_sink *result, uint8 explain_flags,
            join->select_lex->master_unit()->derived->is_materialized_derived())
   {
     table_map used_tables=0;
-    //if (!join->select_lex->type)
+
     if (on_the_fly)
       join->select_lex->set_explain_type(on_the_fly);
 
@@ -22171,19 +22290,8 @@ int JOIN::print_explain(select_result_sink *result, uint8 explain_flags,
         */
         if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE)
         {
-          char mrr_str_buf[128];
-          mrr_str_buf[0]=0;
-          int len;
-          uint mrr_flags= 
-            ((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags;
-          len= table->file->multi_range_read_explain_info(mrr_flags,
-                                                          mrr_str_buf,
-                                                          sizeof(mrr_str_buf));
-          if (len > 0)
-          {
-            extra.append(STRING_WITH_LEN("; "));
-            extra.append(mrr_str_buf, len);
-          }
+          explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick),
+                                  &extra);
         }
 
 	if (need_tmp_table)
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 638de926d75..61a72357d84 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1804,6 +1804,28 @@ void eliminate_tables(JOIN *join);
 /* Index Condition Pushdown entry point function */
 void push_index_cond(JOIN_TAB *tab, uint keyno);
 
+/* EXPLAIN-related utility functions */
+int print_explain_message_line(select_result_sink *result, 
+                               uint8 options,
+                               uint select_number,
+                               const char *select_type,
+                               const char *message);
+void explain_append_mrr_info(QUICK_RANGE_SELECT *quick, String *res);
+int print_explain_row(select_result_sink *result,
+                      uint8 options,
+                      uint select_number,
+                      const char *select_type,
+                      const char *table_name,
+                      //const char *partitions, (todo)
+                      enum join_type jtype,
+                      const char *possible_keys,
+                      const char *index,
+                      const char *key_len,
+                      const char *ref,
+                      ha_rows rows,
+                      const char *extra);
+void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line);
+
 /****************************************************************************
   Temporary table support for SQL Runtime
  ***************************************************************************/
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 095ad409a94..f3bb14b6e9f 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2337,8 +2337,8 @@ void Show_explain_request::call_in_target_thread()
 
   DBUG_ASSERT(current_thd == target_thd);
   set_current_thd(request_thd);
-  if (target_thd->lex->unit.print_explain(explain_buf, 0 /* explain flags*/,
-                                          &printed_anything))
+  if (target_thd->lex->print_explain(explain_buf, 0 /* explain flags*/,
+                                     &printed_anything))
   {
     failed_to_produce= TRUE;
   }
diff --git a/sql/sql_string.h b/sql/sql_string.h
index 58cda343dac..e08b7a2581f 100644
--- a/sql/sql_string.h
+++ b/sql/sql_string.h
@@ -500,6 +500,38 @@ public:
   }
 };
 
+
+// The following class is a backport from MySQL 5.6:
+/**
+  String class wrapper with a preallocated buffer of size buff_sz
+
+  This class allows to replace sequences of:
+     char buff[12345];
+     String str(buff, sizeof(buff));
+     str.length(0);
+  with a simple equivalent declaration:
+     StringBuffer<12345> str;
+*/
+
+template<size_t buff_sz>
+class StringBuffer : public String
+{
+  char buff[buff_sz];
+
+public:
+  StringBuffer() : String(buff, buff_sz, &my_charset_bin) { length(0); }
+  explicit StringBuffer(const CHARSET_INFO *cs) : String(buff, buff_sz, cs)
+  {
+    length(0);
+  }
+  StringBuffer(const char *str, size_t length, const CHARSET_INFO *cs)
+    : String(buff, buff_sz, cs)
+  {
+    set(str, length, cs);
+  }
+};
+
+
 static inline bool check_if_only_end_space(CHARSET_INFO *cs,
                                            const char *str, 
                                            const char *end)
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 28b9fe7eacd..84adfc2c3f5 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1394,7 +1394,10 @@ bool mysql_multi_update(THD *thd,
   thd->abort_on_warning= test(thd->variables.sql_mode &
                               (MODE_STRICT_TRANS_TABLES |
                                MODE_STRICT_ALL_TABLES));
-
+/*  
+  psergey-explain-todo:
+   This is the place where EXPLAIN <multi-table-update> should be handled.
+*/
   List<Item> total_list;
 
   res= mysql_select(thd, &select_lex->ref_pointer_array,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 9664aad1e19..7bac9c8ded7 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -11881,13 +11881,21 @@ describe:
           opt_describe_column {}
         | describe_command opt_extended_describe
           { Lex->describe|= DESCRIBE_NORMAL; }
-          select
+          explanable_command
           {
             LEX *lex=Lex;
             lex->select_lex.options|= SELECT_DESCRIBE;
           }
         ;
 
+explanable_command:
+          select
+        | insert
+        | replace
+        | update
+        | delete
+        ;
+
 describe_command:
           DESC
         | DESCRIBE