1 /* Copyright (c) 2002, 2020, 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
21 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
22
23 // Support for derived tables.
24
25 #include "sql/sql_derived.h"
26
27 #include <stddef.h>
28 #include <string.h>
29 #include <sys/types.h>
30
31 #include "lex_string.h"
32 #include "my_alloc.h"
33 #include "my_base.h"
34 #include "my_bitmap.h"
35 #include "my_dbug.h"
36 #include "my_inttypes.h"
37 #include "my_sys.h"
38 #include "my_table_map.h"
39 #include "mysqld_error.h"
40 #include "sql/auth/auth_acls.h"
41 #include "sql/debug_sync.h" // DEBUG_SYNC
42 #include "sql/handler.h"
43 #include "sql/item.h"
44 #include "sql/mem_root_array.h"
45 #include "sql/opt_trace.h" // opt_trace_disable_etc
46 #include "sql/query_options.h"
47 #include "sql/sql_base.h" // EXTRA_RECORD
48 #include "sql/sql_class.h"
49 #include "sql/sql_const.h"
50 #include "sql/sql_executor.h"
51 #include "sql/sql_lex.h"
52 #include "sql/sql_list.h"
53 #include "sql/sql_opt_exec_shared.h"
54 #include "sql/sql_optimizer.h" // JOIN
55 #include "sql/sql_resolver.h" // check_right_lateral_join
56 #include "sql/sql_tmp_table.h" // Tmp tables
57 #include "sql/sql_union.h" // Query_result_union
58 #include "sql/sql_view.h" // check_duplicate_names
59 #include "sql/table.h"
60 #include "sql/table_function.h"
61 #include "sql/thd_raii.h"
62 #include "thr_lock.h"
63
64 class Opt_trace_context;
65
66 /**
67 Produces, from the first tmp TABLE object, a clone TABLE object for
68 TABLE_LIST 'tl', to have a single materialization of multiple references to
69 a CTE.
70
71 How sharing of a single tmp table works
72 =======================================
73
74 There are several scenarios.
75 (1) Non-recursive CTE referenced only once: nothing special.
76 (2) Non-recursive CTE referenced more than once:
77 - multiple TABLEs, one TABLE_SHARE.
78 - The first ref in setup_materialized_derived() calls
79 create_tmp_table(); others call open_table_from_share().
80 - The first ref in create_derived() calls instantiate_tmp_table()
81 (which calls handler::create() then open_tmp_table()); others call
82 open_tmp_table(). open_tmp_table() calls handler::open().
83 - The first ref in materialize_derived() evaluates the subquery and does
84 all writes to the tmp table.
85 - Finally all refs set up a read access method (table scan, index scan,
86 index lookup, etc) and do reads, possibly interlaced (example: a
87 nested-loop join of two references to the CTE).
88 - The storage engine (MEMORY or InnoDB) must be informed of the uses above;
89 this is done by having TABLE_SHARE::ref_count>=2 for every handler::open()
90 call.
91 (3) Recursive CTE, referenced once or more than once:
92 All of (2) applies, where the set of refs is the non-recursive
93 ones (a recursive ref is a ref appearing in the definition of a recursive
94 CTE). Additionally:
95 - recursive refs do not call setup_materialized_derived(),
96 create_derived(), materialize_derived().
97 - right after a non-recursive ref has been in setup_materialized_derived(),
98 its recursive refs are replaced with clones of that ref, made with
99 open_table_from_share().
100 - the first non-recursive ref in materialized_derived() initiates the
101 with-recursive algorithm:
102 * its recursive refs call open_tmp_table().
103 * Then writes (to the non-recursive ref) and reads (from the recursive
104 refs) happen interlaced.
105 - a particular recursive ref is the UNION table, if UNION DISTINCT is
106 present in the CTE's definition: there is a single TABLE for it,
107 writes/reads to/from it happen interlaced (writes are done by
108 Query_result_union::send_data(); reads are done by the fake_select_lex's
109 JOIN).
110 - Finally all non-recursive refs set up a read access method and do reads,
111 possibly interlaced.
112 - The storage engine (MEMORY or InnoDB) must be informed of the uses above;
113 this is done by having TABLE_SHARE::ref_count>=2 for every handler::open()
114 call.
115 - The Server code handling tmp table creation must also be informed:
116 see how Query_result_union::create_result_table() disables PK promotion.
117
118 How InnoDB manages the uses above
119 =================================
120
121 The storage engine needs to take measures so that inserts and reads
122 don't corrupt each other's behaviour. In InnoDB that means two things
123 (@see row_search_no_mvcc()):
124 (a) A certain way to use its cursor when reading
125 (b) Making the different handlers inform each other when one insertion
126 modifies the structure of the index tree (e.g. splits a page; this
127 triggers a refreshing of all read cursors).
128
129 Requirements on tmp tables used to write/read CTEs
130 ==================================================
131
132 The internal tmp table must support a phase where table scans and
133 insertions happen interlaced, either issued from a single TABLE or from
134 multiple TABLE clones. If from a single TABLE, that object does repetitions
135 of {"write rows" then "init scan / read rows / close scan"}. If from
136 multiple TABLEs, one does "write rows", every other one does "init scan /
137 read rows / close scan".
138 During this, neither updates, nor deletes, nor any other type of read
139 access than table scans, are allowed on this table (they are allowed after
140 the phase's end).
141 Any started table scan on this table:
142 - must remember its position between two read operations, without influence
143 from other scans/inserts;
144 - must return rows inserted before and after it started (be catching up
145 continuously) (however, when it reports EOF it is allowed to stop catching
146 up and report EOF until closed).
147 - must return rows in insertion order.
148 - may be started from the first record (ha_rnd_init, ha_rnd_next) or from
149 the record where the previous scan was ended (position(), ha_rnd_end,
150 [...], ha_rnd_init, ha_rnd_pos(saved position), ha_rnd_next).
151 - must return positions (handler::position()) which are stable if a write
152 later occurs, so that a handler::rnd_pos() happening after the write finds
153 the same record.
154
155 Cursor re-positioning when MEMORY is converted to InnoDB
156 ========================================================
157
158 See create_ondisk_from_heap(). A requirement is that InnoDB is able to
159 start a scan like this: rnd_init, rnd_pos(some PK value), rnd_next.
160
161 @param thd Thread handler
162 @param tl Table reference wanting the copy
163
164 @returns New clone, or NULL if error
165 */
166
clone_tmp_table(THD * thd,TABLE_LIST * tl)167 TABLE *Common_table_expr::clone_tmp_table(THD *thd, TABLE_LIST *tl) {
168 #ifndef DBUG_OFF
169 /*
170 We're adding a clone; if another clone has been opened before, it was not
171 aware of the new one, so perhaps the storage engine has not set up the
172 necessary logic to share data among clones. Check that no clone is open:
173 */
174 Derived_refs_iterator it(tmp_tables[0]);
175 while (TABLE *t = it.get_next())
176 DBUG_ASSERT(!t->is_created() && !t->materialized);
177 #endif
178 TABLE *first = tmp_tables[0]->table;
179 // Allocate clone on the memory root of the TABLE_SHARE.
180 TABLE *t = static_cast<TABLE *>(first->s->mem_root.Alloc(sizeof(TABLE)));
181 if (!t) return nullptr; /* purecov: inspected */
182 if (open_table_from_share(thd, first->s, tl->alias,
183 /*
184 Pass db_stat == 0 to delay opening of table in SE,
185 as table is not instantiated in SE yet.
186 */
187 0,
188 /* We need record[1] for this TABLE instance. */
189 EXTRA_RECORD |
190 /*
191 Use DELAYED_OPEN to have its own record[0]
192 (necessary because db_stat is 0).
193 Otherwise it would be shared with 'first'
194 and thus a write to tmp table would modify
195 the row just read by readers.
196 */
197 DELAYED_OPEN,
198 0, t, false, nullptr))
199 return nullptr; /* purecov: inspected */
200 DBUG_ASSERT(t->s == first->s && t != first && t->file != first->file);
201 t->s->increment_ref_count();
202
203 // In case this clone is used to fill the materialized table:
204 bitmap_set_all(t->write_set);
205 t->reginfo.lock_type = TL_WRITE;
206 t->copy_blobs = true;
207
208 tl->table = t;
209 t->pos_in_table_list = tl;
210
211 t->set_not_started();
212
213 if (tmp_tables.push_back(tl)) return nullptr; /* purecov: inspected */
214
215 return t;
216 }
217
218 /**
219 Replaces the recursive reference in query block 'sl' with a clone of
220 the first tmp table.
221
222 @param thd Thread handler
223 @param sl Query block
224
225 @returns true if error
226 */
substitute_recursive_reference(THD * thd,SELECT_LEX * sl)227 bool Common_table_expr::substitute_recursive_reference(THD *thd,
228 SELECT_LEX *sl) {
229 TABLE_LIST *tl = sl->recursive_reference;
230 DBUG_ASSERT(tl != nullptr && tl->table == nullptr);
231 TABLE *t = clone_tmp_table(thd, tl);
232 if (t == nullptr) return true; /* purecov: inspected */
233 // Eliminate the dummy unit:
234 tl->derived_unit()->exclude_tree(thd);
235 tl->set_derived_unit(nullptr);
236 tl->set_privileges(SELECT_ACL);
237 return false;
238 }
239
240 /**
241 Resolve a derived table or view reference, including recursively resolving
242 contained subqueries.
243
244 @param thd thread handle
245 @param apply_semijoin Apply possible semi-join transforms if this is true
246
247 @returns false if success, true if error
248 */
249
resolve_derived(THD * thd,bool apply_semijoin)250 bool TABLE_LIST::resolve_derived(THD *thd, bool apply_semijoin) {
251 DBUG_TRACE;
252
253 /*
254 Helper class which takes care of restoration of members like
255 THD::derived_tables_processing. These members are changed in this
256 method scope for resolving derived tables.
257 */
258 class Context_handler {
259 public:
260 Context_handler(THD *thd)
261 : m_thd(thd),
262 m_deny_window_func_saved(thd->lex->m_deny_window_func),
263 m_derived_tables_processing_saved(thd->derived_tables_processing) {
264 /*
265 Window functions are allowed; they're aggregated in the derived
266 table's definition.
267 */
268 m_thd->lex->m_deny_window_func = 0;
269 m_thd->derived_tables_processing = true;
270 }
271
272 ~Context_handler() {
273 m_thd->lex->m_deny_window_func = m_deny_window_func_saved;
274 m_thd->derived_tables_processing = m_derived_tables_processing_saved;
275 }
276
277 private:
278 // Thread handle.
279 THD *m_thd;
280
281 // Saved state of THD::LEX::m_deny_window_func.
282 nesting_map m_deny_window_func_saved;
283
284 // Saved state of THD::derived_tables_processing.
285 bool m_derived_tables_processing_saved;
286 };
287
288 if (!is_view_or_derived() || is_merged() || is_table_function()) return false;
289
290 // This early return can be deleted after WL#6570.
291 if (derived->is_prepared()) return false;
292
293 // Dummy derived tables for recursive references disappear before this stage
294 DBUG_ASSERT(this != select_lex->recursive_reference);
295
296 if (is_derived() && derived->m_lateral_deps)
297 select_lex->end_lateral_table = this;
298
299 Context_handler ctx_handler(thd);
300
301 if (derived->prepare_limit(thd, derived->global_parameters()))
302 return true; /* purecov: inspected */
303
304 #ifndef DBUG_OFF // CTEs, derived tables can have outer references
305 if (is_view()) // but views cannot.
306 for (SELECT_LEX *sl = derived->first_select(); sl; sl = sl->next_select()) {
307 // Make sure there are no outer references
308 DBUG_ASSERT(sl->context.outer_context == nullptr);
309 }
310 #endif
311
312 if (m_common_table_expr && m_common_table_expr->recursive &&
313 !derived->is_recursive()) // in first resolution @todo delete in WL#6570
314 {
315 // Ensure it's UNION.
316 if (!derived->is_union()) {
317 my_error(ER_CTE_RECURSIVE_REQUIRES_UNION, MYF(0), alias);
318 return true;
319 }
320 if (derived->global_parameters()->is_ordered()) {
321 /*
322 ORDER BY applied to the UNION causes the use of the union tmp
323 table. The fake_select_lex would want to sort that table, which isn't
324 going to work as the table is incomplete when fake_select_lex first
325 reads it. Workaround: put ORDER BY in the top query.
326 Another reason: allowing
327 ORDER BY <condition using fulltext> would make the UNION tmp table be
328 of MyISAM engine which recursive CTEs don't support.
329 LIMIT is allowed and will stop the row generation after N rows.
330 However, without ORDER BY the CTE's content is ordered in an
331 unpredictable way, so LIMIT theoretically returns an unpredictable
332 subset of rows. Users are on their own.
333 Instead of LIMIT, users can have a counter column and use a WHERE
334 on it, to control depth level, which sounds more intelligent than a
335 limit.
336 */
337 my_error(ER_NOT_SUPPORTED_YET, MYF(0),
338 "ORDER BY over UNION "
339 "in recursive Common Table Expression");
340 return true;
341 }
342 /*
343 Should be:
344 SELECT1 UNION [DISTINCT | ALL] ... SELECTN
345 where SELECT1 is non-recursive, and all non-recursive SELECTs are before
346 all recursive SELECTs.
347 In SQL standard terms, the CTE must be "expandable" except that we allow
348 it to have more than one recursive SELECT.
349 */
350 bool previous_is_recursive = false;
351 SELECT_LEX *last_non_recursive = nullptr;
352 for (SELECT_LEX *sl = derived->first_select(); sl; sl = sl->next_select()) {
353 if (sl->is_recursive()) {
354 if (sl->is_ordered() || sl->has_limit() || sl->is_distinct()) {
355 /*
356 On top of posing implementation problems, it looks meaningless to
357 want to order/limit every iterative sub-result.
358 SELECT DISTINCT, if all expressions are constant, is implemented
359 as LIMIT in QEP_TAB::remove_duplicates(); do_select() starts with
360 send_records=0 so loses track of rows which have been sent in
361 previous iterations.
362 */
363 my_error(ER_NOT_SUPPORTED_YET, MYF(0),
364 "ORDER BY / LIMIT / SELECT DISTINCT"
365 " in recursive query block of Common Table Expression");
366 return true;
367 }
368 if (sl == derived->union_distinct && sl->next_select()) {
369 /*
370 Consider
371 anchor UNION ALL rec1 UNION DISTINCT rec2 UNION ALL rec3:
372 after execution of rec2 we must turn off the duplicate-checking
373 index; it will thus not contain the keys of rows of rec3, so it
374 becomes permanently unusable. The next iteration of rec1 or rec2
375 may insert rows which are actually duplicates of those of rec3.
376 So: if the last QB having DISTINCT to its left is recursive, and
377 it is followed by another QB (necessarily connected with ALL),
378 reject the query.
379 */
380 my_error(ER_NOT_SUPPORTED_YET, MYF(0),
381 "recursive query blocks with"
382 " UNION DISTINCT then UNION ALL, in recursive "
383 "Common Table Expression");
384 return true;
385 }
386 } else {
387 if (previous_is_recursive) {
388 my_error(ER_CTE_RECURSIVE_REQUIRES_NONRECURSIVE_FIRST, MYF(0), alias);
389 return true;
390 }
391 last_non_recursive = sl;
392 }
393 previous_is_recursive = sl->is_recursive();
394 }
395 if (last_non_recursive == nullptr) {
396 my_error(ER_CTE_RECURSIVE_REQUIRES_NONRECURSIVE_FIRST, MYF(0), alias);
397 return true;
398 }
399 derived->first_recursive = last_non_recursive->next_select();
400 DBUG_ASSERT(derived->is_recursive());
401 }
402
403 DEBUG_SYNC(thd, "derived_not_set");
404
405 derived->derived_table = this;
406
407 if (!(derived_result = new (thd->mem_root) Query_result_union()))
408 return true; /* purecov: inspected */
409
410 /// Give the unit to the result (the other fields are ignored).
411 if (derived_result->prepare(thd, derived->types, derived_unit())) return true;
412
413 /*
414 Prepare the underlying query expression of the derived table.
415 */
416 if (derived->prepare(thd, derived_result,
417 !apply_semijoin ? SELECT_NO_SEMI_JOIN : 0, 0))
418 return true;
419
420 if (m_was_table_subquery) {
421 /*
422 When we converted the quantified subquery to a derived table, we gave
423 names to the SELECT list items. But if those were from a view, then
424 rollback_item_tree_changes restored original items, losing the names.
425 We have to re-establish our names. Likewise, if we replaced items with 1
426 (case of EXISTS), they may have been lost.
427 todo remove after WL#6570.
428 */
429 SELECT_LEX *subs_select = derived->first_select();
430 DBUG_ASSERT(!subs_select->first_execution);
431 char buff[NAME_LEN];
432 int i = -1;
433 Item *inner;
434 List_iterator<Item> it_fields_list(subs_select->fields_list);
435 List_iterator<Item> it_all_fields(subs_select->all_fields);
436 while ((i++, it_all_fields++, inner = it_fields_list++)) {
437 if (i < m_first_sj_inner_expr_of_subquery && !inner->basic_const_item()) {
438 // replace with 1; we do it before derived->prepare(), so this will
439 // propagate to all_fields and base_ref_items.
440 auto constant = new (thd->mem_root) Item_int(
441 NAME_STRING("Not_used"), (longlong)1, MY_INT64_NUM_DECIMAL_DIGITS);
442 if (constant == nullptr) return true;
443 it_fields_list.replace(constant);
444 it_all_fields.replace(constant);
445 subs_select->base_ref_items[i] = constant;
446 inner = constant;
447 }
448 uint name_len =
449 snprintf(buff, sizeof(buff), SYNTHETIC_FIELD_NAME "%d", i + 1);
450 inner->orig_name = inner->item_name;
451 inner->item_name.copy(buff, name_len);
452 }
453 }
454
455 if (check_duplicate_names(m_derived_column_names, derived->types, false))
456 return true;
457
458 if (is_derived()) {
459 // The underlying tables of a derived table are all readonly:
460 for (SELECT_LEX *sl = derived->first_select(); sl; sl = sl->next_select())
461 sl->set_tables_readonly();
462 /*
463 A derived table is transparent with respect to privilege checking.
464 This setting means that privilege checks ignore the derived table
465 and are done properly in underlying base tables and views.
466 SELECT_ACL is used because derived tables cannot be used for update,
467 delete or insert.
468 */
469 set_privileges(SELECT_ACL);
470
471 if (derived->m_lateral_deps) {
472 select_lex->end_lateral_table = nullptr;
473 derived->m_lateral_deps &= ~PSEUDO_TABLE_BITS;
474 if (derived->m_lateral_deps == 0) {
475 /*
476 Table doesn't depend on tables in the same FROM clause, so it can be
477 evaluated once per execution of the parent query; having the map
478 equal to 0 is like removing the LATERAL word.
479 */
480 } else {
481 propagate_table_maps(0);
482 if (check_right_lateral_join(this, derived->m_lateral_deps))
483 return true;
484 }
485 }
486 }
487
488 return false;
489 }
490
491 /// Helper function for TABLE_LIST::setup_materialized_derived()
swap_column_names_of_unit_and_tmp_table(List<Item> & unit_items,const Create_col_name_list & tmp_table_col_names)492 static void swap_column_names_of_unit_and_tmp_table(
493 List<Item> &unit_items, const Create_col_name_list &tmp_table_col_names) {
494 if (unit_items.elements != tmp_table_col_names.size())
495 // check_duplicate_names() will find and report error
496 return;
497 List_iterator_fast<Item> li(unit_items);
498 Item *item;
499 uint fieldnr = 0;
500 while ((item = li++)) {
501 const char *s = item->item_name.ptr();
502 size_t l = item->item_name.length();
503 LEX_CSTRING &other_name =
504 const_cast<LEX_CSTRING &>(tmp_table_col_names[fieldnr]);
505 item->item_name.set(other_name.str, other_name.length);
506 other_name.str = s;
507 other_name.length = l;
508 fieldnr++;
509 }
510 }
511
512 /**
513 Prepare a derived table or view for materialization.
514 The derived table must have been
515 - resolved by resolve_derived(),
516 - or resolved as a subquery (by Item_*_subselect_::fix_fields()) then
517 converted to a derived table.
518
519 @param thd THD pointer
520
521 @return false if successful, true if error
522 */
setup_materialized_derived(THD * thd)523 bool TABLE_LIST::setup_materialized_derived(THD *thd)
524
525 {
526 return setup_materialized_derived_tmp_table(thd) ||
527 derived->check_materialized_derived_query_blocks(thd);
528 }
529
530 /**
531 Sets up the tmp table to contain the derived table's rows.
532 @param thd THD pointer
533 @return false if successful, true if error
534 */
setup_materialized_derived_tmp_table(THD * thd)535 bool TABLE_LIST::setup_materialized_derived_tmp_table(THD *thd)
536
537 {
538 DBUG_TRACE;
539
540 DBUG_ASSERT(is_view_or_derived() && !is_merged() && table == nullptr);
541
542 DBUG_PRINT("info", ("algorithm: TEMPORARY TABLE"));
543
544 Opt_trace_context *const trace = &thd->opt_trace;
545 Opt_trace_object trace_wrapper(trace);
546 Opt_trace_object trace_derived(trace, is_view() ? "view" : "derived");
547 trace_derived.add_utf8_table(this)
548 .add("select#", derived->first_select()->select_number)
549 .add("materialized", true);
550
551 set_uses_materialization();
552
553 // From resolver POV, columns of this table are readonly
554 set_readonly();
555
556 if (m_common_table_expr && m_common_table_expr->tmp_tables.size() > 0) {
557 trace_derived.add("reusing_tmp_table", true);
558 table = m_common_table_expr->clone_tmp_table(thd, this);
559 if (table == nullptr) return true; /* purecov: inspected */
560 derived_result->table = table;
561 }
562
563 if (table == nullptr) {
564 // Create the result table for the materialization
565 ulonglong create_options =
566 derived->first_select()->active_options() | TMP_TABLE_ALL_COLUMNS;
567
568 if (m_derived_column_names) {
569 /*
570 Tmp table's columns will be created from derived->types (the SELECT
571 list), names included.
572 But the user asked that the tmp table's columns use other specified
573 names. So, we replace the names of SELECT list items with specified
574 column names, just for the duration of tmp table creation.
575 */
576 swap_column_names_of_unit_and_tmp_table(derived->types,
577 *m_derived_column_names);
578 }
579
580 // If we're materializing directly into the result and we have a UNION
581 // DISTINCT query, we're going to need a unique index for deduplication.
582 // (If we're materializing into a temporary table instead, the deduplication
583 // will happen on that table, and is not set here.) create_result_table()
584 // will figure out whether it wants to create it as the primary key or just
585 // a regular index.
586 bool is_distinct = derived->can_materialize_directly_into_result() &&
587 derived->union_distinct != nullptr;
588
589 bool rc = derived_result->create_result_table(
590 thd, &derived->types, is_distinct, create_options, alias, false, false);
591
592 if (m_derived_column_names) // Restore names
593 swap_column_names_of_unit_and_tmp_table(derived->types,
594 *m_derived_column_names);
595
596 if (rc) return true; /* purecov: inspected */
597
598 table = derived_result->table;
599 table->pos_in_table_list = this;
600 if (m_common_table_expr && m_common_table_expr->tmp_tables.push_back(this))
601 return true; /* purecov: inspected */
602 }
603
604 // Make table's name same as the underlying materialized table
605 set_name_temporary();
606
607 table->s->tmp_table = NON_TRANSACTIONAL_TMP_TABLE;
608
609 // Table is "nullable" if inner table of an outer_join
610 if (is_inner_table_of_outer_join()) table->set_nullable();
611
612 dep_tables |= derived->m_lateral_deps;
613
614 return false;
615 }
616
617 /**
618 Sets up query blocks belonging to the query expression of a materialized
619 derived table.
620 @param thd_arg THD pointer
621 @return false if successful, true if error
622 */
623
check_materialized_derived_query_blocks(THD * thd_arg)624 bool SELECT_LEX_UNIT::check_materialized_derived_query_blocks(THD *thd_arg) {
625 for (SELECT_LEX *sl = first_select(); sl; sl = sl->next_select()) {
626 // All underlying tables are read-only
627 sl->set_tables_readonly();
628 /*
629 Derived tables/view are materialized prior to UPDATE, thus we can skip
630 them from table uniqueness check
631 */
632 sl->propagate_unique_test_exclusion();
633
634 /*
635 SELECT privilege is needed for all materialized derived tables and views,
636 and columns must be marked for read.
637 */
638 if (sl->check_view_privileges(thd_arg, SELECT_ACL, SELECT_ACL)) return true;
639
640 // Set all selected fields to be read:
641 // @todo Do not set fields that are not referenced from outer query
642 List_iterator<Item> it(sl->all_fields);
643 Item *item;
644 Column_privilege_tracker tracker(thd_arg, SELECT_ACL);
645 Mark_field mf(MARK_COLUMNS_READ);
646 while ((item = it++)) {
647 if (item->walk(&Item::check_column_privileges, enum_walk::PREFIX,
648 (uchar *)thd_arg))
649 return true;
650 item->walk(&Item::mark_field_in_map, enum_walk::POSTFIX, (uchar *)&mf);
651 }
652 }
653 return false;
654 }
655
656 /**
657 Prepare a table function for materialization.
658
659 @param thd THD pointer
660
661 @return false if successful, true if error
662 */
setup_table_function(THD * thd)663 bool TABLE_LIST::setup_table_function(THD *thd) {
664 DBUG_TRACE;
665
666 DBUG_ASSERT(is_table_function());
667
668 DBUG_PRINT("info", ("algorithm: TEMPORARY TABLE"));
669
670 Opt_trace_context *const trace = &thd->opt_trace;
671 Opt_trace_object trace_wrapper(trace);
672 Opt_trace_object trace_derived(trace, "table_function");
673 const char *func_name;
674 uint func_name_len;
675 func_name = table_function->func_name();
676 func_name_len = strlen(func_name);
677
678 set_uses_materialization();
679
680 /*
681 A table function has name resolution context of query which owns FROM
682 clause. So it automatically is LATERAL. This end_lateral_table is to
683 make sure a table function won't access tables located after it in FROM
684 clause.
685 */
686 select_lex->end_lateral_table = this;
687
688 if (table_function->init()) return true;
689
690 // Create the result table for the materialization
691 if (table_function->create_result_table(0LL, alias))
692 return true; /* purecov: inspected */
693 table = table_function->table;
694 table->pos_in_table_list = this;
695
696 table->s->tmp_table = NON_TRANSACTIONAL_TMP_TABLE;
697
698 // Table is "nullable" if inner table of an outer_join
699 if (is_inner_table_of_outer_join()) table->set_nullable();
700
701 const char *saved_where = thd->where;
702 thd->where = "a table function argument";
703 enum_mark_columns saved_mark = thd->mark_used_columns;
704 thd->mark_used_columns = MARK_COLUMNS_READ;
705 if (table_function->init_args()) {
706 thd->mark_used_columns = saved_mark;
707 return true;
708 }
709 thd->mark_used_columns = saved_mark;
710 set_privileges(SELECT_ACL);
711 /*
712 Trace needs to be here as it'ss print the table, and columns have to be
713 set up at the moment of printing.
714 */
715 trace_derived.add_utf8_table(this)
716 .add_utf8("function_name", func_name, func_name_len)
717 .add("materialized", true);
718
719 select_lex->end_lateral_table = nullptr;
720
721 propagate_table_maps(0);
722 if (check_right_lateral_join(this, table_function->used_tables()))
723 return true;
724
725 thd->where = saved_where;
726
727 return false;
728 }
729
730 /**
731 Optimize the query expression representing a derived table/view.
732
733 @note
734 If optimizer finds out that the derived table/view is of the type
735 "SELECT a_constant" this functions also materializes it.
736
737 @param thd thread handle
738
739 @returns false if success, true if error.
740 */
741
optimize_derived(THD * thd)742 bool TABLE_LIST::optimize_derived(THD *thd) {
743 DBUG_TRACE;
744
745 SELECT_LEX_UNIT *const unit = derived_unit();
746
747 DBUG_ASSERT(unit && !unit->is_optimized());
748
749 if (unit->optimize(thd, table) || thd->is_error()) return true;
750
751 if (materializable_is_const() &&
752 (create_materialized_table(thd) || materialize_derived(thd)))
753 return true;
754
755 return false;
756 }
757
758 /**
759 Create result table for a materialized derived table/view/table function.
760
761 @param thd thread handle
762
763 This function actually creates the result table for given 'derived'
764 table/view, but it doesn't fill it.
765
766 @returns false if success, true if error.
767 */
768
create_materialized_table(THD * thd)769 bool TABLE_LIST::create_materialized_table(THD *thd) {
770 DBUG_TRACE;
771
772 // @todo: Be able to assert !table->is_created() as well
773 DBUG_ASSERT((is_table_function() || derived_unit()) &&
774 uses_materialization() && table);
775
776 if (!table->is_created()) {
777 Derived_refs_iterator it(this);
778 while (TABLE *t = it.get_next())
779 if (t->is_created()) {
780 if (open_tmp_table(table)) return true; /* purecov: inspected */
781 break;
782 }
783 }
784
785 /*
786 Don't create result table if:
787 1) Table is already created, or
788 2) Table is a constant one with all NULL values.
789 */
790 if (table->is_created() || // 1
791 (select_lex->join != nullptr && // 2
792 (select_lex->join->const_table_map & map()))) // 2
793 {
794 /*
795 At this point, JT_CONST derived tables should be null rows. Otherwise
796 they would have been materialized already.
797 */
798 #ifndef DBUG_OFF
799 if (table != nullptr) {
800 QEP_TAB *tab = table->reginfo.qep_tab;
801 DBUG_ASSERT(tab == nullptr || tab->type() != JT_CONST ||
802 table->has_null_row());
803 }
804 #endif
805 return false;
806 }
807 /* create tmp table */
808 if (instantiate_tmp_table(thd, table)) return true; /* purecov: inspected */
809
810 table->file->ha_extra(HA_EXTRA_IGNORE_DUP_KEY);
811
812 return false;
813 }
814
815 /**
816 Materialize derived table
817
818 @param thd Thread handle
819
820 Derived table is resolved with temporary table. It is created based on the
821 queries defined. After temporary table is materialized, if this is not
822 EXPLAIN, then the entire unit / node is deleted. unit is deleted if UNION is
823 used for derived table and node is deleted is it is a simple SELECT.
824 If you use this function, make sure it's not called at prepare.
825 Due to evaluation of LIMIT clause it can not be used at prepared stage.
826
827 @returns false if success, true if error.
828 */
829
materialize_derived(THD * thd)830 bool TABLE_LIST::materialize_derived(THD *thd) {
831 DBUG_TRACE;
832 DBUG_ASSERT(is_view_or_derived() && uses_materialization());
833 DBUG_ASSERT(table && table->is_created() && !table->materialized);
834
835 Derived_refs_iterator it(this);
836 while (TABLE *t = it.get_next())
837 if (t->materialized) {
838 table->materialized = true;
839 table->set_not_started();
840 return false;
841 }
842
843 /*
844 The with-recursive algorithm needs the table scan to return rows in
845 insertion order.
846 For MEMORY and Temptable it is true.
847 For InnoDB: InnoDB's table scan returns rows in PK order. If the PK
848 is (not) the autogenerated autoincrement InnoDB ROWID, PK order will (not)
849 be the same as insertion order.
850 So let's verify that the table has no MySQL-created PK.
851 */
852 SELECT_LEX_UNIT *const unit = derived_unit();
853 if (unit->is_recursive()) {
854 DBUG_ASSERT(table->s->primary_key == MAX_KEY);
855 }
856
857 if (table->hash_field) {
858 table->file->ha_index_init(0, false);
859 }
860
861 // execute unit without cleaning up
862 bool res = unit->execute(thd);
863
864 if (table->hash_field) {
865 table->file->ha_index_or_rnd_end();
866 }
867
868 if (!res) {
869 /*
870 Here we entirely fix both TABLE_LIST and list of SELECT's as
871 there were no derived tables
872 */
873 if (derived_result->flush()) res = true; /* purecov: inspected */
874 }
875
876 table->materialized = true;
877
878 // Mark the table as not started (default is just zero status),
879 // or read_system() and read_const() will forget to read the row.
880 table->set_not_started();
881
882 return res;
883 }
884
885 /**
886 Clean up the query expression for a materialized derived table
887 */
888
cleanup_derived(THD * thd)889 bool TABLE_LIST::cleanup_derived(THD *thd) {
890 DBUG_ASSERT(is_view_or_derived() && uses_materialization());
891 return derived_unit()->cleanup(thd, false);
892 }
893