1 #ifndef SQL_OPTIMIZER_INCLUDED
2 #define SQL_OPTIMIZER_INCLUDED
3
4 /* Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
5
6 This program is free software; you can redistribute it and/or modify
7 it under the terms of the GNU General Public License, version 2.0,
8 as published by the Free Software Foundation.
9
10 This program is also distributed with certain software (including
11 but not limited to OpenSSL) that is licensed under separate terms,
12 as designated in a particular file or component or in included license
13 documentation. The authors of MySQL hereby grant you an additional
14 permission to link the program and your derivative works with the
15 separately licensed software that they have included with MySQL.
16
17 This program is distributed in the hope that it will be useful,
18 but WITHOUT ANY WARRANTY; without even the implied warranty of
19 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 GNU General Public License, version 2.0, for more details.
21
22 You should have received a copy of the GNU General Public License
23 along with this program; if not, write to the Free Software
24 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
25
26
27 /** @file Classes used for query optimizations */
28
29 /*
30 This structure is used to collect info on potentially sargable
31 predicates in order to check whether they become sargable after
32 reading const tables.
33 We form a bitmap of indexes that can be used for sargable predicates.
34 Only such indexes are involved in range analysis.
35 */
36
37 #include "sql_select.h" // Item_null_array
38
39 class Cost_model_server;
40
41
42 typedef struct st_sargable_param
43 {
44 Field *field; /* field against which to check sargability */
45 Item **arg_value; /* values of potential keys for lookups */
46 uint num_values; /* number of values in the above array */
47 } SARGABLE_PARAM;
48
49 typedef struct st_rollup
50 {
51 enum State { STATE_NONE, STATE_INITED, STATE_READY };
52 State state;
53 Item_null_array null_items;
54 Ref_ptr_array *ref_pointer_arrays;
55 List<Item> *fields;
56 } ROLLUP;
57
58 class JOIN :public Sql_alloc
59 {
60 JOIN(const JOIN &rhs); /**< not implemented */
61 JOIN& operator=(const JOIN &rhs); /**< not implemented */
62
63 public:
JOIN(THD * thd_arg,SELECT_LEX * select)64 JOIN(THD *thd_arg, SELECT_LEX *select)
65 : select_lex(select),
66 unit(select->master_unit()),
67 thd(thd_arg),
68 join_tab(NULL),
69 qep_tab(NULL),
70 best_ref(NULL),
71 map2table(NULL),
72 sort_by_table(NULL),
73 tables(0),
74 primary_tables(0),
75 const_tables(0),
76 tmp_tables(0),
77 send_group_parts(0),
78 sort_and_group(false),
79 first_record(false),
80 // @todo Can this be substituted with select->is_explicitly_grouped()?
81 grouped(select->is_explicitly_grouped()),
82 do_send_rows(true),
83 all_table_map(0),
84 const_table_map(0),
85 found_const_table_map(0),
86 send_records(0),
87 found_records(0),
88 examined_rows(0),
89 row_limit(0),
90 m_select_limit(0),
91 fetch_limit(HA_POS_ERROR),
92 best_positions(NULL),
93 positions(NULL),
94 first_select(sub_select),
95 best_read(0.0),
96 best_rowcount(0),
97 sort_cost(0.0),
98 // Needed in case optimizer short-cuts, set properly in make_tmp_tables_info()
99 fields(&select->item_list),
100 group_fields(),
101 group_fields_cache(),
102 sum_funcs(NULL),
103 sum_funcs_end(),
104 sum_funcs2(NULL),
105 sum_funcs_end2(),
106 tmp_table_param(),
107 lock(thd->lock),
108 rollup(),
109 // @todo Can this be substituted with select->is_implicitly_grouped()?
110 implicit_grouping(select->is_implicitly_grouped()),
111 select_distinct(select->is_distinct()),
112 group_optimized_away(false),
113 simple_order(false),
114 simple_group(false),
115 ordered_index_usage(ordered_index_void),
116 no_order(false),
117 skip_sort_order(false),
118 need_tmp(false),
119 keyuse_array(thd->mem_root),
120 all_fields(select->all_fields),
121 tmp_all_fields1(),
122 tmp_all_fields2(),
123 tmp_all_fields3(),
124 tmp_fields_list1(),
125 tmp_fields_list2(),
126 tmp_fields_list3(),
127 fields_list(select->fields_list),
128 error(0),
129 order(select->order_list.first, ESC_ORDER_BY),
130 group_list(select->group_list.first, ESC_GROUP_BY),
131 explain_flags(),
132 /*
133 Those four members are meaningless before JOIN::optimize(), so force a
134 crash if they are used before that.
135 */
136 where_cond((Item*)1),
137 having_cond((Item*)1),
138 having_for_explain((Item*)1),
139 tables_list((TABLE_LIST*)1),
140 cond_equal(NULL),
141 return_tab(0),
142 ref_ptrs(select->ref_ptr_array_slice(0)),
143 zero_result_cause(NULL),
144 child_subquery_can_materialize(false),
145 allow_outer_refs(false),
146 sj_tmp_tables(),
147 sjm_exec_list(),
148 set_group_rpa(false),
149 group_sent(false),
150 calc_found_rows(false),
151 with_json_agg(select->json_agg_func_used()),
152 optimized(false),
153 executed(false),
154 plan_state(NO_PLAN)
155 {
156 rollup.state= ROLLUP::STATE_NONE;
157 tmp_table_param.end_write_records= HA_POS_ERROR;
158 if (select->order_list.first)
159 explain_flags.set(ESC_ORDER_BY, ESP_EXISTS);
160 if (select->group_list.first)
161 explain_flags.set(ESC_GROUP_BY, ESP_EXISTS);
162 if (select->is_distinct())
163 explain_flags.set(ESC_DISTINCT, ESP_EXISTS);
164 // Calculate the number of groups
165 for (ORDER *group= group_list; group; group= group->next)
166 send_group_parts++;
167 }
168
169 /// Query block that is optimized and executed using this JOIN
170 SELECT_LEX *const select_lex;
171 /// Query expression referring this query block
172 SELECT_LEX_UNIT *const unit;
173 /// Thread handler
174 THD *const thd;
175
176 /**
177 Optimal query execution plan. Initialized with a tentative plan in
178 JOIN::make_join_plan() and later replaced with the optimal plan in
179 get_best_combination().
180 */
181 JOIN_TAB *join_tab;
182 /// Array of QEP_TABs
183 QEP_TAB *qep_tab;
184
185 /**
186 Array of plan operators representing the current (partial) best
187 plan. The array is allocated in JOIN::make_join_plan() and is valid only
188 inside this function. Initially (*best_ref[i]) == join_tab[i].
189 The optimizer reorders best_ref.
190 */
191 JOIN_TAB **best_ref;
192 JOIN_TAB **map2table; ///< mapping between table indexes and JOIN_TABs
193 /*
194 The table which has an index that allows to produce the requried ordering.
195 A special value of 0x1 means that the ordering will be produced by
196 passing 1st non-const table to filesort(). NULL means no such table exists.
197 */
198 TABLE *sort_by_table;
199 /**
200 Before plan has been created, "tables" denote number of input tables in the
201 query block and "primary_tables" is equal to "tables".
202 After plan has been created (after JOIN::get_best_combination()),
203 the JOIN_TAB objects are enumerated as follows:
204 - "tables" gives the total number of allocated JOIN_TAB objects
205 - "primary_tables" gives the number of input tables, including
206 materialized temporary tables from semi-join operation.
207 - "const_tables" are those tables among primary_tables that are detected
208 to be constant.
209 - "tmp_tables" is 0, 1 or 2 and counts the maximum possible number of
210 intermediate tables in post-processing (ie sorting and duplicate removal).
211 Later, tmp_tables will be adjusted to the correct number of
212 intermediate tables, @see JOIN::make_tmp_tables_info.
213 - The remaining tables (ie. tables - primary_tables - tmp_tables) are
214 input tables to materialized semi-join operations.
215 The tables are ordered as follows in the join_tab array:
216 1. const primary table
217 2. non-const primary tables
218 3. intermediate sort/group tables
219 4. possible holes in array
220 5. semi-joined tables used with materialization strategy
221 */
222 uint tables; ///< Total number of tables in query block
223 uint primary_tables; ///< Number of primary input tables in query block
224 uint const_tables; ///< Number of primary tables deemed constant
225 uint tmp_tables; ///< Number of temporary tables used by query
226 uint send_group_parts;
227 /**
228 Indicates that grouping will be performed on the result set during
229 query execution. This field belongs to query execution.
230
231 @see make_group_fields, alloc_group_fields, JOIN::exec
232 */
233 bool sort_and_group;
234 bool first_record;
235 bool grouped; ///< If query contains GROUP BY clause
236 bool do_send_rows; ///< If true, send produced rows using query_result
237 table_map all_table_map; ///< Set of tables contained in query
238 table_map const_table_map; ///< Set of tables found to be const
239 /**
240 Const tables which are either:
241 - not empty
242 - empty but inner to a LEFT JOIN, thus "considered" not empty for the
243 rest of execution (a NULL-complemented row will be used).
244 */
245 table_map found_const_table_map;
246 /* Number of records produced after join + group operation */
247 ha_rows send_records;
248 ha_rows found_records;
249 ha_rows examined_rows;
250 ha_rows row_limit;
251 // m_select_limit is used to decide if we are likely to scan the whole table.
252 ha_rows m_select_limit;
253 /**
254 Used to fetch no more than given amount of rows per one
255 fetch operation of server side cursor.
256 The value is checked in end_send and end_send_group in fashion, similar
257 to offset_limit_cnt:
258 - fetch_limit= HA_POS_ERROR if there is no cursor.
259 - when we open a cursor, we set fetch_limit to 0,
260 - on each fetch iteration we add num_rows to fetch to fetch_limit
261 */
262 ha_rows fetch_limit;
263
264 /**
265 This is the result of join optimization.
266
267 @note This is a scratch array, not used after get_best_combination().
268 */
269 POSITION *best_positions;
270
271 /******* Join optimization state members start *******/
272
273 /* Current join optimization state */
274 POSITION *positions;
275
276 /* We also maintain a stack of join optimization states in * join->positions[] */
277 /******* Join optimization state members end *******/
278
279
280 Next_select_func first_select;
281 /**
282 The cost of best complete join plan found so far during optimization,
283 after optimization phase - cost of picked join order (not taking into
284 account the changes made by test_if_skip_sort_order()).
285 */
286 double best_read;
287 /**
288 The estimated row count of the plan with best read time (see above).
289 */
290 ha_rows best_rowcount;
291 /// Expected cost of filesort.
292 double sort_cost;
293 List<Item> *fields;
294 List<Cached_item> group_fields, group_fields_cache;
295 Item_sum **sum_funcs, ***sum_funcs_end;
296 /** second copy of sumfuncs (for queries with 2 temporary tables */
297 Item_sum **sum_funcs2, ***sum_funcs_end2;
298 Temp_table_param tmp_table_param;
299 MYSQL_LOCK *lock;
300
301 ROLLUP rollup; ///< Used with rollup
302 bool implicit_grouping; ///< True if aggregated but no GROUP BY
303 bool select_distinct; ///< Set if SELECT DISTINCT
304 /**
305 If we have the GROUP BY statement in the query,
306 but the group_list was emptied by optimizer, this
307 flag is TRUE.
308 It happens when fields in the GROUP BY are from
309 constant table
310 */
311 bool group_optimized_away;
312
313 /*
314 simple_xxxxx is set if ORDER/GROUP BY doesn't include any references
315 to other tables than the first non-constant table in the JOIN.
316 It's also set if ORDER/GROUP BY is empty.
317 Used for deciding for or against using a temporary table to compute
318 GROUP/ORDER BY.
319 */
320 bool simple_order, simple_group;
321
322 /*
323 ordered_index_usage is set if an ordered index access
324 should be used instead of a filesort when computing
325 ORDER/GROUP BY.
326 */
327 enum
328 {
329 ordered_index_void, // No ordered index avail.
330 ordered_index_group_by, // Use index for GROUP BY
331 ordered_index_order_by // Use index for ORDER BY
332 } ordered_index_usage;
333
334 /**
335 Is set only in case if we have a GROUP BY clause
336 and no ORDER BY after constant elimination of 'order'.
337 */
338 bool no_order;
339 /** Is set if we have a GROUP BY and we have ORDER BY on a constant. */
340 bool skip_sort_order;
341
342 bool need_tmp;
343
344 // Used and updated by JOIN::make_join_plan() and optimize_keyuse()
345 Key_use_array keyuse_array;
346
347 List<Item> &all_fields; ///< to store all expressions used in query
348 ///Above list changed to use temporary table
349 List<Item> tmp_all_fields1, tmp_all_fields2, tmp_all_fields3;
350 ///Part, shared with list above, emulate following list
351 List<Item> tmp_fields_list1, tmp_fields_list2, tmp_fields_list3;
352 List<Item> &fields_list; ///< hold field list
353 int error; ///< set in optimize(), exec(), prepare_result()
354
355 /**
356 Wrapper for ORDER* pointer to trace origins of ORDER list
357
358 As far as ORDER is just a head object of ORDER expression
359 chain, we need some wrapper object to associate flags with
360 the whole ORDER list.
361 */
362 class ORDER_with_src
363 {
364 /**
365 Private empty class to implement type-safe NULL assignment
366
367 This private utility class allows us to implement a constructor
368 from NULL and only NULL (or 0 -- this is the same thing) and
369 an assignment operator from NULL.
370 Assignments from other pointers still prohibited since other
371 pointer types are incompatible with the "null" type, and the
372 casting is impossible outside of ORDER_with_src class, since
373 the "null" type is private.
374 */
375 struct null {};
376
377 public:
378 ORDER *order; //< ORDER expression that we are wrapping with this class
379 Explain_sort_clause src; //< origin of order list
380
381 private:
382 int flags; //< bitmap of Explain_sort_property
383
384 public:
ORDER_with_src()385 ORDER_with_src() { clean(); }
386
ORDER_with_src(ORDER * order_arg,Explain_sort_clause src_arg)387 ORDER_with_src(ORDER *order_arg, Explain_sort_clause src_arg)
388 : order(order_arg), src(src_arg), flags(order_arg ? ESP_EXISTS : ESP_none)
389 {}
390
391 /**
392 Type-safe NULL assignment
393
394 See a commentary for the "null" type above.
395 */
396 ORDER_with_src &operator=(null *) { clean(); return *this; }
397
398 /**
399 Type-safe constructor from NULL
400
401 See a commentary for the "null" type above.
402 */
ORDER_with_src(null *)403 ORDER_with_src(null *) { clean(); }
404
405 /**
406 Transparent access to the wrapped order list
407
408 These operators are safe, since we don't do any conversion of
409 ORDER_with_src value, but just an access to the wrapped
410 ORDER pointer value.
411 We can use ORDER_with_src objects instead ORDER pointers in
412 a transparent way without accessor functions.
413
414 @note This operator also implements safe "operator bool()"
415 functionality.
416 */
417 operator ORDER *() { return order; }
418 operator const ORDER *() const { return order; }
419
420 ORDER* operator->() const { return order; }
421
clean()422 void clean() { order= NULL; src= ESC_none; flags= ESP_none; }
423
set_flag(Explain_sort_property flag)424 void set_flag(Explain_sort_property flag)
425 {
426 DBUG_ASSERT(order);
427 flags|= flag;
428 }
reset_flag(Explain_sort_property flag)429 void reset_flag(Explain_sort_property flag) { flags&= ~flag; }
get_flag(Explain_sort_property flag)430 bool get_flag(Explain_sort_property flag) const {
431 DBUG_ASSERT(order);
432 return flags & flag;
433 }
get_flags()434 int get_flags() const { DBUG_ASSERT(order); return flags; }
435 };
436
437 /**
438 ORDER BY and GROUP BY lists, to transform with prepare,optimize and exec
439 */
440 ORDER_with_src order, group_list;
441
442 /**
443 Buffer to gather GROUP BY, ORDER BY and DISTINCT QEP details for EXPLAIN
444 */
445 Explain_format_flags explain_flags;
446
447 /**
448 JOIN::having_cond is initially equal to select_lex->having_cond, but may
449 later be changed by optimizations performed by JOIN.
450 The relationship between the JOIN::having_cond condition and the
451 associated variable select_lex->having_value is so that
452 having_value can be:
453 - COND_UNDEF if a having clause was not specified in the query or
454 if it has not been optimized yet
455 - COND_TRUE if the having clause is always true, in which case
456 JOIN::having_cond is set to NULL.
457 - COND_FALSE if the having clause is impossible, in which case
458 JOIN::having_cond is set to NULL
459 - COND_OK otherwise, meaning that the having clause needs to be
460 further evaluated
461 All of the above also applies to the where_cond/select_lex->cond_value
462 pair.
463 */
464 /**
465 Optimized WHERE clause item tree (valid for one single execution).
466 Used in JOIN execution if no tables. Otherwise, attached in pieces to
467 JOIN_TABs and then not used in JOIN execution.
468 Printed by EXPLAIN EXTENDED.
469 Initialized by SELECT_LEX::get_optimizable_conditions().
470 */
471 Item *where_cond;
472 /**
473 Optimized HAVING clause item tree (valid for one single execution).
474 Used in JOIN execution, as last "row filtering" step. With one exception:
475 may be pushed to the JOIN_TABs of temporary tables used in DISTINCT /
476 GROUP BY (see JOIN::make_tmp_tables_info()); in that case having_cond is
477 set to NULL, but is first saved to having_for_explain so that EXPLAIN
478 EXTENDED can still print it.
479 Initialized by SELECT_LEX::get_optimizable_conditions().
480 */
481 Item *having_cond;
482 Item *having_for_explain; ///< Saved optimized HAVING for EXPLAIN
483 /**
484 Pointer set to select_lex->get_table_list() at the start of
485 optimization. May be changed (to NULL) only if opt_sum_query() optimizes
486 tables away.
487 */
488 TABLE_LIST *tables_list;
489 COND_EQUAL *cond_equal;
490 /*
491 Join tab to return to. Points to an element of join->join_tab array, or to
492 join->join_tab[-1].
493 This is used at execution stage to shortcut join enumeration. Currently
494 shortcutting is done to handle outer joins or handle semi-joins with
495 FirstMatch strategy.
496 */
497 plan_idx return_tab;
498
499 /*
500 Used pointer reference for this select.
501 select_lex->ref_pointer_array contains five "slices" of the same length:
502 |========|========|========|========|========|
503 ref_ptrs items0 items1 items2 items3
504 */
505 const Ref_ptr_array ref_ptrs;
506 // Copy of the initial slice above, to be used with different lists
507 Ref_ptr_array items0, items1, items2, items3;
508 // Used by rollup, to restore ref_ptrs after overwriting it.
509 Ref_ptr_array current_ref_ptrs;
510
511 const char *zero_result_cause; ///< not 0 if exec must return zero result
512
513 /**
514 True if, at this stage of processing, subquery materialization is allowed
515 for children subqueries of this JOIN (those in the SELECT list, in WHERE,
516 etc). If false, and we have to evaluate a subquery at this stage, then we
517 must choose EXISTS.
518 */
519 bool child_subquery_can_materialize;
520 /**
521 True if plan search is allowed to use references to expressions outer to
522 this JOIN (for example may set up a 'ref' access looking up an outer
523 expression in the index, etc).
524 */
525 bool allow_outer_refs;
526
527 /* Temporary tables used to weed-out semi-join duplicates */
528 List<TABLE> sj_tmp_tables;
529 List<Semijoin_mat_exec> sjm_exec_list;
530 /* end of allocation caching storage */
531
532 /** TRUE <=> ref_pointer_array is set to items3. */
533 bool set_group_rpa;
534 /** Exec time only: TRUE <=> current group has been sent */
535 bool group_sent;
536 /// If true, calculate found rows for this query block
537 bool calc_found_rows;
538
539 /**
540 This will force tmp table to NOT use index + update for group
541 operation as it'll cause [de]serialization for each json aggregated
542 value and is very ineffective (times worse).
543 Server should use filesort, or tmp table + filesort to resolve GROUP BY
544 with JSON aggregate functions.
545 */
546 bool with_json_agg;
547
548 /// True if plan is const, ie it will return zero or one rows.
plan_is_const()549 bool plan_is_const() const { return const_tables == primary_tables; }
550
551 /**
552 True if plan contains one non-const primary table (ie not including
553 tables taking part in semi-join materialization).
554 */
plan_is_single_table()555 bool plan_is_single_table() { return primary_tables - const_tables == 1; }
556
557 int optimize();
558 void reset();
559 void exec();
560 bool prepare_result();
561 bool destroy();
562 void restore_tmp();
563 bool alloc_func_list();
564 bool make_sum_func_list(List<Item> &all_fields,
565 List<Item> &send_fields,
566 bool before_group_by, bool recompute= FALSE);
567
568 /**
569 Overwrites one slice with the contents of another slice.
570 In the normal case, dst and src have the same size().
571 However: the rollup slices may have smaller size than slice_sz.
572 */
copy_ref_ptr_array(Ref_ptr_array dst_arr,Ref_ptr_array src_arr)573 void copy_ref_ptr_array(Ref_ptr_array dst_arr, Ref_ptr_array src_arr)
574 {
575 DBUG_ASSERT(dst_arr.size() >= src_arr.size());
576 void *dest= dst_arr.array();
577 const void *src= src_arr.array();
578 memcpy(dest, src, src_arr.size() * src_arr.element_size());
579 }
580
581 /// Overwrites 'ref_ptrs' and remembers the the source as 'current'.
set_items_ref_array(Ref_ptr_array src_arr)582 void set_items_ref_array(Ref_ptr_array src_arr)
583 {
584 copy_ref_ptr_array(ref_ptrs, src_arr);
585 current_ref_ptrs= src_arr;
586 }
587
588 /// Initializes 'items0' and remembers that it is 'current'.
init_items_ref_array()589 void init_items_ref_array()
590 {
591 items0= select_lex->ref_ptr_array_slice(1);
592 copy_ref_ptr_array(items0, ref_ptrs);
593 current_ref_ptrs= items0;
594 }
595
596 bool optimize_rollup();
597 bool rollup_process_const_fields();
598 bool rollup_make_fields(List<Item> &all_fields, List<Item> &fields,
599 Item_sum ***func);
600 int rollup_send_data(uint idx);
601 int rollup_write_data(uint idx, TABLE *table);
602 void remove_subq_pushed_predicates();
603 /**
604 Release memory and, if possible, the open tables held by this execution
605 plan (and nested plans). It's used to release some tables before
606 the end of execution in order to increase concurrency and reduce
607 memory consumption.
608 */
609 void join_free();
610 /** Cleanup this JOIN. Not a full cleanup. reusable? */
611 void cleanup();
612
613 MY_ATTRIBUTE((warn_unused_result))
614 bool clear();
615
616 bool save_join_tab();
617 void restore_join_tab();
618 bool init_save_join_tab();
619 /**
620 Return whether the caller should send a row even if the join
621 produced no rows if:
622 - there is an aggregate function (sum_func_count!=0), and
623 - the query is not grouped, and
624 - a possible HAVING clause evaluates to TRUE.
625
626 @note: if there is a having clause, it must be evaluated before
627 returning the row.
628 */
send_row_on_empty_set()629 bool send_row_on_empty_set() const
630 {
631 return (do_send_rows && tmp_table_param.sum_func_count != 0 &&
632 group_list == NULL && !group_optimized_away &&
633 select_lex->having_value != Item::COND_FALSE);
634 }
635
636 bool cache_const_exprs();
637 bool generate_derived_keys();
638 void drop_unused_derived_keys();
639 bool get_best_combination();
640 bool attach_join_conditions(plan_idx last_tab);
641 bool update_equalities_for_sjm();
642 bool add_sorting_to_table(uint idx, ORDER_with_src *order);
643 bool decide_subquery_strategy();
644 void refine_best_rowcount();
645 void mark_const_table(JOIN_TAB *table, Key_use *key);
646 /// State of execution plan. Currently used only for EXPLAIN
647 enum enum_plan_state
648 {
649 NO_PLAN, ///< No plan is ready yet
650 ZERO_RESULT, ///< Zero result cause is set
651 NO_TABLES, ///< Plan has no tables
652 PLAN_READY ///< Plan is ready
653 };
654 /// See enum_plan_state
get_plan_state()655 enum_plan_state get_plan_state() const { return plan_state; }
is_optimized()656 bool is_optimized() const { return optimized; }
set_optimized()657 void set_optimized() { optimized= true; }
is_executed()658 bool is_executed() const { return executed; }
set_executed()659 void set_executed() { executed= true; }
reset_executed()660 void reset_executed() { executed= false; }
661
662 /**
663 Retrieve the cost model object to be used for this join.
664
665 @return Cost model object for the join
666 */
667
cost_model()668 const Cost_model_server* cost_model() const
669 {
670 DBUG_ASSERT(thd != NULL);
671 return thd->cost_model();
672 }
673
674 /**
675 Check if FTS index only access is possible
676 */
677 bool fts_index_access(JOIN_TAB *tab);
678
679 Next_select_func get_end_select_func();
680
681 private:
682 bool optimized; ///< flag to avoid double optimization in EXPLAIN
683 bool executed; ///< Set by exec(), reset by reset()
684
685 /// Final execution plan state. Currently used only for EXPLAIN
686 enum_plan_state plan_state;
687 /**
688 Send current query result set to the client. To be called from JOIN::execute
689
690 @note Explain skips this call during JOIN::execute() execution
691 */
692 void send_data();
693
694 /**
695 Create a temporary table to be used for processing DISTINCT/ORDER
696 BY/GROUP BY.
697
698 @note Will modify JOIN object wrt sort/group attributes
699
700 @param tab the JOIN_TAB object to attach created table to
701 @param tmp_table_fields List of items that will be used to define
702 column types of the table.
703 @param tmp_table_group Group key to use for temporary table, NULL if none.
704 @param save_sum_fields If true, do not replace Item_sum items in
705 @c tmp_fields list with Item_field items referring
706 to fields in temporary table.
707
708 @returns false on success, true on failure
709 */
710 bool create_intermediate_table(QEP_TAB *tab, List<Item> *tmp_table_fields,
711 ORDER_with_src &tmp_table_group,
712 bool save_sum_fields);
713 /**
714 Create the first temporary table to be used for processing DISTINCT/ORDER
715 BY/GROUP BY.
716 */
717 bool create_first_intermediate_table();
718 /**
719 Optimize distinct when used on a subset of the tables.
720
721 E.g.,: SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.b=t2.b
722 In this case we can stop scanning t2 when we have found one t1.a
723 */
724 void optimize_distinct();
725
726 /**
727 Function sets FT hints, initializes FT handlers and
728 checks if FT index can be used as covered.
729 */
730 bool optimize_fts_query();
731
732 bool prune_table_partitions();
733
734 private:
735 void set_prefix_tables();
736 void cleanup_item_list(List<Item> &items) const;
737 void set_semijoin_embedding();
738 bool make_join_plan();
739 bool init_planner_arrays();
740 bool propagate_dependencies();
741 bool extract_const_tables();
742 bool extract_func_dependent_tables();
743 void update_sargable_from_const(SARGABLE_PARAM *sargables);
744 bool estimate_rowcount();
745 void optimize_keyuse();
746 void set_semijoin_info();
747 /**
748 An utility function - apply heuristics and optimize access methods to tables.
749 @note Side effect - this function could set 'Impossible WHERE' zero
750 result.
751 */
752 void adjust_access_methods();
753 void update_depend_map();
754 void update_depend_map(ORDER *order);
755 /**
756 Fill in outer join related info for the execution plan structure.
757
758 For each outer join operation left after simplification of the
759 original query the function set up the following pointers in the linear
760 structure join->join_tab representing the selected execution plan.
761 The first inner table t0 for the operation is set to refer to the last
762 inner table tk through the field t0->last_inner.
763 Any inner table ti for the operation are set to refer to the first
764 inner table ti->first_inner.
765 The first inner table t0 for the operation is set to refer to the
766 first inner table of the embedding outer join operation, if there is any,
767 through the field t0->first_upper.
768 The on expression for the outer join operation is attached to the
769 corresponding first inner table through the field t0->on_expr_ref.
770 Here ti are structures of the JOIN_TAB type.
771
772 EXAMPLE. For the query:
773 @code
774 SELECT * FROM t1
775 LEFT JOIN
776 (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
777 ON (t1.a=t2.a AND t1.b=t3.b)
778 WHERE t1.c > 5,
779 @endcode
780
781 given the execution plan with the table order t1,t2,t3,t4
782 is selected, the following references will be set;
783 t4->last_inner=[t4], t4->first_inner=[t4], t4->first_upper=[t2]
784 t2->last_inner=[t4], t2->first_inner=t3->first_inner=[t2],
785 on expression (t1.a=t2.a AND t1.b=t3.b) will be attached to
786 *t2->on_expr_ref, while t3.a=t4.a will be attached to *t4->on_expr_ref.
787
788 @note
789 The function assumes that the simplification procedure has been
790 already applied to the join query (see simplify_joins).
791 This function can be called only after the execution plan
792 has been chosen.
793 */
794 void make_outerjoin_info();
795
796 /**
797 Initialize ref access for all tables that use it.
798
799 @return False if success, True if error
800
801 @note We cannot setup fields used for ref access before we have sorted
802 the items within multiple equalities according to the final order of
803 the tables involved in the join operation. Currently, this occurs in
804 @see substitute_for_best_equal_field().
805 */
806 bool init_ref_access();
807 bool alloc_qep(uint n);
808 void unplug_join_tabs();
809 bool setup_semijoin_materialized_table(JOIN_TAB *tab, uint tableno,
810 const POSITION *inner_pos,
811 POSITION *sjm_pos);
812
813 bool add_having_as_tmp_table_cond(uint curr_tmp_table);
814 bool make_tmp_tables_info();
815 void set_plan_state(enum_plan_state plan_state_arg);
816 bool compare_costs_of_subquery_strategies(
817 Item_exists_subselect::enum_exec_method *method);
818 ORDER *remove_const(ORDER *first_order, Item *cond,
819 bool change_list, bool *simple_order,
820 const char *clause_type);
821
822 /**
823 Check whether this is a subquery that can be evaluated by index look-ups.
824 If so, change subquery engine to subselect_indexsubquery_engine.
825
826 @retval 1 engine was changed
827 @retval 0 engine wasn't changed
828 @retval -1 OOM
829 */
830 int replace_index_subquery();
831
832 /**
833 Optimize DISTINCT, GROUP BY, ORDER BY clauses
834
835 @retval false ok
836 @retval true an error occured
837 */
838 bool optimize_distinct_group_order();
839
840 /**
841 Test if an index could be used to replace filesort for ORDER BY/GROUP BY
842
843 @details
844 Investigate whether we may use an ordered index as part of either
845 DISTINCT, GROUP BY or ORDER BY execution. An ordered index may be
846 used for only the first of any of these terms to be executed. This
847 is reflected in the order which we check for test_if_skip_sort_order()
848 below. However we do not check for DISTINCT here, as it would have
849 been transformed to a GROUP BY at this stage if it is a candidate for
850 ordered index optimization.
851 If a decision was made to use an ordered index, the availability
852 if such an access path is stored in 'ordered_index_usage' for later
853 use by 'execute' or 'explain'
854 */
855 void test_skip_sort();
856 };
857
858 /// RAII class to ease the call of LEX::mark_broken() if error.
859 class Prepare_error_tracker
860 {
861 public:
Prepare_error_tracker(THD * thd_arg)862 Prepare_error_tracker(THD *thd_arg) : thd(thd_arg) {}
~Prepare_error_tracker()863 ~Prepare_error_tracker()
864 {
865 if (unlikely(thd->is_error()))
866 thd->lex->mark_broken();
867 }
868 private:
869 THD *const thd;
870 };
871
872 bool uses_index_fields_only(Item *item, TABLE *tbl, uint keyno,
873 bool other_tbls_ok);
874 bool remove_eq_conds(THD *thd, Item *cond, Item **retcond,
875 Item::cond_result *cond_value);
876 bool optimize_cond(THD *thd, Item **conds, COND_EQUAL **cond_equal,
877 List<TABLE_LIST> *join_list,
878 Item::cond_result *cond_value);
879 Item* substitute_for_best_equal_field(Item *cond,
880 COND_EQUAL *cond_equal,
881 void *table_join_idx);
882 bool build_equal_items(THD *thd, Item *cond, Item **retcond,
883 COND_EQUAL *inherited, bool do_inherit,
884 List<TABLE_LIST> *join_list,
885 COND_EQUAL **cond_equal_ref);
886 bool is_indexed_agg_distinct(JOIN *join, List<Item_field> *out_args);
887 Key_use_array *create_keyuse_for_table(THD *thd, TABLE *table, uint keyparts,
888 Item_field **fields,
889 List<Item> outer_exprs);
890 Item_equal *find_item_equal(COND_EQUAL *cond_equal, Item_field *item_field,
891 bool *inherited_fl);
892 Item_field *get_best_field(Item_field *item_field, COND_EQUAL *cond_equal);
893 Item *
894 make_cond_for_table(Item *cond, table_map tables, table_map used_table,
895 bool exclude_expensive_cond);
896
897 /**
898 Returns true if arguments are a temporal Field having no date,
899 part and a temporal expression having a date part.
900 @param f Field
901 @param v Expression
902 */
field_time_cmp_date(const Field * f,const Item * v)903 inline bool field_time_cmp_date(const Field *f, const Item *v)
904 {
905 return f->is_temporal() && !f->is_temporal_with_date() &&
906 v->is_temporal_with_date();
907 }
908
909 bool substitute_gc(THD *thd, SELECT_LEX *select_lex, Item *where_cond,
910 ORDER *group_list, ORDER *order);
911
912 #endif /* SQL_OPTIMIZER_INCLUDED */
913