Hello Roy,
Thank you for the review.
The patch is available in the tree:
gleb.shchepa@stripped
Thank you,
Gleb.
On 06/17/2011 03:41 PM, Roy Lyseng wrote:
> 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"
Unfortunately not all:
http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-data-manipulation.html
: 12.2. Data Manipulation Statements
:
: 12.2.1. CALL Syntax
: 12.2.2. DELETE Syntax
: 12.2.3. DO Syntax
: 12.2.4. HANDLER Syntax
: 12.2.5. INSERT Syntax
: 12.2.6. LOAD DATA INFILE Syntax
: 12.2.7. LOAD XML Syntax
: 12.2.8. REPLACE Syntax
: 12.2.9. SELECT Syntax
: 12.2.10. Subquery Syntax
: 12.2.11. UPDATE Syntax
I remember that different RDBMS manufacturers use different definition of DML (for example
PgSQL's definition of DML doesn't include SELECT, they call it "query", not DML :-)
So, I'll change that string but with "Explain all SELECT/INSERT/REPLACE/UPDATE/DELETE
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.
Ok.
>
> 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.
Ok.
>
> + 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.
It is easy to confuse the "empty()" name with a predicate. Moreover, in all STL containers
"empty" method is a predicate (common practice and a part of the standard).
I believe that 50% of our colleagues think that "empty" is a predicate and of course other
50% think that this is an imperative ;-)
OTOH I believe such a confuse is impossible with the "cleanup" name :-)
The practice to use the "cleanup" name is common for MySQL and I'd like to save it.
>
> + bool nil() const { return str == NULL; }
>
> RL: Is is_empty() a more descriptive name than nil()?
Ok.
>
> +
> + 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.
Ok, thanks.
>
> + Then the make_list() method links these Items into "items" list.
>
> RL: method -> function. This is applicable several places.
Ok.
> +
> + 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
Ok.
>
> + 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
Ok.
>
> + */
> + 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
Ok.
>
> +{
> + 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
Ok.
>
> 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 ..." ?
Ok.
>
> +
> 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.
Ok.
>
> }
> 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
>
