 |
MySQL 8.4.2
Source Code Documentation
|
|
MySQL 8.4.2
Source Code Documentation
|
Classes | |
| class | Semijoin_decorrelation |
| Context object used by semijoin equality decorrelation code. More... | |
| struct | Lifted_expressions_map |
Helper singleton class used to track information needed to perform the transform of a correlated scalar subquery in a derived table, as performed by decorrelate_derived_scalar_subquery_pre and decorrelate_derived_scalar_subquery_pre. More... | |
Functions | |
| static bool | simplify_const_condition (THD *thd, Item **cond, bool remove_cond, bool *ret_cond_value) |
| Try to replace a const condition with a simple constant. More... | |
| static Item * | create_rollup_switcher (THD *thd, Query_block *query_block, Item_sum *item, int send_group_parts) |
| static bool | fulltext_uses_rollup_column (const Query_block *query_block) |
| Checks if there are any calls to the MATCH function that take a ROLLUP column as argument in the SELECT list, GROUP BY clause, HAVING clause or ORDER BY clause. More... | |
| static void | update_used_tables_for_join (mem_root_deque< Table_ref * > *tables) |
| Update used tables information for a JOIN expression. More... | |
| static Table_ref ** | make_leaf_tables (Table_ref **list, Table_ref *tables) |
| Make list of leaf tables of join table tree. More... | |
| static void | fix_tables_after_pullout (Query_block *parent_query_block, Query_block *removed_query_block, Table_ref *tr, uint table_adjust, table_map lateral_deps) |
| Update table reference information for conditions and expressions due to query blocks having been merged in from derived tables/views and due to semi-join transformation. More... | |
| static bool | decorrelate_equality (Semijoin_decorrelation &sj_decor, Item_func *func, bool *was_correlated) |
| Try to decorrelate an (in)equality node. More... | |
| static bool | can_decorrelate_operator (Item_func *func, bool only_eq) |
| bool | walk_join_list (mem_root_deque< Table_ref * > &list, std::function< bool(Table_ref *)> action) |
| static bool | build_sj_exprs (THD *thd, mem_root_deque< Item * > *sj_outer_exprs, mem_root_deque< Item * > *sj_inner_exprs, Item_exists_subselect *subq_pred, Query_block *subq_query_block) |
| Builds the list of SJ outer/inner expressions. More... | |
| static bool | replace_subcondition (THD *thd, Item **tree, Item *old_cond, Item *new_cond, bool do_fix_fields, bool *found_ptr=nullptr) |
| Destructively replaces a sub-condition inside a condition tree. More... | |
| void | propagate_nullability (mem_root_deque< Table_ref * > *tables, bool nullable) |
| Propagate nullability into inner tables of outer join operation. More... | |
| bool | find_order_in_list (THD *thd, Ref_item_array ref_item_array, Table_ref *tables, ORDER *order, mem_root_deque< Item * > *fields, bool is_group_field, bool is_window_order) |
| Resolve an ORDER BY or GROUP BY column reference. More... | |
| bool | setup_order (THD *thd, Ref_item_array ref_item_array, Table_ref *tables, mem_root_deque< Item * > *fields, ORDER *order) |
| Resolve and setup list of expressions in ORDER BY clause. More... | |
| static ReplaceResult | wrap_grouped_expressions_for_rollup (Query_block *select, Item *item, Item *parent, unsigned argument_idx) |
| Checks whether an item matches a grouped expression, creates an Item_rollup_group_item around it and replaces the reference to it with that item. More... | |
| static bool | WalkAndReplaceInner (THD *thd, Item *parent, unsigned argument_idx, const function< ReplaceResult(Item *item, Item *parent, unsigned argument_idx)> &get_new_item, Item **child_ref) |
| Helper function for WalkAndReplace() which replaces the Item referenced by "child_ref" if "get_new_item" returns a replacement, or visits the children of "child_ref" otherwise. More... | |
| bool | WalkAndReplace (THD *thd, Item *item, const function< ReplaceResult(Item *item, Item *parent, unsigned argument_idx)> &get_new_item) |
| static bool | refresh_comparators_after_rollup (Item *item) |
| Refreshes the comparators after ROLLUP resolving. More... | |
| bool | validate_gc_assignment (const mem_root_deque< Item * > &fields, const mem_root_deque< Item * > &values, TABLE *table) |
| validate_gc_assignment Check whether the other values except DEFAULT are assigned for generated columns. More... | |
| static bool | baptize_item (THD *thd, Item *item, int *field_no) |
| Helper function to make names for columns of a derived table replacing a scalar or table subquery. More... | |
| static bool | update_context_to_derived (Item *expr, Query_block *new_derived) |
| A minion of transform_grouped_to_derived. More... | |
| static bool | replace_aggregate_in_list (Item::Aggregate_replacement &info, bool was_hidden, mem_root_deque< Item * > *list, Ref_item_array *ref_item_array) |
| A minion of transform_grouped_to_derived. More... | |
| static bool | collect_aggregates (Query_block *select, Item_sum::Collect_grouped_aggregate_info *aggregates) |
| A minion of transform_grouped_to_derived. More... | |
| static bool | query_block_contains_subquery (Query_block *select, Query_expression *slu) |
| A minion of transform_scalar_subqueries_to_join_with_derived. More... | |
| static bool | walk_join_conditions (mem_root_deque< Table_ref * > &list, std::function< bool(Item **expr_p)> action, Item::Collect_scalar_subquery_info *info) |
| static void | remember_transform (THD *thd, Query_block *select) |
| Remember if this transform was performed. More... | |
| static bool | add_partition_by_expr (THD *thd, PT_order_list *partition, Query_block *qb, Item *expr) |
| Given an expression, create an ORDER expression for that expression and add it to a window's ORDER BY list in preparation for synthesizing a window function, cf. More... | |
| static bool | replace_inner_function_calls_in_lifted_predicate (THD *thd, Table_ref *derived, Lifted_expressions_map *lifted_exprs, Query_block *qb) |
| Function calls in lifted join condition. More... | |
| static bool | replace_inner_fields_in_lifted_predicate (THD *thd, Table_ref *derived, Lifted_expressions_map *lifted_exprs, Query_block *qb, uint *field_pos_idx) |
| Fields in lifted join condition. More... | |
| static bool | build_reject_if (THD *thd, Table_ref *derived, Lifted_expressions_map *lifted_exprs, uint field_pos_idx) |
| static std::pair< bool, bool > | item_containing_non_correlated_field (Item *item) |
| WL#15540 check that predicate operand item conforms to our requirements. More... | |
| bool | is_correlated_predicate_eligible (Item *cor_pred) |
| Called to check if the provided correlated predicate is eligible for transformation. More... | |
| static bool | extract_correlated_condition (THD *thd, Item **cond, Item **correlated_cond) |
| Extracts the top level correlated condition in an OR condition. More... | |
| bool | Query_block::prepare (THD *thd, mem_root_deque< Item * > *insert_field_list) |
| Prepare query block for optimization. More... | |
| bool | Query_block::push_conditions_to_derived_tables (THD *thd) |
| Pushes parts of the WHERE condition of this query block to materialized derived tables. More... | |
| bool | Query_block::prepare_values (THD *thd) |
| Prepare a table value constructor query block for optimization. More... | |
| bool | Query_block::apply_local_transforms (THD *thd, bool prune) |
| Does permanent transformations which are local to a query block (which do not merge it to another block). More... | |
| void | Query_block::update_used_tables () |
| Update used tables information for all local expressions. More... | |
| bool | Query_block::resolve_limits (THD *thd) |
| Resolve OFFSET and LIMIT clauses. More... | |
| bool | Item_in_subselect::subquery_allows_materialization (THD *thd, Query_block *query_block, const Query_block *outer) |
| Check if the subquery predicate can be executed via materialization. More... | |
| bool | Query_block::check_view_privileges (THD *thd, Access_bitmask want_privilege_first, Access_bitmask want_privilege_next) |
| Check privileges for views that are merged into query block. More... | |
| bool | Query_block::setup_tables (THD *thd, Table_ref *tables, bool select_insert) |
| Resolve and prepare information about tables for one query block. More... | |
| void | Query_block::remap_tables (THD *thd) |
| Re-map table numbers for all tables in a query block. More... | |
| bool | Query_block::resolve_placeholder_tables (THD *thd, bool apply_semijoin) |
| Resolve derived table, view, table function information for a query block. More... | |
| bool | Query_block::is_row_count_valid_for_semi_join () |
| Check if the offset and limit are valid for a semijoin. More... | |
| bool | Query_block::setup_wild (THD *thd) |
| Expand all '*' in list of expressions with the matching column references. More... | |
| bool | Query_block::setup_conds (THD *thd) |
| Resolve WHERE condition and join conditions. More... | |
| bool | Query_block::setup_join_cond (THD *thd, mem_root_deque< Table_ref * > *tables, bool in_update) |
| Resolve join conditions for a join nest. More... | |
| void | Query_block::reset_nj_counters (mem_root_deque< Table_ref * > *join_list=nullptr) |
| Set NESTED_JOIN::counter=0 in all nested joins in passed list. More... | |
| bool | Query_block::simplify_joins (THD *thd, mem_root_deque< Table_ref * > *join_list, bool top, bool in_sj, Item **new_conds, uint *changelog=nullptr) |
| Simplify joins replacing outer joins by inner joins whenever it's possible. More... | |
| bool | Query_block::record_join_nest_info (mem_root_deque< Table_ref * > *tables) |
| Record join nest info in the select block. More... | |
| void | Query_expression::fix_after_pullout (Query_block *parent_query_block, Query_block *removed_query_block) |
| Fix used tables information for a subquery after query transformations. More... | |
| void | Query_block::fix_after_pullout (Query_block *parent_query_block, Query_block *removed_query_block) |
| void | Query_block::clear_sj_expressions (NESTED_JOIN *nested_join) |
| Remove semijoin condition for this query block. More... | |
| bool | Query_block::build_sj_cond (THD *thd, NESTED_JOIN *nested_join, Query_block *subq_query_block, table_map outer_tables_map, Item **sj_cond, bool *simple_const) |
| Build semijoin condition for th query block. More... | |
| bool | Query_block::decorrelate_condition (Semijoin_decorrelation &sj_decor, Table_ref *join_nest) |
| Decorrelate the WHERE clause or a join condition of a subquery used in an IN or EXISTS predicate. More... | |
| bool | Query_block::allocate_grouping_sets (THD *thd) |
| Initializes the grouping set if the query block includes GROUP BY modifiers. More... | |
| bool | Query_block::populate_grouping_sets (THD *thd) |
| Populates the grouping sets if the query block includes non-primitive grouping. More... | |
| bool | Query_block::convert_subquery_to_semijoin (THD *thd, Item_exists_subselect *subq_pred) |
| Convert a subquery predicate of this query block into a Table_ref semi-join nest. More... | |
| bool | Query_block::merge_derived (THD *thd, Table_ref *derived_table) |
| Merge derived table into query block. More... | |
| bool | Query_block::flatten_subqueries (THD *thd) |
| Convert semi-join subquery predicates into semi-join join nests. More... | |
| void | Query_block::propagate_unique_test_exclusion () |
| Propagate exclusion from table uniqueness test into subqueries. More... | |
| bool | Query_block::add_ftfunc_list (List< Item_func_match > *ftfuncs) |
| Add full-text function elements from a list into this query block. More... | |
| void | Query_block::repoint_contexts_of_join_nests (mem_root_deque< Table_ref * > join_list) |
| Go through a list of tables and join nests, recursively, and repoint its query_block pointer. More... | |
| void | Query_block::merge_contexts (Query_block *inner) |
| Merge name resolution context objects of a subquery into its parent. More... | |
| bool | Query_block::remove_redundant_subquery_clauses (THD *thd) |
| For a table subquery predicate (IN/ANY/ALL/EXISTS/etc): since it does not support LIMIT the following clauses are redundant: More... | |
| bool | Query_block::empty_order_list (Query_block *sl) |
| Empty the ORDER list. More... | |
| bool | Query_block::check_only_full_group_by (THD *thd) |
| Runs checks mandated by ONLY_FULL_GROUP_BY. More... | |
| bool | Query_block::setup_order_final (THD *thd) |
| Do final setup of ORDER BY clause, after the query block is fully resolved. More... | |
| bool | Query_block::setup_group (THD *thd) |
| Resolve and set up the GROUP BY list. More... | |
| ORDER * | Query_block::find_in_group_list (Item *item, int *rollup_level) const |
| Finds a group expression matching the given item, or nullptr if none. More... | |
| int | Query_block::group_list_size () const |
| void | Query_block::mark_item_as_maybe_null_if_non_primitive_grouped (Item *item) const |
| Marks occurrences of group by fields in a function's arguments as nullable, so that we do not optimize them away before we get to add the rollup wrappers. More... | |
| Item * | Query_block::single_visible_field () const |
| size_t | Query_block::num_visible_fields () const |
| bool | Query_block::field_list_is_empty () const |
| Item * | Query_block::resolve_rollup_item (THD *thd, Item *item) |
| Resolve an item (and its tree) for rollup processing by replacing items matching grouped expressions with Item_rollup_group_items and updating properties (m_nullable, PROP_ROLLUP_FIELD). More... | |
| bool | Query_block::resolve_rollup (THD *thd) |
| Resolve items in SELECT list and ORDER BY list for rollup processing. More... | |
| bool | Query_block::resolve_rollup_wfs (THD *thd) |
| Replace group by field references inside window functions with references in the presence of ROLLUP. More... | |
| void | Query_block::delete_unused_merged_columns (mem_root_deque< Table_ref * > *tables) |
| Delete unused columns from merged tables. More... | |
| Item ** | Query_block::add_hidden_item (Item *item) |
| Add item to the hidden part of select list. More... | |
| void | Query_block::remove_hidden_items () |
| Remove hidden items from select list. More... | |
| bool | Query_block::resolve_table_value_constructor_values (THD *thd) |
| Resolve the rows of a table value constructor and aggregate the type of each column across rows. More... | |
| bool | Query_block::transform_table_subquery_to_join_with_derived (THD *thd, Item_exists_subselect *subq_pred) |
| Replace a table subquery ([NOT] {IN, EXISTS}) with a join to a derived table. More... | |
| Table_ref * | Query_block::synthesize_derived (THD *thd, Query_expression *unit, Item *join_cond, bool left_outer, bool use_inner_join) |
| Create a new Table_ref object for this query block, for either: 1) a derived table which will replace the subquery, or 2) an extra derived table for handling grouping, if necessary, cf. More... | |
| bool | Query_block::remove_aggregates (THD *thd, Query_block *select) |
| A minion of transform_grouped_to_derived. More... | |
| bool | Query_block::replace_item_in_expression (Item **expr, bool was_hidden, Item::Item_replacement *info, Item_transformer transformer) |
Minion of transform_grouped_to_derived. More... | |
| bool | Query_block::transform_grouped_to_derived (THD *thd, bool *break_off) |
Minion of transform_scalar_subqueries_to_join_with_derived. More... | |
| bool | Query_block::replace_subquery_in_expr (THD *thd, Item::Css_info *subquery, Table_ref *tr, Item **expr) |
| A minion of transform_scalar_subqueries_to_join_with_derived. More... | |
| bool | Query_block::nest_derived (THD *thd, Item *join_cond, mem_root_deque< Table_ref * > *join_list, Table_ref *new_derived_table) |
| Push the generated derived table to the correct location inside a join nest. More... | |
| bool | Query_block::setup_counts_over_partitions (THD *thd, Table_ref *derived, Lifted_expressions_map *lifted_expressions, mem_root_deque< Item * > &exprs_added_to_group_by, uint hidden_fields) |
| Add all COUNT(0) to SELECT list of the derived table to be used for cardinality checking of the transformed subquery. More... | |
| bool | Query_block::add_inner_exprs_to_group_by (THD *thd, List_iterator< Item > &inner_exprs, Item *selected_item, bool *selected_expr_added_to_group_by, mem_root_deque< Item * > *exprs_added_to_group_by) |
| Run through the inner expressions and add them to the block's GROUP BY if not already present. More... | |
| bool | Query_block::add_inner_fields_to_select_list (THD *thd, Lifted_expressions_map *lifted_exprs, Item *selected_field_or_ref, const uint first_non_hidden) |
Minion of decorrelate_derived_scalar_subquery_pre. More... | |
| bool | Query_block::add_inner_func_calls_to_select_list (THD *thd, Lifted_expressions_map *lifted_exprs) |
| bool | Query_block::decorrelate_derived_scalar_subquery_pre (THD *thd, Table_ref *derived, Item *lifted_where, Lifted_expressions_map *lifted_where_expressions, bool *added_card_check, size_t *added_window_card_checks) |
| We have a correlated scalar subquery, so we must do several things: More... | |
| bool | Query_block::decorrelate_derived_scalar_subquery_post (THD *thd, Table_ref *derived, Lifted_expressions_map *lifted_exprs, bool added_card_check, size_t added_window_card_checks) |
See explanation in companion method decorrelate_derived_scalar_subquery_pre. More... | |
| void | Query_block::replace_referenced_item (Item *const old_item, Item *const new_item) |
| Replace item in select list and preserve its reference count. More... | |
| bool | Query_block::transform_subquery_to_derived (THD *thd, Table_ref **out_tl, Query_expression *subs_query_expression, Item_subselect *subq, bool use_inner_join, bool reject_multiple_rows, Item *join_condition, Item *lifted_where_cond) |
| Converts a subquery to a derived table and inserts it into the FROM clause of the owning query block. More... | |
| bool | Query_block::supported_correlated_scalar_subquery (THD *thd, Item::Css_info *subquery, Item **lifted_where) |
| Called when the scalar subquery is correlated. More... | |
| bool | Query_block::transform_scalar_subqueries_to_join_with_derived (THD *thd) |
| Transform eligible scalar subqueries in the SELECT list, WHERE condition, HAVING condition or JOIN conditions of a query block[*] to an equivalent derived table of a LEFT OUTER join, e.g. More... | |
| bool | Query_block::lift_fulltext_from_having_to_select_list (THD *thd) |
| Copies all non-aggregated calls to the full-text search MATCH function from the HAVING clause to the SELECT list (as hidden items), so that we can materialize their result and not only their input. More... | |
Variables | |
| static const enum_walk | walk_options |
| bool Query_block::add_ftfunc_list | ( | List< Item_func_match > * | ftfuncs | ) |
Add full-text function elements from a list into this query block.
Add a list of full-text function elements into a query block.
| ftfuncs | List of full-text function elements to add. |
Add item to the hidden part of select list.
| item | item to add |
| item | the item to add |
|
private |
Run through the inner expressions and add them to the block's GROUP BY if not already present.
|
private |
Minion of decorrelate_derived_scalar_subquery_pre.
Run through the inner fields and add them to the derived table's SELECT list if not already present (only one can be present, since it's a scalar subquery), and make a note of where in the derived table's field list they are positioned: we need that information in Query_block::decorrelate_derived_scalar_subquery_post
| thd | session context |
| lifted_exprs | the structure containing de-correlation information |
| selected_field_or_ref | the selected field item (actually a field or a ref to a field) if that's what present in the select list, or else a nullptr. |
| first_non_hidden | the index of the first non-hidden field in fields |
|
private |
|
static |
Given an expression, create an ORDER expression for that expression and add it to a window's ORDER BY list in preparation for synthesizing a window function, cf.
setup_counts_over_partitions
| bool Query_block::allocate_grouping_sets | ( | THD * | thd | ) |
Initializes the grouping set if the query block includes GROUP BY modifiers.
| bool Query_block::apply_local_transforms | ( | THD * | thd, |
| bool | prune | ||
| ) |
Does permanent transformations which are local to a query block (which do not merge it to another block).
Apply local transformations, such as join nest simplification.
'Local' means that each transformation happens on one single query block. Also perform partition pruning, which is most effective after transformations have been done. This function also does condition pushdown to derived tables after all the local transformations are applied although condition pushdown is strictly not a local transform.
| thd | thread handler |
| prune | if true, then prune partitions based on const conditions |
Since this is called after flattening of query blocks, call this function while traversing the query block hierarchy top-down.
Helper function to make names for columns of a derived table replacing a scalar or table subquery.
Fields from the query block containing the scalar subquery are moved to the new derived table. We give them synthetic unique names here.
| thd | current session context |
| item | the item we want to name |
| field_no | the field number |
|
static |
|
private |
Build semijoin condition for th query block.
Build equality conditions using outer expressions and inner expressions.
If the equality condition is not constant, add it to the semi-join condition. Otherwise, evaluate it and remove the constant expressions from the outer/inner expressions list if the result is true. If the result is false, remove all the expressions in outer/inner expression list and attach an always false condition to semijoin condition.
| thd | Thread context | |
| nested_join | Join nest | |
| subq_query_block | Query block for the subquery | |
| outer_tables_map | Map of tables from original outer query block | |
| [in,out] | sj_cond | Semi-join condition to be constructed Contains non-equalities on input. |
| [out] | simple_const | true if the returned semi-join condition is a simple true or false predicate, false otherwise. |
|
static |
Builds the list of SJ outer/inner expressions.
| thd | Connection handle | |
| [out] | sj_outer_exprs | Will add outer expressions here |
| [out] | sj_inner_exprs | Will add inner expressions here |
| subq_pred | Item for the subquery | |
| subq_query_block | Single query block for the subquery |
|
inlinestatic |
|
private |
Runs checks mandated by ONLY_FULL_GROUP_BY.
| thd | THD pointer |
| bool Query_block::check_view_privileges | ( | THD * | thd, |
| Access_bitmask | want_privilege_first, | ||
| Access_bitmask | want_privilege_next | ||
| ) |
Check privileges for views that are merged into query block.
Check privileges for the view tables merged into a query block.
| thd | Thread context. |
| want_privilege_first | Privileges requested for the first leaf. |
| want_privilege_next | Privileges requested for the remaining leaves. |
|
private |
Remove semijoin condition for this query block.
Remove SJ outer/inner expressions.
| nested_join | join nest |
|
static |
A minion of transform_grouped_to_derived.
Collect a unique list of aggregate functions used in the transformed query block, which will need to be replaced with fields from the derived table containing the grouping during transform_grouped_to_derived.
| [in] | select | the query block |
| [in,out] | aggregates | the accumulator which will contain the aggregates |
|
private |
Convert a subquery predicate of this query block into a Table_ref semi-join nest.
| thd | Thread handle |
| subq_pred | Subquery predicate to be converted. This is either an IN, =ANY or EXISTS predicate, possibly negated. |
The following transformations are performed:
are transformed into:
Notice that the inner-cond may contain correlated and non-correlated expressions. Further transformations will analyze and break up such expressions.
are transformed into:
are transformed into:
where AJ means "antijoin" and is like a LEFT JOIN; and is-null-cond is false if the row of it1 is "found" and "not_null_compl" (i.e. matches inner-cond).
are transformed into:
|
static |
|
private |
Decorrelate the WHERE clause or a join condition of a subquery used in an IN or EXISTS predicate.
Correlated predicates are removed from the condition and added to the supplied semi-join nest. The predicate must be either a simple (in)equality, or an AND condition that contains one or more simple equalities, in order for decorrelation to be possible.
| sj_decor | Object for recording the decorrelated expressions |
| join_nest | Nest containing join condition to be decorrelated =NULL: decorrelate the WHERE condition |
There are two types of IN and EXISTS queries with non-deterministic functions that may be meaningful (the EXISTS queries below are correlated equivalents of the respective IN queries):
A SELECT * FROM t1 WHERE RAND() IN (SELECT t2.x FROM t2)
B SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE RAND() = t2.x);
Pick a set of random rows that matches against a fixed set (the subquery).
The intuitive interpretation of the IN subquery is that the random function is evaluated per row of the outer query block, whereas in the EXISTS subquery, it should be evaluated per row of the inner query block, and the subquery is evaluated once per row of the outer query block.
A SELECT * FROM t1 WHERE t1.x IN (SELECT RAND() FROM t2)
B SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE RAND() = t1.x);
This is another way of picking a random row, but now the non-determinism occurs in the inner query block.
The user will expect that only query 1A has the evaluation of non-deterministic functions being performed in the outer query block. Using decorrelation for query 1B would change the apparent semantics of the query.
The purpose of decorrelation is to be able to use more execution strategies. Without decorrelation, EXISTS is limited to FirstMatch and DupsWeedout strategies. Decorrelation enables LooseScan and Materialization. We can rule out LooseScan for case 2B, since it requires an indexed column from the subquery, and for case 1B, since it requires that the outer table is partitioned according to the distinct values of the index, and random values do not fulfill that partitioning requirement.
The only strategy left is Materialization. With decorrelation, 1B would be evaluated like 1A, which is not the intuitive way. 2B would also be implemented like 2A, meaning that evaluation of non-deterministic functions would move to the materialization function.
Thus, the intuitive interpretation is to avoid materialization for subqueries with non-deterministic components in the inner query block, and hence such predicates will not be decorrelated.
|
private |
See explanation in companion method decorrelate_derived_scalar_subquery_pre.
|
private |
We have a correlated scalar subquery, so we must do several things:
Add the relevant non-correlated fields or function calls "NCF"[1] to the select list so they can be referenced in the JOIN condition which now holds the earlier WHERE AND predicates that were correlated.
[1] We handle simple column references (fields) separately from general expressions ("function calls"), hence the unusual terminology: by function calls here we mean expressions that are not simple column references, i.e. Item_func's. So, NCF are the inner fields/function calls operand of an equality predicate that contains an outer field reference in the other operand. These were identified in supported_correlated_scalar_subquery, and passed in as lifted_where.
added_window_card_checks lifted_fields.This logic is partially done before setting up the materialized derived table, in the present method (_pre), and partly after setting up the materialized derived table, cf. the companion method (_post).
| thd | session context | |
| derived | the derived table being created in the transform | |
| lifted_where | the WHERE condition we move out to the JOIN cond | |
| [out] | lifted_exprs | mapping of where inner fields and function calls end up in the derived table's fields. |
| [out] | added_card_check | set to true if we are adding a cardinality check |
| [out] | added_window_card_checks | set to # of window functions added to SELECT if the subquery is initially grouped and we need COUNT(*) OVER (...) to be checked |
|
static |
Try to decorrelate an (in)equality node.
The node can be decorrelated if one argument contains only outer references and the other argument contains references only to local tables. Both arguments should be deterministic. const-for-execution values are accepted in both arguments.
| sj_decor | Object for recording the decorrelated expressions | |
| func | The query function node | |
| [out] | was_correlated | = true if comparison is correlated and the the expressions are added to sj_nest. |
|
private |
Delete unused columns from merged tables.
This function is called recursively for each join nest and/or table in the query block. For each merged table that it finds, each column that contains a subquery and is not marked as used is removed and the translation item is set to NULL.
| tables | List of tables and join nests |
|
private |
Empty the ORDER list.
Delete corresponding elements from fields and base_ref_items too. If ORDER list contain any subqueries, delete them from the query block list.
| sl | Query block that possible subquery blocks in the ORDER BY clause are attached to (may be different from "this" when query block has been merged into an outer query block). |
|
static |
Extracts the top level correlated condition in an OR condition.
For ex: (((t1.a = t2.b ) and (t1.c =10)) OR ((t1.a = t2.b) and (t1.d =10))) is the same as (t1.a = t2.b) and ((t1.c = 10) or (t1.d = 10))
So we extract the (t1.a = t2.b) as the correlated condition and leave ((t1.c = 10) or (t1.d = 10)) in the original condition that is passed as the argument.
The caller of the function has to send an OR condition. Only the top level correlated condition is extracted. Caller could repeatedly call this function to extract the inner level correlated conditions as well.
| thd | session context | |
| [in,out] | cond | Original condition that is looked into, to extract the correlated condition. |
| [out] | correlated_cond | correlated condition that is extracted |
| bool Query_block::field_list_is_empty | ( | ) | const |
Finds a group expression matching the given item, or nullptr if none.
When there are multiple candidates, ones that match in name are given priority (such that “a AS c GROUP BY a,b,c” resolves to c, not a); if there is still a tie, the leftmost is given priority.
| item | The item to search for. | |
| [out] | rollup_level | If not nullptr, will be set to the group expression's index (0-based). |
| bool find_order_in_list | ( | THD * | thd, |
| Ref_item_array | ref_item_array, | ||
| Table_ref * | tables, | ||
| ORDER * | order, | ||
| mem_root_deque< Item * > * | fields, | ||
| bool | is_group_field, | ||
| bool | is_window_order | ||
| ) |
Resolve an ORDER BY or GROUP BY column reference.
Given a column reference (represented by 'order') from a GROUP BY or ORDER BY clause, find the actual column it represents. If the column being resolved is from the GROUP BY clause, the procedure searches the SELECT list 'fields' and the columns in the FROM list 'tables'. If 'order' is from the ORDER BY clause, only the SELECT list is being searched.
If 'order' is resolved to an Item, then order->item is set to the found Item. If there is no item for the found column (that is, it was resolved into a table field), order->item is 'fixed' and is added to fields and ref_item_array.
ref_item_array and fields are updated.
| [in] | thd | Pointer to current thread structure |
| [in,out] | ref_item_array | All select, group and order by fields |
| [in] | tables | List of tables to search in (usually FROM clause) |
| [in] | order | Column reference to be resolved |
| [in,out] | fields | List of fields to search in (usually SELECT list; hidden items are ignored) |
| [in] | is_group_field | True if order is a GROUP field, false if ORDER by field |
| [in] | is_window_order | True if order is a Window function's PARTITION BY or ORDER BY field |
| false | if OK |
| true | if error occurred |
| void Query_expression::fix_after_pullout | ( | Query_block * | parent_query_block, |
| Query_block * | removed_query_block | ||
| ) |
Fix used tables information for a subquery after query transformations.
This is for transformations where the subquery remains a subquery - it is not merged, it merely moves up by effect of a transformation on a containing query block. Most actions here involve re-resolving information for conditions and items belonging to the subquery. If the subquery contains an outer reference into removed_query_block or parent_query_block, the relevant information is updated by Item_ident::fix_after_pullout().
|
private |
|
static |
Update table reference information for conditions and expressions due to query blocks having been merged in from derived tables/views and due to semi-join transformation.
This is needed for two reasons:
The function needs to recursively walk down into join nests, in order to cover all conditions and expressions.
For a semi-join, tables from the subquery are added last in the query block. This means that conditions and expressions from the outer query block are unaffected. But all conditions inside the semi-join nest, including join conditions, must have their table numbers changed.
For a derived table/view, tables from the subquery are merged into the outer query, and this function is called for every derived table that is merged in. This algorithm only works when derived tables are merged in the order of their original table numbers.
A hypothetical example with a triple self-join over a mergeable view:
CREATE VIEW v AS SELECT t1.a, t2.b FROM t1 JOIN t2 USING (a); SELECT v1.a, v1.b, v2.b, v3.b FROM v AS v1 JOIN v AS v2 ON ... JOIN v AS v3 ON ...;
The analysis starts with three tables v1, v2 and v3 having numbers 0, 1, 2. First we merge in v1, so we get (t1, t2, v2, v3). v2 and v3 are shifted up. Tables from v1 need to have their table numbers altered (actually they do not since both old and new numbers are 0 and 1, but this is a special case). v2 and v3 are not merged in yet, so we delay pullout on them until they are merged. Conditions and expressions from the outer query are not resolved yet, so regular resolving will take of them later. Then we merge in v2, so we get (t1, t2, t1, t2, v3). The tables from this view gets numbers 2 and 3, and v3 gets number 4. Because v2 had a higher number than the tables from v1, the join nest representing v1 is unaffected. And v3 is still not merged, so the only join nest we need to consider is v2. Finally we merge in v3, and then we have tables (t1, t2, t1, t2, t1, t2), with numbers 0 through 5. Again, since v3 has higher number than any of the already merged in views, only this join nest needs the pullout.
| parent_query_block | Query block being merged into |
| removed_query_block | Query block that is removed (subquery) |
| tr | Table object this pullout is applied to |
| table_adjust | Number of positions that a derived table nest is adjusted, used to fix up semi-join related fields. Tables are adjusted from position N to N+table_adjust |
| lateral_deps | Lateral dependencies of the unit owning removed_query_block |
| bool Query_block::flatten_subqueries | ( | THD * | thd | ) |
Convert semi-join subquery predicates into semi-join join nests.
Convert candidate subquery predicates into semi-join join nests. This transformation is performed once in query lifetime and is irreversible.
We start with a query block that has a semi-join subquery predicate:
and convert the predicate and subquery into a semi-join nest:
that is, in order to do the conversion, we need to
Create the "SEMI JOIN (it1 .. itN)" part and add it into the parent query block's FROM structure. Add "AND subq_where AND oe=ie" into parent query block's WHERE (or ON if the subquery predicate was in an ON condition) Remove the subquery predicate from the parent query block's WHERE
A join may have at most MAX_TABLES tables. This may prevent us from flattening all subqueries when the total number of tables in parent and child selects exceeds MAX_TABLES. In addition, one slot is reserved per semi-join nest, in case the subquery needs to be materialized in a temporary table. We deal with this problem by flattening children's subqueries first and then using a heuristic rule to determine each subquery predicate's priority, which is calculated in this order:
|
static |
Checks if there are any calls to the MATCH function that take a ROLLUP column as argument in the SELECT list, GROUP BY clause, HAVING clause or ORDER BY clause.
Such calls should be rejected, since MATCH only works on base columns.
| int Query_block::group_list_size | ( | ) | const |
| bool is_correlated_predicate_eligible | ( | Item * | cor_pred | ) |
Called to check if the provided correlated predicate is eligible for transformation.
To be eligible, it must have one correlated operand and one non-orrelated ("inner") operand. The correlated operand cannot contain a non-correlated field, but must contain at least one correlated field. It can be a deterministic function over (other) deterministic functions and correlated field(s), recursively, or a simple correlated field.
| cor_pred | correlated predicate that needs to be examined |
| bool Query_block::is_row_count_valid_for_semi_join | ( | ) |
Check if the offset and limit are valid for a semijoin.
A semijoin can be used only if OFFSET is 0 and select LIMIT is not 0.
| false | if OFFSET and LIMIT does not permit a semijoin, |
| true | otherwise. |
|
static |
WL#15540 check that predicate operand item conforms to our requirements.
Any constant arguments are ignored (supported also). The function call must be (recursively) deterministic and cannot contain subqueries.
|
private |
Copies all non-aggregated calls to the full-text search MATCH function from the HAVING clause to the SELECT list (as hidden items), so that we can materialize their result and not only their input.
This is needed when the result will be accessed after aggregation, as the result from MATCH cannot be recalculated from its input alone. It also needs the underlying scan to be positioned on the correct row. Storing the value before aggregation removes the need for evaluating MATCH again after materialization.
Make list of leaf tables of join table tree.
| list | pointer to pointer on list first element Must be set to NULL before first (recursive) call |
| tables | table list |
|
private |
Marks occurrences of group by fields in a function's arguments as nullable, so that we do not optimize them away before we get to add the rollup wrappers.
| void Query_block::merge_contexts | ( | Query_block * | inner | ) |
Merge name resolution context objects of a subquery into its parent.
Merge name resolution context objects belonging to an inner subquery to parent query block.
Update all context objects to have this base query block. Used when a subquery's query block is merged into its parent.
| inner | Subquery for which context objects are to be merged. |
Merge derived table into query block.
Merge a derived table or view into a query block.
If some constraint prevents the derived table from being merged then do nothing, which means the table will be prepared for materialization later.
After this call, check is_merged() to see if the table was really merged.
| thd | Thread handler |
| derived_table | Derived table which is to be merged. |
A view is updatable if any underlying table is updatable. A view is insertable-into if all underlying tables are insertable. A view is not updatable nor insertable if it contains an outer join
|
private |
Push the generated derived table to the correct location inside a join nest.
It will be nested in a new nest along with the outer table to the join which owns the search condition in which we found the scalar subquery. For example:
select t1.i,
t2.i
from t1
left outer join
t2 on
(t1.i < (select max(t2.i) from t2));
in transformed to
select t1.i,
t2.i
from t1
left join
(select max(t2.i) AS `max(t2.i)` from t2) derived_1_0 [*]
on(true)
left join
t2
on((t1.i < derived_1_0.`max(t2.i)`))
[*]: the derived table is nested in here, just ahead of the inner table t2 to which the join condition is attached.
In the original join nest before transformation may look like this (the join order list is reversed relative to the logical order):
(nest_join) t2 LEFT OUTER ON .. = .. (inner table) t1 (outer table)
After the transformation we have this nest structure:
(nest_join) t2 LEFT OUTER ON .. = .. (nest_last_join) derived_1_0 LEFT OUTER ON true t1
The method will recursively inspect and rebuild join nests as needed since the join with the condition may be deeply nested.
| thd | the session context |
| join_cond | the join condition which identifies the join we want to nest into |
| nested_join_list | the join list at the current nesting level |
| derived_table | the table we want to nest |
| size_t Query_block::num_visible_fields | ( | ) | const |
| bool Query_block::populate_grouping_sets | ( | THD * | thd | ) |
Populates the grouping sets if the query block includes non-primitive grouping.
Populate the grouping set bitvector if the query block has non-primitive grouping.
If the non-primitive grouping is ROLLUP or CUBE, the grouping sets have to be computed. The representation of the grouping set is done using a bitfield in the ORDER object. case ROLLUP : Say the query has GROUP BY ROLLUP (a,b) then the grouping sets will be (a,b) (a) () where () represents single group aggregate without any grouping. Here there are 3 grouping sets ranging from 0 to 2 and 0 is the single group aggregate. The bitfield associated with GROUP BY element 'a' will be 3 (i,e. 2+1) The bitfield associated with Group by element 'b' will be 2 as it is part of only set number 2. case CUBE: Say the query has GROUP BY CUBE (a,b) then the grouping sets will be (a,b) (a) (b) (). The number of grouping sets will be (2^n) where n is the number of elements in the GROUP BY list. The bitfield associated with Group by element 'a' will be 6 (i.e. 4+2). The bitfield associated with Group by element 'b' will be 1.
| bool Query_block::prepare | ( | THD * | thd, |
| mem_root_deque< Item * > * | insert_field_list | ||
| ) |
Prepare query block for optimization.
Resolve table and column information. Resolve all expressions (item trees), ie WHERE clause, join conditions, GROUP BY clause, HAVING clause, ORDER BY clause, LIMIT clause. Prepare all subqueries recursively as part of resolving the expressions. Apply permanent transformations to the abstract syntax tree, such as semi-join transformation, derived table transformation, elimination of constant values and redundant clauses (e.g ORDER BY, GROUP BY).
| thd | thread handler |
| insert_field_list | List of fields when used in INSERT, otherwise NULL |
|
private |
Prepare a table value constructor query block for optimization.
In the case of a table value constructor Query_block, we return the result of this function from Query_block::prepare, instead of doing the standard prepare routine.
For a table value constructor block, most preparation of a standard Query_block becomes irrelevant (in particular INTO, FROM, WHERE, GROUP, HAVING and WINDOW). We therefore substitute the standard resolving routine with this one, which is simply responsible for resolving the expressions contained in VALUES, as well as the query result.
| thd | thread handler |
| void propagate_nullability | ( | mem_root_deque< Table_ref * > * | tables, |
| bool | nullable | ||
| ) |
Propagate nullability into inner tables of outer join operation.
| tables | List of tables and join nests, start at m_table_nest |
| nullable | true: Set all underlying tables as nullable |
| void Query_block::propagate_unique_test_exclusion | ( | ) |
Propagate exclusion from table uniqueness test into subqueries.
Propagate exclusion from unique table check into all subqueries belonging to this query block.
This function can be applied to all subqueries of a materialized derived table or view.
| bool Query_block::push_conditions_to_derived_tables | ( | THD * | thd | ) |
Pushes parts of the WHERE condition of this query block to materialized derived tables.
|
static |
A minion of transform_scalar_subqueries_to_join_with_derived.
Determine if the query expression is directly contained in the query block, i.e. it is a subquery.
| select | the query block |
| slu | the query expression |
|
private |
Record join nest info in the select block.
After simplification of inner join, outer join and semi-join structures:
This function is called recursively for each join nest and/or table in the query block.
| tables | List of tables and join nests |
|
static |
Refreshes the comparators after ROLLUP resolving.
This is needed because ROLLUP resolving happens after the comparators have been set up. In ROLLUP resolving, it may turn out that something initially believed to be constant, is not constant after all (e.g., group items that may be NULL in some cases). So we call set_cmp_func() to make Arg_comparator adjust/remove its caches accordingly.
|
private |
Re-map table numbers for all tables in a query block.
| thd | Thread handler |
|
static |
Remember if this transform was performed.
It it was done by a secondary engine, it may need to be rolled back before falling back on primary engine execution.
| bool Query_block::remove_aggregates | ( | THD * | thd, |
| Query_block * | select | ||
| ) |
A minion of transform_grouped_to_derived.
"Remove" any non-window aggregate functions from fields unconditionally. If such an aggregate is found, the query block should have a HAVING clause. This is asserted in debug mode. We "remove" them by replacing them with an Item_int, which should have no adverse effects. This avoids creating trouble for Query_block::add_hidden_item which would otherwise need to keep track of removed items.
| thd | session context |
| select | the query block whose aggregates are being moved into a derived table |
| void Query_block::remove_hidden_items | ( | ) |
Remove hidden items from select list.
|
private |
For a table subquery predicate (IN/ANY/ALL/EXISTS/etc): since it does not support LIMIT the following clauses are redundant:
ORDER BY DISTINCT GROUP BY if there are no aggregate functions and no HAVING clause
For a scalar subquery without LIMIT: ORDER BY is redundant, as the number of rows to order must be 1.
This removal is permanent. Thus, it only makes sense to call this function for regular queries and on first execution of SP/PS
| thd | thread handler |
|
static |
A minion of transform_grouped_to_derived.
Replace occurrences of the aggregate function identified in info.m_target with the the field info.m_replacement in the expressions contained in list. Note that since this is part of a permanent transformation, we use the extra m_permanent_transform flag in the THD
| info | a tuple containing {aggregate, replacement field} |
| was_hidden | true if the aggregate was originally hidden |
| list | the list of expressions |
| ref_item_array | to be kept in sync with any changes in 'list' |
|
static |
Fields in lifted join condition.
We added referenced inner fields to select list, now replace occurences of such fields in the join condition with derived.<Item_field-n>. Since we have now set up materialization for the derived table, we now know the Field to use for a new Item_field.
|
static |
Function calls in lifted join condition.
Replace occurences for inner function calls in lifted predicates with the corresponding field in the derived table. This must be performed before we replace inner field (below) to avoid replacing function arguments of the derived table, eg. if we have ABS(t2.a) = outer_table.a in a subquery, we will lift this predicate. But we have also added ABS(t2.a) to the GROUP BY list in the derived table and its argument t2.a should not be replaced, rather we replace the entire function call with derived_table.abs(t2.a) in the lifted join condition.
|
private |
Minion of transform_grouped_to_derived.
Do a replacement in expr using Item::transform as specified in info using transformer.
Replace item in select list and preserve its reference count.
| old_item | Item to be replaced. |
| new_item | Item to replace the old item. |
If old item is present in base_ref_items, make sure it is replaced there.
Also make sure that reference count for old item is preserved in new item.
|
static |
Destructively replaces a sub-condition inside a condition tree.
The parse tree is also altered.
| thd | thread handler | |
| tree | Must be the handle to the top level condition. This is needed when the top-level condition changes. | |
| old_cond | The condition to be replaced. | |
| new_cond | The condition to be substituted. | |
| do_fix_fields | If true, Item::fix_fields(THD*, Item**) is called for the new condition. | |
| [out] | found_ptr | Pointer to boolean; used only in recursive sub-calls; top call must not specify this argument. Function deposits there if it found the searched Item or not. |
| true | If there was an error. |
| false | If successful. |
|
private |
A minion of transform_scalar_subqueries_to_join_with_derived.
A transform creates a field representing the value of the derived table and adds it as a hidden field to the select list. Next, it replaces the subquery in the item tree with this field. If we replace in a HAVING condition, we build an Item_ref, cf. PTI_simple_ident_ident::itemize which also creates a Item_ref for a field reference in HAVING, because we may need to access the field in a tmp table.
| thd | The session context |
| subquery | The scalar subquery |
| tr | The table reference for the derived table |
| expr | The expression we are replacing (in) |
|
private |
Go through a list of tables and join nests, recursively, and repoint its query_block pointer.
| join_list | List of tables and join nests |
| void Query_block::reset_nj_counters | ( | mem_root_deque< Table_ref * > * | join_list = nullptr | ) |
Set NESTED_JOIN::counter=0 in all nested joins in passed list.
| join_list | Pass NULL. Non-NULL is reserved for recursive inner calls, then it is a list of nested joins to process, and may also contain base tables which will be ignored. |
| bool Query_block::resolve_limits | ( | THD * | thd | ) |
Resolve OFFSET and LIMIT clauses.
Resolve OFFSET and LIMIT clauses for a query block.
| thd | Thread handler |
OFFSET and LIMIT clauses may be attached to query blocks that make up a query expression. OFFSET and LIMIT clauses that apply to a whole query expression are attached to the fake_query_block, hence we can use this interface to resolve them as well.
OFFSET and LIMIT may be unsigned integer literal values or parameters. If parameters, ensure that the type is unsigned integer.
| bool Query_block::resolve_placeholder_tables | ( | THD * | thd, |
| bool | apply_semijoin | ||
| ) |
Resolve derived table, view, table function information for a query block.
Resolve derived table, view or table function references in query block.
| thd | Pointer to THD. |
| apply_semijoin | if true, apply semi-join transform when possible |
|
private |
Resolve items in SELECT list and ORDER BY list for rollup processing.
| thd | session context |
Resolve an item (and its tree) for rollup processing by replacing items matching grouped expressions with Item_rollup_group_items and updating properties (m_nullable, PROP_ROLLUP_FIELD).
Also check any GROUPING function for incorrect column.
| thd | session context |
| item | the item to be processed |
| bool Query_block::resolve_rollup_wfs | ( | THD * | thd | ) |
Replace group by field references inside window functions with references in the presence of ROLLUP.
| thd | session context |
|
private |
Resolve the rows of a table value constructor and aggregate the type of each column across rows.
| thd | thread handler |
| bool Query_block::setup_conds | ( | THD * | thd | ) |
Resolve WHERE condition and join conditions.
| thd | thread handler |
|
private |
Add all COUNT(0) to SELECT list of the derived table to be used for cardinality checking of the transformed subquery.
Minion of decorrelate_derived_scalar_subquery_pre
a. Add COUNT(0) OVER (PARTITION BY group-by-list) b. Add COUNT(0) OVER (PARTITION BY inner-expr) for each inner-expression not already grouped on.
|
private |
Resolve and set up the GROUP BY list.
| thd | Thread handler |
|
private |
Resolve join conditions for a join nest.
| thd | thread handler |
| tables | List of tables with join conditions |
| in_update | True if used in update command that may have CHECK OPTION |
| bool setup_order | ( | THD * | thd, |
| Ref_item_array | ref_item_array, | ||
| Table_ref * | tables, | ||
| mem_root_deque< Item * > * | fields, | ||
| ORDER * | order | ||
| ) |
Resolve and setup list of expressions in ORDER BY clause.
Change order to point at item in select list. If item isn't a number and doesn't exists in the select list, add it to the the field list.
| thd | Current session. |
| ref_item_array | The Ref_item_array for this query block. |
| tables | From clause of the query. |
| fields | All columns, including hidden ones. |
| order | The query block's order clause. |
|
private |
Do final setup of ORDER BY clause, after the query block is fully resolved.
Check that ORDER BY clause is not redundant. Split any aggregate functions.
| thd | Thread handler |
Resolve and prepare information about tables for one query block.
Set up table leaves in the query block based on list of tables.
| thd | Thread handler |
| tables | List of tables to handle |
| select_insert | It is SELECT ... INSERT command |
This function has to be called for all tables that are used by items, as otherwise table->map is not set and all Item_field will be regarded as const items.
|
private |
Expand all '*' in list of expressions with the matching column references.
Function should not be called with no wild cards in select list
| thd | thread handler |
|
static |
Try to replace a const condition with a simple constant.
A true condition is replaced with an empty item pointer if remove_cond is true. Else it is replaced with the constant TRUE. A false condition is replaced with the constant FALSE.
| thd | Thread handler | |
| [in,out] | cond | Address of condition, may be substituted with a literal |
| remove_cond | If true removes a "true" condition. Else replaces it with a constant TRUE. | |
| ret_cond_value | Store the result of the evaluated const condition |
|
private |
Simplify joins replacing outer joins by inner joins whenever it's possible.
The function, during a retrieval of join_list, eliminates those outer joins that can be converted into inner join, possibly nested. It also moves the join conditions for the converted outer joins and from inner joins to conds. The function also calculates some attributes for nested joins:
The first two attributes are used to test whether an outer join can be substituted by an inner join. The third attribute represents the relation 'to be dependent on' for tables. If table t2 is dependent on table t1, then in any evaluated execution plan table access to table t2 must precede access to table t2. This relation is used also to check whether the query contains invalid cross-references. The fourth attribute is an auxiliary one and is used to calculate dep_tables. As the attribute dep_tables qualifies possibles orders of tables in the execution plan, the dependencies required by the straight join modifiers are reflected in this attribute as well. The function also removes all parentheses that can be removed from the join expression without changing its meaning.
E.g. in the query:
the predicate t2.b < 5 rejects nulls. The query is converted first to:
then to the equivalent form:
Similarly the following query:
is converted to:
One conversion might trigger another:
The function removes all unnecessary parentheses from the expression produced by the conversions. E.g.
finally is converted to:
It also will remove parentheses from the following queries:
The benefit of this simplification procedure is that it might return a query for which the optimizer can evaluate execution plans with more join orders. With a left join operation the optimizer does not consider any plan where one of the inner tables is before some of outer tables.
IMPLEMENTATION The function is implemented by a recursive procedure. On the recursive ascent all attributes are calculated, all outer joins that can be converted are replaced and then all unnecessary parentheses are removed. As join list contains join tables in the reverse order sequential elimination of outer joins does not require extra recursive calls.
SEMI-JOIN NOTES Remove all semi-joins that have are within another semi-join (i.e. have an "ancestor" semi-join nest)
EXAMPLES Here is an example of a join query with invalid cross references:
| thd | thread handler | |
| join_list | list representation of the join to be converted | |
| top | true <=> cond is the where condition | |
| in_sj | true <=> processing semi-join nest's children | |
| [in,out] | cond | In: condition to which the join condition for converted outer joins is to be added; Out: new condition |
| changelog | Don't specify this parameter, it is reserved for recursive calls inside this function |
| Item * Query_block::single_visible_field | ( | ) | const |
| bool Item_in_subselect::subquery_allows_materialization | ( | THD * | thd, |
| Query_block * | query_block, | ||
| const Query_block * | outer | ||
| ) |
Check if the subquery predicate can be executed via materialization.
| thd | THD |
| query_block | Query_block of the subquery |
| outer | Parent Query_block (outer to subquery) |
|
private |
Called when the scalar subquery is correlated.
If the type of correlation is not supported, return false and leave *lifted_where unassigned. If it is supported, *lifted_where contains a set of correlated predicates. Currently, we can only de-correlate the WHERE clause: if the clause is not a top level AND, we lift out the entire predicate to the JOIN clause. If it is a top level AND, we lift out only those AND operand predicates which are correlated, leaving un-correlated operand predicates in the subquery's WHERE clause, as lifting all out would be too ineffective, potentially creating large cartesian products in the subquery.
| thd | session context | |
| subquery | the subquery under consideration | |
| [out] | lifted_where | set of predicates lifted out of WHERE |
|
private |
Create a new Table_ref object for this query block, for either: 1) a derived table which will replace the subquery, or 2) an extra derived table for handling grouping, if necessary, cf.
transform_grouped_to_derived.
The derived table is added to the list of used tables for the query block ("outer").
| thd | the session context |
| unit | the query expression for subquery (case 1), or a new query expression for (case 2) |
| join_cond | != nullptr: we are synthesizing a derived table for a subquery within this join condition = nullptr: synthesizing a derived table for a subquery where the subquery is not contained in a join condition |
| left_outer | true for case (1), false for (2) |
| use_inner_join | for case (1): if true/false use INNER/LEFT JOIN |
|
private |
Minion of transform_scalar_subqueries_to_join_with_derived.
Moves implicit grouping down into a derived table to prepare for transform_scalar_subqueries_to_join_with_derived.
Example:
SELECT ( SELECT COUNT(*)
FROM t1 ) AS tot,
IFNULL(MAX(t2.b), 0) + 6 AS mx # MAX(t2.b), FROM t2 and
FROM t2 # WHERE go to derived_1_3
WHERE ANY_VALUE(t2.b) = 10;
is transformed to
SELECT ( SELECT COUNT(*)
FROM t1 ) AS tot,
IFNULL(derived_1_3.`MAX(t2.b)`, 0) + 6 AS mx
FROM ( SELECT MAX(t2.b) AS `MAX(t2.b)`
FROM t2
WHERE (ANY_VALUE(t2.b) = 10)) derived_1_3Create a new query expression object and query block object to represent the contents of a derived table ("new_derived" in the code below, "derived_1_3" in the example above), with a select list which only contains the aggregate functions lifted out of the transformed query block ("MAX(t2.b) AS `MAX(t2.b)`" above) and any fields referenced.
The transformed query block retains the original select list except aggregates and fields are replaced by fields ("derived_1_3.`MAX(t2.b)`") from the new subquery, but it loses its FROM list, replaced by the new derived table ("derived_1_3" above) and its WHERE and HAVING clauses which all go to the derived table's query block.
Any DISTINCT, WINDOW clauses and LIMITs stay in place at the transformed query block.
| thd | session context | |
| [out] | break_off | set to true of transformation could not be performed |
|
private |
Transform eligible scalar subqueries in the SELECT list, WHERE condition, HAVING condition or JOIN conditions of a query block[*] to an equivalent derived table of a LEFT OUTER join, e.g.
as shown in this uncorrelated subquery:
[*] a.k.a "transformed query block" throughout this method and its minions.
SELECT * FROM t1
WHERE t1.a > (SELECT COUNT(a) AS cnt FROM t2); ->
SELECT t1.* FROM t1 LEFT OUTER JOIN
(SELECT COUNT(a) AS cnt FROM t2) AS derived
ON TRUE WHERE t1.a > derived.cnt;
Grouping in the transformed query block may necessitate the grouping to be moved down to another derived table, cf. transform_grouped_to_derived.
Limitations:
| [in,out] | thd | the session context |
|
private |
Converts a subquery to a derived table and inserts it into the FROM clause of the owning query block.
| thd | Connection handle | |
| [out] | out_tl | The created derived table will be stored in this. |
| subs_query_expression | Unit for the subquery | |
| subq | Item for the subquery | |
| use_inner_join | Insert with INNER JOIN, or with LEFT JOIN | |
| reject_multiple_rows | For scalar subqueries where we need run-time cardinality check: true, else false | |
| join_condition | See join_cond in synthesize_derived() | |
| lifted_where_cond | The subquery's where condition, moving to JOIN cond of JOIN with the derived table |
|
private |
Replace a table subquery ([NOT] {IN, EXISTS}) with a join to a derived table.
The principle of this transformation is: FROM [tables] WHERE ... AND/OR oe IN (SELECT ie FROM it) ... becomes FROM (tables) LEFT JOIN (SELECT DISTINCT ie FROM it) AS derived ON oe = derived.ie WHERE ... AND/OR derived.ie IS NOT NULL ... If the subquery predicate is top-level in WHERE, and not negated, we use JOIN instead of LEFT JOIN, and use TRUE instead of IS NOT NULL. If the subquery predicate is negated, we use IS NULL instead of IS NOT NULL. If the subquery predicate is without aggregation(etc), we decorrelate any equality from it, and, if negated, we also decorrelate '<>,<,<=,>,>='; thus we handle EXISTS too. If the subquery cannot be decorrelated, the derived table could be made LATERAL, but as a certain secondary engine doesn't support that we just return an error.
| thd | Connection handle |
| subq | Item for subquery |
|
static |
A minion of transform_grouped_to_derived.
This updates the name resolution contexts in expr to that of new_derived permanently.
| expr | the expression to be updated |
| new_derived | the query block of the new derived table which now holds the expression after it has been moved down. |
| void Query_block::update_used_tables | ( | ) |
Update used tables information for all local expressions.
|
static |
Update used tables information for a JOIN expression.
| bool validate_gc_assignment | ( | const mem_root_deque< Item * > & | fields, |
| const mem_root_deque< Item * > & | values, | ||
| TABLE * | table | ||
| ) |
validate_gc_assignment Check whether the other values except DEFAULT are assigned for generated columns.
| fields | Item_fields list to be filled |
| values | values to fill with |
| table | table to be checked |
| false | OK |
| true | Error occurred |
|
static |
| bool walk_join_list | ( | mem_root_deque< Table_ref * > & | list, |
| std::function< bool(Table_ref *)> | action | ||
| ) |
| bool WalkAndReplace | ( | THD * | thd, |
| Item * | item, | ||
| const function< ReplaceResult(Item *item, Item *parent, unsigned argument_idx)> & | get_new_item | ||
| ) |
|
static |
Helper function for WalkAndReplace() which replaces the Item referenced by "child_ref" if "get_new_item" returns a replacement, or visits the children of "child_ref" otherwise.
|
static |
Checks whether an item matches a grouped expression, creates an Item_rollup_group_item around it and replaces the reference to it with that item.
|
static |
1.9.2