1 /*
2 Copyright (c) 2002, 2011, Oracle and/or its affiliates.
3 Copyright (c) 2010, 2021, MariaDB
4
5 This program is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation; version 2 of the License.
8
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU General Public License for more details.
13
14 You should have received a copy of the GNU General Public License
15 along with this program; if not, write to the Free Software
16 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */
17
18
19 /*
20 Derived tables
21 These were introduced by Sinisa <sinisa@mysql.com>
22 */
23
24
25 #include "mariadb.h" /* NO_EMBEDDED_ACCESS_CHECKS */
26 #include "sql_priv.h"
27 #include "unireg.h"
28 #include "sql_select.h"
29 #include "derived_handler.h"
30 #include "sql_base.h"
31 #include "sql_view.h" // check_duplicate_names
32 #include "sql_acl.h" // SELECT_ACL
33 #include "sql_class.h"
34 #include "sql_derived.h"
35 #include "sql_cte.h"
36 #include "my_json_writer.h"
37 #include "opt_trace.h"
38
39 typedef bool (*dt_processor)(THD *thd, LEX *lex, TABLE_LIST *derived);
40
41 static bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived);
42 static bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived);
43 static bool mysql_derived_optimize(THD *thd, LEX *lex, TABLE_LIST *derived);
44 static bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived);
45 static bool mysql_derived_create(THD *thd, LEX *lex, TABLE_LIST *derived);
46 static bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived);
47 static bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived);
48 static bool mysql_derived_merge_for_insert(THD *thd, LEX *lex,
49 TABLE_LIST *derived);
50
51 dt_processor processors[]=
52 {
53 &mysql_derived_init,
54 &mysql_derived_prepare,
55 &mysql_derived_optimize,
56 &mysql_derived_merge,
57 &mysql_derived_merge_for_insert,
58 &mysql_derived_create,
59 &mysql_derived_fill,
60 &mysql_derived_reinit,
61 };
62
63 /*
64 Run specified phases on all derived tables/views in given LEX.
65
66 @param lex LEX for this thread
67 @param phases phases to run derived tables/views through
68
69 @return FALSE OK
70 @return TRUE Error
71 */
72 bool
mysql_handle_derived(LEX * lex,uint phases)73 mysql_handle_derived(LEX *lex, uint phases)
74 {
75 bool res= FALSE;
76 DBUG_ENTER("mysql_handle_derived");
77 DBUG_PRINT("enter", ("phases: 0x%x", phases));
78 if (!lex->derived_tables)
79 DBUG_RETURN(FALSE);
80
81 lex->thd->derived_tables_processing= TRUE;
82
83 for (uint phase= 0; phase < DT_PHASES && !res; phase++)
84 {
85 uint phase_flag= DT_INIT << phase;
86 if (phase_flag > phases)
87 break;
88 if (!(phases & phase_flag))
89 continue;
90
91 for (SELECT_LEX *sl= lex->all_selects_list;
92 sl && !res;
93 sl= sl->next_select_in_list())
94 {
95 TABLE_LIST *cursor= sl->get_table_list();
96 sl->changed_elements|= TOUCHED_SEL_DERIVED;
97 /*
98 DT_MERGE_FOR_INSERT is not needed for views/derived tables inside
99 subqueries. Views and derived tables of subqueries should be
100 processed normally.
101 */
102 if (phases == DT_MERGE_FOR_INSERT &&
103 cursor && (cursor->top_table()->select_lex !=
104 lex->first_select_lex()))
105 continue;
106 for (;
107 cursor && !res;
108 cursor= cursor->next_local)
109 {
110 if (!cursor->is_view_or_derived() && phases == DT_MERGE_FOR_INSERT)
111 continue;
112 uint8 allowed_phases= (cursor->is_merged_derived() ? DT_PHASES_MERGE :
113 DT_PHASES_MATERIALIZE | DT_MERGE_FOR_INSERT);
114 /*
115 Skip derived tables to which the phase isn't applicable.
116 TODO: mark derived at the parse time, later set it's type
117 (merged or materialized)
118 */
119 if ((phase_flag != DT_PREPARE && !(allowed_phases & phase_flag)) ||
120 (cursor->merged_for_insert && phase_flag != DT_REINIT &&
121 phase_flag != DT_PREPARE))
122 continue;
123 res= (*processors[phase])(lex->thd, lex, cursor);
124 }
125 if (lex->describe)
126 {
127 /*
128 Force join->join_tmp creation, because we will use this JOIN
129 twice for EXPLAIN and we have to have unchanged join for EXPLAINing
130 */
131 sl->uncacheable|= UNCACHEABLE_EXPLAIN;
132 sl->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
133 }
134 }
135 }
136 lex->thd->derived_tables_processing= FALSE;
137 DBUG_RETURN(res);
138 }
139
140 /*
141 Run through phases for the given derived table/view.
142
143 @param lex LEX for this thread
144 @param derived the derived table to handle
145 @param phase_map phases to process tables/views through
146
147 @details
148
149 This function process the derived table (view) 'derived' to performs all
150 actions that are to be done on the table at the phases specified by
151 phase_map. The processing is carried out starting from the actions
152 performed at the earlier phases (those having smaller ordinal numbers).
153
154 @note
155 This function runs specified phases of the derived tables handling on the
156 given derived table/view. This function is used in the chain of calls:
157 SELECT_LEX::handle_derived ->
158 TABLE_LIST::handle_derived ->
159 mysql_handle_single_derived
160 This chain of calls implements the bottom-up handling of the derived tables:
161 i.e. most inner derived tables/views are handled first. This order is
162 required for the all phases except the merge and the create steps.
163 For the sake of code simplicity this order is kept for all phases.
164
165 @return FALSE ok
166 @return TRUE error
167 */
168
169 bool
mysql_handle_single_derived(LEX * lex,TABLE_LIST * derived,uint phases)170 mysql_handle_single_derived(LEX *lex, TABLE_LIST *derived, uint phases)
171 {
172 bool res= FALSE;
173 uint8 allowed_phases= (derived->is_merged_derived() ? DT_PHASES_MERGE :
174 DT_PHASES_MATERIALIZE);
175 DBUG_ENTER("mysql_handle_single_derived");
176 DBUG_PRINT("enter", ("phases: 0x%x allowed: 0x%x alias: '%s'",
177 phases, allowed_phases,
178 (derived->alias.str ? derived->alias.str : "<NULL>")));
179 if (!lex->derived_tables)
180 DBUG_RETURN(FALSE);
181
182 if (derived->select_lex)
183 derived->select_lex->changed_elements|= TOUCHED_SEL_DERIVED;
184 else
185 DBUG_ASSERT(derived->prelocking_placeholder);
186 lex->thd->derived_tables_processing= TRUE;
187
188 for (uint phase= 0; phase < DT_PHASES; phase++)
189 {
190 uint phase_flag= DT_INIT << phase;
191 if (phase_flag > phases)
192 break;
193 if (!(phases & phase_flag))
194 continue;
195 /* Skip derived tables to which the phase isn't applicable. */
196 if (phase_flag != DT_PREPARE &&
197 !(allowed_phases & phase_flag))
198 continue;
199
200 if ((res= (*processors[phase])(lex->thd, lex, derived)))
201 break;
202 }
203
204 lex->thd->derived_tables_processing= FALSE;
205 DBUG_RETURN(res);
206 }
207
208
209 /**
210 Merge a derived table/view into the embedding select
211
212 @param thd thread handle
213 @param lex LEX of the embedding query.
214 @param derived reference to the derived table.
215
216 @details
217 This function merges the given derived table / view into the parent select
218 construction. Any derived table/reference to view occurred in the FROM
219 clause of the embedding select is represented by a TABLE_LIST structure a
220 pointer to which is passed to the function as in the parameter 'derived'.
221 This structure contains the number/map, alias, a link to SELECT_LEX of the
222 derived table and other info. If the 'derived' table is used in a nested join
223 then additionally the structure contains a reference to the ON expression
224 for this join.
225
226 The merge process results in elimination of the derived table (or the
227 reference to a view) such that:
228 - the FROM list of the derived table/view is wrapped into a nested join
229 after which the nest is added to the FROM list of the embedding select
230 - the WHERE condition of the derived table (view) is ANDed with the ON
231 condition attached to the table.
232
233 @note
234 Tables are merged into the leaf_tables list, original derived table is removed
235 from this list also. SELECT_LEX::table_list list is left untouched.
236 Where expression is merged with derived table's on_expr and can be found after
237 the merge through the SELECT_LEX::table_list.
238
239 Examples of the derived table/view merge:
240
241 Schema:
242 Tables: t1(f1), t2(f2), t3(f3)
243 View v1: SELECT f1 FROM t1 WHERE f1 < 1
244
245 Example with a view:
246 Before merge:
247
248 The query (Q1): SELECT f1,f2 FROM t2 LEFT JOIN v1 ON f1 = f2
249
250 (LEX of the main query)
251 |
252 (select_lex)
253 |
254 (FROM table list)
255 |
256 (join list)= t2, v1
257 / \
258 / (on_expr)= (f1 = f2)
259 |
260 (LEX of the v1 view)
261 |
262 (select_lex)= SELECT f1 FROM t1 WHERE f1 < 1
263
264
265 After merge:
266
267 The rewritten query Q1 (Q1'):
268 SELECT f1,f2 FROM t2 LEFT JOIN (t1) ON ((f1 = f2) and (f1 < 1))
269
270 (LEX of the main query)
271 |
272 (select_lex)
273 |
274 (FROM table list)
275 |
276 (join list)= t2, (t1)
277 \
278 (on_expr)= (f1 = f2) and (f1 < 1)
279
280 In this example table numbers are assigned as follows:
281 (outer select): t2 - 1, v1 - 2
282 (inner select): t1 - 1
283 After the merge table numbers will be:
284 (outer select): t2 - 1, t1 - 2
285
286 Example with a derived table:
287 The query Q2:
288 SELECT f1,f2
289 FROM (SELECT f1 FROM t1, t3 WHERE f1=f3 and f1 < 1) tt, t2
290 WHERE f1 = f2
291
292 Before merge:
293 (LEX of the main query)
294 |
295 (select_lex)
296 / \
297 (FROM table list) (WHERE clause)= (f1 = f2)
298 |
299 (join list)= tt, t2
300 / \
301 / (on_expr)= (empty)
302 /
303 (select_lex)= SELECT f1 FROM t1, t3 WHERE f1 = f3 and f1 < 1
304
305 After merge:
306
307 The rewritten query Q2 (Q2'):
308 SELECT f1,f2
309 FROM (t1, t3) JOIN t2 ON (f1 = f3 and f1 < 1)
310 WHERE f1 = f2
311
312 (LEX of the main query)
313 |
314 (select_lex)
315 / \
316 (FROM table list) (WHERE clause)= (f1 = f2)
317 |
318 (join list)= t2, (t1, t3)
319 \
320 (on_expr)= (f1 = f3 and f1 < 1)
321
322 In this example table numbers are assigned as follows:
323 (outer select): tt - 1, t2 - 2
324 (inner select): t1 - 1, t3 - 2
325 After the merge table numbers will be:
326 (outer select): t1 - 1, t2 - 2, t3 - 3
327
328 @return FALSE if derived table/view were successfully merged.
329 @return TRUE if an error occur.
330 */
331
332 static
mysql_derived_merge(THD * thd,LEX * lex,TABLE_LIST * derived)333 bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived)
334 {
335 bool res= FALSE;
336 SELECT_LEX *dt_select= derived->get_single_select();
337 table_map map;
338 uint tablenr;
339 SELECT_LEX *parent_lex= derived->select_lex;
340 Query_arena *arena, backup;
341 DBUG_ENTER("mysql_derived_merge");
342 DBUG_PRINT("enter", ("Alias: '%s' Unit: %p",
343 (derived->alias.str ? derived->alias.str : "<NULL>"),
344 derived->get_unit()));
345 const char *cause= NULL;
346
347 if (derived->merged)
348 {
349
350 DBUG_PRINT("info", ("Irreversibly merged: exit"));
351 DBUG_RETURN(FALSE);
352 }
353
354 if (dt_select->uncacheable & UNCACHEABLE_RAND)
355 {
356 /* There is random function => fall back to materialization. */
357 cause= "Random function in the select";
358 if (unlikely(thd->trace_started()))
359 {
360 OPT_TRACE_VIEWS_TRANSFORM(thd, trace_wrapper, trace_derived,
361 derived->is_derived() ? "derived" : "view",
362 derived->alias.str ? derived->alias.str : "<NULL>",
363 derived->get_unit()->first_select()->select_number,
364 "materialized");
365 trace_derived.add("cause", cause);
366 }
367 derived->change_refs_to_fields();
368 derived->set_materialized_derived();
369 DBUG_RETURN(FALSE);
370 }
371
372 if (derived->dt_handler)
373 {
374 derived->change_refs_to_fields();
375 derived->set_materialized_derived();
376 DBUG_RETURN(FALSE);
377 }
378
379 arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test
380
381 if (!derived->merged_for_insert ||
382 (derived->is_multitable() &&
383 (thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
384 thd->lex->sql_command == SQLCOM_DELETE_MULTI)))
385 {
386 /*
387 Check whether there is enough free bits in table map to merge subquery.
388 If not - materialize it. This check isn't cached so when there is a big
389 and small subqueries, and the bigger one can't be merged it wouldn't
390 block the smaller one.
391 */
392 if (parent_lex->get_free_table_map(&map, &tablenr) ||
393 dt_select->leaf_tables.elements + tablenr > MAX_TABLES)
394 {
395 /* There is no enough table bits, fall back to materialization. */
396 cause= "Not enough table bits to merge subquery";
397 goto unconditional_materialization;
398 }
399
400 if (dt_select->options & OPTION_SCHEMA_TABLE)
401 parent_lex->options |= OPTION_SCHEMA_TABLE;
402
403 if (!derived->get_unit()->prepared)
404 {
405 dt_select->leaf_tables.empty();
406 make_leaves_list(thd, dt_select->leaf_tables, derived, TRUE, 0);
407 }
408
409 derived->nested_join= (NESTED_JOIN*) thd->calloc(sizeof(NESTED_JOIN));
410 if (!derived->nested_join)
411 {
412 res= TRUE;
413 goto exit_merge;
414 }
415
416 /* Merge derived table's subquery in the parent select. */
417 if (parent_lex->merge_subquery(thd, derived, dt_select, tablenr, map))
418 {
419 res= TRUE;
420 goto exit_merge;
421 }
422
423 /*
424 exclude select lex so it doesn't show up in explain.
425 do this only for derived table as for views this is already done.
426
427 From sql_view.cc
428 Add subqueries units to SELECT into which we merging current view.
429 unit(->next)* chain starts with subqueries that are used by this
430 view and continues with subqueries that are used by other views.
431 We must not add any subquery twice (otherwise we'll form a loop),
432 to do this we remember in end_unit the first subquery that has
433 been already added.
434 */
435 derived->get_unit()->exclude_level();
436 if (parent_lex->join)
437 parent_lex->join->table_count+= dt_select->join->table_count - 1;
438 }
439 derived->merged= TRUE;
440 if (derived->get_unit()->prepared)
441 {
442 Item *expr= derived->on_expr;
443 expr= and_conds(thd, expr, dt_select->join ? dt_select->join->conds : 0);
444 if (expr)
445 expr->top_level_item();
446
447 if (expr && (derived->prep_on_expr || expr != derived->on_expr))
448 {
449 derived->on_expr= expr;
450 derived->prep_on_expr= expr->copy_andor_structure(thd);
451 }
452 thd->where= "on clause";
453 if (derived->on_expr &&
454 derived->on_expr->fix_fields_if_needed_for_bool(thd, &derived->on_expr))
455 {
456 res= TRUE; /* purecov: inspected */
457 goto exit_merge;
458 }
459 // Update used tables cache according to new table map
460 if (derived->on_expr)
461 {
462 derived->on_expr->fix_after_pullout(parent_lex, &derived->on_expr,
463 TRUE);
464 fix_list_after_tbl_changes(parent_lex, &derived->nested_join->join_list);
465 }
466 }
467
468 exit_merge:
469 if (arena)
470 thd->restore_active_arena(arena, &backup);
471 DBUG_RETURN(res);
472
473 unconditional_materialization:
474
475 if (unlikely(thd->trace_started()))
476 {
477 OPT_TRACE_VIEWS_TRANSFORM(thd,trace_wrapper, trace_derived,
478 derived->is_derived() ? "derived" : "view",
479 derived->alias.str ? derived->alias.str : "<NULL>",
480 derived->get_unit()->first_select()->select_number,
481 "materialized");
482 trace_derived.add("cause", cause);
483 }
484
485 derived->change_refs_to_fields();
486 derived->set_materialized_derived();
487 if (!derived->table || !derived->table->is_created())
488 res= mysql_derived_create(thd, lex, derived);
489 goto exit_merge;
490 }
491
492
493 /**
494 Merge a view for the embedding INSERT/UPDATE/DELETE
495
496 @param thd thread handle
497 @param lex LEX of the embedding query.
498 @param derived reference to the derived table.
499
500 @details
501 This function substitutes the derived table for the first table from
502 the query of the derived table thus making it a correct target table for the
503 INSERT/UPDATE/DELETE statements. As this operation is correct only for
504 single table views only, for multi table views this function does nothing.
505 The derived parameter isn't checked to be a view as derived tables aren't
506 allowed for INSERT/UPDATE/DELETE statements.
507
508 @return FALSE if derived table/view were successfully merged.
509 @return TRUE if an error occur.
510 */
511
512 static
mysql_derived_merge_for_insert(THD * thd,LEX * lex,TABLE_LIST * derived)513 bool mysql_derived_merge_for_insert(THD *thd, LEX *lex, TABLE_LIST *derived)
514 {
515 DBUG_ENTER("mysql_derived_merge_for_insert");
516 DBUG_PRINT("enter", ("Alias: '%s' Unit: %p",
517 (derived->alias.str ? derived->alias.str : "<NULL>"),
518 derived->get_unit()));
519 DBUG_PRINT("info", ("merged_for_insert: %d is_materialized_derived: %d "
520 "is_multitable: %d single_table_updatable: %d "
521 "merge_underlying_list: %d",
522 derived->merged_for_insert,
523 derived->is_materialized_derived(),
524 derived->is_multitable(),
525 derived->single_table_updatable(),
526 derived->merge_underlying_list != 0));
527 if (derived->merged_for_insert)
528 DBUG_RETURN(FALSE);
529 if (derived->init_derived(thd, FALSE))
530 DBUG_RETURN(TRUE);
531 if (derived->is_materialized_derived())
532 DBUG_RETURN(mysql_derived_prepare(thd, lex, derived));
533 if ((thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
534 thd->lex->sql_command == SQLCOM_DELETE_MULTI))
535 DBUG_RETURN(FALSE);
536 if (!derived->is_multitable())
537 {
538 if (!derived->single_table_updatable())
539 DBUG_RETURN(derived->create_field_translation(thd));
540 if (derived->merge_underlying_list)
541 {
542 derived->table= derived->merge_underlying_list->table;
543 derived->schema_table= derived->merge_underlying_list->schema_table;
544 derived->merged_for_insert= TRUE;
545 DBUG_ASSERT(derived->table);
546 }
547 }
548 DBUG_RETURN(FALSE);
549 }
550
551
552 /*
553 Initialize a derived table/view
554
555 @param thd Thread handle
556 @param lex LEX of the embedding query.
557 @param derived reference to the derived table.
558
559 @detail
560 Fill info about derived table/view without preparing an
561 underlying select. Such as: create a field translation for views, mark it as
562 a multitable if it is and so on.
563
564 @return
565 false OK
566 true Error
567 */
568
569 static
mysql_derived_init(THD * thd,LEX * lex,TABLE_LIST * derived)570 bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived)
571 {
572 SELECT_LEX_UNIT *unit= derived->get_unit();
573 DBUG_ENTER("mysql_derived_init");
574 DBUG_PRINT("enter", ("Alias: '%s' Unit: %p",
575 (derived->alias.str ? derived->alias.str : "<NULL>"),
576 derived->get_unit()));
577
578 // Skip already prepared views/DT
579 if (!unit || unit->prepared)
580 DBUG_RETURN(FALSE);
581
582 bool res= derived->init_derived(thd, TRUE);
583
584 derived->updatable= derived->updatable && derived->is_view();
585
586 DBUG_RETURN(res);
587 }
588
589
590 /**
591 @brief
592 Prevent name resolution out of context of ON expressions in derived tables
593
594 @param
595 join_list list of tables used in from list of a derived
596
597 @details
598 The function sets the Name_resolution_context::outer_context to NULL
599 for all ON expressions contexts in the given join list. It does this
600 recursively for all nested joins the list contains.
601 */
602
nullify_outer_context_for_on_clauses(List<TABLE_LIST> & join_list)603 static void nullify_outer_context_for_on_clauses(List<TABLE_LIST>& join_list)
604 {
605 List_iterator<TABLE_LIST> li(join_list);
606 while (TABLE_LIST *table= li++)
607 {
608 if (table->on_context)
609 table->on_context->outer_context= NULL;
610 if (table->nested_join)
611 nullify_outer_context_for_on_clauses(table->nested_join->join_list);
612 }
613 }
614
615
616 /*
617 Create temporary table structure (but do not fill it)
618
619 @param thd Thread handle
620 @param lex LEX of the embedding query.
621 @param derived reference to the derived table.
622
623 @detail
624 Prepare underlying select for a derived table/view. To properly resolve
625 names in the embedding query the TABLE structure is created. Actual table
626 is created later by the mysql_derived_create function.
627
628 This function is called before any command containing derived table
629 is executed. All types of derived tables are handled by this function:
630 - Anonymous derived tables, or
631 - Named derived tables (aka views).
632
633 The table reference, contained in @c derived, is updated with the
634 fields of a new temporary table.
635 Derived tables are stored in @c thd->derived_tables and closed by
636 close_thread_tables().
637
638 This function is part of the procedure that starts in
639 open_and_lock_tables(), a procedure that - among other things - introduces
640 new table and table reference objects (to represent derived tables) that
641 don't exist in the privilege database. This means that normal privilege
642 checking cannot handle them. Hence this function does some extra tricks in
643 order to bypass normal privilege checking, by exploiting the fact that the
644 current state of privilege verification is attached as GRANT_INFO structures
645 on the relevant TABLE and TABLE_REF objects.
646
647 For table references, the current state of accrued access is stored inside
648 TABLE_LIST::grant. Hence this function must update the state of fulfilled
649 privileges for the new TABLE_LIST, an operation which is normally performed
650 exclusively by the table and database access checking functions,
651 check_access() and check_grant(), respectively. This modification is done
652 for both views and anonymous derived tables: The @c SELECT privilege is set
653 as fulfilled by the user. However, if a view is referenced and the table
654 reference is queried against directly (see TABLE_LIST::referencing_view),
655 the state of privilege checking (GRANT_INFO struct) is copied as-is to the
656 temporary table.
657
658 Only the TABLE structure is created here, actual table is created by the
659 mysql_derived_create function.
660
661 @note This function sets @c SELECT_ACL for @c TEMPTABLE views as well as
662 anonymous derived tables, but this is ok since later access checking will
663 distinguish between them.
664
665 @see mysql_handle_derived(), mysql_derived_fill(), GRANT_INFO
666
667 @return
668 false OK
669 true Error
670 */
671
672 static
mysql_derived_prepare(THD * thd,LEX * lex,TABLE_LIST * derived)673 bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
674 {
675 SELECT_LEX_UNIT *unit= derived->get_unit();
676 bool res= FALSE;
677 DBUG_ENTER("mysql_derived_prepare");
678 DBUG_PRINT("enter", ("unit: %p table_list: %p alias: '%s'",
679 unit, derived, derived->alias.str));
680 if (!unit)
681 DBUG_RETURN(FALSE);
682
683 SELECT_LEX *first_select= unit->first_select();
684
685 if (derived->is_recursive_with_table() &&
686 !derived->is_with_table_recursive_reference() &&
687 !derived->with->rec_result && derived->with->get_sq_rec_ref())
688 {
689 /*
690 This is a non-recursive reference to a recursive CTE whose
691 specification unit has not been prepared at the regular processing of
692 derived table references. This can happen only in the case when
693 the specification unit has no recursive references at the top level.
694 Force the preparation of the specification unit. Use a recursive
695 table reference from a subquery for this.
696 */
697 DBUG_ASSERT(derived->with->get_sq_rec_ref());
698 if (unlikely(mysql_derived_prepare(lex->thd, lex,
699 derived->with->get_sq_rec_ref())))
700 DBUG_RETURN(TRUE);
701 }
702
703 if (unit->prepared && derived->is_recursive_with_table() &&
704 !derived->table)
705 {
706 /*
707 Here 'derived' is either a non-recursive table reference to a recursive
708 with table or a recursive table reference to a recursvive table whose
709 specification has been already prepared (a secondary recursive table
710 reference.
711 */
712 if (!(derived->derived_result= new (thd->mem_root) select_unit(thd)))
713 DBUG_RETURN(TRUE); // out of memory
714 thd->create_tmp_table_for_derived= TRUE;
715 res= derived->derived_result->create_result_table(
716 thd, &unit->types, FALSE,
717 (first_select->options |
718 thd->variables.option_bits |
719 TMP_TABLE_ALL_COLUMNS),
720 &derived->alias, FALSE, FALSE, FALSE, 0);
721 thd->create_tmp_table_for_derived= FALSE;
722
723 if (likely(!res) && !derived->table)
724 {
725 derived->derived_result->set_unit(unit);
726 derived->table= derived->derived_result->table;
727 if (derived->is_with_table_recursive_reference())
728 {
729 /* Here 'derived" is a secondary recursive table reference */
730 unit->with_element->rec_result->rec_table_refs.push_back(derived);
731 }
732 }
733 DBUG_ASSERT(derived->table || res);
734 goto exit;
735 }
736
737 // Skip already prepared views/DT
738 if (unit->prepared ||
739 (derived->merged_for_insert &&
740 !(derived->is_multitable() &&
741 (thd->lex->sql_command == SQLCOM_UPDATE_MULTI ||
742 thd->lex->sql_command == SQLCOM_DELETE_MULTI))))
743 {
744 /*
745 System versioned tables may still require to get versioning conditions
746 when modifying view (see vers_setup_conds()). Only UPDATE and DELETE are
747 affected because they use WHERE condition.
748 */
749 if (!unit->prepared &&
750 derived->table->versioned() &&
751 derived->merge_underlying_list &&
752 /* choose only those merged views that do not select from other views */
753 !derived->merge_underlying_list->merge_underlying_list)
754 {
755 switch (thd->lex->sql_command)
756 {
757 case SQLCOM_DELETE:
758 case SQLCOM_DELETE_MULTI:
759 case SQLCOM_UPDATE:
760 case SQLCOM_UPDATE_MULTI:
761 if ((res= first_select->vers_setup_conds(thd,
762 derived->merge_underlying_list)))
763 goto exit;
764 if (derived->merge_underlying_list->where)
765 {
766 Query_arena_stmt on_stmt_arena(thd);
767 derived->where= and_items(thd, derived->where,
768 derived->merge_underlying_list->where);
769 }
770 default:
771 break;
772 }
773 }
774 DBUG_RETURN(FALSE);
775 }
776
777 /* prevent name resolving out of derived table */
778 for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select())
779 {
780 // Prevent it for the WHERE clause
781 sl->context.outer_context= 0;
782
783 // And for ON clauses, if there are any
784 nullify_outer_context_for_on_clauses(*sl->join_list);
785
786 if (!derived->is_with_table_recursive_reference() ||
787 (!derived->with->with_anchor &&
788 !derived->with->is_with_prepared_anchor()))
789 {
790 /*
791 Prepare underlying views/DT first unless 'derived' is a recursive
792 table reference and either the anchors from the specification of
793 'derived' has been already prepared or there no anchor in this
794 specification
795 */
796 if ((res= sl->handle_derived(lex, DT_PREPARE)))
797 goto exit;
798 }
799 if (derived->outer_join && sl->first_cond_optimization)
800 {
801 /* Mark that table is part of OUTER JOIN and fields may be NULL */
802 for (TABLE_LIST *cursor= (TABLE_LIST*) sl->table_list.first;
803 cursor;
804 cursor= cursor->next_local)
805 cursor->outer_join|= JOIN_TYPE_OUTER;
806 }
807 }
808
809 if (unlikely(thd->trace_started()))
810 {
811 /*
812 Add to optimizer trace whether a derived table/view
813 is merged into the parent select or not.
814 */
815 OPT_TRACE_VIEWS_TRANSFORM(thd, trace_wrapper, trace_derived,
816 derived->is_derived() ? "derived" : "view",
817 derived->alias.str ? derived->alias.str : "<NULL>",
818 derived->get_unit()->first_select()->select_number,
819 derived->is_merged_derived() ? "merged" : "materialized");
820 }
821 /*
822 Above cascade call of prepare is important for PS protocol, but after it
823 is called we can check if we really need prepare for this derived
824 */
825 if (derived->merged)
826 {
827 DBUG_PRINT("info", ("Irreversibly merged: exit"));
828 DBUG_RETURN(FALSE);
829 }
830
831 derived->fill_me= FALSE;
832
833 if ((!derived->is_with_table_recursive_reference() ||
834 !derived->derived_result) &&
835 !(derived->derived_result= new (thd->mem_root) select_unit(thd)))
836 DBUG_RETURN(TRUE); // out of memory
837
838 // st_select_lex_unit::prepare correctly work for single select
839 if ((res= unit->prepare(derived, derived->derived_result, 0)))
840 goto exit;
841 if (derived->with &&
842 (res= derived->with->rename_columns_of_derived_unit(thd, unit)))
843 goto exit;
844 if ((res= check_duplicate_names(thd, unit->types, 0)))
845 goto exit;
846
847 /*
848 Check whether we can merge this derived table into main select.
849 Depending on the result field translation will or will not
850 be created.
851 */
852 if (!derived->is_with_table_recursive_reference() &&
853 derived->init_derived(thd, FALSE))
854 goto exit;
855
856 /*
857 Temp table is created so that it hounours if UNION without ALL is to be
858 processed
859
860 As 'distinct' parameter we always pass FALSE (0), because underlying
861 query will control distinct condition by itself. Correct test of
862 distinct underlying query will be is_unit_op &&
863 !unit->union_distinct->next_select() (i.e. it is union and last distinct
864 SELECT is last SELECT of UNION).
865 */
866 thd->create_tmp_table_for_derived= TRUE;
867 if (!(derived->table) &&
868 derived->derived_result->create_result_table(thd, &unit->types, FALSE,
869 (first_select->options |
870 thd->variables.option_bits |
871 TMP_TABLE_ALL_COLUMNS),
872 &derived->alias,
873 FALSE, FALSE, FALSE,
874 0))
875 {
876 thd->create_tmp_table_for_derived= FALSE;
877 goto exit;
878 }
879 thd->create_tmp_table_for_derived= FALSE;
880
881 if (!derived->table)
882 derived->table= derived->derived_result->table;
883 DBUG_ASSERT(derived->table);
884 if (derived->is_derived() && derived->is_merged_derived())
885 first_select->mark_as_belong_to_derived(derived);
886
887 derived->dt_handler= derived->find_derived_handler(thd);
888 if (derived->dt_handler)
889 {
890 char query_buff[4096];
891 String derived_query(query_buff, sizeof(query_buff), thd->charset());
892 derived_query.length(0);
893 derived->derived->print(&derived_query,
894 enum_query_type(QT_VIEW_INTERNAL |
895 QT_ITEM_ORIGINAL_FUNC_NULLIF |
896 QT_PARSABLE));
897 if (!thd->make_lex_string(&derived->derived_spec,
898 derived_query.ptr(), derived_query.length()))
899 {
900 delete derived->dt_handler;
901 derived->dt_handler= NULL;
902 }
903 }
904
905 exit:
906 /* Hide "Unknown column" or "Unknown function" error */
907 if (derived->view)
908 {
909 if (thd->is_error() &&
910 (thd->get_stmt_da()->sql_errno() == ER_BAD_FIELD_ERROR ||
911 thd->get_stmt_da()->sql_errno() == ER_FUNC_INEXISTENT_NAME_COLLISION ||
912 thd->get_stmt_da()->sql_errno() == ER_SP_DOES_NOT_EXIST))
913 {
914 thd->clear_error();
915 my_error(ER_VIEW_INVALID, MYF(0), derived->db.str,
916 derived->table_name.str);
917 }
918 }
919
920 /*
921 if it is preparation PS only or commands that need only VIEW structure
922 then we do not need real data and we can skip execution (and parameters
923 is not defined, too)
924 */
925 if (res)
926 {
927 if (!derived->is_with_table_recursive_reference())
928 {
929 if (derived->table && derived->table->s->tmp_table)
930 free_tmp_table(thd, derived->table);
931 delete derived->derived_result;
932 }
933 }
934 else
935 {
936 TABLE *table= derived->table;
937 table->derived_select_number= first_select->select_number;
938 table->s->tmp_table= INTERNAL_TMP_TABLE;
939 #ifndef NO_EMBEDDED_ACCESS_CHECKS
940 if (derived->is_view())
941 table->grant= derived->grant;
942 else
943 {
944 DBUG_ASSERT(derived->is_derived());
945 DBUG_ASSERT(derived->is_anonymous_derived_table());
946 table->grant.privilege= SELECT_ACL;
947 derived->grant.privilege= SELECT_ACL;
948 }
949 #endif
950 /* Add new temporary table to list of open derived tables */
951 if (!derived->is_with_table_recursive_reference())
952 {
953 table->next= thd->derived_tables;
954 thd->derived_tables= table;
955 }
956
957 /* If table is used by a left join, mark that any column may be null */
958 if (derived->outer_join)
959 table->maybe_null= 1;
960 }
961 DBUG_RETURN(res);
962 }
963
964
965 /**
966 Runs optimize phase for a derived table/view.
967
968 @param thd thread handle
969 @param lex LEX of the embedding query.
970 @param derived reference to the derived table.
971
972 @details
973 Runs optimize phase for given 'derived' derived table/view.
974 If optimizer finds out that it's of the type "SELECT a_constant" then this
975 functions also materializes it.
976
977 @return FALSE ok.
978 @return TRUE if an error occur.
979 */
980
981 static
mysql_derived_optimize(THD * thd,LEX * lex,TABLE_LIST * derived)982 bool mysql_derived_optimize(THD *thd, LEX *lex, TABLE_LIST *derived)
983 {
984 SELECT_LEX_UNIT *unit= derived->get_unit();
985 SELECT_LEX *first_select= unit->first_select();
986 SELECT_LEX *save_current_select= lex->current_select;
987 bool res= FALSE;
988 DBUG_ENTER("mysql_derived_optimize");
989 DBUG_PRINT("enter", ("Alias: '%s' Unit: %p",
990 (derived->alias.str ? derived->alias.str : "<NULL>"),
991 derived->get_unit()));
992 if (derived->merged)
993 {
994 DBUG_PRINT("info", ("Irreversibly merged: exit"));
995 DBUG_RETURN(FALSE);
996 }
997
998 if (derived->is_materialized_derived() && derived->dt_handler)
999 {
1000 /* Create an object for execution of the query specifying the table */
1001 if (!(derived->pushdown_derived=
1002 new (thd->mem_root) Pushdown_derived(derived, derived->dt_handler)))
1003 {
1004 delete derived->dt_handler;
1005 derived->dt_handler= NULL;
1006 DBUG_RETURN(TRUE);
1007 }
1008 }
1009
1010 lex->current_select= first_select;
1011
1012 if (unit->is_unit_op())
1013 {
1014 if (unit->optimized)
1015 DBUG_RETURN(FALSE);
1016 // optimize union without execution
1017 res= unit->optimize();
1018 }
1019 else if (unit->derived)
1020 {
1021 if (!derived->is_merged_derived())
1022 {
1023 JOIN *join= first_select->join;
1024 unit->set_limit(unit->global_parameters());
1025 if (join &&
1026 join->optimization_state == JOIN::OPTIMIZATION_PHASE_1_DONE &&
1027 join->with_two_phase_optimization)
1028 {
1029 if (unit->optimized_2)
1030 DBUG_RETURN(FALSE);
1031 unit->optimized_2= TRUE;
1032 }
1033 else
1034 {
1035 if (unit->optimized)
1036 DBUG_RETURN(FALSE);
1037 unit->optimized= TRUE;
1038 if (!join)
1039 {
1040 /*
1041 This happens when derived is used in SELECT for which
1042 zer_result_cause != 0.
1043 In this case join is already destroyed.
1044 */
1045 DBUG_RETURN(FALSE);
1046 }
1047 }
1048 if ((res= join->optimize()))
1049 goto err;
1050 if (join->table_count == join->const_tables)
1051 derived->fill_me= TRUE;
1052 }
1053 }
1054 /*
1055 Materialize derived tables/views of the "SELECT a_constant" type.
1056 Such tables should be materialized at the optimization phase for
1057 correct constant evaluation.
1058 */
1059 if (!res && derived->fill_me && !derived->merged_for_insert)
1060 {
1061 if (derived->is_merged_derived())
1062 {
1063 derived->change_refs_to_fields();
1064 derived->set_materialized_derived();
1065 }
1066 if ((res= mysql_derived_create(thd, lex, derived)))
1067 goto err;
1068 if ((res= mysql_derived_fill(thd, lex, derived)))
1069 goto err;
1070 }
1071 err:
1072 lex->current_select= save_current_select;
1073 DBUG_RETURN(res);
1074 }
1075
1076
1077 /**
1078 Actually create result table for a materialized derived table/view.
1079
1080 @param thd thread handle
1081 @param lex LEX of the embedding query.
1082 @param derived reference to the derived table.
1083
1084 @details
1085 This function actually creates the result table for given 'derived'
1086 table/view, but it doesn't fill it.
1087 'thd' and 'lex' parameters are not used by this function.
1088
1089 @return FALSE ok.
1090 @return TRUE if an error occur.
1091 */
1092
1093 static
mysql_derived_create(THD * thd,LEX * lex,TABLE_LIST * derived)1094 bool mysql_derived_create(THD *thd, LEX *lex, TABLE_LIST *derived)
1095 {
1096 DBUG_ENTER("mysql_derived_create");
1097 DBUG_PRINT("enter", ("Alias: '%s' Unit: %p",
1098 (derived->alias.str ? derived->alias.str : "<NULL>"),
1099 derived->get_unit()));
1100 TABLE *table= derived->table;
1101 SELECT_LEX_UNIT *unit= derived->get_unit();
1102
1103 if (table->is_created())
1104 DBUG_RETURN(FALSE);
1105 select_unit *result= derived->derived_result;
1106 if (table->s->db_type() == TMP_ENGINE_HTON)
1107 {
1108 result->tmp_table_param.keyinfo= table->s->key_info;
1109 if (create_internal_tmp_table(table, result->tmp_table_param.keyinfo,
1110 result->tmp_table_param.start_recinfo,
1111 &result->tmp_table_param.recinfo,
1112 (unit->first_select()->options |
1113 thd->variables.option_bits | TMP_TABLE_ALL_COLUMNS)))
1114 DBUG_RETURN(TRUE);
1115 }
1116 if (open_tmp_table(table))
1117 DBUG_RETURN(TRUE);
1118 table->file->extra(HA_EXTRA_WRITE_CACHE);
1119 table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
1120 DBUG_RETURN(FALSE);
1121 }
1122
1123
register_as_derived_with_rec_ref(With_element * rec_elem)1124 void TABLE_LIST::register_as_derived_with_rec_ref(With_element *rec_elem)
1125 {
1126 rec_elem->derived_with_rec_ref.link_in_list(this, &this->next_with_rec_ref);
1127 is_derived_with_recursive_reference= true;
1128 get_unit()->uncacheable|= UNCACHEABLE_DEPENDENT;
1129 }
1130
1131
is_nonrecursive_derived_with_rec_ref()1132 bool TABLE_LIST::is_nonrecursive_derived_with_rec_ref()
1133 {
1134 return is_derived_with_recursive_reference;
1135 }
1136
1137
1138 /**
1139 @brief
1140 Fill the recursive with table
1141
1142 @param thd The thread handle
1143
1144 @details
1145 The method is called only for recursive with tables.
1146 The method executes the recursive part of the specification
1147 of this with table until no more rows are added to the table
1148 or the number of the performed iteration reaches the allowed
1149 maximum.
1150
1151 @retval
1152 false on success
1153 true on failure
1154 */
1155
fill_recursive(THD * thd)1156 bool TABLE_LIST::fill_recursive(THD *thd)
1157 {
1158 bool rc= false;
1159 st_select_lex_unit *unit= get_unit();
1160 rc= with->instantiate_tmp_tables();
1161 while (!rc && !with->all_are_stabilized())
1162 {
1163 if (with->level > thd->variables.max_recursive_iterations)
1164 break;
1165 with->prepare_for_next_iteration();
1166 rc= unit->exec_recursive();
1167 }
1168 if (!rc)
1169 {
1170 TABLE *src= with->rec_result->table;
1171 rc =src->insert_all_rows_into_tmp_table(thd,
1172 table,
1173 &with->rec_result->tmp_table_param,
1174 true);
1175 }
1176 return rc;
1177 }
1178
1179
1180 /*
1181 Execute subquery of a materialized derived table/view and fill the result
1182 table.
1183
1184 @param thd Thread handle
1185 @param lex LEX for this thread
1186 @param derived reference to the derived table.
1187
1188 @details
1189 Execute subquery of given 'derived' table/view and fill the result
1190 table. After result table is filled, if this is not the EXPLAIN statement
1191 and the table is not specified with a recursion the entire unit / node
1192 is deleted. unit is deleted if UNION is used for derived table and node
1193 is deleted is it is a simple SELECT.
1194 'lex' is unused and 'thd' is passed as an argument to an underlying function.
1195
1196 @note
1197 If you use this function, make sure it's not called at prepare.
1198 Due to evaluation of LIMIT clause it can not be used at prepared stage.
1199
1200 @return FALSE OK
1201 @return TRUE Error
1202 */
1203
1204 static
mysql_derived_fill(THD * thd,LEX * lex,TABLE_LIST * derived)1205 bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
1206 {
1207 Field_iterator_table field_iterator;
1208 SELECT_LEX_UNIT *unit= derived->get_unit();
1209 bool derived_is_recursive= derived->is_recursive_with_table();
1210 bool res= FALSE;
1211 DBUG_ENTER("mysql_derived_fill");
1212 DBUG_PRINT("enter", ("Alias: '%s' Unit: %p",
1213 (derived->alias.str ? derived->alias.str : "<NULL>"),
1214 derived->get_unit()));
1215
1216 if (unit->executed && !unit->uncacheable && !unit->describe &&
1217 !derived_is_recursive)
1218 DBUG_RETURN(FALSE);
1219 /*check that table creation passed without problems. */
1220 DBUG_ASSERT(derived->table && derived->table->is_created());
1221 select_unit *derived_result= derived->derived_result;
1222 SELECT_LEX *save_current_select= lex->current_select;
1223
1224 if (derived->pushdown_derived)
1225 {
1226 int res;
1227 if (unit->executed)
1228 DBUG_RETURN(FALSE);
1229 /* Execute the query that specifies the derived table by a foreign engine */
1230 res= derived->pushdown_derived->execute();
1231 unit->executed= true;
1232 delete derived->pushdown_derived;
1233 DBUG_RETURN(res);
1234 }
1235
1236 if (unit->executed && !derived_is_recursive &&
1237 (unit->uncacheable & UNCACHEABLE_DEPENDENT))
1238 {
1239 if ((res= derived->table->file->ha_delete_all_rows()))
1240 goto err;
1241 JOIN *join= unit->first_select()->join;
1242 join->first_record= false;
1243 for (uint i= join->top_join_tab_count;
1244 i < join->top_join_tab_count + join->aggr_tables;
1245 i++)
1246 {
1247 if ((res= join->join_tab[i].table->file->ha_delete_all_rows()))
1248 goto err;
1249 }
1250 }
1251
1252 if (derived_is_recursive)
1253 {
1254 if (derived->is_with_table_recursive_reference())
1255 {
1256 /* Here only one iteration step is performed */
1257 res= unit->exec_recursive();
1258 }
1259 else
1260 {
1261 /* In this case all iteration are performed */
1262 res= derived->fill_recursive(thd);
1263 }
1264 }
1265 else if (unit->is_unit_op())
1266 {
1267 // execute union without clean up
1268 res= unit->exec();
1269 }
1270 else
1271 {
1272 SELECT_LEX *first_select= unit->first_select();
1273 unit->set_limit(unit->global_parameters());
1274 if (unit->select_limit_cnt == HA_POS_ERROR)
1275 first_select->options&= ~OPTION_FOUND_ROWS;
1276
1277 lex->current_select= first_select;
1278 res= mysql_select(thd,
1279 first_select->table_list.first,
1280 first_select->with_wild,
1281 first_select->item_list, first_select->where,
1282 (first_select->order_list.elements+
1283 first_select->group_list.elements),
1284 first_select->order_list.first,
1285 first_select->group_list.first,
1286 first_select->having, (ORDER*) NULL,
1287 (first_select->options |thd->variables.option_bits |
1288 SELECT_NO_UNLOCK),
1289 derived_result, unit, first_select);
1290 }
1291
1292 if (!res && !derived_is_recursive)
1293 {
1294 if (derived_result->flush())
1295 res= TRUE;
1296 unit->executed= TRUE;
1297
1298 if (derived->field_translation)
1299 {
1300 /* reset translation table to materialized table */
1301 field_iterator.set_table(derived->table);
1302 for (uint i= 0;
1303 !field_iterator.end_of_fields();
1304 field_iterator.next(), i= i + 1)
1305 {
1306 Item *item;
1307
1308 if (!(item= field_iterator.create_item(thd)))
1309 {
1310 res= TRUE;
1311 break;
1312 }
1313 thd->change_item_tree(&derived->field_translation[i].item, item);
1314 }
1315 }
1316 }
1317 err:
1318 if (res || (!derived_is_recursive && !lex->describe && !unit->uncacheable))
1319 unit->cleanup();
1320 lex->current_select= save_current_select;
1321
1322 DBUG_RETURN(res);
1323 }
1324
1325
1326 /**
1327 Re-initialize given derived table/view for the next execution.
1328
1329 @param thd thread handle
1330 @param lex LEX for this thread
1331 @param derived reference to the derived table.
1332
1333 @details
1334 Re-initialize given 'derived' table/view for the next execution.
1335 All underlying views/derived tables are recursively reinitialized prior
1336 to re-initialization of given derived table.
1337 'thd' and 'lex' are passed as arguments to called functions.
1338
1339 @return FALSE OK
1340 @return TRUE Error
1341 */
1342
1343 static
mysql_derived_reinit(THD * thd,LEX * lex,TABLE_LIST * derived)1344 bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived)
1345 {
1346 DBUG_ENTER("mysql_derived_reinit");
1347 DBUG_PRINT("enter", ("Alias: '%s' Unit: %p",
1348 (derived->alias.str ? derived->alias.str : "<NULL>"),
1349 derived->get_unit()));
1350 st_select_lex_unit *unit= derived->get_unit();
1351
1352 derived->merged_for_insert= FALSE;
1353 unit->unclean();
1354 unit->types.empty();
1355 /* for derived tables & PS (which can't be reset by Item_subselect) */
1356 unit->reinit_exec_mechanism();
1357 unit->set_thd(thd);
1358 DBUG_RETURN(FALSE);
1359 }
1360
1361
1362 /*
1363 @brief
1364 Given condition cond and transformer+argument, try transforming as many
1365 conjuncts as possible.
1366
1367 @detail
1368 The motivation of this function is to convert the condition that's being
1369 pushed into a WHERE clause with derived_field_transformer_for_where or
1370 with derived_grouping_field_transformer_for_where.
1371 The transformer may fail for some sub-condition, in this case we want to
1372 convert the most restrictive part of the condition that can be pushed.
1373
1374 This function only does it for top-level AND: conjuncts that could not be
1375 converted are dropped.
1376
1377 @return
1378 Converted condition, or NULL if nothing could be converted
1379 */
1380
transform_condition_or_part(THD * thd,Item * cond,Item_transformer transformer,uchar * arg)1381 Item *transform_condition_or_part(THD *thd,
1382 Item *cond,
1383 Item_transformer transformer,
1384 uchar *arg)
1385 {
1386 if (cond->type() != Item::COND_ITEM ||
1387 ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC)
1388 {
1389 Item *new_item= cond->transform(thd, transformer, arg);
1390 // Indicate that the condition is not pushable
1391 if (!new_item)
1392 cond->clear_extraction_flag();
1393 return new_item;
1394 }
1395
1396 List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
1397 Item *item;
1398 while ((item=li++))
1399 {
1400 Item *new_item= item->transform(thd, transformer, arg);
1401 if (!new_item)
1402 {
1403 // Indicate that the condition is not pushable
1404 item->clear_extraction_flag();
1405 li.remove();
1406 }
1407 else
1408 li.replace(new_item);
1409 }
1410
1411 switch (((Item_cond*) cond)->argument_list()->elements)
1412 {
1413 case 0:
1414 return NULL;
1415 case 1:
1416 return ((Item_cond*) cond)->argument_list()->head();
1417 default:
1418 return cond;
1419 }
1420 }
1421
1422
1423 /**
1424 @brief
1425 Extract condition that can be pushed into a derived table/view
1426
1427 @param thd the thread handle
1428 @param cond current condition
1429 @param derived the reference to the derived table/view
1430
1431 @details
1432 This function builds the most restrictive condition depending only on
1433 the derived table/view (directly or indirectly through equality) that
1434 can be extracted from the given condition cond and pushes it into the
1435 derived table/view.
1436
1437 Example of the transformation:
1438
1439 SELECT *
1440 FROM t1,
1441 (
1442 SELECT x,MAX(y) AS max_y
1443 FROM t2
1444 GROUP BY x
1445 ) AS d_tab
1446 WHERE d_tab.x>1 AND d_tab.max_y<30;
1447
1448 =>
1449
1450 SELECT *
1451 FROM t1,
1452 (
1453 SELECT x,z,MAX(y) AS max_y
1454 FROM t2
1455 WHERE x>1
1456 HAVING max_y<30
1457 GROUP BY x
1458 ) AS d_tab
1459 WHERE d_tab.x>1 AND d_tab.max_y<30;
1460
1461 In details:
1462 1. Check what pushable formula can be extracted from cond
1463 2. Build a clone PC of the formula that can be extracted
1464 (the clone is built only if the extracted formula is a AND subformula
1465 of cond or conjunction of such subformulas)
1466 Do for every select specifying derived table/view:
1467 3. If there is no HAVING clause prepare PC to be conjuncted with
1468 WHERE clause of the select. Otherwise do 4-7.
1469 4. Check what formula PC_where can be extracted from PC to be pushed
1470 into the WHERE clause of the select
1471 5. Build PC_where and if PC_where is a conjunct(s) of PC remove it from PC
1472 getting PC_having
1473 6. Prepare PC_where to be conjuncted with the WHERE clause of the select
1474 7. Prepare PC_having to be conjuncted with the HAVING clause of the select
1475 @note
1476 This method is similar to pushdown_cond_for_in_subquery()
1477
1478 @retval TRUE if an error occurs
1479 @retval FALSE otherwise
1480 */
1481
pushdown_cond_for_derived(THD * thd,Item * cond,TABLE_LIST * derived)1482 bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
1483 {
1484 DBUG_ENTER("pushdown_cond_for_derived");
1485 if (!cond)
1486 DBUG_RETURN(false);
1487
1488 st_select_lex_unit *unit= derived->get_unit();
1489 st_select_lex *first_sl= unit->first_select();
1490 st_select_lex *sl= first_sl;
1491
1492 if (derived->prohibit_cond_pushdown)
1493 DBUG_RETURN(false);
1494
1495 /* Do not push conditions into constant derived */
1496 if (unit->executed)
1497 DBUG_RETURN(false);
1498
1499 /* Do not push conditions into recursive with tables */
1500 if (derived->is_recursive_with_table())
1501 DBUG_RETURN(false);
1502
1503 /* Do not push conditions into unit with global ORDER BY ... LIMIT */
1504 if (unit->fake_select_lex && unit->fake_select_lex->explicit_limit)
1505 DBUG_RETURN(false);
1506
1507 /* Check whether any select of 'unit' allows condition pushdown */
1508 bool some_select_allows_cond_pushdown= false;
1509 for (; sl; sl= sl->next_select())
1510 {
1511 if (sl->cond_pushdown_is_allowed())
1512 {
1513 some_select_allows_cond_pushdown= true;
1514 break;
1515 }
1516 }
1517 if (!some_select_allows_cond_pushdown)
1518 DBUG_RETURN(false);
1519
1520 /* 1. Check what pushable formula can be extracted from cond */
1521 Item *extracted_cond;
1522 cond->check_pushable_cond(&Item::pushable_cond_checker_for_derived,
1523 (uchar *)(&derived->table->map));
1524 /* 2. Build a clone PC of the formula that can be extracted */
1525 extracted_cond=
1526 cond->build_pushable_cond(thd,
1527 &Item::pushable_equality_checker_for_derived,
1528 ((uchar *)&derived->table->map));
1529 if (!extracted_cond)
1530 {
1531 /* Nothing can be pushed into the derived table */
1532 DBUG_RETURN(false);
1533 }
1534
1535 st_select_lex *save_curr_select= thd->lex->current_select;
1536 for (; sl; sl= sl->next_select())
1537 {
1538 Item *extracted_cond_copy;
1539 if (!sl->cond_pushdown_is_allowed())
1540 continue;
1541 /*
1542 For each select of the unit except the last one
1543 create a clone of extracted_cond
1544 */
1545 extracted_cond_copy= !sl->next_select() ?
1546 extracted_cond :
1547 extracted_cond->build_clone(thd);
1548 if (!extracted_cond_copy)
1549 continue;
1550
1551 /*
1552 Rename the columns of all non-first selects of a union to be compatible
1553 by names with the columns of the first select. It will allow to use copies
1554 of the same expression pushed into having clauses of different selects.
1555 */
1556 if (sl != first_sl)
1557 {
1558 DBUG_ASSERT(sl->item_list.elements == first_sl->item_list.elements);
1559 List_iterator_fast<Item> it(sl->item_list);
1560 List_iterator_fast<Item> nm_it(unit->types);
1561 while (Item *item= it++)
1562 item->share_name_with(nm_it++);
1563 }
1564
1565 /* Collect fields that are used in the GROUP BY of sl */
1566 if (sl->have_window_funcs())
1567 {
1568 if (sl->group_list.first || sl->join->implicit_grouping)
1569 continue;
1570 ORDER *common_partition_fields=
1571 sl->find_common_window_func_partition_fields(thd);
1572 if (!common_partition_fields)
1573 continue;
1574 sl->collect_grouping_fields_for_derived(thd, common_partition_fields);
1575 }
1576 else
1577 sl->collect_grouping_fields_for_derived(thd, sl->group_list.first);
1578
1579 Item *remaining_cond= NULL;
1580 /* Do 4-6 */
1581 sl->pushdown_cond_into_where_clause(thd, extracted_cond_copy,
1582 &remaining_cond,
1583 &Item::derived_field_transformer_for_where,
1584 (uchar *) sl);
1585
1586 if (!remaining_cond)
1587 continue;
1588 /*
1589 7. Prepare PC_having to be conjuncted with the HAVING clause of
1590 the select
1591 */
1592 remaining_cond=
1593 remaining_cond->transform(thd,
1594 &Item::derived_field_transformer_for_having,
1595 (uchar *) sl);
1596 if (!remaining_cond)
1597 continue;
1598
1599 if (remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor,
1600 0, 0))
1601 continue;
1602
1603 mark_or_conds_to_avoid_pushdown(remaining_cond);
1604
1605 sl->cond_pushed_into_having= remaining_cond;
1606 }
1607 thd->lex->current_select= save_curr_select;
1608 DBUG_RETURN(false);
1609 }
1610
1611
1612 /**
1613 @brief
1614 Look for provision of the derived_handler interface by a foreign engine
1615
1616 @param thd The thread handler
1617
1618 @details
1619 The function looks through its tables of the query that specifies this
1620 derived table searching for a table whose handlerton owns a
1621 create_derived call-back function. If the call of this function returns
1622 a derived_handler interface object then the server will push the query
1623 specifying the derived table into this engine.
1624 This is a responsibility of the create_derived call-back function to
1625 check whether the engine can execute the query.
1626
1627 @retval the found derived_handler if the search is successful
1628 0 otherwise
1629 */
1630
find_derived_handler(THD * thd)1631 derived_handler *TABLE_LIST::find_derived_handler(THD *thd)
1632 {
1633 if (!derived || is_recursive_with_table())
1634 return 0;
1635 for (SELECT_LEX *sl= derived->first_select(); sl; sl= sl->next_select())
1636 {
1637 if (!(sl->join))
1638 continue;
1639 for (TABLE_LIST *tbl= sl->join->tables_list; tbl; tbl= tbl->next_local)
1640 {
1641 if (!tbl->table)
1642 continue;
1643 handlerton *ht= tbl->table->file->partition_ht();
1644 if (!ht->create_derived)
1645 continue;
1646 derived_handler *dh= ht->create_derived(thd, this);
1647 if (dh)
1648 {
1649 dh->set_derived(this);
1650 return dh;
1651 }
1652 }
1653 }
1654 return 0;
1655 }
1656
1657
get_first_table()1658 TABLE_LIST *TABLE_LIST::get_first_table()
1659 {
1660 for (SELECT_LEX *sl= derived->first_select(); sl; sl= sl->next_select())
1661 {
1662 if (!(sl->join))
1663 continue;
1664 for (TABLE_LIST *tbl= sl->join->tables_list; tbl; tbl= tbl->next_local)
1665 {
1666 if (!tbl->table)
1667 continue;
1668 return tbl;
1669 }
1670 }
1671 return 0;
1672 }
1673