Hi Gleb,
I approve the coding for this worklog.
Please consider the (rather few) suggestions that I have provided inline. They
should all be tagged with RL: at the front of the line.
=== modified file 'client/mysqltest.cc'
--- client/mysqltest.cc 2011-03-22 15:40:32 +0000
+++ client/mysqltest.cc 2011-06-16 12:58:35 +0000
@@ -201,6 +201,7 @@ static char *opt_plugin_dir= 0;
static my_regex_t ps_re; /* the query can be run using PS protocol */
static my_regex_t sp_re; /* the query can be run as a SP */
static my_regex_t view_re; /* the query can be run as a view*/
+static my_regex_t explain_re;/* the query can be converted to EXPLAIN */
static void init_re(void);
static int match_re(my_regex_t *, char *);
@@ -6389,7 +6390,7 @@ static struct my_option my_long_options[
{"view-protocol", OPT_VIEW_PROTOCOL, "Use views for select.",
&view_protocol, &view_protocol, 0,
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
- {"explain-protocol", OPT_EXPLAIN_PROTOCOL, "Explains all select.",
+ {"explain-protocol", OPT_EXPLAIN_PROTOCOL, "Explains all select/update/etc.",
RL: Suggestion: "Explain all DML statements"
=== modified file 'sql/CMakeLists.txt'
--- sql/CMakeLists.txt 2011-02-02 08:30:13 +0000
+++ sql/CMakeLists.txt 2011-06-16 12:58:35 +0000
@@ -72,6 +72,7 @@ SET(SQL_SHARED_SOURCES
mf_iocache.cc
my_decimal.cc
net_serv.cc
+ opt_explain.cc
opt_range.cc
opt_sum.cc
parse_file.cc
=== added file 'sql/opt_explain.cc'
--- sql/opt_explain.cc 1970-01-01 00:00:00 +0000
+++ sql/opt_explain.cc 2011-06-16 12:58:35 +0000
@@ -0,0 +1,1541 @@
+/* Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software Foundation,
+ 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
+
+/** @file "EXPLAIN <command>" implementation */
+
+#include "opt_explain.h"
+#include "sql_select.h"
+#include "sql_partition.h" // for make_used_partitions_str()
+
+static bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit,
+ select_result *result);
+
+/**
+ A base for all Explain_* classes
+
+ Explain_* classes collect and output EXPLAIN data.
+
+ This class hierarchy is a successor of the old select_describe() function of 5.5.
+*/
+
+class Explain
+{
+private:
+ List<Item> items; ///< item list to feed select_result::send_data()
+ Item_null *nil; ///< pre-allocated NULL item to fill empty columns in EXPLAIN
+
+protected:
+ /**
+ Helper class to keep string data in MEM_ROOT before passing to Item_string
+
+ Since Item_string constructors doesn't copy input string parameter data
+ in the most cases, those input strings must have the same lifetime as
+ Item_string objects, i.e. lifetime of MEM_ROOT.
+ This class allocates input parameters for Item_string objects in MEM_ROOT.
+ */
+ struct mem_root_str
+ {
+ THD * const thd;
RL: Is there a reason to use THD here? A mem_root pointer would probably make
more sense.
RL: I was told once to write "*const" instead of "* const". It seems to be the
current practice in e.g. sql_select.cc. But it is up to you.
+ const char *str;
+ size_t length;
+
+ mem_root_str(THD *thd_arg) : thd(thd_arg) { cleanup(); }
+ void cleanup()
+ {
+ str= NULL;
+ length= 0;
+ }
RL: Consider naming this function empty() instead of cleanup(). This name is
IMHO more describing.
+ bool nil() const { return str == NULL; }
RL: Is is_empty() a more descriptive name than nil()?
+
+ bool set(const char *str_arg)
+ {
+ return set(str_arg, strlen(str_arg));
+ }
+ bool set(const String &s)
+ {
+ return set(s.ptr(), s.length());
+ }
+ /**
+ Make a copy of the string in MEM_ROOT
+
+ @param str_arg string to copy
+ @param length_arg input string length
+
+ @return false if success, true if error
+ */
+ bool set(const char *str_arg, size_t length_arg)
+ {
+ if (!(str= static_cast<char *>(thd->memdup(str_arg, length_arg))))
+ return true; /* purecov: inspected */
+ length= length_arg;
+ return false;
+ }
+ /**
+ Make a copy of string constant
+
+ Variant of set() usable when the str_arg argument lives longer
+ than the mem_root_str instance.
+ */
+ void set_const(const char *str_arg)
+ {
+ str= str_arg;
+ length= strlen(str_arg);
+ }
+ };
+
+ /*
+ Next "col_*" fields are intended for the filling by "explain_*()" methods.
RL: The following "col_*" fields are intended to be filled by "explain_*()"
functions.
+ Then the make_list() method links these Items into "items" list.
RL: method -> function. This is applicable several places.
+
+ NOTE: NULL value or mem_root_str.nil()==true means that Item_null object
+ will be pushed into "items" list instead.
+ */
+ Item_uint *col_id; ///< "id" column: seq. number of SELECT withing the query
+ mem_root_str col_select_type; ///< "select_type" column
+ mem_root_str col_table_name; ///< "table" to which the row of output refers
+ mem_root_str col_partitions; ///< "partitions" column
+ mem_root_str col_join_type; ///< "type" column, see join_type_str array
+ mem_root_str col_possible_keys; ///< "possible_keys": comma-separated list
+ mem_root_str col_key; ///< "key" column: index that is actually decided to use
+ mem_root_str col_key_len; ///< "key_length" column: length of the "key" above
+ mem_root_str col_ref; ///< "ref":columns/constants which are compared to "key"
+ Item_int *col_rows; ///< "rows": estimated number of examined table rows
+ Item_float *col_filtered; ///< "filtered": % of rows filtered by condition
+ mem_root_str col_extra; ///< "extra" column: additional information
+
+ THD * const thd; ///< cached THD pointer
+ const CHARSET_INFO * const cs; ///< cached pointer to system_charset_info
+ JOIN * const join; ///< top-level JOIN (if any) provided by caller
+
+ select_result * const external_result; ///< stream (if any) provided by caller
+
+protected:
+ explicit Explain(THD *thd_arg, JOIN *join_arg= NULL)
+ : nil(NULL),
+ col_select_type(thd_arg),
+ col_table_name(thd_arg),
+ col_partitions(thd_arg),
+ col_join_type(thd_arg),
+ col_possible_keys(thd_arg),
+ col_key(thd_arg),
+ col_key_len(thd_arg),
+ col_ref(thd_arg),
+ col_extra(thd_arg),
+ thd(thd_arg),
+ cs(system_charset_info),
+ join(join_arg),
+ external_result(join ? join->result : NULL)
+ {
+ init_columns();
+ }
+ virtual ~Explain() {}
+
+public:
+ bool send();
+
+private:
+ void init_columns();
+ bool make_list();
+ bool push(Item *item) { return items.push_back(item ? item : nil); }
+ bool push(const mem_root_str &s)
+ {
+ if (s.nil())
+ return items.push_back(nil);
+ Item_string *item= new Item_string(s.str, s.length, cs);
+ return item == NULL || items.push_back(item);
+ }
+
+protected:
+ bool describe(uint8 mask) { return thd->lex->describe & mask; }
+
+ SELECT_LEX *select_lex() const
+ {
+ return join ? join->select_lex : &thd->lex->select_lex;
+ }
+
+ /**
+ Prepare the self-allocated result object
+
+ For queries with top-level JOIN the caller provides pre-allocated
+ select_send object. Then that JOIN object prepares the select_send
+ object calling result->prepare() in JOIN::prepare(),
+ result->initalize_tables() in JOIN::optimize() and result->prepare2()
+ in JOIN::exec().
+ However without the presence of the top-level JOIN we have to
+ prepare/initialize select_send object manually.
+ */
+ bool prepare(select_result *result)
+ {
+ DBUG_ASSERT(join == NULL);
+ List<Item> dummy;
+ return result->prepare(dummy, select_lex()->master_unit()) ||
+ result->prepare2();
+ }
+
+ virtual bool send_to(select_result *to);
+
+ /*
+ Rest of the methods are overloadable functions, those calculate and fill
+ "col_*" fields with Items for further sending as EXPLAIN columns.
+
+ "explain_*" methods return false on success and true on error (usually OOM).
+ */
+ virtual bool explain_id();
+ virtual bool explain_select_type();
+ virtual bool explain_table_name() { return false; }
+ virtual bool explain_partitions() { return false; }
+ virtual bool explain_join_type() { return false; }
+ virtual bool explain_possible_keys() { return false; }
+ /** fill col_key and and col_key_len fields together */
+ virtual bool explain_key_and_len() { return false; }
+ virtual bool explain_ref() { return false; }
+ /** fill col_rows and col_filtered fields together */
+ virtual bool explain_rows_and_filtered() { return false; }
+ virtual bool explain_extra();
+};
+
+
+/**
+ Explain_no_table class outputs a trivial EXPLAIN row with "extra" column
+
+ This class is intended for simple cases to produce EXPLAIN output
+ with "No tables used", "No matching records" etc.
+ Optionally it can output number of estimated rows in the "row"
+ column.
+
+ @note This class also produces EXPLAIN rows for inner units (if any).
+*/
+
+class Explain_no_table: public Explain
+{
+private:
+ const char *message; ///< cached "message" argument
+ const ha_rows rows; ///< HA_POS_ERROR or cached "rows" argument
+
+public:
+ Explain_no_table(THD *thd_arg, JOIN *join_arg, const char *message_arg)
+ : Explain(thd_arg, join_arg), message(message_arg), rows(HA_POS_ERROR)
+ {}
+
+ Explain_no_table(THD *thd_arg, const char *message_arg,
+ ha_rows rows_arg= HA_POS_ERROR)
+ : Explain(thd_arg), message(message_arg), rows(rows_arg)
+ {}
+
+protected:
+ virtual bool explain_rows_and_filtered();
+ virtual bool explain_extra();
+};
+
+
+/**
+ Explain_union class outputs EXPLAIN row for UNION
+*/
+
+class Explain_union : public Explain
+{
+public:
+ Explain_union(THD *thd_arg, JOIN *join_arg) : Explain(thd_arg, join_arg)
+ {
+ /* it's a UNION: */
+ DBUG_ASSERT(join_arg->select_lex == join_arg->unit->fake_select_lex);
+ }
+
+protected:
+ virtual bool explain_id();
+ virtual bool explain_table_name();
+ virtual bool explain_join_type();
+ virtual bool explain_extra();
+};
+
+
+
+/**
+ Common base class for Explain_join and Explain_table
+*/
+
+class Explain_table_base : public Explain {
+protected:
+ const TABLE *table;
+ key_map usable_keys;
+
+ Explain_table_base(THD * const thd_arg, JOIN * const join_arg)
+ : Explain(thd_arg, join_arg), table(NULL)
+ {}
+
+ Explain_table_base(THD * const thd_arg, TABLE * const table_arg)
+ : Explain(thd_arg), table(table_arg)
+ {}
+
+ virtual bool explain_partitions();
+ virtual bool explain_possible_keys();
+
+ bool explain_key_and_len_quick(const SQL_SELECT *select);
+ bool explain_key_and_len_index(int key);
+ bool explain_key_and_len_index(int key, uint key_length);
+ void explain_extra_common(const SQL_SELECT *select,
+ const JOIN_TAB *tab,
+ int quick_type,
+ uint keyno,
+ bool need_tmp_table,
+ bool need_sort,
+ String *str_extra);
+};
+
+
+/**
+ Explain_join class produces EXPLAIN output for JOINs
+*/
+
+class Explain_join : public Explain_table_base
+{
+private:
+ const bool need_tmp_table; ///< add "Using temporary" to "extra" if true
+ const bool need_order; ///< add "Using filesort"" to "extra" if true
+ const bool distinct; ///< add "Distinct" string to "extra" column if true
+
+ uint tabnum; ///< current tab number in join->join_tab[]
+ JOIN_TAB *tab; ///< current JOIN_TAB
+ int quick_type; ///< current quick type, see anon. enum at QUICK_SELECT_I
+ table_map used_tables; ///< accumulate used tables bitmap
+ uint last_sjm_table; ///< last materialized semi-joined table
+
+public:
+ Explain_join(THD *thd_arg, JOIN *join_arg,
+ bool need_tmp_table_arg, bool need_order_arg,
+ bool distinct_arg)
+ : Explain_table_base(thd_arg, join_arg), need_tmp_table(need_tmp_table_arg),
+ need_order(need_order_arg), distinct(distinct_arg),
+ tabnum(0), used_tables(0), last_sjm_table(MAX_TABLES)
+ {
+ /* it is not UNION: */
+ DBUG_ASSERT(join_arg->select_lex != join_arg->unit->fake_select_lex);
+ }
+
+protected:
+ virtual bool send_to(select_result *to);
+ virtual bool explain_table_name();
+ virtual bool explain_join_type();
+ virtual bool explain_key_and_len();
+ virtual bool explain_ref();
+ virtual bool explain_rows_and_filtered();
+ virtual bool explain_extra();
+};
+
+
+/**
+ Explain_table class produce EXPLAIN output for queries without top-level JOIN
+
+ This class is a simplified version of the Explain_join class. It works in the
+ context of queries which implementation lacks top-level JOIN object (EXPLAIN
+ single-table UPDATE and DELETE).
+*/
+
+class Explain_table: public Explain_table_base
+{
+private:
+ const SQL_SELECT * const select; ///< cached "select" argument
+ const uint key; ///< cached "key" number argument
+ const ha_rows limit; ///< HA_POS_ERROR or cached "limit" argument
+ const bool need_tmp_table; ///< cached need_tmp_table argument
+ const bool need_sort; ///< cached need_sort argument
+
+public:
+ Explain_table(THD * const thd_arg, TABLE * const table_arg,
+ const SQL_SELECT *select_arg,
+ uint key_arg, ha_rows limit_arg,
+ bool need_tmp_table_arg, bool need_sort_arg)
+ : Explain_table_base(thd_arg, table_arg), select(select_arg), key(key_arg),
+ limit(limit_arg),
+ need_tmp_table(need_tmp_table_arg), need_sort(need_sort_arg)
+ {
+ usable_keys= table->keys_in_use_for_query;
+ }
+
+private:
+ virtual bool explain_table_name();
+ virtual bool explain_join_type();
+ virtual bool explain_key_and_len();
+ virtual bool explain_rows_and_filtered();
+ virtual bool explain_extra();
+};
+
+
+static join_type calc_join_type(int quick_type)
+{
+ if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
+ return JT_INDEX_MERGE;
+ else
+ return JT_RANGE;
+}
+
+
+/* Explain class functions ****************************************************/
+
+
+/**
+ Explain class main function
+
+ This method:
+ a) allocates a select_send object (if no one pre-allocated available),
+ b) calculates and sends whole EXPLAIN data.
+
+ @return false if success, true if error
+*/
+
+bool Explain::send()
+{
+ DBUG_ENTER("Explain::send");
+ /* Don't log this into the slow query log */
+ thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED |
+ SERVER_QUERY_NO_GOOD_INDEX_USED);
+
+ select_result *result;
+ if (external_result == NULL)
+ {
+ /* Create select_result object if the called doesn't provide one: */
RL: called -> caller
+ if (!(result= new select_send))
+ DBUG_RETURN(true); /* purecov: inspected */
+ if (thd->send_explain_fields(result) || prepare(result))
+ {
+ delete result;
+ DBUG_RETURN(true); /* purecov: inspected */
+ }
+ }
+ else
+ {
+ result= external_result;
+ external_result->reset_offset_limit_cnt();
+ }
+
+ if (nil == NULL && !(nil= new Item_null))
+ DBUG_RETURN(true); /* purecov: inspected */
+ bool ret= send_to(result);
+
+ if (ret && join)
+ join->error= 1; /* purecov: inspected */
+
+ for (SELECT_LEX_UNIT *unit= select_lex()->first_inner_unit();
+ unit && !ret;
+ unit= unit->next_unit())
+ ret= mysql_explain_union(thd, unit, result);
+
+ if (external_result == NULL)
+ {
+ if (ret)
+ result->abort_result_set(); /* purecov: inspected */
+ else
+ result->send_eof();
+ delete result;
+ }
+ DBUG_RETURN(ret);
+}
+
+
+/**
+ Reset all "col_*" fields
+*/
+
+void Explain::init_columns()
+{
+ col_id= NULL;
+ col_select_type.cleanup();
+ col_table_name.cleanup();
+ col_partitions.cleanup();
+ col_join_type.cleanup();
+ col_possible_keys.cleanup();
+ col_key.cleanup();
+ col_key_len.cleanup();
+ col_ref.cleanup();
+ col_rows= NULL;
+ col_filtered= NULL;
+ col_extra.cleanup();
+}
+
+
+/**
+ Calculate EXPLAIN column values and link them into "items" list
+
+ @return false if success, true if error
+*/
+
+bool Explain::make_list()
+{
+ if (explain_id() ||
+ explain_select_type() ||
+ explain_table_name() ||
+ explain_partitions() ||
+ explain_join_type() ||
+ explain_possible_keys() ||
+ explain_key_and_len() ||
+ explain_ref() ||
+ explain_rows_and_filtered() ||
+ explain_extra())
+ return true; /* purecov: inspected */
+
+ /*
+ NOTE: the number/types of items pushed into item_list must be in sync with
+ EXPLAIN column types as they're "defined" in THD::send_explain_fields()
+ */
+ return push(col_id) ||
+ push(col_select_type) ||
+ push(col_table_name) ||
+ (describe(DESCRIBE_PARTITIONS) && push(col_partitions)) ||
+ push(col_join_type) ||
+ push(col_possible_keys) ||
+ push(col_key) ||
+ push(col_key_len) ||
+ push(col_ref) ||
+ push(col_rows) ||
+ (describe(DESCRIBE_EXTENDED) && push(col_filtered)) ||
+ push(col_extra);
+}
+
+
+/**
+ Make "items" list and send it to select_result output stream
+
+ @note An overloaded Explain_join::send_to() function sends
+ one item list per each JOIN::join_tab[] element.
+
+ @return false if success, true if error
+*/
+
+bool Explain::send_to(select_result *to)
+{
+ const bool ret= make_list() || to->send_data(items);
+ items.empty();
+ init_columns();
+ return ret;
+}
+
+
+bool Explain::explain_id()
+{
+ col_id= new Item_uint(select_lex()->select_number);
+ return col_id == NULL;
+}
+
+
+bool Explain::explain_select_type()
+{
+ if (select_lex()->type)
+ col_select_type.set(select_lex()->type);
+ else if (select_lex()->first_inner_unit() || select_lex()->next_select())
+ col_select_type.set_const("PRIMARY");
+ else
+ col_select_type.set_const("SIMPLE");
+ return col_select_type.nil();
+}
+
+
+bool Explain::explain_extra()
+{
+ col_extra.set_const("");
+ return false;
+}
+
+
+/* Explain_no_table class functions *******************************************/
+
+
+bool Explain_no_table::explain_rows_and_filtered()
+{
+ if (rows == HA_POS_ERROR)
+ return false;
+ col_rows= new Item_int(rows, MY_INT64_NUM_DECIMAL_DIGITS);
+ return col_rows == NULL;
+}
+
+
+bool Explain_no_table::explain_extra()
+{
+ return col_extra.set(message);
+}
+
+
+/* Explain_union class functions **********************************************/
+
+
+bool Explain_union::explain_id()
+{
+ return false;
+}
+
+
+bool Explain_union::explain_table_name()
+{
+ SELECT_LEX *last_select= join->unit->first_select()->last_select();
+ // # characters needed to print select_number of last select
+ int last_length= (int)log10((double)last_select->select_number)+1;
+
+ SELECT_LEX *sl= join->unit->first_select();
+ uint len= 6, lastop= 0;
+ char table_name_buffer[NAME_LEN];
+ memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
+ /*
+ - len + lastop: current position in table_name_buffer
+ - 6 + last_length: the number of characters needed to print
+ '...,'<last_select->select_number>'>\0'
+ */
+ for (;
+ sl && len + lastop + 6 + last_length < NAME_CHAR_LEN;
+ sl= sl->next_select())
+ {
+ len+= lastop;
+ lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
+ "%u,", sl->select_number);
+ }
+ if (sl || len + lastop >= NAME_CHAR_LEN)
+ {
+ memcpy(table_name_buffer + len, STRING_WITH_LEN("...,"));
+ len+= 4;
+ lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
+ "%u,", last_select->select_number);
+ }
+ len+= lastop;
+ table_name_buffer[len - 1]= '>'; // change ',' to '>'
+
+ return col_table_name.set(table_name_buffer, len);
+}
+
+
+bool Explain_union::explain_join_type()
+{
+ col_join_type.set_const(join_type_str[JT_ALL]);
+ return false;
+}
+
+
+bool Explain_union::explain_extra()
+{
+ /*
+ here we assume that the query will return at least two rows, so we
+ show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
+ and no filesort will be actually done, but executing all selects in
+ the UNION to provide precise EXPLAIN information will hardly be
+ appreciated :)
+ */
+ if (join->unit->global_parameters->order_list.first)
+ {
+ col_extra.set_const("Using filesort");
+ return false;
+ }
+ return Explain::explain_extra();
+}
+
+
+/* Explain_table_base class functions *****************************************/
+
+
+bool Explain_table_base::explain_partitions()
+{
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ if (!table->derived_select_number && table->part_info)
+ {
+ String s;
+ make_used_partitions_str(table->part_info, &s);
+ return col_partitions.set(s);
+ }
+#endif
+ return false;
+}
+
+
+bool Explain_table_base::explain_possible_keys()
+{
+ if (usable_keys.is_clear_all())
+ return false;
+
+ StringBuff<512> str_possible_keys(cs);
+
+ for (uint j= 0 ; j < table->s->keys ; j++)
+ {
+ if (usable_keys.is_set(j))
+ {
+ if (str_possible_keys.length())
+ str_possible_keys.append(',');
+ str_possible_keys.append(table->key_info[j].name,
+ strlen(table->key_info[j].name), cs);
+ }
+ }
+ if (str_possible_keys.length())
+ return col_possible_keys.set(str_possible_keys);
+ return false;
+}
+
+
+bool Explain_table_base::explain_key_and_len_quick(const SQL_SELECT *select)
+{
+ DBUG_ASSERT(select && select->quick);
+
+ StringBuff<512> str_key(cs);
+ StringBuff<512> str_key_len(cs);
+
+ select->quick->add_keys_and_lengths(&str_key, &str_key_len);
+ return col_key.set(str_key) || col_key_len.set(str_key_len);
+}
+
+
+bool Explain_table_base::explain_key_and_len_index(int key)
+{
+ DBUG_ASSERT(key != MAX_KEY);
+ return explain_key_and_len_index(key, table->key_info[key].key_length);
+}
+
+
+bool Explain_table_base::explain_key_and_len_index(int key, uint key_length)
+{
+ DBUG_ASSERT(key != MAX_KEY);
+
+ const KEY *key_info= table->key_info + key;
+ char buff_key_len[24];
+ const int length= longlong2str(key_length, buff_key_len, 10) - buff_key_len;
+ return col_key.set(key_info->name) || col_key_len.set(buff_key_len, length);
+}
+
+
+void Explain_table_base::explain_extra_common(const SQL_SELECT *select,
+ const JOIN_TAB *tab,
+ int quick_type,
+ uint keyno,
+ bool need_tmp_table,
+ bool need_sort,
+ String *str_extra)
+{
+ if ((keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno
&&
+ table->file->pushed_idx_cond) || (tab && tab->cache_idx_cond))
+ str_extra->append(STRING_WITH_LEN("; Using index condition"));
+
+ switch (quick_type) {
+ case QUICK_SELECT_I::QS_TYPE_ROR_UNION:
+ case QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT:
+ case QUICK_SELECT_I::QS_TYPE_INDEX_MERGE:
+ str_extra->append(STRING_WITH_LEN("; Using "));
+ select->quick->add_info_string(str_extra);
+ break;
+ default: ;
+ }
+
+ if (select)
+ {
+ if (tab && tab->use_quick == QS_DYNAMIC_RANGE)
+ {
+ /* 4 bits per 1 hex digit + terminating '\0' */
+ char buf[MAX_KEY / 4 + 1];
+ str_extra->append(STRING_WITH_LEN("; Range checked for each "
+ "record (index map: 0x"));
+ str_extra->append(tab->keys.print(buf));
+ str_extra->append(')');
+ }
+ else if (select->cond)
+ {
+ const Item *pushed_cond= table->file->pushed_cond;
+
+ if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN)
&&
+ pushed_cond)
+ {
+ str_extra->append(STRING_WITH_LEN("; Using where with pushed condition"));
+ if (describe(DESCRIBE_EXTENDED))
+ {
+ str_extra->append(STRING_WITH_LEN(": "));
+ ((Item *)pushed_cond)->print(str_extra, QT_ORDINARY);
+ }
+ }
+ else
+ str_extra->append(STRING_WITH_LEN("; Using where"));
+ }
+ }
+ if (table->reginfo.not_exists_optimize)
+ str_extra->append(STRING_WITH_LEN("; Not exists"));
+
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
+ !(((QUICK_RANGE_SELECT*)(select->quick))->mrr_flags &
+ (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED)))
+ {
+ /*
+ During normal execution of a query, multi_range_read_init() is
+ called to initialize MRR. If HA_MRR_SORTED is set at this point,
+ multi_range_read_init() for any native MRR implementation will
+ revert to default MRR because they cannot produce sorted output
+ currently.
+ Calling multi_range_read_init() can potentially be costly, so it
+ is not done when executing an EXPLAIN. We therefore make the
+ assumption that HA_MRR_SORTED means no MRR. If some MRR native
+ implementation will support sorted output in the future, a
+ function "bool mrr_supports_sorted()" should be added in the
+ handler.
+ */
+ str_extra->append(STRING_WITH_LEN("; Using MRR"));
+ }
+ if (need_sort)
+ str_extra->append(STRING_WITH_LEN("; Using filesort"));
+ if (need_tmp_table)
+ str_extra->append(STRING_WITH_LEN("; Using temporary"));
+}
+
+
+/* Explain_join class functions ***********************************************/
+
+
+bool Explain_join::send_to(select_result *to)
+{
+ for (; tabnum < join->tables; tabnum++)
+ {
+ tab= join->join_tab + tabnum;
+ table= tab->table;
+ usable_keys= tab->keys;
+ quick_type= -1;
+
+ if (tab->type == JT_ALL && tab->select &&
tab->select->quick)
+ {
+ quick_type= tab->select->quick->get_type();
+ tab->type= calc_join_type(quick_type);
+ }
+
+ if (Explain_table_base::send_to(external_result))
+ return true; /* purecov: inspected */
+
+ used_tables|= table->map;
+ }
+ return false;
+}
+
+
+bool Explain_join::explain_table_name()
+{
+ if (table->derived_select_number)
+ {
+ /* Derived table name generation */
+ char table_name_buffer[NAME_LEN];
+ const size_t len= my_snprintf(table_name_buffer,
+ sizeof(table_name_buffer) - 1,
+ "<derived%u>", table->derived_select_number);
+ return col_table_name.set(table_name_buffer, len);
+ }
+ else
+ return col_table_name.set(table->pos_in_table_list->alias);
+}
+
+
+bool Explain_join::explain_join_type()
+{
+ col_join_type.set_const(join_type_str[tab->type]);
+ return false;
+}
+
+
+bool Explain_join::explain_key_and_len()
+{
+ if (tab->ref.key_parts)
+ return explain_key_and_len_index(tab->ref.key, tab->ref.key_length);
+ else if (tab->type == JT_NEXT)
+ return explain_key_and_len_index(tab->index);
+ else if (tab->select && tab->select->quick)
+ return explain_key_and_len_quick(tab->select);
+ else
+ {
+ const TABLE_LIST *table_list= table->pos_in_table_list;
+ if (table_list->schema_table &&
+ table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
+ {
+ StringBuff<512> str_key(cs);
+ const char *f_name;
+ int f_idx;
+ if (table_list->has_db_lookup_value)
+ {
+ f_idx= table_list->schema_table->idx_field1;
+ f_name= table_list->schema_table->fields_info[f_idx].field_name;
+ str_key.append(f_name, strlen(f_name), cs);
+ }
+ if (table_list->has_table_lookup_value)
+ {
+ if (table_list->has_db_lookup_value)
+ str_key.append(',');
+ f_idx= table_list->schema_table->idx_field2;
+ f_name= table_list->schema_table->fields_info[f_idx].field_name;
+ str_key.append(f_name, strlen(f_name), cs);
+ }
+ if (str_key.length())
+ return col_key.set(str_key);
+ }
+ }
+ return false;
+}
+
+
+bool Explain_join::explain_ref()
+{
+ if (tab->ref.key_parts)
+ {
+ StringBuff<512> str_ref(cs);
+ for (const store_key * const *ref= tab->ref.key_copy; *ref; ref++)
+ {
+ if (str_ref.length())
+ str_ref.append(',');
+ str_ref.append((*ref)->name(), strlen((*ref)->name()), cs);
+ }
+ return col_ref.set(str_ref);
+ }
+ return false;
+}
+
+
+bool Explain_join::explain_rows_and_filtered()
+{
+ if (table->pos_in_table_list->schema_table)
+ return false;
+
+ double examined_rows;
+ if (tab->select && tab->select->quick)
+ examined_rows= rows2double(tab->select->quick->records);
+ else if (tab->type == JT_NEXT || tab->type == JT_ALL)
+ {
+ if (tab->limit)
+ examined_rows= rows2double(tab->limit);
+ else
+ {
+ table->file->info(HA_STATUS_VARIABLE);
+ examined_rows= rows2double(table->file->stats.records);
+ }
+ }
+ else
+ examined_rows= join->best_positions[tabnum].records_read;
+
+ col_rows= new Item_int((longlong) (ulonglong) examined_rows,
+ MY_INT64_NUM_DECIMAL_DIGITS);
+ if (col_rows == NULL)
+ return true; /* purecov: inspected */
+
+ /* Add "filtered" field */
+ if (describe(DESCRIBE_EXTENDED))
+ {
+ float f= 0.0;
+ if (examined_rows)
+ f= 100.0 * join->best_positions[tabnum].records_read / examined_rows;
+ col_filtered= new Item_float(f, 2);
+ return col_filtered == NULL;
+ }
+ return false;
+}
+
+
+bool Explain_join::explain_extra()
+{
+ StringBuff<512> str_extra(cs);
+
+ if (tab->info)
+ col_extra.set(tab->info);
+ else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
+ {
+ if (tab->packed_info & TAB_INFO_USING_INDEX)
+ str_extra.append(STRING_WITH_LEN("; Using index"));
+ if (tab->packed_info & TAB_INFO_USING_WHERE)
+ str_extra.append(STRING_WITH_LEN("; Using where"));
+ if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
+ str_extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+ /* Skip initial "; "*/
+ const char *str= str_extra.ptr();
+ uint32 len= str_extra.length();
+ if (len)
+ {
+ str += 2;
+ len -= 2;
+ }
+ col_extra.set(str, len);
+ }
+ else
+ {
+ const SQL_SELECT *select= tab->select;
+ uint keyno= MAX_KEY;
+ if (tab->ref.key_parts)
+ keyno= tab->ref.key;
+ else if (select && select->quick)
+ keyno = select->quick->index;
+
+ explain_extra_common(select, tab, quick_type, keyno,
+ (tabnum == 0 && need_tmp_table),
+ (tabnum == 0 && need_order),
+ &str_extra);
+
+ const TABLE_LIST *table_list= table->pos_in_table_list;
+ if (table_list->schema_table &&
+ table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
+ {
+ if (!table_list->table_open_method)
+ str_extra.append(STRING_WITH_LEN("; Skip_open_table"));
+ else if (table_list->table_open_method == OPEN_FRM_ONLY)
+ str_extra.append(STRING_WITH_LEN("; Open_frm_only"));
+ else
+ str_extra.append(STRING_WITH_LEN("; Open_full_table"));
+ if (table_list->has_db_lookup_value &&
+ table_list->has_table_lookup_value)
+ str_extra.append(STRING_WITH_LEN("; Scanned 0 databases"));
+ else if (table_list->has_db_lookup_value ||
+ table_list->has_table_lookup_value)
+ str_extra.append(STRING_WITH_LEN("; Scanned 1 database"));
+ else
+ str_extra.append(STRING_WITH_LEN("; Scanned all databases"));
+ }
+ if (((tab->type == JT_NEXT || tab->type == JT_CONST) &&
+ table->covering_keys.is_set(tab->index)) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
+ !((QUICK_ROR_INTERSECT_SELECT*) select->quick)->need_to_fetch_row) ||
+ table->key_read)
+ {
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
+ {
+ QUICK_GROUP_MIN_MAX_SELECT *qgs=
+ (QUICK_GROUP_MIN_MAX_SELECT *) select->quick;
+ str_extra.append(STRING_WITH_LEN("; Using index for group-by"));
+ qgs->append_loose_scan_type(&str_extra);
+ }
+ else
+ str_extra.append(STRING_WITH_LEN("; Using index"));
+ }
+
+ if (distinct && test_all_bits(used_tables,thd->used_tables))
+ str_extra.append(STRING_WITH_LEN("; Distinct"));
+
+ if (tab->loosescan_match_tab)
+ str_extra.append(STRING_WITH_LEN("; LooseScan"));
+
+ if (tab->flush_weedout_table)
+ str_extra.append(STRING_WITH_LEN("; Start temporary"));
+ if (tab->check_weed_out_table)
+ str_extra.append(STRING_WITH_LEN("; End temporary"));
+ else if (tab->do_firstmatch)
+ {
+ if (tab->do_firstmatch == join->join_tab - 1)
+ str_extra.append(STRING_WITH_LEN("; FirstMatch"));
+ else
+ {
+ str_extra.append(STRING_WITH_LEN("; FirstMatch("));
+ TABLE *prev_table= tab->do_firstmatch->table;
+ if (prev_table->derived_select_number)
+ {
+ char namebuf[NAME_LEN];
+ /* Derived table name generation */
+ int len= my_snprintf(namebuf, sizeof(namebuf)-1,
+ "<derived%u>",
+ prev_table->derived_select_number);
+ str_extra.append(namebuf, len);
+ }
+ else
+ str_extra.append(prev_table->pos_in_table_list->alias);
+ str_extra.append(STRING_WITH_LEN(")"));
+ }
+ }
+ uint sj_strategy= join->best_positions[tabnum].sj_strategy;
+ if (sj_is_materialize_strategy(sj_strategy))
+ {
+ if (join->best_positions[tabnum].n_sj_tables == 1)
+ str_extra.append(STRING_WITH_LEN("; Materialize"));
+ else
+ {
+ last_sjm_table= tabnum + join->best_positions[tabnum].n_sj_tables - 1;
+ str_extra.append(STRING_WITH_LEN("; Start materialize"));
+ }
+ if (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)
+ str_extra.append(STRING_WITH_LEN("; Scan"));
+ }
+ else if (last_sjm_table == tabnum)
+ {
+ str_extra.append(STRING_WITH_LEN("; End materialize"));
+ }
+
+ for (uint part= 0; part < tab->ref.key_parts; part++)
+ {
+ if (tab->ref.cond_guards[part])
+ {
+ str_extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+ break;
+ }
+ }
+
+ if (tabnum > 0 && tab[-1].next_select == sub_select_cache)
+ {
+ str_extra.append(STRING_WITH_LEN("; Using join buffer ("));
+ if ((tab->use_join_cache & JOIN_CACHE::ALG_BNL))
+ str_extra.append(STRING_WITH_LEN("BNL"));
+ else if ((tab->use_join_cache & JOIN_CACHE::ALG_BKA))
+ str_extra.append(STRING_WITH_LEN("BKA"));
+ else if ((tab->use_join_cache & JOIN_CACHE::ALG_BKA_UNIQUE))
+ str_extra.append(STRING_WITH_LEN("BKA_UNIQUE"));
+ else
+ DBUG_ASSERT(0); /* purecov: inspected */
+ if (tab->use_join_cache & JOIN_CACHE::NON_INCREMENTAL_BUFFER)
+ str_extra.append(STRING_WITH_LEN(", regular buffers)"));
+ else
+ str_extra.append(STRING_WITH_LEN(", incremental buffers)"));
+ }
+
+ /* Skip initial "; "*/
+ const char *str= str_extra.ptr();
+ uint32 len= str_extra.length();
+ if (len)
+ {
+ str += 2;
+ len -= 2;
+ }
+ col_extra.set(str, len);
+ }
+ return col_extra.nil();
+}
+
+
+/* Explain_table class functions **********************************************/
+
+
+bool Explain_table::explain_table_name()
+{
+ return col_table_name.set(table->alias);
+}
+
+
+bool Explain_table::explain_join_type()
+{
+ join_type jt;
+ if (select && select->quick)
+ jt= calc_join_type(select->quick->get_type());
+ else
+ jt= JT_ALL;
+
+ col_join_type.set_const(join_type_str[jt]);
+ return false;
+}
+
+
+bool Explain_table::explain_key_and_len()
+{
+ if (key != MAX_KEY)
+ return explain_key_and_len_index(key);
+ if (select && select->quick)
+ return explain_key_and_len_quick(select);
+ return false;
+}
+
+
+bool Explain_table::explain_rows_and_filtered()
+{
+ double examined_rows;
+ if (select && select->quick)
+ examined_rows= rows2double(select->quick->records);
+ else if (!select && !need_sort && limit != HA_POS_ERROR)
+ examined_rows= rows2double(limit);
+ else
+ {
+ table->file->info(HA_STATUS_VARIABLE);
+ examined_rows= rows2double(table->file->stats.records);
+ }
+ col_rows= new Item_int((longlong) (ulonglong) examined_rows,
+ MY_INT64_NUM_DECIMAL_DIGITS);
+ if (col_rows == NULL)
+ return true; /* purecov: inspected */
+
+ if (describe(DESCRIBE_EXTENDED))
+ {
+ col_filtered= new Item_float(100.0, 2);
+ if (col_filtered == NULL)
+ return true; /* purecov: inspected */
+ }
+ return false;
+}
+
+
+bool Explain_table::explain_extra()
+{
+ StringBuff<512> str_extra(cs);
+ const uint keyno= (select && select->quick) ? select->quick->index :
key;
+ const int quick_type= (select && select->quick) ?
select->quick->get_type()
+ : -1;
+ explain_extra_common(select, NULL, quick_type, keyno, need_tmp_table,
+ need_sort, &str_extra);
+
+ /* Skip initial "; "*/
+ const char *str= str_extra.ptr();
+ uint32 len= str_extra.length();
+ if (len)
+ {
+ str += 2;
+ len -= 2;
+ }
+ return col_extra.set(str, len);
+}
+
+
+/**
+ EXPLAIN functionality for insert_select, multi_update and multi_delete
+
+ This class objects substitute insert_select, multi_update and multi_delete
+ data interceptor objects to implement EXPLAIN for INSERT, REPLACE and
+ multi-table UPDATE and DELETE queries.
+ explain_send class object initializes tables like insert_select, multi_update
+ or multi_delete data interceptor do, but it suppress table data modification
+ by the underlying interceptor object.
+ Thus, we can use explain_send object in the context of EXPLAIN INSERT/
+ REPLACE/UPDATE/DELETE query like we use select_send in the context of
+ EXPLAIN SELECT command:
+ 1) in presence of lex->describe flag we pass explain_send object to the
+ mysql_select() function,
+ 2) it call prepare(), prepare2() and initialize_tables() methods to
+ mark modified tables etc.
+
+*/
+
+class explain_send : public select_send {
+protected:
+ /*
+ As far as we use explain_send object in a place of select_send, explain_send
+ have to pass multiple invocation of its prepare(), prepare2() and
+ initialize_tables() methods, since JOIN::exec() of subqueries runs
+ these methods of select_send multiple times by design.
+ insert_select, multi_update and multi_delete class methods are not intended
+ for multiple invocations, so "prepared", "prepared2" and "initialized" flags
+ guard data interceptor object from method re-invocation.
+ */
+ bool prepared; ///< prepare() is done
+ bool prepared2; ///< prepare2() is done
+ bool initialized; ///< initialize_tables() is done
+
+ /**
+ Pointer to underlying insert_select, multi_update or multi_delete object
+ */
+ select_result_interceptor *interceptor;
+
+public:
+ explain_send(select_result_interceptor *interceptor_arg)
+ : prepared(false), prepared2(false), initialized(false),
+ interceptor(interceptor_arg)
+ {}
+
+protected:
+ virtual int prepare(List<Item> &list, SELECT_LEX_UNIT *u)
+ {
+ if (prepared)
+ return false;
+ prepared= true;
+ return select_send::prepare(list, u) || interceptor->prepare(list, u);
+ }
+
+ virtual int prepare2(void)
+ {
+ if (prepared2)
+ return false;
+ prepared2= true;
+ return select_send::prepare2() || interceptor->prepare2();
+ }
+
+ virtual bool initialize_tables(JOIN *join)
+ {
+ if (initialized)
+ return false;
+ initialized= true;
+ return select_send::initialize_tables(join) ||
+ interceptor->initialize_tables(join);
+ }
+
+ virtual void cleanup()
+ {
+ select_send::cleanup();
+ interceptor->cleanup();
+ }
+};
+
+
+/******************************************************************************
+ External function implementations
+******************************************************************************/
+
+
+/**
+ Send a message as an "extra" column value
+
+ This function forms the 1st row of the QEP output with a simple text message.
+ This is useful to explain such trivial cases as "No tables used" etc.
+
+ @note Also this function explains the rest of QEP (subqueries or joined
+ tables if any).
+
+ @param thd current THD
+ @param join JOIN
+ @param message text message for the "extra" column.
+
+ @return false if success, true if error
+*/
+
+bool explain_no_table(THD *thd, JOIN *join, const char *message)
+{
+ DBUG_ENTER("explain_no_table");
+ const bool ret= Explain_no_table(thd, join, message).send();
+ DBUG_RETURN(ret);
+}
+
+
+/**
+ Send a message as an "extra" column value
+
+ This function forms the 1st row of the QEP output with a simple text message.
+ This is useful to explain such trivial cases as "No tables used" etc.
+
+ @note Also this function explains the rest of QEP (subqueries if any).
+
+ @param thd current THD
+ @param message text message for the "extra" column.
+ @param rows HA_POS_ERROR or a value for the "rows" column.
+
+ @return false if success, true if error
+*/
+
+bool explain_no_table(THD *thd, const char *message, ha_rows rows)
+{
+ DBUG_ENTER("explain_no_table");
+ const bool ret= Explain_no_table(thd, message, rows).send();
+ DBUG_RETURN(ret);
+
+}
+
+
+/**
+ EXPLAIN handling for single-table UPDATE and DELETE queries
+
+ Send to the client a QEP data set for single-table EXPLAIN UPDATE/DELETE
+ queries. As far as single-table UPDATE/DELETE are implemented without
+ the regular JOIN tree, we can't reuse mysql_explain_union() directly,
+ thus we deal with this single table in a special way and then call
+ mysql_explain_union() for subqueries (if any).
+
+ @param thd current THD
+ @param table TABLE object to update/delete rows in the UPDATE/DELETE
+ query.
+ @param select SQL_SELECT object that represents quick access methods/
+ WHERE clause.
+ @param key MAX_KEY or and index number of the key that was chosen
+ to access table data.
+ @param limit HA_POS_ERROR or LIMIT value.
+ @param need_tmp_table true if it requires temporary table -- "Using temporary"
+ string in the "extra" column.
+ @param need_sort true if it requires filesort() -- "Using filesort"
+ string in the "extra" column.
+
+ @return false if success, true if error
+*/
+
+bool explain_single_table_modification(THD *thd,
+ TABLE *table,
+ const SQL_SELECT *select,
+ uint key,
+ ha_rows limit,
+ bool need_tmp_table,
+ bool need_sort)
+{
+ DBUG_ENTER("explain_single_table_modification");
+ const bool ret= Explain_table(thd, table, select, key, limit,
+ need_tmp_table, need_sort).send();
+ DBUG_RETURN(ret);
+}
+
+
+/**
+ EXPLAIN handling for EXPLAIN SELECT queries
+
+ Send QEP to the client.
+
+ @param thd current THD
+ @param join JOIN
+ @param need_tmp_table true if it requires a temporary table --
+ "Using temporary" string in the "extra" column.
+ @param need_order true if it requires filesort() -- "Using filesort"
+ string in the "extra" column.
+ @param distinct true if there is the DISTINCT clause (not optimized
+ out) -- "Distinct" string in the "extra" column.
+
+ @return false if success, true if error
+*/
+
+bool explain_query_specification(THD *thd, JOIN *join, bool need_tmp_table,
+ bool need_order, bool distinct)
+{
+ DBUG_ENTER("explain_query_specification");
+ DBUG_PRINT("info", ("Select %p, type %s",
+ join->select_lex, join->select_lex->type));
+ bool ret;
+ if (join->select_lex == join->unit->fake_select_lex)
+ ret= Explain_union(thd, join).send();
+ else
+ ret= Explain_join(thd, join, need_tmp_table, need_order, distinct).send();
+ DBUG_RETURN(ret);
+}
+
+
+/**
+ EXPLAIN handling for INSERT, REPLACE and multi-table UPDATE/DELETE queries
+
+ Send to the client a QEP data set for data-modifying commands those have a
+ regular JOIN tree (INSERT...SELECT, REPLACE...SELECT and multi-table
+ UPDATE and DELETE queries) like mysql_select() does for SELECT queries in
+ the "describe" mode.
+
+ @note @see explain_single_table_modification() for single-table
+ UPDATE/DELETE EXPLAIN handling.
+
+ @note Unlike the mysql_select function, explain_multi_table_modification
+ calls abort_result_set() itself in the case of failure (OOM etc.)
+ since explain_multi_table_modification() uses internally created
+ select_result stream.
+
+ @param thd current THD
+ @param result pointer to select_insert, multi_delete or multi_update object:
+ the function uses it to call result->prepare(),
+ result->prepare2() and result->initialize_tables() only but
+ not to modify table data or to send a result to client.
+ @return false if success, true if error
+*/
+
+bool explain_multi_table_modification(THD *thd,
+ select_result_interceptor *result)
+{
+ DBUG_ENTER("explain_multi_table_modification");
+ explain_send explain(result);
+ bool res= explain_query_expression(thd, &explain);
+ DBUG_RETURN(res);
+}
+
+
+/**
+ EXPLAIN handling for SELECT and table-modifying queries that have JOIN
+
+ Send to the client a QEP data set for SELECT or data-modifying commands
+ those have a regular JOIN tree (INSERT...SELECT, REPLACE...SELECT and
+ multi-table UPDATE and DELETE queries) like mysql_select() does for SELECT
+ queries in the "describe" mode.
+
+ @note @see explain_single_table_modification() for single-table
+ UPDATE/DELETE EXPLAIN handling.
+
+ @note explain_query_expression() calls abort_result_set() itself in the
+ case of failure (OOM etc.) since explain_multi_table_modification()
+ uses internally created select_result stream.
+
+ @param thd current THD
+ @param result pointer to select_result, select_insert, multi_delete or
+ multi_update object: the function uses it to call
+ result->prepare(), result->prepare2() and
+ result->initialize_tables() only but not to modify table data
+ or to send a result to client.
+ @return false if success, true if error
+*/
+
+bool explain_query_expression(THD *thd, select_result *result)
+{
+ DBUG_ENTER("explain_query_expression");
+ const bool res= thd->send_explain_fields(result) ||
+ mysql_explain_union(thd, &thd->lex->unit, result) ||
+ thd->is_error();
+ /*
+ The code which prints the extended description is not robust
+ against malformed queries, so skip it if we have an error.
+ */
+ if (!res && (thd->lex->describe & DESCRIBE_EXTENDED) &&
+ thd->lex->sql_command == SQLCOM_SELECT) // TODO: implement for INSERT/etc
+ {
+ StringBuff<1024> str;
+ /*
+ The warnings system requires input in utf8, @see mysqld_show_warnings().
+ */
+ thd->lex->unit.print(&str, QT_TO_SYSTEM_CHARSET);
+ str.append('\0');
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_YES, str.ptr());
+ }
+ if (res)
+ result->abort_result_set();
+ else
+ result->send_eof();
+ DBUG_RETURN(res);
+}
+
+
+/**
+ Explain UNION or subqueries of the unit
+
+ If the unit is a UNION, explain it as a UNION. Otherwise explain nested
+ subselects.
+
+ @param thd thread object
+ @param unit unit object
+ @param result result stream to send QEP dataset
+
+ @return false if success, true if error
+*/
+bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
+{
+ DBUG_ENTER("mysql_explain_union");
+ bool res= 0;
+ SELECT_LEX *first= unit->first_select();
+
+ for (SELECT_LEX *sl= first;
+ sl;
+ sl= sl->next_select())
+ {
+ // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only
+ uint8 uncacheable= (sl->uncacheable & ~UNCACHEABLE_EXPLAIN);
+ sl->type= (((&thd->lex->select_lex)==sl)?
+ (sl->first_inner_unit() || sl->next_select() ?
+ "PRIMARY" : "SIMPLE"):
+ ((sl == first)?
+ ((sl->linkage == DERIVED_TABLE_TYPE) ?
+ "DERIVED":
+ ((uncacheable & UNCACHEABLE_DEPENDENT) ?
+ "DEPENDENT SUBQUERY":
+ (uncacheable?"UNCACHEABLE SUBQUERY":
+ "SUBQUERY"))):
+ ((uncacheable & UNCACHEABLE_DEPENDENT) ?
+ "DEPENDENT UNION":
+ uncacheable?"UNCACHEABLE UNION":
+ "UNION")));
+ sl->options|= SELECT_DESCRIBE;
+ }
+ if (unit->is_union())
+ {
+ unit->fake_select_lex->select_number= UINT_MAX; // jost for initialization
+ unit->fake_select_lex->type= "UNION RESULT";
+ unit->fake_select_lex->options|= SELECT_DESCRIBE;
+ if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
+ res= unit->exec();
+ }
+ else
+ {
+ thd->lex->current_select= first;
+ unit->set_limit(unit->global_parameters);
+ res= mysql_select(thd, &first->ref_pointer_array,
+ first->table_list.first,
+ first->with_wild, first->item_list,
+ first->where,
+ first->order_list.elements +
+ first->group_list.elements,
+ first->order_list.first,
+ first->group_list.first,
+ first->having,
+ thd->lex->proc_list.first,
+ first->options | thd->variables.option_bits | SELECT_DESCRIBE,
+ result, unit, first);
+ }
+ DBUG_RETURN(res || thd->is_error());
+}
+
+
=== added file 'sql/opt_explain.h'
--- sql/opt_explain.h 1970-01-01 00:00:00 +0000
+++ sql/opt_explain.h 2011-06-16 12:58:35 +0000
@@ -0,0 +1,73 @@
+/* Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software Foundation,
+ 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
+
+
+#ifndef OPT_EXPLAIN_INCLUDED
+#define OPT_EXPLAIN_INCLUDED
+
+/** @file "EXPLAIN <command>"
+
+Single table UPDATE/DELETE commands are explained by the
+explain_single_table_modification() function.
+
+A query expression (complete SELECT query possibly including
+subqueries and unions), INSERT...SELECT and multitable UPDATE/DELETE
+commands are explained like this:
+
+(1) explain_query_expression()
+
+Is the entry point. Forwards the job to mysql_explain_union().
+
+(2) mysql_explain_union()
+
+Is for a SELECT_LEX_UNIT, prepares, optimizes, executes one JOIN for
+each "top-level" SELECT_LEXs of the unit (like: all SELECTs of a
+UNION; but not subqueries), and one JOIN for the fake SELECT_LEX of
+UNION); each JOIN execution (JOIN::exec()) calls explain_query_specification()
+
+(3) explain_query_specification()
+
+Is for a single SELECT_LEX (fake or not). It needs a prepared and
+optimized JOIN, for which it builds the EXPLAIN rows. But it also
+launches the EXPLAIN process for "inner units" (==subqueries of this
+SELECT_LEX), by calling mysql_explain_union() for each of them.
+*/
+
+class JOIN;
+class select_result;
+class select_result_interceptor;
+class SQL_SELECT;
+class TABLE;
+class THD;
+
+#include "my_base.h"
+
+bool explain_no_table(THD *thd, JOIN *join, const char *message);
+bool explain_no_table(THD *thd, const char *message,
+ ha_rows rows= HA_POS_ERROR);
+bool explain_single_table_modification(THD *thd,
+ TABLE *table,
+ const SQL_SELECT *select,
+ uint key,
+ ha_rows limit,
+ bool need_tmp_table,
+ bool need_sort);
+bool explain_query_specification(THD *thd, JOIN *join, bool need_tmp_table,
+ bool need_order, bool distinct);
+bool explain_multi_table_modification(THD *thd,
+ select_result_interceptor *result);
+bool explain_query_expression(THD *thd, select_result *result);
+
+#endif /* OPT_EXPLAIN_INCLUDED */
=== modified file 'sql/opt_range.h'
--- sql/opt_range.h 2011-03-22 11:44:40 +0000
+++ sql/opt_range.h 2011-06-16 12:58:35 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -427,8 +427,6 @@ protected:
friend uint quick_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range);
friend range_seq_t quick_range_seq_init(void *init_param,
uint n_ranges, uint flags);
- friend void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
- bool distinct,const char *message);
friend class QUICK_SELECT_DESC;
friend class QUICK_INDEX_MERGE_SELECT;
friend class QUICK_ROR_INTERSECT_SELECT;
=== modified file 'sql/sql_class.h'
--- sql/sql_class.h 2011-03-24 08:00:03 +0000
+++ sql/sql_class.h 2011-06-16 12:58:35 +0000
@@ -3111,6 +3111,14 @@ public:
*/
virtual void cleanup();
void set_thd(THD *thd_arg) { thd= thd_arg; }
+
+ /**
+ If we execute EXPLAIN SELECT ... LIMIT (or any other EXPLAIN query)
+ we have to ignore offset value sending EXPLAIN output rows since
+ offset value belongs to the underlying query, not to the whole EXPLAIN.
+ */
+ void reset_offset_limit_cnt() { unit->offset_limit_cnt= 0; }
+
#ifdef EMBEDDED_LIBRARY
virtual void begin_dataset() {}
#else
@@ -3808,6 +3816,11 @@ public:
*/
#define CF_WRITE_RPL_INFO_COMMAND (1U << 12)
+/**
+ Identifies statements that can be explained with EXPLAIN.
+*/
+#define CF_CAN_BE_EXPLAINED (1U << 13)
+
/* Bits in server_command_flags */
/**
=== modified file 'sql/sql_delete.cc'
--- sql/sql_delete.cc 2011-03-17 17:39:31 +0000
+++ sql/sql_delete.cc 2011-06-16 12:58:35 +0000
@@ -35,6 +35,7 @@
#include "sp_head.h"
#include "sql_trigger.h"
#include "transaction.h"
+#include "opt_explain.h"
#include "records.h" // init_read_record,
// end_read_record
@@ -60,6 +61,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *
ha_rows deleted= 0;
bool reverse= FALSE;
bool skip_record;
+ bool need_sort= FALSE;
+ bool err= true;
ORDER *order= (ORDER *) ((order_list && order_list->elements) ?
order_list->first : NULL);
uint usable_index= MAX_KEY;
@@ -144,6 +147,13 @@ bool mysql_delete(THD *thd, TABLE_LIST *
/* Update the table->file->stats.records number */
table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
ha_rows const maybe_deleted= table->file->stats.records;
+
+ if (thd->lex->describe)
+ {
+ err= explain_no_table(thd, "Deleting all rows", maybe_deleted);
+ goto exit_without_my_ok;
+ }
+
DBUG_PRINT("debug", ("Trying to use delete_all_rows()"));
if (!(error=table->file->ha_delete_all_rows()))
{
@@ -169,14 +179,28 @@ bool mysql_delete(THD *thd, TABLE_LIST *
Item::cond_result result;
conds= remove_eq_conds(thd, conds, &result);
if (result == Item::COND_FALSE) // Impossible where
+ {
limit= 0;
+
+ if (thd->lex->describe)
+ {
+ err= explain_no_table(thd, "Impossible WHERE");
+ goto exit_without_my_ok;
+ }
+ }
}
#ifdef WITH_PARTITION_STORAGE_ENGINE
if (prune_partitions(thd, table, conds))
{
+ /* No matching records */
+ if (thd->lex->describe)
+ {
+ err= explain_no_table(thd, "No matching records after partition pruning");
+ goto exit_without_my_ok;
+ }
+
free_underlaid_joins(thd, select_lex);
- // No matching record
my_ok(thd, 0);
DBUG_RETURN(0);
}
@@ -219,23 +243,33 @@ bool mysql_delete(THD *thd, TABLE_LIST *
DBUG_RETURN(TRUE);
}
}
+
+ if (order)
+ {
+ table->update_const_key_parts(conds);
+ order= simple_remove_const(order, conds);
+
+ usable_index= get_index_for_order(order, table, select, limit,
+ &need_sort, &reverse);
+ }
+
+ if (thd->lex->describe)
+ {
+ err= explain_single_table_modification(thd, table, select, usable_index,
+ limit, false, need_sort);
+ goto exit_without_my_ok;
+ }
+
if (options & OPTION_QUICK)
(void) table->file->extra(HA_EXTRA_QUICK);
- if (order)
+ if (need_sort)
{
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;
- usable_index= get_index_for_order(order, table, select, limit,
- &need_sort, &reverse);
- if (need_sort)
{
DBUG_ASSERT(usable_index == MAX_KEY);
table->sort.io_cache= (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
@@ -361,6 +395,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
(void) table->file->extra(HA_EXTRA_NORMAL);
cleanup:
+ DBUG_ASSERT(!thd->lex->describe);
/*
Invalidate the table in the query cache if something changed. This must
be before binlog writing and ha_autocommit_...
@@ -414,6 +449,11 @@ cleanup:
DBUG_PRINT("info",("%ld records deleted",(long) deleted));
}
DBUG_RETURN(thd->is_error() || thd->killed);
+
+exit_without_my_ok:
+ delete select;
+ free_underlaid_joins(thd, select_lex);
+ DBUG_RETURN((err || thd->is_error() || thd->killed) ? 1 : 0);
}
=== modified file 'sql/sql_insert.cc'
--- sql/sql_insert.cc 2011-02-15 17:14:15 +0000
+++ sql/sql_insert.cc 2011-06-16 12:58:35 +0000
@@ -76,6 +76,7 @@
#include "transaction.h"
#include "sql_audit.h"
#include "debug_sync.h"
+#include "opt_explain.h"
#ifndef EMBEDDED_LIBRARY
static bool delayed_get_table(THD *thd, MDL_request *grl_protection_request,
@@ -453,11 +454,12 @@ void upgrade_lock_type(THD *thd, thr_loc
the statement indirectly via a stored function or trigger:
if it is used, that will lead to a deadlock between the
client connection and the delayed thread.
+ - we're running the EXPLAIN INSERT command
*/
if (specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE) ||
thd->variables.max_insert_delayed_threads == 0 ||
thd->locked_tables_mode > LTM_LOCK_TABLES ||
- thd->lex->uses_stored_routines())
+ thd->lex->uses_stored_routines() || thd->lex->describe)
{
*lock_type= TL_WRITE;
return;
@@ -498,6 +500,7 @@ void upgrade_lock_type(THD *thd, thr_loc
thread may be old and use the before-the-change value.
*/
*lock_type= TL_WRITE;
+ return;
}
}
}
@@ -657,6 +660,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
bool ignore)
{
int error, res;
+ bool err= true;
bool transactional_table, joins_freed= FALSE;
bool changed;
bool was_insert_delayed= (table_list->lock_type == TL_WRITE_DELAYED);
@@ -729,7 +733,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
!ignore && (thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
MODE_STRICT_ALL_TABLES))))
- goto abort;
+ goto exit_without_my_ok;
/* mysql_prepare_insert set table_list->table if it was not set */
table= table_list->table;
@@ -760,16 +764,27 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
if (values->elements != value_count)
{
my_error(ER_WRONG_VALUE_COUNT_ON_ROW, MYF(0), counter);
- goto abort;
+ goto exit_without_my_ok;
}
if (setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0))
- goto abort;
+ goto exit_without_my_ok;
}
its.rewind ();
/* Restore the current context. */
ctx_state.restore_state(context, table_list);
+ if (thd->lex->describe)
+ {
+ /*
+ Send "No tables used" and stop execution here since
+ there is no SELECT to explain.
+ */
+
+ err= explain_no_table(thd, "No tables used");
+ goto exit_without_my_ok;
+ }
+
/*
Fill in the given fields and dump it to the table file
*/
@@ -797,7 +812,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
(info.handle_duplicates == DUP_UPDATE) &&
(table->next_number_field != NULL) &&
rpl_master_has_bug(active_mi->rli, 24432, TRUE, NULL, NULL))
- goto abort;
+ goto exit_without_my_ok;
#endif
error=0;
@@ -1071,7 +1086,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
if (error)
- goto abort;
+ goto exit_without_my_ok;
if (values_list.elements == 1 && (!(thd->variables.option_bits &
OPTION_WARNINGS) ||
!thd->cuted_fields))
{
@@ -1099,7 +1114,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
thd->abort_on_warning= 0;
DBUG_RETURN(FALSE);
-abort:
+exit_without_my_ok:
#ifndef EMBEDDED_LIBRARY
if (lock_type == TL_WRITE_DELAYED)
end_delayed_insert(thd);
@@ -1109,7 +1124,7 @@ abort:
if (!joins_freed)
free_underlaid_joins(thd, &thd->lex->select_lex);
thd->abort_on_warning= 0;
- DBUG_RETURN(TRUE);
+ DBUG_RETURN(err);
}
=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc 2011-03-17 17:39:31 +0000
+++ sql/sql_parse.cc 2011-06-16 12:58:35 +0000
@@ -64,7 +64,6 @@
// sp_grant_privileges, ...
#include "sql_test.h" // mysql_print_status
#include "sql_select.h" // handle_select, mysql_select,
- // mysql_explain_union
#include "sql_load.h" // mysql_load
#include "sql_servers.h" // create_servers, alter_servers,
// drop_servers, servers_reload
@@ -96,6 +95,7 @@
#include "set_var.h"
#include "mysql/psi/mysql_statement.h"
#include "sql_bootstrap.h"
+#include "opt_explain.h"
#define FLAGSTR(V,F) ((V)&(F)?#F" ":"")
@@ -322,22 +322,31 @@ void init_update_queries(void)
sql_command_flags[SQLCOM_DROP_EVENT]= CF_CHANGES_DATA |
CF_AUTO_COMMIT_TRANS;
sql_command_flags[SQLCOM_UPDATE]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_UPDATE_MULTI]= CF_CHANGES_DATA |
CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_INSERT]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_INSERT_SELECT]= CF_CHANGES_DATA |
CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_DELETE]= CF_CHANGES_DATA |
CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_DELETE_MULTI]= CF_CHANGES_DATA |
CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_REPLACE]= CF_CHANGES_DATA |
CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_REPLACE_SELECT]= CF_CHANGES_DATA |
CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_SELECT]= CF_REEXECUTION_FRAGILE |
+ CF_CAN_BE_EXPLAINED |
CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_SET_OPTION]= CF_REEXECUTION_FRAGILE |
CF_AUTO_COMMIT_TRANS;
sql_command_flags[SQLCOM_DO]= CF_REEXECUTION_FRAGILE |
@@ -460,6 +469,13 @@ bool is_update_query(enum enum_sql_comma
return (sql_command_flags[command] & CF_CHANGES_DATA) != 0;
}
+
+bool is_explainable_query(enum enum_sql_command command)
+{
+ DBUG_ASSERT(command >= 0 && command <= SQLCOM_END);
+ return (sql_command_flags[command] & CF_CAN_BE_EXPLAINED) != 0;
+}
+
/**
Check if a sql command is allowed to write to log tables.
@param command The SQL command
@@ -825,7 +841,7 @@ out:
This is a helper function to mysql_execute_command.
- @note SQLCOM_MULTI_UPDATE is an exception and delt with elsewhere.
+ @note SQLCOM_UPDATE_MULTI is an exception and delt with elsewhere.
@see mysql_execute_command
@returns Status code
@@ -1867,6 +1883,7 @@ mysql_execute_command(THD *thd)
bool have_table_map_for_update= FALSE;
#endif
DBUG_ENTER("mysql_execute_command");
+ DBUG_ASSERT(!lex->describe || is_explainable_query(lex->sql_command));
#ifdef WITH_PARTITION_STORAGE_ENGINE
thd->work_part_info= 0;
@@ -2910,7 +2927,7 @@ end_with_restore_list:
case SQLCOM_REPLACE_SELECT:
case SQLCOM_INSERT_SELECT:
{
- select_result *sel_result;
+ select_insert *sel_result;
DBUG_ASSERT(first_table == all_tables && first_table != 0);
if ((res= insert_precheck(thd, all_tables)))
break;
@@ -2941,21 +2958,26 @@ end_with_restore_list:
lex->duplicates,
lex->ignore)))
{
- res= handle_select(thd, lex, sel_result, OPTION_SETUP_TABLES_DONE);
- /*
- Invalidate the table in the query cache if something changed
- after unlocking when changes become visible.
- TODO: this is workaround. right way will be move invalidating in
- the unlock procedure.
- */
- if (!res && first_table->lock_type == TL_WRITE_CONCURRENT_INSERT
&&
- thd->lock)
+ if (lex->describe)
+ res= explain_multi_table_modification(thd, sel_result);
+ else
{
- /* INSERT ... SELECT should invalidate only the very first table */
- TABLE_LIST *save_table= first_table->next_local;
- first_table->next_local= 0;
- query_cache_invalidate3(thd, first_table, 1);
- first_table->next_local= save_table;
+ res= handle_select(thd, lex, sel_result, OPTION_SETUP_TABLES_DONE);
+ /*
+ Invalidate the table in the query cache if something changed
+ after unlocking when changes become visible.
+ TODO: this is workaround. right way will be move invalidating in
+ the unlock procedure.
RL: Please convert TODO to @todo
+ */
+ if (!res && first_table->lock_type == TL_WRITE_CONCURRENT_INSERT
&&
+ thd->lock)
+ {
+ /* INSERT ... SELECT should invalidate only the very first table */
+ TABLE_LIST *save_table= first_table->next_local;
+ first_table->next_local= 0;
+ query_cache_invalidate3(thd, first_table, 1);
+ first_table->next_local= save_table;
+ }
}
delete sel_result;
}
@@ -3019,21 +3041,26 @@ end_with_restore_list:
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();
+ if (lex->describe)
+ res= explain_multi_table_modification(thd, del_result);
+ else
+ {
+ 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();
+ if (res)
+ del_result->abort_result_set();
+ }
MYSQL_MULTI_DELETE_DONE(res, del_result->num_deleted());
- if (res)
- del_result->abort_result_set();
delete del_result;
}
else
@@ -4501,7 +4528,7 @@ finish:
static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables)
{
LEX *lex= thd->lex;
- select_result *result=lex->result;
+ select_result *result= lex->result;
bool res;
/* assign global limit variable if limit is not given */
{
@@ -4523,30 +4550,7 @@ static bool execute_sqlcom_select(THD *t
*/
if (!(result= new select_send()))
return 1; /* purecov: inspected */
- thd->send_explain_fields(result);
- res= mysql_explain_union(thd, &thd->lex->unit, result);
- /*
- The code which prints the extended description is not robust
- against malformed queries, so skip it if we have an error.
- */
- if (!res && (lex->describe & DESCRIBE_EXTENDED))
- {
- char buff[1024];
- String str(buff,(uint32) sizeof(buff), system_charset_info);
- str.length(0);
- /*
- The warnings system requires input in utf8, @see
- mysqld_show_warnings().
- */
- thd->lex->unit.print(&str, QT_TO_SYSTEM_CHARSET);
- str.append('\0');
- push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
- ER_YES, str.ptr());
- }
- if (res)
- result->abort_result_set();
- else
- result->send_eof();
+ res= explain_query_expression(thd, result);
delete result;
}
else
=== modified file 'sql/sql_parse.h'
--- sql/sql_parse.h 2011-03-09 20:54:55 +0000
+++ sql/sql_parse.h 2011-06-16 12:58:35 +0000
@@ -80,6 +80,7 @@ bool check_identifier_name(LEX_STRING *s
uint err_code, const char *param_for_err_msg);
bool mysql_test_parse_for_slave(THD *thd,char *inBuf,uint length);
bool is_update_query(enum enum_sql_command command);
+bool is_explainable_query(enum enum_sql_command command);
bool is_log_table_write_query(enum enum_sql_command command);
bool is_rpl_info_table_write_query(enum enum_sql_command command);
bool alloc_query(THD *thd, const char *packet, uint packet_length);
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc 2011-03-29 07:30:44 +0000
+++ sql/sql_select.cc 2011-06-16 12:58:35 +0000
@@ -48,6 +48,7 @@
#include <my_bit.h>
#include <hash.h>
#include <ft_global.h>
+#include "opt_explain.h"
#define PREV_BITS(type,A) ((type) (((type) 1 << (A)) -1))
@@ -250,8 +251,6 @@ static bool setup_sum_funcs(THD *thd, It
static bool prepare_sum_aggregators(Item_sum **func_ptr, bool need_distinct);
static bool init_sum_functions(Item_sum **func, Item_sum **end);
static bool update_sum_func(Item_sum **func);
-void select_describe(JOIN *join, bool need_tmp_table,bool need_order,
- bool distinct, const char *message=NullS);
static Item *remove_additional_cond(Item* conds);
static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
static bool replace_subcondition(JOIN *join, Item **tree,
@@ -2794,8 +2793,8 @@ JOIN::exec()
if (!tables_list && (tables || !select_lex->with_sum_func))
{ // Only test of functions
if (select_options & SELECT_DESCRIBE)
- select_describe(this, FALSE, FALSE, FALSE,
- (zero_result_cause?zero_result_cause:"No tables used"));
+ explain_no_table(thd, this, zero_result_cause ? zero_result_cause
+ : "No tables used");
else
{
if (result->send_result_set_metadata(*columns_list,
@@ -2889,10 +2888,12 @@ JOIN::exec()
keys_in_use_for_query))))
order=0;
having= tmp_having;
- select_describe(this, need_tmp,
- order != 0 && !skip_sort_order,
- select_distinct,
- !tables ? "No tables used" : NullS);
+ if (tables)
+ explain_query_specification(thd, this, need_tmp,
+ order != 0 && !skip_sort_order,
+ select_distinct);
+ else
+ explain_no_table(thd, this, "No tables used");
DBUG_VOID_RETURN;
}
@@ -11938,7 +11939,7 @@ return_zero_rows(JOIN *join, select_resu
if (select_options & SELECT_DESCRIBE)
{
- select_describe(join, FALSE, FALSE, FALSE, info);
+ explain_no_table(join->thd, join, info);
DBUG_RETURN(0);
}
@@ -22763,647 +22764,6 @@ void JOIN::clear()
}
}
-/**
- EXPLAIN handling.
-
- Send a description about what how the select will be done to stdout.
-*/
-
-void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
- bool distinct,const char *message)
-{
- List<Item> field_list;
- List<Item> item_list;
- THD *thd=join->thd;
- select_result *result=join->result;
- Item *item_null= new Item_null();
- CHARSET_INFO *cs= system_charset_info;
- int quick_type;
- DBUG_ENTER("select_describe");
- DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
- (ulong)join->select_lex, join->select_lex->type,
- message ? message : "NULL"));
- /* Don't log this into the slow query log */
- thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED |
SERVER_QUERY_NO_GOOD_INDEX_USED);
- join->unit->offset_limit_cnt= 0;
-
- /*
- NOTE: the number/types of items pushed into item_list must be in sync with
- EXPLAIN column types as they're "defined" in THD::send_explain_fields()
- */
- if (message)
- {
- item_list.push_back(new Item_int((int32)
- join->select_lex->select_number));
- item_list.push_back(new Item_string(join->select_lex->type,
- strlen(join->select_lex->type), cs));
- for (uint i=0 ; i < 7; i++)
- item_list.push_back(item_null);
- if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
- item_list.push_back(item_null);
- if (join->thd->lex->describe & DESCRIBE_EXTENDED)
- item_list.push_back(item_null);
-
- item_list.push_back(new Item_string(message,strlen(message),cs));
- if (result->send_data(item_list))
- join->error= 1;
- }
- else if (join->select_lex == join->unit->fake_select_lex)
- {
- /*
- here we assume that the query will return at least two rows, so we
- show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
- and no filesort will be actually done, but executing all selects in
- the UNION to provide precise EXPLAIN information will hardly be
- appreciated :)
- */
- char table_name_buffer[NAME_CHAR_LEN];
- item_list.empty();
- /* id */
- item_list.push_back(new Item_null);
- /* select_type */
- item_list.push_back(new Item_string(join->select_lex->type,
- strlen(join->select_lex->type),
- cs));
- /* table */
- {
- SELECT_LEX *last_select= join->unit->first_select()->last_select();
- // # characters needed to print select_number of last select
- int last_length= (int)log10((double)last_select->select_number)+1;
-
- SELECT_LEX *sl= join->unit->first_select();
- uint len= 6, lastop= 0;
- memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
- /*
- - len + lastop: current position in table_name_buffer
- - 6 + last_length: the number of characters needed to print
- '...,'<last_select->select_number>'>\0'
- */
- for (;
- sl && len + lastop + 6 + last_length < NAME_CHAR_LEN;
- sl= sl->next_select())
- {
- len+= lastop;
- lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
- "%u,", sl->select_number);
- }
- if (sl || len + lastop >= NAME_CHAR_LEN)
- {
- memcpy(table_name_buffer + len, STRING_WITH_LEN("...,"));
- len+= 4;
- lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
- "%u,", last_select->select_number);
- }
- len+= lastop;
- table_name_buffer[len - 1]= '>'; // change ',' to '>'
- item_list.push_back(new Item_string(table_name_buffer, len, cs));
- }
- /* partitions */
- if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
- item_list.push_back(item_null);
- /* type */
- item_list.push_back(new Item_string(join_type_str[JT_ALL],
- strlen(join_type_str[JT_ALL]),
- cs));
- /* possible_keys */
- item_list.push_back(item_null);
- /* key*/
- item_list.push_back(item_null);
- /* key_len */
- item_list.push_back(item_null);
- /* ref */
- item_list.push_back(item_null);
- /* in_rows */
- if (join->thd->lex->describe & DESCRIBE_EXTENDED)
- item_list.push_back(item_null);
- /* rows */
- item_list.push_back(item_null);
- /* extra */
- if (join->unit->global_parameters->order_list.first)
- item_list.push_back(new Item_string("Using filesort",
- 14, cs));
- else
- item_list.push_back(new Item_string("", 0, cs));
-
- if (result->send_data(item_list))
- join->error= 1;
- }
- else
- {
- table_map used_tables=0;
- uint last_sjm_table= MAX_TABLES;
- for (uint i=0 ; i < join->tables ; i++)
- {
- JOIN_TAB *tab=join->join_tab+i;
- TABLE *table=tab->table;
- TABLE_LIST *table_list= tab->table->pos_in_table_list;
- char buff[512];
- char buff1[512], buff2[512], buff3[512];
- char keylen_str_buf[64];
- String extra(buff, sizeof(buff),cs);
- char table_name_buffer[NAME_LEN];
- String tmp1(buff1,sizeof(buff1),cs);
- String tmp2(buff2,sizeof(buff2),cs);
- String tmp3(buff3,sizeof(buff3),cs);
- extra.length(0);
- tmp1.length(0);
- tmp2.length(0);
- tmp3.length(0);
-
- quick_type= -1;
- item_list.empty();
- /* id */
- item_list.push_back(new Item_uint((uint32)
- join->select_lex->select_number));
- /* select_type */
- item_list.push_back(new Item_string(join->select_lex->type,
- strlen(join->select_lex->type),
- cs));
- if (tab->type == JT_ALL && tab->select &&
tab->select->quick)
- {
- quick_type= tab->select->quick->get_type();
- if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
- (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
- (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
- tab->type = JT_INDEX_MERGE;
- else
- tab->type = JT_RANGE;
- }
- /* table */
- if (table->derived_select_number)
- {
- /* Derived table name generation */
- int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1,
- "<derived%u>",
- table->derived_select_number);
- item_list.push_back(new Item_string(table_name_buffer, len, cs));
- }
- else
- {
- TABLE_LIST *real_table= table->pos_in_table_list;
- item_list.push_back(new Item_string(real_table->alias,
- strlen(real_table->alias),
- cs));
- }
- /* "partitions" column */
- if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
- {
-#ifdef WITH_PARTITION_STORAGE_ENGINE
- partition_info *part_info;
- if (!table->derived_select_number &&
- (part_info= table->part_info))
- {
- Item_string *item_str= new Item_string(cs);
- make_used_partitions_str(part_info, &item_str->str_value);
- item_list.push_back(item_str);
- }
- else
- item_list.push_back(item_null);
-#else
- /* just produce empty column if partitioning is not compiled in */
- item_list.push_back(item_null);
-#endif
- }
- /* "type" column */
- item_list.push_back(new Item_string(join_type_str[tab->type],
- strlen(join_type_str[tab->type]),
- cs));
- /* Build "possible_keys" value and add it to item_list */
- if (!tab->keys.is_clear_all())
- {
- uint j;
- for (j=0 ; j < table->s->keys ; j++)
- {
- if (tab->keys.is_set(j))
- {
- if (tmp1.length())
- tmp1.append(',');
- tmp1.append(table->key_info[j].name,
- strlen(table->key_info[j].name),
- system_charset_info);
- }
- }
- }
- if (tmp1.length())
- item_list.push_back(new Item_string(tmp1.ptr(),tmp1.length(),cs));
- else
- item_list.push_back(item_null);
-
- /* Build "key", "key_len", and "ref" values and add them to item_list */
- if (tab->ref.key_parts)
- {
- KEY *key_info=table->key_info+ tab->ref.key;
- register uint length;
- item_list.push_back(new Item_string(key_info->name,
- strlen(key_info->name),
- system_charset_info));
- length= longlong2str(tab->ref.key_length, keylen_str_buf, 10) -
- keylen_str_buf;
- item_list.push_back(new Item_string(keylen_str_buf, length,
- system_charset_info));
- for (store_key **ref=tab->ref.key_copy ; *ref ; ref++)
- {
- if (tmp2.length())
- tmp2.append(',');
- tmp2.append((*ref)->name(), strlen((*ref)->name()),
- system_charset_info);
- }
- item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
- }
- else if (tab->type == JT_NEXT)
- {
- KEY *key_info=table->key_info+ tab->index;
- register uint length;
- item_list.push_back(new Item_string(key_info->name,
- strlen(key_info->name),cs));
- length= longlong2str(key_info->key_length, keylen_str_buf, 10) -
- keylen_str_buf;
- item_list.push_back(new Item_string(keylen_str_buf,
- length,
- system_charset_info));
- item_list.push_back(item_null);
- }
- else if (tab->select && tab->select->quick)
- {
- tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
- item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
- item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs));
- item_list.push_back(item_null);
- }
- else
- {
- if (table_list->schema_table &&
- table_list->schema_table->i_s_requested_object &
OPTIMIZE_I_S_TABLE)
- {
- const char *tmp_buff;
- int f_idx;
- if (table_list->has_db_lookup_value)
- {
- f_idx= table_list->schema_table->idx_field1;
- tmp_buff= table_list->schema_table->fields_info[f_idx].field_name;
- tmp2.append(tmp_buff, strlen(tmp_buff), cs);
- }
- if (table_list->has_table_lookup_value)
- {
- if (table_list->has_db_lookup_value)
- tmp2.append(',');
- f_idx= table_list->schema_table->idx_field2;
- tmp_buff= table_list->schema_table->fields_info[f_idx].field_name;
- tmp2.append(tmp_buff, strlen(tmp_buff), cs);
- }
- if (tmp2.length())
- item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
- else
- item_list.push_back(item_null);
- }
- else
- item_list.push_back(item_null);
- item_list.push_back(item_null);
- item_list.push_back(item_null);
- }
-
- /* Add "rows" field to item_list. */
- if (table_list->schema_table)
- {
- /* in_rows */
- if (join->thd->lex->describe & DESCRIBE_EXTENDED)
- item_list.push_back(item_null);
- /* rows */
- item_list.push_back(item_null);
- }
- else
- {
- double examined_rows;
- if (tab->select && tab->select->quick)
- examined_rows= rows2double(tab->select->quick->records);
- else if (tab->type == JT_NEXT || tab->type == JT_ALL)
- {
- if (tab->limit)
- examined_rows= rows2double(tab->limit);
- else
- {
- tab->table->file->info(HA_STATUS_VARIABLE);
- examined_rows= rows2double(tab->table->file->stats.records);
- }
- }
- else
- examined_rows= join->best_positions[i].records_read;
-
- item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows,
- MY_INT64_NUM_DECIMAL_DIGITS));
-
- /* Add "filtered" field to item_list. */
- if (join->thd->lex->describe & DESCRIBE_EXTENDED)
- {
- float f= 0.0;
- if (examined_rows)
- f= (float) (100.0 * join->best_positions[i].records_read /
- examined_rows);
- item_list.push_back(new Item_float(f, 2));
- }
- }
-
- /* Build "Extra" field and add it to item_list. */
- my_bool key_read=table->key_read;
- if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
- table->covering_keys.is_set(tab->index))
- key_read=1;
- if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
- !((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row)
- key_read=1;
-
- if (tab->info)
- item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
- else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
- {
- if (tab->packed_info & TAB_INFO_USING_INDEX)
- extra.append(STRING_WITH_LEN("; Using index"));
- if (tab->packed_info & TAB_INFO_USING_WHERE)
- extra.append(STRING_WITH_LEN("; Using where"));
- if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
- extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
- /* Skip initial "; "*/
- const char *str= extra.ptr();
- uint32 len= extra.length();
- if (len)
- {
- str += 2;
- len -= 2;
- }
- item_list.push_back(new Item_string(str, len, cs));
- }
- else
- {
- uint keyno= MAX_KEY;
- if (tab->ref.key_parts)
- keyno= tab->ref.key;
- else if (tab->select && tab->select->quick)
- keyno = tab->select->quick->index;
-
- if ((keyno != MAX_KEY && keyno ==
table->file->pushed_idx_cond_keyno &&
- table->file->pushed_idx_cond) || tab->cache_idx_cond)
- extra.append(STRING_WITH_LEN("; Using index condition"));
-
- if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
- quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
- quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
- {
- extra.append(STRING_WITH_LEN("; Using "));
- tab->select->quick->add_info_string(&extra);
- }
- if (tab->select)
- {
- if (tab->use_quick == QS_DYNAMIC_RANGE)
- {
- /* 4 bits per 1 hex digit + terminating '\0' */
- char buf[MAX_KEY / 4 + 1];
- extra.append(STRING_WITH_LEN("; Range checked for each "
- "record (index map: 0x"));
- extra.append(tab->keys.print(buf));
- extra.append(')');
- }
- else if (tab->select->cond)
- {
- const Item *pushed_cond= tab->table->file->pushed_cond;
-
- if
(thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
- pushed_cond)
- {
- extra.append(STRING_WITH_LEN("; Using where with pushed "
- "condition"));
- if (thd->lex->describe & DESCRIBE_EXTENDED)
- {
- extra.append(STRING_WITH_LEN(": "));
- ((Item *)pushed_cond)->print(&extra, QT_ORDINARY);
- }
- }
- else
- extra.append(STRING_WITH_LEN("; Using where"));
- }
- }
- if (table_list->schema_table &&
- table_list->schema_table->i_s_requested_object &
OPTIMIZE_I_S_TABLE)
- {
- if (!table_list->table_open_method)
- extra.append(STRING_WITH_LEN("; Skip_open_table"));
- else if (table_list->table_open_method == OPEN_FRM_ONLY)
- extra.append(STRING_WITH_LEN("; Open_frm_only"));
- else
- extra.append(STRING_WITH_LEN("; Open_full_table"));
- if (table_list->has_db_lookup_value &&
- table_list->has_table_lookup_value)
- extra.append(STRING_WITH_LEN("; Scanned 0 databases"));
- else if (table_list->has_db_lookup_value ||
- table_list->has_table_lookup_value)
- extra.append(STRING_WITH_LEN("; Scanned 1 database"));
- else
- extra.append(STRING_WITH_LEN("; Scanned all databases"));
- }
- if (key_read)
- {
- if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
- {
- QUICK_GROUP_MIN_MAX_SELECT *qgs=
- (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick;
- extra.append(STRING_WITH_LEN("; Using index for group-by"));
- qgs->append_loose_scan_type(&extra);
- }
- else
- extra.append(STRING_WITH_LEN("; Using index"));
- }
- if (table->reginfo.not_exists_optimize)
- extra.append(STRING_WITH_LEN("; Not exists"));
-
- if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
- !(((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags &
- (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED)))
- {
- /*
- During normal execution of a query, multi_range_read_init() is
- called to initialize MRR. If HA_MRR_SORTED is set at this point,
- multi_range_read_init() for any native MRR implementation will
- revert to default MRR because they cannot produce sorted output
- currently.
- Calling multi_range_read_init() can potentially be costly, so it
- is not done when executing an EXPLAIN. We therefore make the
- assumption that HA_MRR_SORTED means no MRR. If some MRR native
- implementation will support sorted output in the future, a
- function "bool mrr_supports_sorted()" should be added in the
- handler.
- */
- extra.append(STRING_WITH_LEN("; Using MRR"));
- }
- if (need_tmp_table)
- {
- need_tmp_table=0;
- extra.append(STRING_WITH_LEN("; Using temporary"));
- }
- if (need_order)
- {
- need_order=0;
- extra.append(STRING_WITH_LEN("; Using filesort"));
- }
- if (distinct & test_all_bits(used_tables,thd->used_tables))
- extra.append(STRING_WITH_LEN("; Distinct"));
-
- if (tab->loosescan_match_tab)
- {
- extra.append(STRING_WITH_LEN("; LooseScan"));
- }
-
- if (tab->flush_weedout_table)
- extra.append(STRING_WITH_LEN("; Start temporary"));
- if (tab->check_weed_out_table)
- extra.append(STRING_WITH_LEN("; End temporary"));
- else if (tab->do_firstmatch)
- {
- if (tab->do_firstmatch == join->join_tab - 1)
- extra.append(STRING_WITH_LEN("; FirstMatch"));
- else
- {
- extra.append(STRING_WITH_LEN("; FirstMatch("));
- TABLE *prev_table=tab->do_firstmatch->table;
- if (prev_table->derived_select_number)
- {
- char namebuf[NAME_LEN];
- /* Derived table name generation */
- int len= my_snprintf(namebuf, sizeof(namebuf)-1,
- "<derived%u>",
- prev_table->derived_select_number);
- extra.append(namebuf, len);
- }
- else
- extra.append(prev_table->pos_in_table_list->alias);
- extra.append(STRING_WITH_LEN(")"));
- }
- }
- uint sj_strategy= join->best_positions[i].sj_strategy;
- if (sj_is_materialize_strategy(sj_strategy))
- {
- if (join->best_positions[i].n_sj_tables == 1)
- extra.append(STRING_WITH_LEN("; Materialize"));
- else
- {
- last_sjm_table= i + join->best_positions[i].n_sj_tables - 1;
- extra.append(STRING_WITH_LEN("; Start materialize"));
- }
- if (sj_strategy == SJ_OPT_MATERIALIZE_SCAN)
- extra.append(STRING_WITH_LEN("; Scan"));
- }
- else if (last_sjm_table == i)
- {
- extra.append(STRING_WITH_LEN("; End materialize"));
- }
-
- for (uint part= 0; part < tab->ref.key_parts; part++)
- {
- if (tab->ref.cond_guards[part])
- {
- extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
- break;
- }
- }
-
- if (i > 0 && tab[-1].next_select == sub_select_cache)
- {
- extra.append(STRING_WITH_LEN("; Using join buffer ("));
- if ((tab->use_join_cache & JOIN_CACHE::ALG_BNL))
- extra.append(STRING_WITH_LEN("BNL"));
- else if ((tab->use_join_cache & JOIN_CACHE::ALG_BKA))
- extra.append(STRING_WITH_LEN("BKA"));
- else if ((tab->use_join_cache & JOIN_CACHE::ALG_BKA_UNIQUE))
- extra.append(STRING_WITH_LEN("BKA_UNIQUE"));
- else
- DBUG_ASSERT(0);
- if (tab->use_join_cache & JOIN_CACHE::NON_INCREMENTAL_BUFFER)
- extra.append(STRING_WITH_LEN(", regular buffers)"));
- else
- extra.append(STRING_WITH_LEN(", incremental buffers)"));
- }
-
- /* Skip initial "; "*/
- const char *str= extra.ptr();
- uint32 len= extra.length();
- if (len)
- {
- str += 2;
- len -= 2;
- }
- item_list.push_back(new Item_string(str, len, cs));
- }
- // For next iteration
- used_tables|=table->map;
- if (result->send_data(item_list))
- join->error= 1;
- }
- }
- for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
- unit;
- unit= unit->next_unit())
- {
- if (mysql_explain_union(thd, unit, result))
- DBUG_VOID_RETURN;
- }
- DBUG_VOID_RETURN;
-}
-
-
-bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
-{
- DBUG_ENTER("mysql_explain_union");
- bool res= 0;
- SELECT_LEX *first= unit->first_select();
-
- for (SELECT_LEX *sl= first;
- sl;
- sl= sl->next_select())
- {
- // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only
- uint8 uncacheable= (sl->uncacheable & ~UNCACHEABLE_EXPLAIN);
- sl->type= (((&thd->lex->select_lex)==sl)?
- (sl->first_inner_unit() || sl->next_select() ?
- "PRIMARY" : "SIMPLE"):
- ((sl == first)?
- ((sl->linkage == DERIVED_TABLE_TYPE) ?
- "DERIVED":
- ((uncacheable & UNCACHEABLE_DEPENDENT) ?
- "DEPENDENT SUBQUERY":
- (uncacheable?"UNCACHEABLE SUBQUERY":
- "SUBQUERY"))):
- ((uncacheable & UNCACHEABLE_DEPENDENT) ?
- "DEPENDENT UNION":
- uncacheable?"UNCACHEABLE UNION":
- "UNION")));
- sl->options|= SELECT_DESCRIBE;
- }
- if (unit->is_union())
- {
- unit->fake_select_lex->select_number= UINT_MAX; // jost for initialization
- unit->fake_select_lex->type= "UNION RESULT";
- unit->fake_select_lex->options|= SELECT_DESCRIBE;
- if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE)))
- res= unit->exec();
- }
- else
- {
- thd->lex->current_select= first;
- unit->set_limit(unit->global_parameters);
- res= mysql_select(thd, &first->ref_pointer_array,
- first->table_list.first,
- first->with_wild, first->item_list,
- first->where,
- first->order_list.elements +
- first->group_list.elements,
- first->order_list.first,
- first->group_list.first,
- first->having,
- thd->lex->proc_list.first,
- first->options | thd->variables.option_bits | SELECT_DESCRIBE,
- result, unit, first);
- }
- DBUG_RETURN(res || thd->is_error());
-}
-
static void print_table_array(THD *thd, String *str, TABLE_LIST **table,
TABLE_LIST **end, enum_query_type query_type)
=== modified file 'sql/sql_select.h'
--- sql/sql_select.h 2011-03-29 07:30:44 +0000
+++ sql/sql_select.h 2011-06-16 12:58:35 +0000
@@ -2248,8 +2248,6 @@ bool mysql_select(THD *thd, Item ***rref
select_result *result, SELECT_LEX_UNIT *unit,
SELECT_LEX *select_lex);
void free_underlaid_joins(THD *thd, SELECT_LEX *select);
-bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit,
- select_result *result);
Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
Item ***copy_func, Field **from_field,
Field **def_field,
=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc 2011-03-28 08:10:39 +0000
+++ sql/sql_show.cc 2011-06-16 12:58:35 +0000
@@ -20,7 +20,7 @@
#include "sql_priv.h"
#include "unireg.h"
#include "sql_acl.h" // fill_schema_*_privileges
-#include "sql_select.h" // For select_describe
+#include "sql_select.h"
#include "sql_base.h" // close_tables_for_reopen
#include "sql_show.h"
#include "sql_table.h" // filename_to_tablename,
=== modified file 'sql/sql_string.h'
--- sql/sql_string.h 2011-03-22 11:44:40 +0000
+++ sql/sql_string.h 2011-06-16 12:58:35 +0000
@@ -422,6 +422,32 @@ public:
}
};
+
+/**
+ 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:
+ StringBuff<12345> str;
+*/
+
+template<size_t buff_sz>
+class StringBuff : public String
RL: I'd appreciate it if you spell out the name: StringBuffer
+{
+ char buff[buff_sz];
+
+public:
+ StringBuff() : String(buff, buff_sz, &my_charset_bin) { length(0); }
+ explicit StringBuff(const CHARSET_INFO *cs) : String(buff, buff_sz, cs)
+ {
+ length(0);
+ }
+};
+
+
static inline bool check_if_only_end_space(const CHARSET_INFO *cs, char *str,
char *end)
{
=== modified file 'sql/sql_update.cc'
--- sql/sql_update.cc 2011-02-25 16:41:57 +0000
+++ sql/sql_update.cc 2011-06-16 12:58:35 +0000
@@ -38,6 +38,7 @@
#include "records.h" // init_read_record,
// end_read_record
#include "filesort.h" // filesort
+#include "opt_explain.h"
#include "sql_derived.h" // mysql_derived_prepare,
// mysql_handle_derived,
// mysql_derived_filling
@@ -262,10 +263,12 @@ int mysql_update(THD *thd,
bool safe_update= test(thd->variables.option_bits & OPTION_SAFE_UPDATES);
bool used_key_is_modified= FALSE, transactional_table, will_batch;
int res;
- int error, loc_error;
+ int error= 1;
+ int loc_error;
uint used_index, dup_key_found;
bool need_sort= TRUE;
bool reverse= FALSE;
+ bool using_filesort;
#ifndef NO_EMBEDDED_ACCESS_CHECKS
uint want_privilege;
#endif
@@ -273,7 +276,7 @@ int mysql_update(THD *thd,
ha_rows updated, found;
key_map old_covering_keys;
TABLE *table;
- SQL_SELECT *select;
+ SQL_SELECT *select= 0;
RL: = NULL, please
READ_RECORD info;
SELECT_LEX *select_lex= &thd->lex->select_lex;
ulonglong id;
@@ -305,7 +308,8 @@ int mysql_update(THD *thd,
mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
DBUG_RETURN(1);
}
- mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ if (!thd->lex->describe)
+ mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
THD_STAGE_INFO(thd, stage_init);
table= table_list->table;
@@ -392,7 +396,14 @@ int mysql_update(THD *thd,
#ifdef WITH_PARTITION_STORAGE_ENGINE
if (prune_partitions(thd, table, conds))
- {
+ { // No matching records
+ if (thd->lex->describe)
+ {
+ error= explain_no_table(thd,
+ "No matching records after partition pruning");
+ goto exit_without_my_ok;
+ }
RL: Can I ask you to replace the text above with "No matching rows ..." ?
+
free_underlaid_joins(thd, select_lex);
my_ok(thd); // No matching records
DBUG_RETURN(0);
@@ -406,6 +417,12 @@ int mysql_update(THD *thd,
if (error || !limit ||
(select && select->check_quick(thd, safe_update, limit)))
{
+ if (thd->lex->describe && !error && !thd->is_error())
+ {
+ error= explain_no_table(thd, "Impossible WHERE");
+ goto exit_without_my_ok;
+ }
RL: The logic here is quite unmaintaintable - it should be refactored.
RL: However, not a mandatory review comment.
+
delete select;
free_underlaid_joins(thd, select_lex);
/*
@@ -431,7 +448,7 @@ int mysql_update(THD *thd,
{
my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
- goto err;
+ goto exit_without_my_ok;
}
}
init_ftfuncs(thd, select_lex, 1);
@@ -451,11 +468,21 @@ int mysql_update(THD *thd,
}
#ifdef WITH_PARTITION_STORAGE_ENGINE
- if (used_key_is_modified || order ||
- partition_key_modified(table, table->write_set))
-#else
- if (used_key_is_modified || order)
+ used_key_is_modified|= partition_key_modified(table, table->write_set);
#endif
+
+ using_filesort= order && (need_sort||used_key_is_modified);
+ if (thd->lex->describe)
+ {
+ const bool using_tmp_table= !using_filesort &&
+ (used_key_is_modified || order);
+ error= explain_single_table_modification(thd, table, select, used_index,
+ limit, using_tmp_table,
+ using_filesort);
+ goto exit_without_my_ok;
+ }
+
+ if (used_key_is_modified || order)
{
/*
We can't update table directly; We must first search after all
@@ -469,7 +496,7 @@ int mysql_update(THD *thd,
}
/* note: We avoid sorting if we sort on the used index */
- if (order && (need_sort || used_key_is_modified))
+ if (using_filesort)
{
/*
Doing an ORDER BY; Let filesort find and sort the rows we are going
@@ -490,7 +517,7 @@ int mysql_update(THD *thd,
&examined_rows, &found_rows))
== HA_POS_ERROR)
{
- goto err;
+ goto exit_without_my_ok;
RL: Some of the "goto exit_without_my_ok" lines contain TAB characters.
}
thd->inc_examined_row_count(examined_rows);
/*
@@ -511,11 +538,11 @@ int mysql_update(THD *thd,
IO_CACHE tempfile;
if (open_cached_file(&tempfile, mysql_tmpdir,TEMP_PREFIX,
DISK_BUFFER_SIZE, MYF(MY_WME)))
- goto err;
+ goto exit_without_my_ok;
/* If quick select is used, initialize it before retrieving rows. */
if (select && select->quick && select->quick->reset())
- goto err;
+ goto exit_without_my_ok;
table->file->try_semi_consistent_read(1);
/*
@@ -592,7 +619,7 @@ int mysql_update(THD *thd,
error=1; /* purecov: inspected */
select->file=tempfile; // Read row ptrs from this file
if (error >= 0)
- goto err;
+ goto exit_without_my_ok;
}
if (table->key_read)
table->restore_column_maps_after_mark_index();
@@ -602,7 +629,7 @@ int mysql_update(THD *thd,
table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
if (select && select->quick && select->quick->reset())
- goto err;
+ goto exit_without_my_ok;
table->file->try_semi_consistent_read(1);
init_read_record(&info, thd, table, select, 0, 1, FALSE);
@@ -911,12 +938,12 @@ int mysql_update(THD *thd,
*updated_return= updated;
DBUG_RETURN((error >= 0 || thd->is_error()) ? 1 : 0);
-err:
+exit_without_my_ok:
delete select;
free_underlaid_joins(thd, select_lex);
table->set_keyread(FALSE);
thd->abort_on_warning= 0;
- DBUG_RETURN(1);
+ DBUG_RETURN(error);
}
/*
@@ -1301,7 +1328,8 @@ int mysql_multi_update_prepare(THD *thd)
mysql_handle_derived(lex, &mysql_derived_cleanup);
DBUG_RETURN(TRUE);
}
- mysql_handle_derived(lex, &mysql_derived_cleanup);
+ if (!thd->lex->describe)
+ mysql_handle_derived(lex, &mysql_derived_cleanup);
DBUG_RETURN (FALSE);
}
@@ -1338,24 +1366,29 @@ bool mysql_multi_update(THD *thd,
(MODE_STRICT_TRANS_TABLES |
MODE_STRICT_ALL_TABLES));
- List<Item> total_list;
+ if (thd->lex->describe)
+ res= explain_multi_table_modification(thd, *result);
+ else
+ {
+ 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,
- options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
- OPTION_SETUP_TABLES_DONE,
- *result, unit, select_lex);
-
- DBUG_PRINT("info",("res: %d report_error: %d", res, (int) thd->is_error()));
- res|= thd->is_error();
- if (unlikely(res))
- {
- /* If we had a another error reported earlier then this will be ignored */
- (*result)->send_error(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR));
- (*result)->abort_result_set();
+ 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,
+ options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
+ OPTION_SETUP_TABLES_DONE,
+ *result, unit, select_lex);
+
+ DBUG_PRINT("info",("res: %d report_error: %d",res, (int) thd->is_error()));
+ res|= thd->is_error();
+ if (unlikely(res))
+ {
+ /* If we had a another error reported earlier then this will be ignored */
+ (*result)->send_error(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR));
+ (*result)->abort_result_set();
+ }
}
thd->abort_on_warning= 0;
DBUG_RETURN(res);
=== modified file 'sql/sql_view.cc'
--- sql/sql_view.cc 2011-03-17 17:39:31 +0000
+++ sql/sql_view.cc 2011-06-16 12:58:35 +0000
@@ -1277,8 +1277,8 @@ bool mysql_make_view(THD *thd, File_pars
underlying tables.
Skip this step if we are opening view for prelocking only.
*/
- if (!table->prelocking_placeholder &&
- (old_lex->sql_command == SQLCOM_SELECT && old_lex->describe))
+ if (!table->prelocking_placeholder && old_lex->describe &&
+ is_explainable_query(old_lex->sql_command))
{
if (check_table_access(thd, SELECT_ACL, view_tables, FALSE,
UINT_MAX, TRUE) &&
=== modified file 'sql/sql_yacc.yy'
--- sql/sql_yacc.yy 2011-03-09 20:54:55 +0000
+++ sql/sql_yacc.yy 2011-06-16 12:58:35 +0000
@@ -11371,11 +11371,16 @@ describe:
opt_describe_column {}
| describe_command opt_extended_describe
{ Lex->describe|= DESCRIBE_NORMAL; }
+ explanable_command
+ { Lex->select_lex.options|= SELECT_DESCRIBE; }
+ ;
+
+explanable_command:
select
- {
- LEX *lex=Lex;
- lex->select_lex.options|= SELECT_DESCRIBE;
- }
+ | insert
+ | replace
+ | update
+ | delete
;
describe_command:
Thanks,
Roy
