1 /* Copyright (c) 2011, 2016, Oracle and/or its affiliates. All rights reserved.
2
3 This program is free software; you can redistribute it and/or modify
4 it under the terms of the GNU General Public License, version 2.0,
5 as published by the Free Software Foundation.
6
7 This program is also distributed with certain software (including
8 but not limited to OpenSSL) that is licensed under separate terms,
9 as designated in a particular file or component or in included license
10 documentation. The authors of MySQL hereby grant you an additional
11 permission to link the program and your derivative works with the
12 separately licensed software that they have included with MySQL.
13
14 This program is distributed in the hope that it will be useful,
15 but WITHOUT ANY WARRANTY; without even the implied warranty of
16 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 GNU General Public License, version 2.0, for more details.
18
19 You should have received a copy of the GNU General Public License
20 along with this program; if not, write to the Free Software Foundation,
21 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
22
23 /** @file "EXPLAIN <command>" implementation */
24
25 #include "opt_explain.h"
26 #include "sql_select.h"
27 #include "sql_optimizer.h" // JOIN
28 #include "sql_partition.h" // for make_used_partitions_str()
29 #include "sql_join_buffer.h" // JOIN_CACHE
30 #include "filesort.h" // Filesort
31 #include "opt_explain_format.h"
32 #include "sql_base.h" // lock_tables
33 #include "sql_acl.h" // check_global_access, PROCESS_ACL
34 #include "debug_sync.h" // DEBUG_SYNC
35 #include "opt_trace.h" // Opt_trace_*
36 #include "sql_parse.h" // is_explainable_query
37 #include "mysqld_thd_manager.h" // Global_THD_manager
38
39 typedef qep_row::extra extra;
40
41 static bool mysql_explain_unit(THD *thd, SELECT_LEX_UNIT *unit);
42
43 const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
44 "ALL","range","index","fulltext",
45 "ref_or_null","unique_subquery","index_subquery",
46 "index_merge"
47 };
48
49 static const enum_query_type cond_print_flags=
50 enum_query_type(QT_ORDINARY | QT_SHOW_SELECT_NUMBER);
51
52 static const char plan_not_ready[]= "Plan isn't ready yet";
53
54 /**
55 A base for all Explain_* classes
56
57 Explain_* classes collect and output EXPLAIN data.
58
59 This class hierarchy is a successor of the old select_describe() function of 5.5.
60 */
61
62 class Explain
63 {
64 protected:
65 THD *const thd; ///< cached THD which runs the EXPLAIN command
66 const CHARSET_INFO *const cs; ///< cached pointer to system_charset_info
67 /**
68 Cached SELECT_LEX of the explained query. Used for all explained stmts,
69 including single-table UPDATE (provides way to access ORDER BY of
70 UPDATE).
71 */
72 SELECT_LEX *const select_lex;
73
74 Explain_format *const fmt; ///< shortcut for thd->lex->explain_format
75 enum_parsing_context context_type; ///< associated value for struct. explain
76
77 bool order_list; ///< if query block has ORDER BY
78
79 const bool explain_other; ///< if we explain other thread than us
80
81 protected:
82 class Lazy_condition: public Lazy
83 {
84 Item *const condition;
85 public:
Lazy_condition(Item * condition_arg)86 Lazy_condition(Item *condition_arg): condition(condition_arg) {}
eval(String * ret)87 virtual bool eval(String *ret)
88 {
89 ret->length(0);
90 if (condition)
91 condition->print(ret, cond_print_flags);
92 return false;
93 }
94 };
95
Explain(enum_parsing_context context_type_arg,THD * thd_arg,SELECT_LEX * select_lex_arg)96 explicit Explain(enum_parsing_context context_type_arg,
97 THD *thd_arg, SELECT_LEX *select_lex_arg)
98 : thd(thd_arg),
99 cs(system_charset_info),
100 select_lex(select_lex_arg),
101 fmt(thd->lex->explain_format),
102 context_type(context_type_arg),
103 order_list(false),
104 explain_other(thd_arg != select_lex_arg->master_unit()->thd)
105 {
106 if (explain_other)
107 select_lex_arg->master_unit()
108 ->thd->query_plan.assert_plan_is_locked_if_other();
109 }
110
111 public:
~Explain()112 virtual ~Explain() {}
113
114 bool send();
115
116 /**
117 Tells if it is allowed to print the WHERE / GROUP BY / etc
118 clauses.
119 */
can_print_clauses() const120 bool can_print_clauses() const
121 {
122 /*
123 Certain implementations of Item::print() modify the item, so cannot be
124 called by another thread which does not own the item. Moreover, the
125 owning thread may be modifying the item at this moment (example:
126 Item_in_subselect::finalize_materialization_transform() is done
127 at first execution of the subquery, which happens after the parent query
128 has a plan, and affects how the parent query would be printed).
129 */
130 return !explain_other;
131 }
132
133 protected:
134 /**
135 Explain everything but subqueries
136 */
137 virtual bool shallow_explain();
138 /**
139 Explain the rest of things after the @c shallow_explain() call
140 */
141 bool explain_subqueries();
142 bool mark_subqueries(Item *item, qep_row *destination);
143 bool prepare_columns();
144
145 /**
146 Push a part of the "extra" column into formatter
147
148 Traditional formatter outputs traditional_extra_tags[tag] as is.
149 Hierarchical formatter outputs a property with the json_extra_tags[tag] name
150 and a boolean value of true.
151
152 @param tag type of the "extra" part
153
154 @retval false Ok
155 @retval true Error (OOM)
156 */
push_extra(Extra_tag tag)157 bool push_extra(Extra_tag tag)
158 {
159 extra *e= new extra(tag);
160 return e == NULL || fmt->entry()->col_extra.push_back(e);
161 }
162
163 /**
164 Push a part of the "extra" column into formatter
165
166 @param tag type of the "extra" part
167 @param arg for traditional formatter: rest of the part text,
168 for hierarchical format: string value of the property
169
170 @retval false Ok
171 @retval true Error (OOM)
172 */
push_extra(Extra_tag tag,const String & arg)173 bool push_extra(Extra_tag tag, const String &arg)
174 {
175 if (arg.is_empty())
176 return push_extra(tag);
177 extra *e= new extra(tag, arg.dup(thd->mem_root));
178 return !e || !e->data || fmt->entry()->col_extra.push_back(e);
179 }
180
181 /**
182 Push a part of the "extra" column into formatter
183
184 @param tag type of the "extra" part
185 @param arg for traditional formatter: rest of the part text,
186 for hierarchical format: string value of the property
187
188 NOTE: arg must be a long-living string constant.
189
190 @retval false Ok
191 @retval true Error (OOM)
192 */
push_extra(Extra_tag tag,const char * arg)193 bool push_extra(Extra_tag tag, const char *arg)
194 {
195 extra *e= new extra(tag, arg);
196 return !e || fmt->entry()->col_extra.push_back(e);
197 }
198
199 /*
200 Rest of the functions are overloadable functions, those calculate and fill
201 "col_*" fields with Items for further sending as EXPLAIN columns.
202
203 "explain_*" functions return false on success and true on error (usually OOM).
204 */
205 virtual bool explain_id();
206 virtual bool explain_select_type();
explain_table_name()207 virtual bool explain_table_name() { return false; }
explain_partitions()208 virtual bool explain_partitions() { return false; }
explain_join_type()209 virtual bool explain_join_type() { return false; }
explain_possible_keys()210 virtual bool explain_possible_keys() { return false; }
211 /** fill col_key and and col_key_len fields together */
explain_key_and_len()212 virtual bool explain_key_and_len() { return false; }
explain_ref()213 virtual bool explain_ref() { return false; }
214 /** fill col_rows and col_filtered fields together */
explain_rows_and_filtered()215 virtual bool explain_rows_and_filtered() { return false; }
explain_extra()216 virtual bool explain_extra() { return false; }
explain_modify_flags()217 virtual bool explain_modify_flags() { return false; }
218
219 protected:
220 /**
221 Returns true if the WHERE, ORDER BY, GROUP BY, etc clauses can safely be
222 traversed: it means that we can iterate through them (no element is
223 added/removed/replaced); the internal details of an element can change
224 though (in particular if that element is an Item_subselect).
225
226 By default, if we are explaining another connection, this is not safe.
227 */
can_walk_clauses()228 virtual bool can_walk_clauses() { return !explain_other; }
229 virtual enum_parsing_context
230 get_subquery_context(SELECT_LEX_UNIT *unit) const;
231 };
232
get_subquery_context(SELECT_LEX_UNIT * unit) const233 enum_parsing_context Explain::get_subquery_context(SELECT_LEX_UNIT *unit) const
234 {
235 return unit->get_explain_marker();
236 }
237
238 /**
239 Explain_no_table class outputs a trivial EXPLAIN row with "extra" column
240
241 This class is intended for simple cases to produce EXPLAIN output
242 with "No tables used", "No matching records" etc.
243 Optionally it can output number of estimated rows in the "row"
244 column.
245
246 @note This class also produces EXPLAIN rows for inner units (if any).
247 */
248
249 class Explain_no_table: public Explain
250 {
251 private:
252 const char *message; ///< cached "message" argument
253 const ha_rows rows; ///< HA_POS_ERROR or cached "rows" argument
254
255 public:
Explain_no_table(THD * thd_arg,SELECT_LEX * select_lex_arg,const char * message_arg,enum_parsing_context context_type_arg=CTX_JOIN,ha_rows rows_arg=HA_POS_ERROR)256 Explain_no_table(THD *thd_arg, SELECT_LEX *select_lex_arg,
257 const char *message_arg,
258 enum_parsing_context context_type_arg= CTX_JOIN,
259 ha_rows rows_arg= HA_POS_ERROR)
260 : Explain(context_type_arg, thd_arg, select_lex_arg),
261 message(message_arg), rows(rows_arg)
262 {
263 if (can_walk_clauses())
264 order_list= MY_TEST(select_lex_arg->order_list.elements);
265 }
266
267 protected:
268 virtual bool shallow_explain();
269
270 virtual bool explain_rows_and_filtered();
271 virtual bool explain_extra();
272 virtual bool explain_modify_flags();
273 private:
274 enum_parsing_context get_subquery_context(SELECT_LEX_UNIT *unit) const;
275 };
276
277
278 /**
279 Explain_union_result class outputs EXPLAIN row for UNION
280 */
281
282 class Explain_union_result : public Explain
283 {
284 public:
Explain_union_result(THD * thd_arg,SELECT_LEX * select_lex_arg)285 Explain_union_result(THD *thd_arg, SELECT_LEX *select_lex_arg)
286 : Explain(CTX_UNION_RESULT, thd_arg, select_lex_arg)
287 {
288 /* it's a UNION: */
289 DBUG_ASSERT(select_lex_arg ==
290 select_lex_arg->master_unit()->fake_select_lex);
291 // Use optimized values from fake_select_lex's join
292 order_list= MY_TEST(select_lex_arg->join->order);
293 // A plan exists so the reads above are safe:
294 DBUG_ASSERT(select_lex_arg->join->get_plan_state() != JOIN::NO_PLAN);
295 }
296
297 protected:
298 virtual bool explain_id();
299 virtual bool explain_table_name();
300 virtual bool explain_join_type();
301 virtual bool explain_extra();
302 /* purecov: begin deadcode */
can_walk_clauses()303 virtual bool can_walk_clauses()
304 {
305 DBUG_ASSERT(0); // UNION result can't have conditions
306 return true; // Because we know that we have a plan
307 }
308 /* purecov: end */
309 };
310
311
312
313 /**
314 Common base class for Explain_join and Explain_table
315 */
316
317 class Explain_table_base : public Explain {
318 protected:
319 const TABLE *table;
320 /**
321 The QEP_TAB which we are currently explaining. It is NULL for the
322 inserted table in INSERT/REPLACE SELECT.
323 @note that you should never read quick() or condition(), they may change
324 under your feet without holding the mutex ; read quick_optim() and
325 condition_optim() instead.
326 */
327 QEP_TAB *tab;
328 key_map usable_keys;
329
Explain_table_base(enum_parsing_context context_type_arg,THD * const thd_arg,SELECT_LEX * select_lex=NULL,TABLE * const table_arg=NULL)330 Explain_table_base(enum_parsing_context context_type_arg,
331 THD *const thd_arg, SELECT_LEX *select_lex= NULL,
332 TABLE *const table_arg= NULL)
333 : Explain(context_type_arg, thd_arg, select_lex), table(table_arg),
334 tab(NULL)
335 {}
336
337 virtual bool explain_partitions();
338 virtual bool explain_possible_keys();
339
340 bool explain_key_parts(int key, uint key_parts);
341 bool explain_key_and_len_quick(QUICK_SELECT_I *quick);
342 bool explain_key_and_len_index(int key);
343 bool explain_key_and_len_index(int key, uint key_length, uint key_parts);
344 bool explain_extra_common(int quick_type,
345 uint keyno);
346 bool explain_tmptable_and_filesort(bool need_tmp_table_arg,
347 bool need_sort_arg);
348 };
349
350
351 /**
352 Explain_join class produces EXPLAIN output for JOINs
353 */
354
355 class Explain_join : public Explain_table_base
356 {
357 private:
358 bool need_tmp_table; ///< add "Using temporary" to "extra" if true
359 bool need_order; ///< add "Using filesort"" to "extra" if true
360 const bool distinct; ///< add "Distinct" string to "extra" column if true
361
362 JOIN *join; ///< current JOIN
363 int quick_type; ///< current quick type, see anon. enum at QUICK_SELECT_I
364 table_map used_tables; ///< accumulate used tables bitmap
365
366 public:
Explain_join(THD * thd_arg,SELECT_LEX * select_lex_arg,bool need_tmp_table_arg,bool need_order_arg,bool distinct_arg)367 Explain_join(THD *thd_arg, SELECT_LEX *select_lex_arg,
368 bool need_tmp_table_arg, bool need_order_arg,
369 bool distinct_arg)
370 : Explain_table_base(CTX_JOIN, thd_arg, select_lex_arg),
371 need_tmp_table(need_tmp_table_arg),
372 need_order(need_order_arg), distinct(distinct_arg),
373 join(select_lex_arg->join), used_tables(0)
374 {
375 DBUG_ASSERT(select_lex->join->thd == select_lex->master_unit()->thd);
376 DBUG_ASSERT(join->get_plan_state() == JOIN::PLAN_READY);
377 /* it is not UNION: */
378 DBUG_ASSERT(join->select_lex != join->unit->fake_select_lex);
379 order_list= MY_TEST(join->order);
380 }
381
382 private:
383 // Next 4 functions begin and end context for GROUP BY, ORDER BY and DISTINC
384 bool begin_sort_context(Explain_sort_clause clause, enum_parsing_context ctx);
385 bool end_sort_context(Explain_sort_clause clause, enum_parsing_context ctx);
386 bool begin_simple_sort_context(Explain_sort_clause clause,
387 enum_parsing_context ctx);
388 bool end_simple_sort_context(Explain_sort_clause clause,
389 enum_parsing_context ctx);
390 bool explain_qep_tab(size_t tab_num);
391
392 protected:
393 virtual bool shallow_explain();
394
395 virtual bool explain_table_name();
396 virtual bool explain_join_type();
397 virtual bool explain_key_and_len();
398 virtual bool explain_ref();
399 virtual bool explain_rows_and_filtered();
400 virtual bool explain_extra();
401 virtual bool explain_select_type();
402 virtual bool explain_id();
403 virtual bool explain_modify_flags();
can_walk_clauses()404 virtual bool can_walk_clauses()
405 {
406 return true; // Because we know that we have a plan
407 }
408 };
409
410
411 /**
412 Explain_table class produce EXPLAIN output for queries without top-level JOIN
413
414 This class is a simplified version of the Explain_join class. It works in the
415 context of queries which implementation lacks top-level JOIN object (EXPLAIN
416 single-table UPDATE and DELETE).
417 */
418
419 class Explain_table: public Explain_table_base
420 {
421 private:
422 const uint key; ///< cached "key" number argument
423 const ha_rows limit; ///< HA_POS_ERROR or cached "limit" argument
424 const bool need_tmp_table; ///< cached need_tmp_table argument
425 const bool need_sort; ///< cached need_sort argument
426 const enum_mod_type mod_type; ///< Table modification type
427 const bool used_key_is_modified; ///< UPDATE command updates used key
428 const char *message; ///< cached "message" argument
429
430 public:
Explain_table(THD * const thd_arg,SELECT_LEX * select_lex_arg,TABLE * const table_arg,QEP_TAB * tab_arg,uint key_arg,ha_rows limit_arg,bool need_tmp_table_arg,bool need_sort_arg,enum_mod_type mod_type_arg,bool used_key_is_modified_arg,const char * msg)431 Explain_table(THD *const thd_arg, SELECT_LEX *select_lex_arg,
432 TABLE *const table_arg,
433 QEP_TAB *tab_arg,
434 uint key_arg, ha_rows limit_arg,
435 bool need_tmp_table_arg, bool need_sort_arg,
436 enum_mod_type mod_type_arg, bool used_key_is_modified_arg,
437 const char *msg)
438 : Explain_table_base(CTX_JOIN, thd_arg, select_lex_arg, table_arg),
439 key(key_arg),
440 limit(limit_arg),
441 need_tmp_table(need_tmp_table_arg), need_sort(need_sort_arg),
442 mod_type(mod_type_arg), used_key_is_modified(used_key_is_modified_arg),
443 message(msg)
444 {
445 tab= tab_arg;
446 usable_keys= table->possible_quick_keys;
447 if (can_walk_clauses())
448 order_list= MY_TEST(select_lex_arg->order_list.elements);
449 }
450
451 virtual bool explain_modify_flags();
452
453 private:
454 virtual bool explain_tmptable_and_filesort(bool need_tmp_table_arg,
455 bool need_sort_arg);
456 virtual bool shallow_explain();
457
458 virtual bool explain_ref();
459 virtual bool explain_table_name();
460 virtual bool explain_join_type();
461 virtual bool explain_key_and_len();
462 virtual bool explain_rows_and_filtered();
463 virtual bool explain_extra();
464
can_walk_clauses()465 virtual bool can_walk_clauses()
466 {
467 return true; // Because we know that we have a plan
468 }
469 };
470
471
472 /* Explain class functions ****************************************************/
473
474
shallow_explain()475 bool Explain::shallow_explain()
476 {
477 return prepare_columns() || fmt->flush_entry();
478 }
479
480
481 /**
482 Qualify subqueries with WHERE/HAVING/ORDER BY/GROUP BY clause type marker
483
484 @param item Item tree to find subqueries
485 @param destination For WHERE clauses
486
487 @note WHERE clause belongs to TABLE or QEP_TAB. The @c destination parameter
488 provides a pointer to QEP data for such a table to associate a future
489 subquery EXPLAIN output with table QEP provided.
490
491 @retval false OK
492 @retval true Error
493 */
494
mark_subqueries(Item * item,qep_row * destination)495 bool Explain::mark_subqueries(Item *item, qep_row *destination)
496 {
497 if (item == NULL || !fmt->is_hierarchical())
498 return false;
499
500 item->compile(&Item::explain_subquery_checker,
501 reinterpret_cast<uchar **>(&destination),
502 &Item::explain_subquery_propagator,
503 NULL);
504 return false;
505 }
506
explain_ref_key(Explain_format * fmt,uint key_parts,store_key * key_copy[])507 static bool explain_ref_key(Explain_format *fmt,
508 uint key_parts, store_key *key_copy[])
509 {
510 if (key_parts == 0)
511 return false;
512
513 for (uint part_no= 0; part_no < key_parts; part_no++)
514 {
515 const store_key *const s_key= key_copy[part_no];
516 if (s_key == NULL)
517 {
518 // Const keys don't need to be copied
519 if (fmt->entry()->col_ref.push_back(store_key_const_item::static_name))
520 return true; /* purecov: inspected */
521 }
522 else if (fmt->entry()->col_ref.push_back(s_key->name()))
523 return true; /* purecov: inspected */
524 }
525 return false;
526 }
527
528
529 enum_parsing_context
get_subquery_context(SELECT_LEX_UNIT * unit) const530 Explain_no_table::get_subquery_context(SELECT_LEX_UNIT *unit) const
531 {
532 const enum_parsing_context context= Explain::get_subquery_context(unit);
533 if (context == CTX_OPTIMIZED_AWAY_SUBQUERY)
534 return context;
535 if (context == CTX_DERIVED)
536 return context;
537 else if (message != plan_not_ready)
538 /*
539 When zero result is given all subqueries are considered as optimized
540 away.
541 */
542 return CTX_OPTIMIZED_AWAY_SUBQUERY;
543 return context;
544 }
545
546
547 /**
548 Traverses SQL clauses of this query specification to identify children
549 subqueries, marks each of them with the clause they belong to.
550 Then goes though all children subqueries and produces their EXPLAIN
551 output, attached to the proper clause's context.
552
553 @param result result stream
554
555 @retval false Ok
556 @retval true Error (OOM)
557 */
explain_subqueries()558 bool Explain::explain_subqueries()
559 {
560 for (SELECT_LEX_UNIT *unit= select_lex->first_inner_unit();
561 unit;
562 unit= unit->next_unit())
563 {
564 DBUG_ASSERT(explain_other || unit->is_optimized());
565 SELECT_LEX *sl= unit->first_select();
566 enum_parsing_context context= get_subquery_context(unit);
567 if (context == CTX_NONE)
568 context= CTX_OPTIMIZED_AWAY_SUBQUERY;
569
570 if (fmt->begin_context(context, unit))
571 return true;
572
573 if (mysql_explain_unit(thd, unit))
574 return true;
575
576 /*
577 This must be after mysql_explain_unit() so that JOIN::optimize() has run
578 and had a chance to choose materialization.
579 */
580 if (fmt->is_hierarchical() &&
581 (context == CTX_WHERE || context == CTX_HAVING ||
582 context == CTX_SELECT_LIST ||
583 context == CTX_GROUP_BY_SQ || context == CTX_ORDER_BY_SQ) &&
584 (!explain_other ||
585 (sl->join && sl->join->get_plan_state() != JOIN::NO_PLAN)) &&
586 // Check below requires complete plan
587 unit->item &&
588 (unit->item->get_engine_for_explain()->engine_type() ==
589 subselect_engine::HASH_SJ_ENGINE))
590 {
591 fmt->entry()->is_materialized_from_subquery= true;
592 fmt->entry()->col_table_name.set_const("<materialized_subquery>");
593 fmt->entry()->using_temporary= true;
594 fmt->entry()->col_join_type.set_const(join_type_str[JT_EQ_REF]);
595 fmt->entry()->col_key.set_const("<auto_key>");
596
597 const subselect_hash_sj_engine * const engine=
598 static_cast<const subselect_hash_sj_engine *>
599 (unit->item->get_engine_for_explain());
600 const QEP_TAB * const tmp_tab= engine->get_qep_tab();
601
602 char buff_key_len[24];
603 fmt->entry()->col_key_len.set(buff_key_len,
604 longlong2str(tmp_tab->table()->key_info[0].key_length,
605 buff_key_len, 10) - buff_key_len);
606
607 if (explain_ref_key(fmt, tmp_tab->ref().key_parts,
608 tmp_tab->ref().key_copy))
609 return true;
610
611 fmt->entry()->col_rows.set(1);
612 /*
613 The value to look up depends on the outer value, so the materialized
614 subquery is dependent and not cacheable:
615 */
616 fmt->entry()->is_dependent= true;
617 fmt->entry()->is_cacheable= false;
618 }
619
620 if (fmt->end_context(context))
621 return true;
622 }
623 return false;
624 }
625
626
627 /**
628 Pre-calculate table property values for further EXPLAIN output
629 */
prepare_columns()630 bool Explain::prepare_columns()
631 {
632 return explain_id() ||
633 explain_select_type() ||
634 explain_table_name() ||
635 explain_partitions() ||
636 explain_join_type() ||
637 explain_possible_keys() ||
638 explain_key_and_len() ||
639 explain_ref() ||
640 explain_modify_flags() ||
641 explain_rows_and_filtered() ||
642 explain_extra();
643 }
644
645
646 /**
647 Explain class main function
648
649 This function:
650 a) allocates a Query_result_send object (if no one pre-allocated available),
651 b) calculates and sends whole EXPLAIN data.
652
653 @return false if success, true if error
654 */
655
send()656 bool Explain::send()
657 {
658 DBUG_ENTER("Explain::send");
659
660 if (fmt->begin_context(context_type, NULL))
661 DBUG_RETURN(true);
662
663 /* Don't log this into the slow query log */
664 thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED |
665 SERVER_QUERY_NO_GOOD_INDEX_USED);
666
667 bool ret= shallow_explain() || explain_subqueries();
668
669 if (!ret)
670 ret= fmt->end_context(context_type);
671
672 DBUG_RETURN(ret);
673 }
674
675
explain_id()676 bool Explain::explain_id()
677 {
678 if (select_lex->select_number < INT_MAX)
679 fmt->entry()->col_id.set(select_lex->select_number);
680 return false;
681 }
682
683
explain_select_type()684 bool Explain::explain_select_type()
685 {
686 // ignore top-level SELECT_LEXes
687 // Elaborate only when plan is ready
688 if (select_lex->master_unit()->outer_select() &&
689 select_lex->join &&
690 select_lex->join->get_plan_state() != JOIN::NO_PLAN)
691 {
692 fmt->entry()->is_dependent= select_lex->is_dependent();
693 if (select_lex->type() != SELECT_LEX::SLT_DERIVED)
694 fmt->entry()->is_cacheable= select_lex->is_cacheable();
695 }
696 fmt->entry()->col_select_type.set(select_lex->type());
697 return false;
698 }
699
700
701 /* Explain_no_table class functions *******************************************/
702
703
shallow_explain()704 bool Explain_no_table::shallow_explain()
705 {
706 return (fmt->begin_context(CTX_MESSAGE) ||
707 Explain::shallow_explain() ||
708 (can_walk_clauses() &&
709 mark_subqueries(select_lex->where_cond(), fmt->entry())) ||
710 fmt->end_context(CTX_MESSAGE));
711 }
712
713
explain_rows_and_filtered()714 bool Explain_no_table::explain_rows_and_filtered()
715 {
716 /* Don't print estimated # of rows in table for INSERT/REPLACE. */
717 if (rows == HA_POS_ERROR || fmt->entry()->mod_type == MT_INSERT ||
718 fmt->entry()->mod_type == MT_REPLACE)
719 return false;
720 fmt->entry()->col_rows.set(rows);
721 return false;
722 }
723
724
explain_extra()725 bool Explain_no_table::explain_extra()
726 {
727 return fmt->entry()->col_message.set(message);
728 }
729
730
explain_modify_flags()731 bool Explain_no_table::explain_modify_flags()
732 {
733 THD *const query_thd= select_lex->master_unit()->thd;
734 switch (query_thd->query_plan.get_command()) {
735 case SQLCOM_UPDATE_MULTI:
736 case SQLCOM_UPDATE:
737 fmt->entry()->mod_type= MT_UPDATE;
738 break;
739 case SQLCOM_DELETE_MULTI:
740 case SQLCOM_DELETE:
741 fmt->entry()->mod_type= MT_DELETE;
742 break;
743 case SQLCOM_INSERT_SELECT:
744 case SQLCOM_INSERT:
745 fmt->entry()->mod_type= MT_INSERT;
746 break;
747 case SQLCOM_REPLACE_SELECT:
748 case SQLCOM_REPLACE:
749 fmt->entry()->mod_type= MT_REPLACE;
750 break;
751 default: ;
752 }
753 return false;
754 }
755
756
757 /* Explain_union_result class functions ****************************************/
758
759
explain_id()760 bool Explain_union_result::explain_id()
761 {
762 return false;
763 }
764
765
explain_table_name()766 bool Explain_union_result::explain_table_name()
767 {
768 // Get the last of UNION's selects
769 SELECT_LEX *last_select=
770 select_lex->master_unit()->first_select()->last_select();
771 // # characters needed to print select_number of last select
772 int last_length= (int)log10((double)last_select->select_number)+1;
773
774 SELECT_LEX *sl= select_lex->master_unit()->first_select();
775 size_t len= 6, lastop= 0;
776 char table_name_buffer[NAME_LEN];
777 memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
778 /*
779 - len + lastop: current position in table_name_buffer
780 - 6 + last_length: the number of characters needed to print
781 '...,'<last_select->select_number>'>\0'
782 */
783 for (;
784 sl && len + lastop + 6 + last_length < NAME_CHAR_LEN;
785 sl= sl->next_select())
786 {
787 len+= lastop;
788 lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
789 "%u,", sl->select_number);
790 }
791 if (sl || len + lastop >= NAME_CHAR_LEN)
792 {
793 memcpy(table_name_buffer + len, STRING_WITH_LEN("...,"));
794 len+= 4;
795 lastop= my_snprintf(table_name_buffer + len, NAME_CHAR_LEN - len,
796 "%u,", last_select->select_number);
797 }
798 len+= lastop;
799 table_name_buffer[len - 1]= '>'; // change ',' to '>'
800
801 return fmt->entry()->col_table_name.set(table_name_buffer, len);
802 }
803
804
explain_join_type()805 bool Explain_union_result::explain_join_type()
806 {
807 fmt->entry()->col_join_type.set_const(join_type_str[JT_ALL]);
808 return false;
809 }
810
811
explain_extra()812 bool Explain_union_result::explain_extra()
813 {
814 if (!fmt->is_hierarchical())
815 {
816 /*
817 Currently we always use temporary table for UNION result
818 */
819 if (push_extra(ET_USING_TEMPORARY))
820 return true;
821 /*
822 here we assume that the query will return at least two rows, so we
823 show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
824 and no filesort will be actually done, but executing all selects in
825 the UNION to provide precise EXPLAIN information will hardly be
826 appreciated :)
827 */
828 if (order_list)
829 {
830 return push_extra(ET_USING_FILESORT);
831 }
832 }
833 return Explain::explain_extra();
834 }
835
836
837 /* Explain_table_base class functions *****************************************/
838
839
explain_partitions()840 bool Explain_table_base::explain_partitions()
841 {
842 if (table->part_info)
843 return make_used_partitions_str(table->part_info,
844 &fmt->entry()->col_partitions);
845 return false;
846 }
847
848
explain_possible_keys()849 bool Explain_table_base::explain_possible_keys()
850 {
851 if (usable_keys.is_clear_all())
852 return false;
853
854 for (uint j= 0 ; j < table->s->keys ; j++)
855 {
856 if (usable_keys.is_set(j) &&
857 fmt->entry()->col_possible_keys.push_back(table->key_info[j].name))
858 return true;
859 }
860 return false;
861 }
862
863
explain_key_parts(int key,uint key_parts)864 bool Explain_table_base::explain_key_parts(int key, uint key_parts)
865 {
866 KEY_PART_INFO *kp= table->key_info[key].key_part;
867 for (uint i= 0; i < key_parts; i++, kp++)
868 if (fmt->entry()->col_key_parts.push_back(kp->field->field_name))
869 return true;
870 return false;
871 }
872
873
explain_key_and_len_quick(QUICK_SELECT_I * quick)874 bool Explain_table_base::explain_key_and_len_quick(QUICK_SELECT_I *quick)
875 {
876 bool ret= false;
877 StringBuffer<512> str_key(cs);
878 StringBuffer<512> str_key_len(cs);
879
880 if (quick->index != MAX_KEY)
881 ret= explain_key_parts(quick->index,
882 quick->used_key_parts);
883 quick->add_keys_and_lengths(&str_key, &str_key_len);
884 return (ret || fmt->entry()->col_key.set(str_key) ||
885 fmt->entry()->col_key_len.set(str_key_len));
886 }
887
888
explain_key_and_len_index(int key)889 bool Explain_table_base::explain_key_and_len_index(int key)
890 {
891 DBUG_ASSERT(key != MAX_KEY);
892 return explain_key_and_len_index(key, table->key_info[key].key_length,
893 table->key_info[key].user_defined_key_parts);
894 }
895
896
explain_key_and_len_index(int key,uint key_length,uint key_parts)897 bool Explain_table_base::explain_key_and_len_index(int key, uint key_length,
898 uint key_parts)
899 {
900 DBUG_ASSERT(key != MAX_KEY);
901
902 char buff_key_len[24];
903 const KEY *key_info= table->key_info + key;
904 const size_t length= longlong2str(key_length, buff_key_len, 10) - buff_key_len;
905 const bool ret= explain_key_parts(key, key_parts);
906 return (ret || fmt->entry()->col_key.set(key_info->name) ||
907 fmt->entry()->col_key_len.set(buff_key_len, length));
908 }
909
910
explain_extra_common(int quick_type,uint keyno)911 bool Explain_table_base::explain_extra_common(int quick_type,
912 uint keyno)
913 {
914 if (((keyno != MAX_KEY &&
915 keyno == table->file->pushed_idx_cond_keyno &&
916 table->file->pushed_idx_cond) ||
917 (tab && tab->cache_idx_cond)))
918 {
919 StringBuffer<160> buff(cs);
920 if (fmt->is_hierarchical() && can_print_clauses())
921 {
922 if (table->file->pushed_idx_cond)
923 table->file->pushed_idx_cond->print(&buff, cond_print_flags);
924 else
925 tab->cache_idx_cond->print(&buff, cond_print_flags);
926 }
927 if (push_extra(ET_USING_INDEX_CONDITION, buff))
928 return true; /* purecov: inspected */
929 }
930
931 const TABLE* pushed_root= table->file->root_of_pushed_join();
932 if (pushed_root && select_lex->join &&
933 select_lex->join->get_plan_state() == JOIN::PLAN_READY)
934 {
935 char buf[128];
936 size_t len;
937 int pushed_id= 0;
938 for (QEP_TAB* prev= select_lex->join->qep_tab; prev <= tab; prev++)
939 {
940 const TABLE* prev_root= prev->table()->file->root_of_pushed_join();
941 if (prev_root == prev->table())
942 {
943 pushed_id++;
944 if (prev_root == pushed_root)
945 break;
946 }
947 }
948 if (pushed_root == table)
949 {
950 uint pushed_count= tab->table()->file->number_of_pushed_joins();
951 len= my_snprintf(buf, sizeof(buf)-1,
952 "Parent of %d pushed join@%d",
953 pushed_count, pushed_id);
954 }
955 else
956 {
957 len= my_snprintf(buf, sizeof(buf)-1,
958 "Child of '%s' in pushed join@%d",
959 tab->table()->file->parent_of_pushed_join()->alias,
960 pushed_id);
961 }
962
963 {
964 StringBuffer<128> buff(cs);
965 buff.append(buf,len);
966 if (push_extra(ET_PUSHED_JOIN, buff))
967 return true;
968 }
969 }
970
971 switch (quick_type) {
972 case QUICK_SELECT_I::QS_TYPE_ROR_UNION:
973 case QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT:
974 case QUICK_SELECT_I::QS_TYPE_INDEX_MERGE:
975 {
976 StringBuffer<32> buff(cs);
977 tab->quick_optim()->add_info_string(&buff);
978 if (fmt->is_hierarchical())
979 {
980 /*
981 We are replacing existing col_key value with a quickselect info,
982 but not the reverse:
983 */
984 DBUG_ASSERT(fmt->entry()->col_key.length);
985 if (fmt->entry()->col_key.set(buff)) // keep col_key_len intact
986 return true;
987 }
988 else
989 {
990 if (push_extra(ET_USING, buff))
991 return true;
992 }
993 }
994 break;
995 default: ;
996 }
997
998 if (tab)
999 {
1000 if (tab->dynamic_range())
1001 {
1002 StringBuffer<64> str(STRING_WITH_LEN("index map: 0x"), cs);
1003 /* 4 bits per 1 hex digit + terminating '\0' */
1004 char buf[MAX_KEY / 4 + 1];
1005 str.append(tab->keys().print(buf));
1006 if (push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD, str))
1007 return true;
1008 }
1009 else if (tab->condition_optim())
1010 {
1011 const Item *pushed_cond= table->file->pushed_cond;
1012
1013 if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
1014 pushed_cond)
1015 {
1016 StringBuffer<64> buff(cs);
1017 if (can_print_clauses())
1018 ((Item *)pushed_cond)->print(&buff, cond_print_flags);
1019 if (push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION, buff))
1020 return true;
1021 }
1022 else
1023 {
1024 if (fmt->is_hierarchical() && can_print_clauses())
1025 {
1026 Lazy_condition *c= new Lazy_condition(tab->condition_optim());
1027 if (c == NULL)
1028 return true;
1029 fmt->entry()->col_attached_condition.set(c);
1030 }
1031 else if (push_extra(ET_USING_WHERE))
1032 return true;
1033 }
1034 }
1035 }
1036 if (table->reginfo.not_exists_optimize && push_extra(ET_NOT_EXISTS))
1037 return true;
1038
1039 if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE)
1040 {
1041 uint mrr_flags=
1042 ((QUICK_RANGE_SELECT*)(tab->quick_optim()))->mrr_flags;
1043
1044 /*
1045 During normal execution of a query, multi_range_read_init() is
1046 called to initialize MRR. If HA_MRR_SORTED is set at this point,
1047 multi_range_read_init() for any native MRR implementation will
1048 revert to default MRR if not HA_MRR_SUPPORT_SORTED.
1049 Calling multi_range_read_init() can potentially be costly, so it
1050 is not done when executing an EXPLAIN. We therefore simulate
1051 its effect here:
1052 */
1053 if (mrr_flags & HA_MRR_SORTED && !(mrr_flags & HA_MRR_SUPPORT_SORTED))
1054 mrr_flags|= HA_MRR_USE_DEFAULT_IMPL;
1055
1056 if (!(mrr_flags & HA_MRR_USE_DEFAULT_IMPL) && push_extra(ET_USING_MRR))
1057 return true;
1058 }
1059
1060 if (tab && tab->type() == JT_FT &&
1061 (table->file->ha_table_flags() & HA_CAN_FULLTEXT_HINTS))
1062 {
1063 /*
1064 Print info about FT hints.
1065 */
1066 StringBuffer<64> buff(cs);
1067 Ft_hints *ft_hints= tab->ft_func()->get_hints();
1068 bool not_first= false;
1069 if (ft_hints->get_flags() & FT_SORTED)
1070 {
1071 buff.append("sorted");
1072 not_first= true;
1073 }
1074 else if (ft_hints->get_flags() & FT_NO_RANKING)
1075 {
1076 buff.append("no_ranking");
1077 not_first= true;
1078 }
1079 if (ft_hints->get_op_type() != FT_OP_UNDEFINED &&
1080 ft_hints->get_op_type() != FT_OP_NO)
1081 {
1082 char buf[64];
1083 size_t len= 0;
1084
1085 if (not_first)
1086 buff.append(", ");
1087 switch (ft_hints->get_op_type())
1088 {
1089 case FT_OP_GT:
1090 len= my_snprintf(buf, sizeof(buf) - 1,
1091 "rank > %g", ft_hints->get_op_value());
1092 break;
1093 case FT_OP_GE:
1094 len= my_snprintf(buf, sizeof(buf) - 1,
1095 "rank >= %g", ft_hints->get_op_value());
1096 break;
1097 default:
1098 DBUG_ASSERT(0);
1099 }
1100
1101 buff.append(buf, len, cs);
1102 not_first= true;
1103 }
1104
1105 if (ft_hints->get_limit() != HA_POS_ERROR)
1106 {
1107 char buf[64];
1108 size_t len= 0;
1109
1110 if (not_first)
1111 buff.append(", ");
1112
1113 len= my_snprintf(buf, sizeof(buf) - 1,
1114 "limit = %d", ft_hints->get_limit());
1115 buff.append(buf, len, cs);
1116 not_first= true;
1117 }
1118 if (not_first)
1119 push_extra(ET_FT_HINTS, buff);
1120
1121 }
1122
1123 return false;
1124 }
1125
explain_tmptable_and_filesort(bool need_tmp_table_arg,bool need_sort_arg)1126 bool Explain_table_base::explain_tmptable_and_filesort(bool need_tmp_table_arg,
1127 bool need_sort_arg)
1128 {
1129 /*
1130 For hierarchical EXPLAIN we output "Using temporary" and
1131 "Using filesort" with related ORDER BY, GROUP BY or DISTINCT
1132 */
1133 if (fmt->is_hierarchical())
1134 return false;
1135
1136 if (need_tmp_table_arg && push_extra(ET_USING_TEMPORARY))
1137 return true;
1138 if (need_sort_arg && push_extra(ET_USING_FILESORT))
1139 return true;
1140 return false;
1141 }
1142
1143
explain_modify_flags()1144 bool Explain_join::explain_modify_flags()
1145 {
1146 THD::Query_plan const *query_plan= &table->in_use->query_plan;
1147 /*
1148 Because we are PLAN_READY, the following data structures are not changing
1149 and thus are safe to read.
1150 */
1151 switch (query_plan->get_command()) {
1152 case SQLCOM_UPDATE_MULTI:
1153 if (!bitmap_is_clear_all(&table->def_write_set) &&
1154 table->s->table_category != TABLE_CATEGORY_TEMPORARY)
1155 fmt->entry()->mod_type= MT_UPDATE;
1156 break;
1157 case SQLCOM_DELETE_MULTI:
1158 for (TABLE_LIST *at= query_plan->get_lex()->auxiliary_table_list.first;
1159 at;
1160 at= at->next_local)
1161 {
1162 if (at->correspondent_table->is_updatable() &&
1163 at->correspondent_table->updatable_base_table()->table == table)
1164 {
1165 fmt->entry()->mod_type= MT_DELETE;
1166 break;
1167 }
1168 }
1169 break;
1170 case SQLCOM_INSERT_SELECT:
1171 if (table == query_plan->get_lex()->insert_table_leaf->table)
1172 fmt->entry()->mod_type= MT_INSERT;
1173 break;
1174 case SQLCOM_REPLACE_SELECT:
1175 if (table == query_plan->get_lex()->insert_table_leaf->table)
1176 fmt->entry()->mod_type= MT_REPLACE;
1177 break;
1178 default: ;
1179 };
1180 return false;
1181 }
1182
1183
1184 /* Explain_join class functions ***********************************************/
1185
begin_sort_context(Explain_sort_clause clause,enum_parsing_context ctx)1186 bool Explain_join::begin_sort_context(Explain_sort_clause clause,
1187 enum_parsing_context ctx)
1188 {
1189 const Explain_format_flags *flags= &join->explain_flags;
1190 return (flags->get(clause, ESP_EXISTS) &&
1191 !flags->get(clause, ESP_IS_SIMPLE) &&
1192 fmt->begin_context(ctx, NULL, flags));
1193 }
1194
1195
end_sort_context(Explain_sort_clause clause,enum_parsing_context ctx)1196 bool Explain_join::end_sort_context(Explain_sort_clause clause,
1197 enum_parsing_context ctx)
1198 {
1199 const Explain_format_flags *flags= &join->explain_flags;
1200 return (flags->get(clause, ESP_EXISTS) &&
1201 !flags->get(clause, ESP_IS_SIMPLE) &&
1202 fmt->end_context(ctx));
1203 }
1204
1205
begin_simple_sort_context(Explain_sort_clause clause,enum_parsing_context ctx)1206 bool Explain_join::begin_simple_sort_context(Explain_sort_clause clause,
1207 enum_parsing_context ctx)
1208 {
1209 const Explain_format_flags *flags= &join->explain_flags;
1210 return (flags->get(clause, ESP_IS_SIMPLE) &&
1211 fmt->begin_context(ctx, NULL, flags));
1212 }
1213
1214
end_simple_sort_context(Explain_sort_clause clause,enum_parsing_context ctx)1215 bool Explain_join::end_simple_sort_context(Explain_sort_clause clause,
1216 enum_parsing_context ctx)
1217 {
1218 const Explain_format_flags *flags= &join->explain_flags;
1219 return (flags->get(clause, ESP_IS_SIMPLE) &&
1220 fmt->end_context(ctx));
1221 }
1222
1223
shallow_explain()1224 bool Explain_join::shallow_explain()
1225 {
1226 qep_row *join_entry= fmt->entry();
1227
1228 join_entry->col_read_cost.set(join->best_read);
1229
1230 LEX const*query_lex= join->thd->query_plan.get_lex();
1231 if (query_lex->insert_table_leaf &&
1232 query_lex->insert_table_leaf->select_lex == join->select_lex)
1233 {
1234 table= query_lex->insert_table_leaf->table;
1235 /*
1236 The target table for INSERT/REPLACE doesn't actually belong to join,
1237 thus tab is set to NULL. But in order to print it we add it to the
1238 list of plan rows. Explain printing code (traditional/json) will deal with
1239 it.
1240 */
1241 tab= NULL;
1242 if (fmt->begin_context(CTX_QEP_TAB) ||
1243 prepare_columns() ||
1244 fmt->flush_entry() ||
1245 fmt->end_context(CTX_QEP_TAB))
1246 return true; /* purecov: inspected */
1247 }
1248
1249 if (begin_sort_context(ESC_ORDER_BY, CTX_ORDER_BY))
1250 return true; /* purecov: inspected */
1251 if (begin_sort_context(ESC_DISTINCT, CTX_DISTINCT))
1252 return true; /* purecov: inspected */
1253 if (begin_sort_context(ESC_GROUP_BY, CTX_GROUP_BY))
1254 return true; /* purecov: inspected */
1255
1256 if (join->sort_cost > 0.0)
1257 {
1258 /*
1259 Due to begin_sort_context() calls above, fmt->entry() returns another
1260 context than stored in join_entry.
1261 */
1262 DBUG_ASSERT(fmt->entry() != join_entry || !fmt->is_hierarchical());
1263 fmt->entry()->col_read_cost.set(join->sort_cost);
1264 }
1265
1266 if (begin_sort_context(ESC_BUFFER_RESULT, CTX_BUFFER_RESULT))
1267 return true; /* purecov: inspected */
1268
1269 for (size_t t= 0,
1270 cnt= fmt->is_hierarchical() ? join->primary_tables : join->tables;
1271 t < cnt; t++)
1272 {
1273 if (explain_qep_tab(t))
1274 return true;
1275 }
1276
1277 if (end_sort_context(ESC_BUFFER_RESULT, CTX_BUFFER_RESULT))
1278 return true;
1279 if (end_sort_context(ESC_GROUP_BY, CTX_GROUP_BY))
1280 return true;
1281 if (end_sort_context(ESC_DISTINCT, CTX_DISTINCT))
1282 return true;
1283 if (end_sort_context(ESC_ORDER_BY, CTX_ORDER_BY))
1284 return true;
1285
1286 return false;
1287 }
1288
1289
explain_qep_tab(size_t tabnum)1290 bool Explain_join::explain_qep_tab(size_t tabnum)
1291 {
1292 tab= join->qep_tab + tabnum;
1293 if (!tab->position())
1294 return false;
1295 table= tab->table();
1296 usable_keys= tab->keys();
1297 quick_type= -1;
1298
1299 if (tab->type() == JT_RANGE || tab->type() == JT_INDEX_MERGE)
1300 {
1301 DBUG_ASSERT(tab->quick_optim());
1302 quick_type= tab->quick_optim()->get_type();
1303 }
1304
1305 if (tab->starts_weedout())
1306 fmt->begin_context(CTX_DUPLICATES_WEEDOUT);
1307
1308 const bool first_non_const= tabnum == join->const_tables;
1309
1310 if (first_non_const)
1311 {
1312 if (begin_simple_sort_context(ESC_ORDER_BY, CTX_SIMPLE_ORDER_BY))
1313 return true;
1314 if (begin_simple_sort_context(ESC_DISTINCT, CTX_SIMPLE_DISTINCT))
1315 return true;
1316 if (begin_simple_sort_context(ESC_GROUP_BY, CTX_SIMPLE_GROUP_BY))
1317 return true;
1318 }
1319
1320 Semijoin_mat_exec *const sjm= tab->sj_mat_exec();
1321 const enum_parsing_context c= sjm ? CTX_MATERIALIZATION : CTX_QEP_TAB;
1322
1323 if (fmt->begin_context(c) || prepare_columns())
1324 return true;
1325
1326 fmt->entry()->query_block_id= table->pos_in_table_list->query_block_id();
1327
1328 if (sjm)
1329 {
1330 if (sjm->is_scan)
1331 {
1332 fmt->entry()->col_rows.cleanup(); // TODO: set(something reasonable)
1333 }
1334 else
1335 {
1336 fmt->entry()->col_rows.set(1);
1337 }
1338 }
1339
1340 if (fmt->flush_entry() ||
1341 (can_walk_clauses() &&
1342 mark_subqueries(tab->condition_optim(), fmt->entry())))
1343 return true;
1344
1345 if (sjm && fmt->is_hierarchical())
1346 {
1347 for (size_t sjt= sjm->inner_table_index, end= sjt + sjm->table_count;
1348 sjt < end; sjt++)
1349 {
1350 if (explain_qep_tab(sjt))
1351 return true;
1352 }
1353 }
1354
1355 if (fmt->end_context(c))
1356 return true;
1357
1358 if (first_non_const)
1359 {
1360 if (end_simple_sort_context(ESC_GROUP_BY, CTX_SIMPLE_GROUP_BY))
1361 return true;
1362 if (end_simple_sort_context(ESC_DISTINCT, CTX_SIMPLE_DISTINCT))
1363 return true;
1364 if (end_simple_sort_context(ESC_ORDER_BY, CTX_SIMPLE_ORDER_BY))
1365 return true;
1366 }
1367
1368 if (tab->finishes_weedout() &&
1369 fmt->end_context(CTX_DUPLICATES_WEEDOUT))
1370 return true;
1371
1372 used_tables|= tab->table_ref->map();
1373
1374 return false;
1375 }
1376
1377
explain_table_name()1378 bool Explain_join::explain_table_name()
1379 {
1380 if (table->pos_in_table_list->is_view_or_derived() && !fmt->is_hierarchical())
1381 {
1382 /* Derived table name generation */
1383 char table_name_buffer[NAME_LEN];
1384 const size_t len= my_snprintf(table_name_buffer,
1385 sizeof(table_name_buffer) - 1,
1386 "<derived%u>",
1387 table->pos_in_table_list->query_block_id());
1388 return fmt->entry()->col_table_name.set(table_name_buffer, len);
1389 }
1390 else
1391 return fmt->entry()->col_table_name.set(table->pos_in_table_list->alias);
1392 }
1393
1394
explain_select_type()1395 bool Explain_join::explain_select_type()
1396 {
1397 if (tab && sj_is_materialize_strategy(tab->get_sj_strategy()))
1398 fmt->entry()->col_select_type.set(st_select_lex::SLT_MATERIALIZED);
1399 else
1400 return Explain::explain_select_type();
1401 return false;
1402 }
1403
1404
explain_id()1405 bool Explain_join::explain_id()
1406 {
1407 if (tab && sj_is_materialize_strategy(tab->get_sj_strategy()))
1408 fmt->entry()->col_id.set(tab->sjm_query_block_id());
1409 else
1410 return Explain::explain_id();
1411 return false;
1412 }
1413
1414
explain_join_type()1415 bool Explain_join::explain_join_type()
1416 {
1417 fmt->entry()->col_join_type.set_const(join_type_str[tab ? tab->type() : JT_ALL]);
1418 return false;
1419 }
1420
1421
explain_key_and_len()1422 bool Explain_join::explain_key_and_len()
1423 {
1424 if (!tab)
1425 return false;
1426 if (tab->ref().key_parts)
1427 return explain_key_and_len_index(tab->ref().key, tab->ref().key_length,
1428 tab->ref().key_parts);
1429 else if (tab->type() == JT_INDEX_SCAN || tab->type() == JT_FT)
1430 return explain_key_and_len_index(tab->index());
1431 else if (tab->type() == JT_RANGE || tab->type() == JT_INDEX_MERGE ||
1432 ((tab->type() == JT_REF || tab->type() == JT_REF_OR_NULL) &&
1433 tab->quick_optim()))
1434 return explain_key_and_len_quick(tab->quick_optim());
1435 else
1436 {
1437 const TABLE_LIST *table_list= table->pos_in_table_list;
1438 if (table_list->schema_table &&
1439 table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
1440 {
1441 StringBuffer<512> str_key(cs);
1442 const char *f_name;
1443 int f_idx;
1444 if (table_list->has_db_lookup_value)
1445 {
1446 f_idx= table_list->schema_table->idx_field1;
1447 f_name= table_list->schema_table->fields_info[f_idx].field_name;
1448 str_key.append(f_name, strlen(f_name), cs);
1449 }
1450 if (table_list->has_table_lookup_value)
1451 {
1452 if (table_list->has_db_lookup_value)
1453 str_key.append(',');
1454 f_idx= table_list->schema_table->idx_field2;
1455 f_name= table_list->schema_table->fields_info[f_idx].field_name;
1456 str_key.append(f_name, strlen(f_name), cs);
1457 }
1458 if (str_key.length())
1459 return fmt->entry()->col_key.set(str_key);
1460 }
1461 }
1462 return false;
1463 }
1464
1465
explain_ref()1466 bool Explain_join::explain_ref()
1467 {
1468 if (!tab)
1469 return false;
1470 return explain_ref_key(fmt, tab->ref().key_parts, tab->ref().key_copy);
1471 }
1472
human_readable_size(char * buf,int buf_len,double data_size)1473 static void human_readable_size(char *buf, int buf_len, double data_size)
1474 {
1475 char size[]= " KMGTP";
1476 int i;
1477 for (i= 0; data_size > 1024 && i < 5; i++)
1478 data_size/= 1024;
1479 const char mult= i == 0 ? 0 : size[i];
1480 my_snprintf(buf, buf_len, "%llu%c", (ulonglong)data_size, mult);
1481 buf[buf_len - 1]= 0;
1482 }
1483
1484
explain_rows_and_filtered()1485 bool Explain_join::explain_rows_and_filtered()
1486 {
1487 if (!tab || tab->table_ref->schema_table)
1488 return false;
1489
1490 POSITION *const pos= tab->position();
1491
1492 fmt->entry()->col_rows.set(static_cast<ulonglong>(pos->rows_fetched));
1493 fmt->entry()->col_filtered.
1494 set(pos->rows_fetched ?
1495 static_cast<float>(100.0 * tab->position()->filter_effect) :
1496 0.0f);
1497 // Print cost-related info
1498 double prefix_rows= pos->prefix_rowcount;
1499 fmt->entry()->col_prefix_rows.set(static_cast<ulonglong>(prefix_rows));
1500 double const cond_cost= join->cost_model()->row_evaluate_cost(prefix_rows);
1501 fmt->entry()->col_cond_cost.set(cond_cost < 0 ? 0 : cond_cost);
1502
1503 fmt->entry()->col_read_cost.set(pos->read_cost < 0.0 ?
1504 0.0 : pos->read_cost);
1505 fmt->entry()->col_prefix_cost.set(pos->prefix_cost);
1506
1507 // Calculate amount of data from this table per query
1508 char data_size_str[32];
1509 double data_size= prefix_rows * tab->table()->s->rec_buff_length;
1510 human_readable_size(data_size_str, sizeof(data_size_str), data_size);
1511 fmt->entry()->col_data_size_query.set(data_size_str);
1512
1513 return false;
1514 }
1515
1516
explain_extra()1517 bool Explain_join::explain_extra()
1518 {
1519 if (!tab)
1520 return false;
1521 if (tab->type() == JT_SYSTEM && tab->position()->rows_fetched == 0.0)
1522 {
1523 if (push_extra(ET_CONST_ROW_NOT_FOUND))
1524 return true; /* purecov: inspected */
1525 }
1526 else if (tab->type() == JT_CONST && tab->position()->rows_fetched == 0.0)
1527 {
1528 if (push_extra(ET_UNIQUE_ROW_NOT_FOUND))
1529 return true; /* purecov: inspected */
1530 }
1531 else if (tab->type() == JT_CONST && tab->position()->rows_fetched == 1.0 &&
1532 tab->table()->has_null_row())
1533 {
1534 if (push_extra(ET_IMPOSSIBLE_ON_CONDITION))
1535 return true; /* purecov: inspected */
1536 }
1537 else
1538 {
1539 uint keyno= MAX_KEY;
1540 if (tab->ref().key_parts)
1541 keyno= tab->ref().key;
1542 else if (tab->type() == JT_RANGE || tab->type() == JT_INDEX_MERGE)
1543 keyno = tab->quick_optim()->index;
1544
1545 if (explain_extra_common(quick_type, keyno))
1546 return true;
1547
1548 const TABLE_LIST *table_list= table->pos_in_table_list;
1549 if (table_list->schema_table &&
1550 table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
1551 {
1552 if (!table_list->table_open_method)
1553 {
1554 if (push_extra(ET_SKIP_OPEN_TABLE))
1555 return true;
1556 }
1557 else if (table_list->table_open_method == OPEN_FRM_ONLY)
1558 {
1559 if (push_extra(ET_OPEN_FRM_ONLY))
1560 return true;
1561 }
1562 else
1563 {
1564 if (push_extra(ET_OPEN_FULL_TABLE))
1565 return true;
1566 }
1567
1568 StringBuffer<32> buff(cs);
1569 if (table_list->has_db_lookup_value &&
1570 table_list->has_table_lookup_value)
1571 {
1572 if (push_extra(ET_SCANNED_DATABASES, "0"))
1573 return true;
1574 }
1575 else if (table_list->has_db_lookup_value ||
1576 table_list->has_table_lookup_value)
1577 {
1578 if (push_extra(ET_SCANNED_DATABASES, "1"))
1579 return true;
1580 }
1581 else
1582 {
1583 if (push_extra(ET_SCANNED_DATABASES, "all"))
1584 return true;
1585 }
1586 }
1587 if (((tab->type() == JT_INDEX_SCAN || tab->type() == JT_CONST) &&
1588 table->covering_keys.is_set(tab->index())) ||
1589 (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
1590 !((QUICK_ROR_INTERSECT_SELECT*) tab->quick_optim())->need_to_fetch_row) ||
1591 /*
1592 Notice that table->key_read can change on the fly (grep
1593 for set_keyread); so EXPLAIN CONNECTION reads a changing variable,
1594 fortunately it's a bool and not a pointer and the consequences
1595 cannot be severe (at worst, wrong EXPLAIN).
1596 */
1597 table->key_read || tab->keyread_optim())
1598 {
1599 if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
1600 {
1601 QUICK_GROUP_MIN_MAX_SELECT *qgs=
1602 (QUICK_GROUP_MIN_MAX_SELECT *) tab->quick_optim();
1603 StringBuffer<64> buff(cs);
1604 qgs->append_loose_scan_type(&buff);
1605 if (push_extra(ET_USING_INDEX_FOR_GROUP_BY, buff))
1606 return true;
1607 }
1608 else
1609 {
1610 if (push_extra(ET_USING_INDEX))
1611 return true;
1612 }
1613 }
1614
1615 if (explain_tmptable_and_filesort(need_tmp_table, need_order))
1616 return true;
1617 need_tmp_table= need_order= false;
1618
1619 if (distinct && test_all_bits(used_tables,
1620 join->thd->query_plan.get_lex()->used_tables) &&
1621 push_extra(ET_DISTINCT))
1622 return true;
1623
1624 if (tab->do_loosescan() && push_extra(ET_LOOSESCAN))
1625 return true;
1626
1627 if (tab->starts_weedout())
1628 {
1629 if (!fmt->is_hierarchical() && push_extra(ET_START_TEMPORARY))
1630 return true;
1631 }
1632 if (tab->finishes_weedout())
1633 {
1634 if (!fmt->is_hierarchical() && push_extra(ET_END_TEMPORARY))
1635 return true;
1636 }
1637 else if (tab->do_firstmatch())
1638 {
1639 if (tab->firstmatch_return == PRE_FIRST_PLAN_IDX)
1640 {
1641 if (push_extra(ET_FIRST_MATCH))
1642 return true;
1643 }
1644 else
1645 {
1646 StringBuffer<64> buff(cs);
1647 TABLE *prev_table= join->qep_tab[tab->firstmatch_return].table();
1648 if (prev_table->pos_in_table_list->query_block_id() &&
1649 !fmt->is_hierarchical() &&
1650 prev_table->pos_in_table_list->is_derived())
1651 {
1652 char namebuf[NAME_LEN];
1653 /* Derived table name generation */
1654 size_t len= my_snprintf(namebuf, sizeof(namebuf)-1,
1655 "<derived%u>",
1656 prev_table->pos_in_table_list->query_block_id());
1657 buff.append(namebuf, len);
1658 }
1659 else
1660 buff.append(prev_table->pos_in_table_list->alias);
1661 if (push_extra(ET_FIRST_MATCH, buff))
1662 return true;
1663 }
1664 }
1665
1666 if (tab->has_guarded_conds() && push_extra(ET_FULL_SCAN_ON_NULL_KEY))
1667 return true;
1668
1669 if (tab->op && tab->op->type() == QEP_operation::OT_CACHE)
1670 {
1671 const JOIN_CACHE::enum_join_cache_type t=
1672 static_cast<JOIN_CACHE*>(tab->op)->cache_type();
1673 StringBuffer<64> buff(cs);
1674 if (t == JOIN_CACHE::ALG_BNL)
1675 buff.append("Block Nested Loop");
1676 else if (t == JOIN_CACHE::ALG_BKA)
1677 buff.append("Batched Key Access");
1678 else if (t == JOIN_CACHE::ALG_BKA_UNIQUE)
1679 buff.append("Batched Key Access (unique)");
1680 else
1681 DBUG_ASSERT(0); /* purecov: inspected */
1682 if (push_extra(ET_USING_JOIN_BUFFER, buff))
1683 return true;
1684 }
1685 }
1686 if (fmt->is_hierarchical() &&
1687 (!bitmap_is_clear_all(table->read_set) ||
1688 !bitmap_is_clear_all(table->write_set)))
1689 {
1690 Field **fld;
1691 for (fld= table->field; *fld; fld++)
1692 {
1693 if (!bitmap_is_set(table->read_set, (*fld)->field_index) &&
1694 !bitmap_is_set(table->write_set, (*fld)->field_index))
1695 continue;
1696 fmt->entry()->col_used_columns.push_back((*fld)->field_name);
1697 }
1698 }
1699 return false;
1700 }
1701
1702
1703 /* Explain_table class functions **********************************************/
1704
explain_modify_flags()1705 bool Explain_table::explain_modify_flags()
1706 {
1707 fmt->entry()->mod_type= mod_type;
1708 return false;
1709 }
1710
1711
explain_tmptable_and_filesort(bool need_tmp_table_arg,bool need_sort_arg)1712 bool Explain_table::explain_tmptable_and_filesort(bool need_tmp_table_arg,
1713 bool need_sort_arg)
1714 {
1715 if (fmt->is_hierarchical())
1716 {
1717 /*
1718 For hierarchical EXPLAIN we output "using_temporary_table" and
1719 "using_filesort" with related ORDER BY, GROUP BY or DISTINCT
1720 (excluding the single-table UPDATE command that updates used key --
1721 in this case we output "using_temporary_table: for update"
1722 at the "table" node)
1723 */
1724 if (need_tmp_table_arg)
1725 {
1726 DBUG_ASSERT(used_key_is_modified || order_list);
1727 if (used_key_is_modified && push_extra(ET_USING_TEMPORARY, "for update"))
1728 return true;
1729 }
1730 }
1731 else
1732 {
1733 if (need_tmp_table_arg && push_extra(ET_USING_TEMPORARY))
1734 return true;
1735
1736 if (need_sort_arg && push_extra(ET_USING_FILESORT))
1737 return true;
1738 }
1739
1740 return false;
1741 }
1742
1743
shallow_explain()1744 bool Explain_table::shallow_explain()
1745 {
1746 Explain_format_flags flags;
1747 if (order_list)
1748 {
1749 flags.set(ESC_ORDER_BY, ESP_EXISTS);
1750 if (need_sort)
1751 flags.set(ESC_ORDER_BY, ESP_USING_FILESORT);
1752 if (!used_key_is_modified && need_tmp_table)
1753 flags.set(ESC_ORDER_BY, ESP_USING_TMPTABLE);
1754 }
1755
1756 if (order_list && fmt->begin_context(CTX_ORDER_BY, NULL, &flags))
1757 return true;
1758
1759 if (fmt->begin_context(CTX_QEP_TAB))
1760 return true;
1761
1762 if (Explain::shallow_explain() ||
1763 (can_walk_clauses() &&
1764 mark_subqueries(select_lex->where_cond(), fmt->entry())))
1765 return true;
1766
1767 if (fmt->end_context(CTX_QEP_TAB))
1768 return true;
1769
1770 if (order_list && fmt->end_context(CTX_ORDER_BY))
1771 return true;
1772
1773 return false;
1774 }
1775
1776
explain_table_name()1777 bool Explain_table::explain_table_name()
1778 {
1779 return fmt->entry()->col_table_name.set(table->alias);
1780 }
1781
1782
explain_join_type()1783 bool Explain_table::explain_join_type()
1784 {
1785 join_type jt;
1786 if (tab && tab->quick_optim())
1787 jt= calc_join_type(tab->quick_optim()->get_type());
1788 else if (key != MAX_KEY)
1789 jt= JT_INDEX_SCAN;
1790 else
1791 jt= JT_ALL;
1792
1793 fmt->entry()->col_join_type.set_const(join_type_str[jt]);
1794 return false;
1795 }
1796
1797
explain_ref()1798 bool Explain_table::explain_ref()
1799 {
1800 if (tab && tab->quick_optim())
1801 {
1802 int key_parts= tab->quick_optim()->used_key_parts;
1803 while(key_parts--)
1804 {
1805 fmt->entry()->col_ref.push_back("const");
1806 }
1807 }
1808 return false;
1809 }
1810
1811
explain_key_and_len()1812 bool Explain_table::explain_key_and_len()
1813 {
1814 if (tab && tab->quick_optim())
1815 return explain_key_and_len_quick(tab->quick_optim());
1816 else if (key != MAX_KEY)
1817 return explain_key_and_len_index(key);
1818 return false;
1819 }
1820
1821
explain_rows_and_filtered()1822 bool Explain_table::explain_rows_and_filtered()
1823 {
1824 /* Don't print estimated # of rows in table for INSERT/REPLACE. */
1825 if (fmt->entry()->mod_type == MT_INSERT ||
1826 fmt->entry()->mod_type == MT_REPLACE)
1827 return false;
1828
1829 ha_rows examined_rows=
1830 table->in_use->query_plan.get_modification_plan()->examined_rows;
1831 fmt->entry()->col_rows.set(static_cast<long long>(examined_rows));
1832
1833 fmt->entry()->col_filtered.set(100.0);
1834
1835 return false;
1836 }
1837
1838
explain_extra()1839 bool Explain_table::explain_extra()
1840 {
1841 if (message)
1842 return fmt->entry()->col_message.set(message);
1843
1844 uint keyno;
1845 int quick_type;
1846 if (tab && tab->quick_optim())
1847 {
1848 keyno= tab->quick_optim()->index;
1849 quick_type= tab->quick_optim()->get_type();
1850 }
1851 else
1852 {
1853 keyno= key;
1854 quick_type= -1;
1855 }
1856
1857 return (explain_extra_common(quick_type, keyno) ||
1858 explain_tmptable_and_filesort(need_tmp_table, need_sort));
1859 }
1860
1861
1862 /******************************************************************************
1863 External function implementations
1864 ******************************************************************************/
1865
1866
1867 /**
1868 Send a message as an "extra" column value
1869
1870 This function forms the 1st row of the QEP output with a simple text message.
1871 This is useful to explain such trivial cases as "No tables used" etc.
1872
1873 @note Also this function explains the rest of QEP (subqueries or joined
1874 tables if any).
1875
1876 @param thd current THD
1877 @param select_lex select_lex to explain
1878 @param message text message for the "extra" column.
1879 @param ctx current query context, CTX_JOIN in most cases.
1880
1881 @return false if success, true if error
1882 */
1883
explain_no_table(THD * thd,SELECT_LEX * select_lex,const char * message,enum_parsing_context ctx)1884 bool explain_no_table(THD *thd, SELECT_LEX *select_lex, const char *message,
1885 enum_parsing_context ctx)
1886 {
1887 DBUG_ENTER("explain_no_table");
1888 const bool ret= Explain_no_table(thd, select_lex, message, ctx,
1889 HA_POS_ERROR).send();
1890 DBUG_RETURN(ret);
1891 }
1892
1893
1894 /**
1895 Check that we are allowed to explain all views in list.
1896 Because this function is called only when we have a complete plan, we know
1897 that:
1898 - views contained in merge-able views have been merged and brought up in
1899 the top list of tables, so we only need to scan this list
1900 - table_list is not changing while we are reading it.
1901 If we don't have a complete plan, EXPLAIN output does not contain table
1902 names, so we don't need to check views.
1903
1904 @param table_list table to start with, usually lex->query_tables
1905
1906 @returns
1907 true Caller can't EXPLAIN query due to lack of rights on a view in the
1908 query
1909 false Caller can EXPLAIN query
1910 */
1911
check_acl_for_explain(const TABLE_LIST * table_list)1912 static bool check_acl_for_explain(const TABLE_LIST *table_list)
1913 {
1914 for (const TABLE_LIST *tbl= table_list; tbl; tbl= tbl->next_global)
1915 {
1916 if (tbl->is_view() && tbl->view_no_explain)
1917 {
1918 my_message(ER_VIEW_NO_EXPLAIN, ER(ER_VIEW_NO_EXPLAIN), MYF(0));
1919 return true;
1920 }
1921 }
1922 return false;
1923 }
1924
1925
1926 /**
1927 EXPLAIN handling for single-table UPDATE and DELETE queries
1928
1929 Send to the client a QEP data set for single-table EXPLAIN UPDATE/DELETE
1930 queries. As far as single-table UPDATE/DELETE are implemented without
1931 the regular JOIN tree, we can't reuse explain_unit() directly,
1932 thus we deal with this single table in a special way and then call
1933 explain_unit() for subqueries (if any).
1934
1935 @param thd current THD
1936 @param plan table modification plan
1937 @param select Query's select lex
1938
1939 @return false if success, true if error
1940 */
1941
explain_single_table_modification(THD * ethd,const Modification_plan * plan,SELECT_LEX * select)1942 bool explain_single_table_modification(THD *ethd,
1943 const Modification_plan *plan,
1944 SELECT_LEX *select)
1945 {
1946 DBUG_ENTER("explain_single_table_modification");
1947 Query_result_send result;
1948 const THD *const query_thd= select->master_unit()->thd;
1949 const bool other= (query_thd != ethd);
1950 bool ret;
1951
1952 /**
1953 Prepare the self-allocated result object
1954
1955 For queries with top-level JOIN the caller provides pre-allocated
1956 Query_result_send object. Then that JOIN object prepares the
1957 Query_result_send object calling result->prepare() in SELECT_LEX::prepare(),
1958 result->initalize_tables() in JOIN::optimize() and result->prepare2()
1959 in JOIN::exec().
1960 However without the presence of the top-level JOIN we have to
1961 prepare/initialize Query_result_send object manually.
1962 */
1963 List<Item> dummy;
1964 if (result.prepare(dummy, ethd->lex->unit) ||
1965 result.prepare2())
1966 DBUG_RETURN(true); /* purecov: inspected */
1967
1968 ethd->lex->explain_format->send_headers(&result);
1969
1970 if (!other)
1971 {
1972 for (SELECT_LEX_UNIT *unit= select->first_inner_unit();
1973 unit;
1974 unit= unit->next_unit())
1975 {
1976 // Derived tables and const subqueries are already optimized
1977 if (!unit->is_optimized() && unit->optimize(ethd))
1978 DBUG_RETURN(true); /* purecov: inspected */
1979 }
1980 }
1981
1982
1983 if (!plan || plan->zero_result)
1984 {
1985 ret= Explain_no_table(ethd, select,
1986 plan ? plan->message : plan_not_ready,
1987 CTX_JOIN,
1988 HA_POS_ERROR).send();
1989 }
1990 else
1991 {
1992 // Check access rights for views
1993 if (other &&
1994 check_acl_for_explain(query_thd->query_plan.get_lex()->query_tables))
1995 ret= true;
1996 else
1997 ret= Explain_table(ethd, select, plan->table,
1998 plan->tab,
1999 plan->key,
2000 plan->limit,
2001 plan->need_tmp_table,
2002 plan->need_sort,
2003 plan->mod_type,
2004 plan->used_key_is_modified,
2005 plan->message).send() ||
2006 ethd->is_error();
2007 }
2008 if (ret)
2009 result.abort_result_set();
2010 else
2011 result.send_eof();
2012 DBUG_RETURN(ret);
2013 }
2014
2015
2016 /**
2017 Explain select_lex's join.
2018
2019 @param ethd THD of explaining thread
2020 @param select_lex explain join attached to given select_lex
2021 @param ctx current explain context
2022 */
2023
2024 bool
explain_query_specification(THD * ethd,SELECT_LEX * select_lex,enum_parsing_context ctx)2025 explain_query_specification(THD *ethd, SELECT_LEX *select_lex,
2026 enum_parsing_context ctx)
2027 {
2028 Opt_trace_context * const trace= ðd->opt_trace;
2029 Opt_trace_object trace_wrapper(trace);
2030 Opt_trace_object trace_exec(trace, "join_explain");
2031 trace_exec.add_select_number(select_lex->select_number);
2032 Opt_trace_array trace_steps(trace, "steps");
2033 JOIN *join= select_lex->join;
2034
2035 if (!join || join->get_plan_state() == JOIN::NO_PLAN)
2036 return explain_no_table(ethd, select_lex, plan_not_ready, ctx);
2037
2038 const bool other= (join->thd != ethd);
2039 THD::Query_plan const *query_plan= &join->thd->query_plan;
2040
2041 // Check access rights for views
2042 if (other && check_acl_for_explain(query_plan->get_lex()->query_tables))
2043 return true;
2044
2045 THD_STAGE_INFO(ethd, stage_explaining);
2046
2047 bool ret;
2048
2049 switch (join->get_plan_state())
2050 {
2051 case JOIN::ZERO_RESULT:
2052 {
2053 ret= explain_no_table(ethd, select_lex, join->zero_result_cause,
2054 ctx);
2055 break;
2056 }
2057 case JOIN::NO_TABLES:
2058 {
2059 if (query_plan->get_lex()->insert_table_leaf &&
2060 query_plan->get_lex()->insert_table_leaf->select_lex == select_lex)
2061 {
2062 // INSERT/REPLACE SELECT ... FROM dual
2063 ret= Explain_table(ethd, select_lex,
2064 query_plan->get_lex()->insert_table_leaf->table,
2065 NULL,
2066 MAX_KEY,
2067 HA_POS_ERROR,
2068 false,
2069 false,
2070 (query_plan->get_lex()->sql_command == SQLCOM_INSERT_SELECT ?
2071 MT_INSERT : MT_REPLACE),
2072 false,
2073 NULL).send() || ethd->is_error();
2074 }
2075 else
2076 ret= explain_no_table(ethd, select_lex, "No tables used", CTX_JOIN);
2077
2078 break;
2079 }
2080 case JOIN::PLAN_READY:
2081 {
2082 /*
2083 (1) If this connection is explaining its own query
2084 (2) and it hasn't already prepared the JOIN's result,
2085 then we need to prepare it (for example, to materialize I_S tables).
2086 */
2087 if (!other && !join->is_executed() && join->prepare_result())
2088 return true; /* purecov: inspected */
2089
2090 const Explain_format_flags *flags= &join->explain_flags;
2091 const bool need_tmp_table= flags->any(ESP_USING_TMPTABLE);
2092 const bool need_order= flags->any(ESP_USING_FILESORT);
2093 const bool distinct= flags->get(ESC_DISTINCT, ESP_EXISTS);
2094
2095 if (select_lex == select_lex->master_unit()->fake_select_lex)
2096 ret= Explain_union_result(ethd, select_lex).send();
2097 else
2098 ret= Explain_join(ethd, select_lex, need_tmp_table, need_order,
2099 distinct).send();
2100 break;
2101 }
2102 default:
2103 DBUG_ASSERT(0); /* purecov: inspected */
2104 ret= true;
2105 }
2106 DBUG_ASSERT(ret || !ethd->is_error());
2107 ret|= ethd->is_error();
2108 return ret;
2109 }
2110
2111
2112 /**
2113 EXPLAIN handling for SELECT, INSERT/REPLACE SELECT, and multi-table
2114 UPDATE/DELETE queries
2115
2116 Send to the client a QEP data set for any DML statement that has a QEP
2117 represented completely by JOIN object(s).
2118
2119 This function uses a specific Query_result object for sending explain
2120 output to the client.
2121
2122 When explaining own query, the existing Query_result object (found
2123 in outermost SELECT_LEX_UNIT or SELECT_LEX) is used. However, if the
2124 Query_result is unsuitable for explanation (need_explain_interceptor()
2125 returns true), wrap the Query_result inside an Query_result_explain object.
2126
2127 When explaining other query, create a Query_result_send object and prepare it
2128 as if it was a regular SELECT query.
2129
2130 @note see explain_single_table_modification() for single-table
2131 UPDATE/DELETE EXPLAIN handling.
2132
2133 @note Unlike handle_query(), explain_query() calls abort_result_set()
2134 itself in the case of failure (OOM etc.) since it may use
2135 an internally created Query_result object that has to be deleted
2136 before exiting the function.
2137
2138 @param ethd THD of the explaining session
2139 @param unit query tree to explain
2140
2141 @return false if success, true if error
2142 */
2143
explain_query(THD * ethd,SELECT_LEX_UNIT * unit)2144 bool explain_query(THD *ethd, SELECT_LEX_UNIT *unit)
2145 {
2146 DBUG_ENTER("explain_query");
2147
2148 const THD *const query_thd= unit->thd; // THD of query to be explained
2149 const bool other= (ethd != query_thd);
2150
2151 Query_result *explain_result= NULL;
2152
2153 if (!other)
2154 explain_result= unit->query_result() ?
2155 unit->query_result() : unit->first_select()->query_result();
2156
2157 Query_result_explain explain_wrapper(unit, explain_result);
2158
2159 if (other)
2160 {
2161 if (!((explain_result= new Query_result_send)))
2162 DBUG_RETURN(true); /* purecov: inspected */
2163 List<Item> dummy;
2164 if (explain_result->prepare(dummy, ethd->lex->unit) ||
2165 explain_result->prepare2())
2166 DBUG_RETURN(true); /* purecov: inspected */
2167 }
2168 else
2169 {
2170 DBUG_ASSERT(unit->is_optimized());
2171 if (explain_result->need_explain_interceptor())
2172 explain_result= &explain_wrapper;
2173 }
2174
2175 ethd->lex->explain_format->send_headers(explain_result);
2176
2177 // Reset OFFSET/LIMIT for EXPLAIN output
2178 ethd->lex->unit->offset_limit_cnt= 0;
2179 ethd->lex->unit->select_limit_cnt= 0;
2180
2181 const bool res= mysql_explain_unit(ethd, unit);
2182 /*
2183 1) The code which prints the extended description is not robust
2184 against malformed queries, so skip it if we have an error.
2185 2) The code also isn't thread-safe, skip if explaining other thread
2186 (see Explain::can_print_clauses())
2187 3) Currently only SELECT queries can be printed (TODO: fix this)
2188 */
2189 if (!res && // (1)
2190 !other && // (2)
2191 query_thd->query_plan.get_command() == SQLCOM_SELECT) // (3)
2192 {
2193 StringBuffer<1024> str;
2194 /*
2195 The warnings system requires input in utf8, see mysqld_show_warnings().
2196 */
2197 unit->print(&str, enum_query_type(QT_TO_SYSTEM_CHARSET |
2198 QT_SHOW_SELECT_NUMBER));
2199 str.append('\0');
2200 push_warning(ethd, Sql_condition::SL_NOTE, ER_YES, str.ptr());
2201 }
2202
2203 if (res)
2204 explain_result->abort_result_set();
2205 else
2206 explain_result->send_eof();
2207
2208 if (other)
2209 delete explain_result;
2210
2211 DBUG_RETURN(res);
2212 }
2213
2214
2215 /**
2216 Explain UNION or subqueries of the unit
2217
2218 If the unit is a UNION, explain it as a UNION. Otherwise explain nested
2219 subselects.
2220
2221 @param ethd THD of explaining thread
2222 @param unit unit object, might not belong to ethd
2223
2224 @return false if success, true if error
2225 */
2226
mysql_explain_unit(THD * ethd,SELECT_LEX_UNIT * unit)2227 bool mysql_explain_unit(THD *ethd, SELECT_LEX_UNIT *unit)
2228 {
2229 DBUG_ENTER("mysql_explain_unit");
2230 bool res= false;
2231 if (unit->is_union())
2232 res= unit->explain(ethd);
2233 else
2234 res= explain_query_specification(ethd, unit->first_select(), CTX_JOIN);
2235 DBUG_ASSERT(res || !ethd->is_error());
2236 res|= ethd->is_error();
2237 DBUG_RETURN(res);
2238 }
2239
2240 /**
2241 Callback function used by mysql_explain_other() to find thd based
2242 on the thread id.
2243
2244 @note It acquires LOCK_thd_data mutex and LOCK_query_plan mutex,
2245 when it finds matching thd.
2246 It is the responsibility of the caller to release LOCK_thd_data.
2247 We release LOCK_query_plan in the DTOR.
2248 */
2249 class Find_thd_query_lock: public Find_THD_Impl
2250 {
2251 public:
Find_thd_query_lock(my_thread_id value)2252 explicit Find_thd_query_lock(my_thread_id value)
2253 : m_id(value), m_thd(NULL)
2254 {}
~Find_thd_query_lock()2255 ~Find_thd_query_lock()
2256 {
2257 if (m_thd)
2258 m_thd->unlock_query_plan();
2259 }
operator ()(THD * thd)2260 virtual bool operator()(THD *thd)
2261 {
2262 if (thd->thread_id() == m_id)
2263 {
2264 mysql_mutex_lock(&thd->LOCK_thd_data);
2265 thd->lock_query_plan();
2266 m_thd= thd;
2267 return true;
2268 }
2269 return false;
2270 }
2271 private:
2272 const my_thread_id m_id; ///< The thread id we are looking for.
2273 THD *m_thd; ///< THD we found, having this ID.
2274 };
2275
2276
2277 /**
2278 Entry point for EXPLAIN CONNECTION: locates the connection by its ID, takes
2279 proper locks, explains its current statement, releases locks.
2280 @param THD executing this function (== the explainer)
2281 */
mysql_explain_other(THD * thd)2282 void mysql_explain_other(THD *thd)
2283 {
2284 bool res= false;
2285 THD *query_thd= NULL;
2286 bool send_ok= false;
2287 char *user;
2288 bool unlock_thd_data= false;
2289 THD::Query_plan *qp;
2290 DEBUG_SYNC(thd, "before_explain_other");
2291 /*
2292 Check for a super user, if:
2293 1) connected user don't have enough rights, or
2294 2) has switched to another user
2295 then it's not super user.
2296 */
2297 if (!(thd->m_main_security_ctx.check_access(
2298 GLOBAL_ACLS & ~GRANT_ACL)) || // (1)
2299 (0 != strcmp(thd->m_main_security_ctx.priv_user().str, // (2)
2300 thd->security_context()->priv_user().str) ||
2301 0 != my_strcasecmp(system_charset_info,
2302 thd->m_main_security_ctx.priv_host().str,
2303 thd->security_context()->priv_host().str)))
2304 {
2305 // Can see only connections of this user
2306 user= (char *) thd->security_context()->priv_user().str;
2307 }
2308 else
2309 {
2310 // Can see all connections
2311 user= NULL;
2312 }
2313
2314 // Pick thread
2315 Find_thd_query_lock find_thd_query_lock(thd->lex->query_id);
2316 if (!thd->killed)
2317 {
2318 query_thd= Global_THD_manager::
2319 get_instance()->find_thd(&find_thd_query_lock);
2320 if (query_thd)
2321 unlock_thd_data= true;
2322 }
2323
2324 if (!query_thd)
2325 {
2326 my_error(ER_NO_SUCH_THREAD, MYF(0), thd->lex->query_id);
2327 goto err;
2328 }
2329
2330 qp= &query_thd->query_plan;
2331
2332 if (query_thd->get_protocol()->connection_alive() &&
2333 !query_thd->system_thread && qp->get_command() != SQLCOM_END)
2334 {
2335 /*
2336 Don't explain:
2337 1) Prepared statements
2338 2) EXPLAIN to avoid clash in EXPLAIN code
2339 3) statements of stored routine
2340 */
2341 if (!qp->is_ps_query() && // (1)
2342 is_explainable_query(qp->get_command()) &&
2343 !qp->get_lex()->describe && // (2)
2344 qp->get_lex()->sphead == NULL) // (3)
2345 {
2346 Security_context *tmp_sctx= query_thd->security_context();
2347 DBUG_ASSERT(tmp_sctx->user().str);
2348 if (user && strcmp(tmp_sctx->user().str, user))
2349 {
2350 my_error(ER_ACCESS_DENIED_ERROR, MYF(0),
2351 thd->security_context()->priv_user().str,
2352 thd->security_context()->priv_host().str,
2353 (thd->password ?
2354 ER(ER_YES) :
2355 ER(ER_NO)));
2356 goto err;
2357 }
2358 mysql_mutex_unlock(&query_thd->LOCK_thd_data);
2359 unlock_thd_data= false;
2360 }
2361 else
2362 {
2363 my_error(ER_EXPLAIN_NOT_SUPPORTED, MYF(0));
2364 goto err;
2365 }
2366 }
2367 else
2368 {
2369 send_ok= true;
2370 goto err;
2371 }
2372 DEBUG_SYNC(thd, "explain_other_got_thd");
2373 // Get topmost query
2374 switch(qp->get_command())
2375 {
2376 case SQLCOM_UPDATE_MULTI:
2377 case SQLCOM_DELETE_MULTI:
2378 case SQLCOM_REPLACE_SELECT:
2379 case SQLCOM_INSERT_SELECT:
2380 case SQLCOM_SELECT:
2381 res= explain_query(thd, qp->get_lex()->unit);
2382 break;
2383 case SQLCOM_UPDATE:
2384 case SQLCOM_DELETE:
2385 case SQLCOM_INSERT:
2386 case SQLCOM_REPLACE:
2387 res= explain_single_table_modification(thd, qp->get_modification_plan(),
2388 qp->get_lex()->unit->first_select());
2389 break;
2390 default:
2391 DBUG_ASSERT(0); /* purecov: inspected */
2392 send_ok= true; /* purecov: inspected */
2393 break;
2394 }
2395
2396 err:
2397 if (unlock_thd_data)
2398 mysql_mutex_unlock(&query_thd->LOCK_thd_data);
2399
2400 DEBUG_SYNC(thd, "after_explain_other");
2401 if (!res && send_ok)
2402 my_ok(thd, 0);
2403 }
2404
2405
register_in_thd()2406 void Modification_plan::register_in_thd()
2407 {
2408 thd->lock_query_plan();
2409 DBUG_ASSERT(thd->query_plan.get_modification_plan() == NULL);
2410 thd->query_plan.set_modification_plan(this);
2411 thd->unlock_query_plan();
2412 }
2413
2414
2415 /**
2416 Modification_plan's constructor, to represent that we will use an access
2417 method on the table.
2418
2419 @details
2420 Create single table modification plan. The plan is registered in the
2421 given thd unless the modification is done in a sub-statement
2422 (function/trigger).
2423
2424 @param thd_arg owning thread
2425 @param mt modification type - MT_INSERT/MT_UPDATE/etc
2426 @param tab_arg Table to modify
2427 @param key_arg MAX_KEY or and index number of the key that was chosen
2428 to access table data.
2429 @param limit_arg HA_POS_ERROR or LIMIT value.
2430 @param need_tmp_table_arg true if it requires temporary table --
2431 "Using temporary"
2432 string in the "extra" column.
2433 @param need_sort_arg true if it requires filesort() -- "Using filesort"
2434 string in the "extra" column.
2435 @param used_key_is_modified UPDATE updates used key column
2436 @param rows How many rows we plan to modify in the table.
2437 */
2438
Modification_plan(THD * thd_arg,enum_mod_type mt,QEP_TAB * tab_arg,uint key_arg,ha_rows limit_arg,bool need_tmp_table_arg,bool need_sort_arg,bool used_key_is_modified_arg,ha_rows rows)2439 Modification_plan::Modification_plan(THD *thd_arg,
2440 enum_mod_type mt, QEP_TAB *tab_arg,
2441 uint key_arg,
2442 ha_rows limit_arg, bool need_tmp_table_arg,
2443 bool need_sort_arg,
2444 bool used_key_is_modified_arg,
2445 ha_rows rows) :
2446 thd(thd_arg), mod_type(mt), table(tab_arg->table()), tab(tab_arg),
2447 key(key_arg), limit(limit_arg),
2448 need_tmp_table(need_tmp_table_arg), need_sort(need_sort_arg),
2449 used_key_is_modified(used_key_is_modified_arg), message(NULL),
2450 zero_result(false), examined_rows(rows)
2451 {
2452 DBUG_ASSERT(current_thd == thd);
2453 if (!thd->in_sub_stmt)
2454 register_in_thd();
2455 }
2456
2457
2458 /**
2459 Modification_plan's constructor, to convey a message in the "extra" column
2460 of EXPLAIN. This is for the case where this message is the main information
2461 (there is no access path to the table).
2462
2463 @details
2464 Create minimal single table modification plan. The plan is registered in the
2465 given thd unless the modification is done in a sub-statement
2466 (function/trigger).
2467
2468 @param thd_arg Owning thread
2469 @param mt Modification type - MT_INSERT/MT_UPDATE/etc
2470 @param table_arg Table to modify
2471 @param message_arg Message
2472 @param zero_result_arg If we shortcut execution
2473 @param rows How many rows we plan to modify in the table.
2474 */
2475
Modification_plan(THD * thd_arg,enum_mod_type mt,TABLE * table_arg,const char * message_arg,bool zero_result_arg,ha_rows rows)2476 Modification_plan::Modification_plan(THD *thd_arg,
2477 enum_mod_type mt, TABLE *table_arg,
2478 const char *message_arg,
2479 bool zero_result_arg,
2480 ha_rows rows) :
2481 thd(thd_arg), mod_type(mt), table(table_arg),
2482 tab(NULL), key(MAX_KEY), limit(HA_POS_ERROR), need_tmp_table(false),
2483 need_sort(false), used_key_is_modified(false), message(message_arg),
2484 zero_result(zero_result_arg), examined_rows(rows)
2485 {
2486 DBUG_ASSERT(current_thd == thd);
2487 if (!thd->in_sub_stmt)
2488 register_in_thd();
2489 };
2490
2491
~Modification_plan()2492 Modification_plan::~Modification_plan()
2493 {
2494 if (!thd->in_sub_stmt)
2495 {
2496 thd->lock_query_plan();
2497 DBUG_ASSERT(current_thd == thd &&
2498 thd->query_plan.get_modification_plan() == this);
2499 thd->query_plan.set_modification_plan(NULL);
2500 thd->unlock_query_plan();
2501 }
2502 }
2503