MySQL 8.0.29
Source Code Documentation
Query Resolver

Classes

class  Semijoin_decorrelation
 Context object used by semijoin equality decorrelation code. More...
 
struct  Lifted_fields_map
 

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 Itemcreate_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_LIST * > *tables)
 Update used tables information for a JOIN expression. More...
 
static TABLE_LIST ** make_leaf_tables (TABLE_LIST **list, TABLE_LIST *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_LIST *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_LIST * > &list, std::function< bool(TABLE_LIST *)> 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_LIST * > *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_LIST *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_LIST *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...
 
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_LIST * > &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...
 
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, ulong want_privilege_first, ulong want_privilege_next)
 Check privileges for views that are merged into query block. More...
 
bool Query_block::setup_tables (THD *thd, TABLE_LIST *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::resolve_subquery (THD *thd)
 Resolve predicate involving subquery. 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_LIST * > *tables, bool in_update)
 Resolve join conditions for a join nest. More...
 
void Query_block::reset_nj_counters (mem_root_deque< TABLE_LIST * > *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_LIST * > *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_LIST * > *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)
 Build semijoin condition for th query block. More...
 
bool Query_block::decorrelate_condition (Semijoin_decorrelation &sj_decor, TABLE_LIST *join_nest)
 Decorrelate the WHERE clause or a join condition of a subquery used in an IN or EXISTS predicate. 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_LIST semi-join nest. More...
 
bool Query_block::merge_derived (THD *thd, TABLE_LIST *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...
 
bool Query_block::is_in_select_list (Item *i)
 
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_LIST * > 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...
 
void Query_block::remove_redundant_subquery_clauses (THD *thd, int hidden_group_field_count)
 For a table subquery predicate (IN/ANY/ALL/EXISTS/etc): since it does not support LIMIT the following clauses are redundant: More...
 
void 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...
 
ORDERQuery_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_rollup_item (Item *item)
 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...
 
ItemQuery_block::single_visible_field () const
 
size_t Query_block::num_visible_fields () const
 
bool Query_block::field_list_is_empty () const
 
void Query_block::remove_hidden_fields ()
 
ItemQuery_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_LIST * > *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_LISTQuery_block::synthesize_derived (THD *thd, Query_expression *unit, Item *join_cond, bool left_outer, bool use_inner_join)
 Create a new TABLE_LIST 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...
 
void Query_block::remove_derived (THD *thd, TABLE_LIST *tl)
 Remove a derived table we added previously as part of transform_scalar_subqueries_to_join_with_derived. More...
 
bool Query_block::remove_aggregates (THD *thd, Query_block *select)
 A 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_LIST *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_LIST * > *join_list, TABLE_LIST *new_derived_table)
 Push the generated derived table to the correct location inside a join nest. More...
 
bool Query_block::decorrelate_derived_scalar_subquery_pre (THD *thd, TABLE_LIST *derived, Item *lifted_where, Lifted_fields_map *lifted_where_fields, bool *added_card_check)
 We have a correlated scalar subquery, so we must do several things: More...
 
bool Query_block::decorrelate_derived_scalar_subquery_post (THD *thd, TABLE_LIST *derived, Lifted_fields_map *lifted_where_fields, bool added_card_check)
 See explanation in companion method decorrelate_derived_scalar_subquery_pre. More...
 
bool Query_block::transform_subquery_to_derived (THD *thd, TABLE_LIST **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...
 

Detailed Description

Function Documentation

◆ add_ftfunc_list()

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.

Parameters
ftfuncsList of full-text function elements to add.
Returns
false if success, true if error

◆ add_hidden_item()

Item ** Query_block::add_hidden_item ( Item item)

Add item to the hidden part of select list.

Parameters
itemitem to add
Returns
Pointer to reference of the added item
Parameters
itemitem to add
Returns
Pointer to reference to the added item

◆ apply_local_transforms()

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.

Parameters
thdthread handler
pruneif true, then prune partitions based on const conditions
Returns
false if success, true if error

Since this is called after flattening of query blocks, call this function while traversing the query block hierarchy top-down.

◆ baptize_item()

static bool baptize_item ( THD thd,
Item item,
int *  field_no 
)
static

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.

Parameters
thdcurrent session context
itemthe item we want to name
field_nothe field number
Returns
true on error

◆ build_sj_cond()

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 
)
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.

Parameters
thdThread context
nested_joinJoin nest
subq_query_blockQuery block for the subquery
outer_tables_mapMap of tables from original outer query block
[out]sj_condSemi-join condition to be constructed
Returns
false if success, true if error

◆ build_sj_exprs()

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 
)
static

Builds the list of SJ outer/inner expressions.

Parameters
thdConnection handle
[out]sj_outer_exprsWill add outer expressions here
[out]sj_inner_exprsWill add inner expressions here
subq_predItem for the subquery
subq_query_blockSingle query block for the subquery
Returns
true if error

◆ can_decorrelate_operator()

static bool can_decorrelate_operator ( Item_func func,
bool  only_eq 
)
inlinestatic

◆ check_only_full_group_by()

bool Query_block::check_only_full_group_by ( THD thd)
private

Runs checks mandated by ONLY_FULL_GROUP_BY.

Parameters
thdTHD pointer
Returns
true if ONLY_FULL_GROUP_BY is violated.

◆ check_view_privileges()

bool Query_block::check_view_privileges ( THD thd,
ulong  want_privilege_first,
ulong  want_privilege_next 
)

Check privileges for views that are merged into query block.

Check privileges for the view tables merged into a query block.

Parameters
thdThread context.
want_privilege_firstPrivileges requested for the first leaf.
want_privilege_nextPrivileges requested for the remaining leaves.
Note
Beware that it can't properly check privileges in cases when table being changed is not the first table in the list of leaf tables (for example, for multi-UPDATE).
The inner loop is slightly inefficient. A view will have its privileges checked once for every base table that it refers to.
Returns
false if success, true if error.

◆ clear_sj_expressions()

void Query_block::clear_sj_expressions ( NESTED_JOIN nested_join)
private

Remove semijoin condition for this query block.

Remove SJ outer/inner expressions.

Parameters
nested_joinjoin nest

◆ collect_aggregates()

static bool collect_aggregates ( Query_block select,
Item_sum::Collect_grouped_aggregate_info aggregates 
)
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.

Parameters
[in]selectthe query block
[in,out]aggregatesthe accumulator which wll contain the aggregates
Returns
true on error

◆ convert_subquery_to_semijoin()

bool Query_block::convert_subquery_to_semijoin ( THD thd,
Item_exists_subselect subq_pred 
)
private

Convert a subquery predicate of this query block into a TABLE_LIST semi-join nest.

Parameters
thdThread handle
subq_predSubquery predicate to be converted. This is either an IN, =ANY or EXISTS predicate, possibly negated.
Returns
false if success, true if error

The following transformations are performed:

  1. IN/=ANY predicates on the form:
SELECT ...
FROM ot1 ... otN
WHERE (oe1, ... oeM) IN (SELECT ie1, ..., ieM
FROM it1 ... itK
[WHERE inner-cond])
[AND outer-cond]
[GROUP BY ...] [HAVING ...] [ORDER BY ...]
const std::string SELECT("SELECT")
Name of the static privileges.
@ WHERE
Definition: sql_yacc.h:679
@ FROM
Definition: sql_yacc.h:242
@ HAVING
Definition: sql_yacc.h:262
@ BY
Definition: sql_yacc.h:93
Definition: table.h:279

are transformed into:

SELECT ...
FROM (ot1 ... otN) SJ (it1 ... itK)
ON (oe1, ... oeM) = (ie1, ..., ieM)
[AND inner-cond]
[WHERE outer-cond]
[GROUP BY ...] [HAVING ...] [ORDER BY ...]

Notice that the inner-cond may contain correlated and non-correlated expressions. Further transformations will analyze and break up such expressions.

  1. EXISTS predicates on the form:
SELECT ...
FROM ot1 ... otN
WHERE EXISTS (SELECT expressions
FROM it1 ... itK
[WHERE inner-cond])
[AND outer-cond]
[GROUP BY ...] [HAVING ...] [ORDER BY ...]
@ EXISTS
Definition: sql_yacc.h:216

are transformed into:

SELECT ...
FROM (ot1 ... otN) SJ (it1 ... itK)
[ON inner-cond]
[WHERE outer-cond]
[GROUP BY ...] [HAVING ...] [ORDER BY ...]
  1. Negated EXISTS predicates on the form:
SELECT ...
FROM ot1 ... otN
WHERE NOT EXISTS (SELECT expressions
FROM it1 ... itK
[WHERE inner-cond])
[AND outer-cond]
[GROUP BY ...] [HAVING ...] [ORDER BY ...]

are transformed into:

SELECT ...
FROM (ot1 ... otN) AJ (it1 ... itK)
[ON inner-cond]
[WHERE outer-cond AND is-null-cond(it1)]
[GROUP BY ...] [HAVING ...] [ORDER BY ...]

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).

  1. Negated IN predicates on the form:
SELECT ...
FROM ot1 ... otN
WHERE (oe1, ... oeM) NOT IN (SELECT ie1, ..., ieM
FROM it1 ... itK
[WHERE inner-cond])
[AND outer-cond]
[GROUP BY ...] [HAVING ...] [ORDER BY ...]

are transformed into:

SELECT ...
FROM (ot1 ... otN) AJ (it1 ... itK)
ON (oe1, ... oeM) = (ie1, ..., ieM)
[AND inner-cond]
[WHERE outer-cond]
[GROUP BY ...] [HAVING ...] [ORDER BY ...]
  1. The cases 1/2 (respectively 3/4) above also apply when the predicate is decorated with IS TRUE or IS NOT FALSE (respectively IS NOT TRUE or IS FALSE).

◆ create_rollup_switcher()

Item * create_rollup_switcher ( THD thd,
Query_block query_block,
Item_sum item,
int  send_group_parts 
)
static

◆ decorrelate_condition()

bool Query_block::decorrelate_condition ( Semijoin_decorrelation sj_decor,
TABLE_LIST join_nest 
)
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.

Parameters
sj_decorObject for recording the decorrelated expressions
join_nestNest containing join condition to be decorrelated =NULL: decorrelate the WHERE condition
Returns
false if success, true if error

Decorrelation for subqueries containing non-deterministic components:

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):

  1. Non-deterministic function as substitute for expression from outer query block:

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.

  1. Non-deterministic function as substitute for expression from inner 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.

◆ decorrelate_derived_scalar_subquery_post()

bool Query_block::decorrelate_derived_scalar_subquery_post ( THD thd,
TABLE_LIST derived,
Lifted_fields_map lifted_where_fields,
bool  added_card_check 
)
private

See explanation in companion method decorrelate_derived_scalar_subquery_pre.

◆ decorrelate_derived_scalar_subquery_pre()

bool Query_block::decorrelate_derived_scalar_subquery_pre ( THD thd,
TABLE_LIST derived,
Item lifted_where,
Lifted_fields_map lifted_fields,
bool *  added_card_check 
)
private

We have a correlated scalar subquery, so we must do several things:

  1. Add the relevant non-correlated fields "NCF"(*) to the select list so they can be referenced in the JOIN condition which now holds the earlier WHERE AND predicates that were correlated. (*) i.e. the inner fields involved in a predicate that contains an outer field reference. These were identified in supported_correlated_scalar_subquery, and passed in as 'lifted_where'.
  2. Add a COUNT(*) to select list so it can be referenced from the transformed query's WHERE clause for cardinality check, if needed, i.e. when there is no aggregate function in the subquery's single(*) select expression. (*) single because we have a scalar subquery. Add this to NCF. If it does contain an aggregate function, there will be only one row per group iff the NCF are part of any GROUP BY list, and we add them to it, so that property holds.
  3. Add grouping on NCF to the subquery. If already grouped, add the NCF at end of grouping list. Note that this might result in a grouped query that might fail the functional dependency checks. So we wrap any non-grouped field in the select list in Item_func_any_value. We can safely add the Item_func_any_value because subqueries with cardinalities greater than one will be rejected anyway.
  4. Remember the set of NCF so we can create derived.field and derived.count(field) (NCF'), after setting up the materialized derived table, cf. 'lifted_fields'.
  5. Update the correlated fields in the JOIN condition to no longer be outer references, and the NCF' to refer to the derived table's fields, NCF.

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").

Parameters
thdsession context
derivedthe derived table being created in the transform
lifted_wherethe WHERE condition we move out to the JOIN cond
[out]lifted_fieldsmapping of where inner fields end up in the derived table's fields.
[out]added_card_checkset to true if we are adding a cardinality check

◆ decorrelate_equality()

static bool decorrelate_equality ( Semijoin_decorrelation sj_decor,
Item_func func,
bool *  was_correlated 
)
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.

Note
that a predicate like '(a,b) IN ((c,d))' is changed to two equalities only during optimization, so at the present stage it isn't decorrelate-able.
Parameters
sj_decorObject for recording the decorrelated expressions
funcThe query function node
[out]was_correlated= true if comparison is correlated and the the expressions are added to sj_nest.
Returns
false if success, true if error

◆ delete_unused_merged_columns()

void Query_block::delete_unused_merged_columns ( mem_root_deque< TABLE_LIST * > *  tables)
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.

Parameters
tablesList of tables and join nests

◆ empty_order_list()

void Query_block::empty_order_list ( Query_block sl)
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.

Parameters
slQuery 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).

◆ extract_correlated_condition()

static bool extract_correlated_condition ( THD thd,
Item **  cond,
Item **  correlated_cond 
)
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.

Parameters
thdsession context
[in,out]condOriginal condition that is looked into, to extract the correlated condition.
[out]correlated_condcorrelated condition that is extracted
Returns
false when a correlated condition is successfully extracted. true when no correlated condition could be extracted.

◆ field_list_is_empty()

bool Query_block::field_list_is_empty ( ) const

◆ find_in_group_list()

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.

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.

Parameters
itemThe item to search for.
[out]rollup_levelIf not nullptr, will be set to the group expression's index (0-based).

◆ find_order_in_list()

bool find_order_in_list ( THD thd,
Ref_item_array  ref_item_array,
TABLE_LIST 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.

Parameters
[in]thdPointer to current thread structure
[in,out]ref_item_arrayAll select, group and order by fields
[in]tablesList of tables to search in (usually FROM clause)
[in]orderColumn reference to be resolved
[in,out]fieldsList of fields to search in (usually SELECT list; hidden items are ignored)
[in]is_group_fieldTrue if order is a GROUP field, false if ORDER by field
[in]is_window_orderTrue if order is a Window function's PARTITION BY or ORDER BY field
Return values
falseif OK
trueif error occurred

◆ fix_after_pullout() [1/2]

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().

◆ fix_after_pullout() [2/2]

void Query_block::fix_after_pullout ( Query_block parent_query_block,
Query_block removed_query_block 
)
private
See also
Query_expression::fix_after_pullout

◆ fix_tables_after_pullout()

static void fix_tables_after_pullout ( Query_block parent_query_block,
Query_block removed_query_block,
TABLE_LIST tr,
uint  table_adjust,
table_map  lateral_deps 
)
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:

  1. Since table numbers are changed, we need to update used_tables information for all conditions and expressions that are possibly touched.
  2. For semi-join, some column references are changed from outer references to local references.

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.

Parameters
parent_query_blockQuery block being merged into
removed_query_blockQuery block that is removed (subquery)
trTable object this pullout is applied to
table_adjustNumber 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_depsLateral dependencies of the unit owning removed_query_block

◆ flatten_subqueries()

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.

Conversion of one subquery predicate

We start with a query block that has a semi-join subquery predicate:

SELECT ...
FROM ot, ...
WHERE oe IN (SELECT ie FROM it1 ... itN WHERE subq_where) AND outer_where

and convert the predicate and subquery into a semi-join nest:

SELECT ...
FROM ot SEMI JOIN (it1 ... itN), ...
WHERE outer_where AND subq_where AND oe=ie
Definition: sql_optimizer.h:125

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

Considerations when converting many predicates

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:

  1. Prefer dependent subqueries over non-dependent ones
  2. Prefer subqueries with many tables over those with fewer tables
  3. Prefer early subqueries over later ones (to make sort deterministic)
Returns
false if success, true if error

◆ fulltext_uses_rollup_column()

static bool fulltext_uses_rollup_column ( const Query_block query_block)
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.

◆ group_list_size()

int Query_block::group_list_size ( ) const

◆ is_correlated_predicate_eligible()

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 non-correlated operand and one correlated operand, and the non-correlated operand must be a simple column reference (Else we need to group on expressions in the derived table - not supported currently).

Parameters
cor_predcorrelated predicate that needs to be examined
Returns
true if predicate is eligible for transformation.

◆ is_in_select_list()

bool Query_block::is_in_select_list ( Item i)

◆ is_row_count_valid_for_semi_join()

bool Query_block::is_row_count_valid_for_semi_join ( )
private

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.

Return values
falseif OFFSET and LIMIT does not permit a semijoin,
trueotherwise.

◆ make_leaf_tables()

static TABLE_LIST ** make_leaf_tables ( TABLE_LIST **  list,
TABLE_LIST tables 
)
static

Make list of leaf tables of join table tree.

Parameters
listpointer to pointer on list first element Must be set to NULL before first (recursive) call
tablestable list
Returns
pointer on pointer to next_leaf of last element

◆ mark_item_as_maybe_null_if_rollup_item()

void Query_block::mark_item_as_maybe_null_if_rollup_item ( Item item)
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.

◆ merge_contexts()

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.

Parameters
innerSubquery for which context objects are to be merged.

◆ merge_derived()

bool Query_block::merge_derived ( THD thd,
TABLE_LIST derived_table 
)

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.

Parameters
thdThread handler
derived_tableDerived table which is to be merged.
Returns
false if successful, true if error

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

See also
mysql_register_view()

◆ nest_derived()

bool Query_block::nest_derived ( THD thd,
Item join_cond,
mem_root_deque< TABLE_LIST * > *  nested_join_list,
TABLE_LIST derived_table 
)
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.

Parameters
thdthe session context
join_condthe join condition which identifies the join we want to nest into
nested_join_listthe join list at the current nesting level
derived_tablethe table we want to nest
Returns
true on error

◆ num_visible_fields()

size_t Query_block::num_visible_fields ( ) const

◆ prepare()

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).

Parameters
thdthread handler
insert_field_listList of fields when used in INSERT, otherwise NULL
Returns
false if success, true if error
Note
on privilege checking for SELECT query that possibly contains view or derived table references:
  • When this function is called, it is assumed that the precheck() function has been called. precheck() ensures that the user has some SELECT privileges to the tables involved in the query. When resolving views it has also been established that the user has some privileges for them. To prepare a view for privilege checking, it is also needed to call check_view_privileges() after views have been merged into the query. This is not necessary for unnamed derived tables since it has already been established that we have SELECT privileges for the underlying tables by the precheck functions. (precheck() checks a query without resolved views, ie. before tables are opened, so underlying tables of views are not yet available).
  • When a query block is resolved, always ensure that the user has SELECT privileges to the columns referenced in the WHERE clause, the join conditions, the GROUP BY clause, the HAVING clause and the ORDER BY clause.
  • When resolving the outer-most query block, ensure that the user also has SELECT privileges to the columns in the selected expressions.
  • When setting up a derived table or view for materialization, ensure that the user has SELECT privileges to the columns in the selected expressions
  • Column privileges are normally checked by Item_field::fix_fields(). Exceptions are select list of derived tables/views which are checked in TABLE_LIST::setup_materialized_derived(), and natural/using join conditions that are checked in mark_common_columns().
  • As far as INSERT, UPDATE and DELETE statements have the same expressions as a SELECT statement, this note applies to those statements as well.

◆ prepare_values()

bool Query_block::prepare_values ( THD thd)
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.

Parameters
thdthread handler
Returns
false if success, true if error

◆ propagate_nullability()

void propagate_nullability ( mem_root_deque< TABLE_LIST * > *  tables,
bool  nullable 
)

Propagate nullability into inner tables of outer join operation.

Parameters
tablesList of tables and join nests, start at top_join_list
nullabletrue: Set all underlying tables as nullable

◆ propagate_unique_test_exclusion()

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.

◆ push_conditions_to_derived_tables()

bool Query_block::push_conditions_to_derived_tables ( THD thd)

Pushes parts of the WHERE condition of this query block to materialized derived tables.

◆ query_block_contains_subquery()

static bool query_block_contains_subquery ( Query_block select,
Query_expression slu 
)
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.

Parameters
selectthe query block
sluthe query expression
Returns
true if slu is directly contained in select, else false

◆ record_join_nest_info()

bool Query_block::record_join_nest_info ( mem_root_deque< TABLE_LIST * > *  tables)
private

Record join nest info in the select block.

After simplification of inner join, outer join and semi-join structures:

  • record the remaining semi-join structures in the enclosing query block.
  • record transformed join conditions in TABLE_LIST objects.

This function is called recursively for each join nest and/or table in the query block.

Parameters
tablesList of tables and join nests
Returns
False if successful, True if failure

◆ refresh_comparators_after_rollup()

static bool refresh_comparators_after_rollup ( Item item)
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.

◆ remap_tables()

void Query_block::remap_tables ( THD thd)
private

Re-map table numbers for all tables in a query block.

Parameters
thdThread handler
Note
This function needs to be called after setup_tables() has been called, and after a query block for a subquery has been merged into a parent quary block.

◆ remember_transform()

static void remember_transform ( THD thd,
Query_block select 
)
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.

◆ remove_aggregates()

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.

Parameters
thdsession context
selectthe query block whose aggregates are being moved into a derived table
Returns
true on error, else false

◆ remove_derived()

void Query_block::remove_derived ( THD thd,
TABLE_LIST tl 
)

Remove a derived table we added previously as part of transform_scalar_subqueries_to_join_with_derived.

This can happen when the transformed scalar subquery is part of a view that is not used in a query block referencing the view, e.g. if the view has a scalar subquery in the select list and this field is not referenced by the query invoking the view.

Parameters
thdSession state
tlThe derived table that should be removed

◆ remove_hidden_fields()

void Query_block::remove_hidden_fields ( )

◆ remove_hidden_items()

void Query_block::remove_hidden_items ( )

Remove hidden items from select list.

◆ remove_redundant_subquery_clauses()

void Query_block::remove_redundant_subquery_clauses ( THD thd,
int  hidden_group_field_count 
)
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

Parameters
thdthread handler
hidden_group_field_countNumber of hidden group fields added by setup_group().

◆ replace_aggregate_in_list()

static bool replace_aggregate_in_list ( Item::Aggregate_replacement info,
bool  was_hidden,
mem_root_deque< Item * > *  list,
Ref_item_array ref_item_array 
)
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

Parameters
infoa tuple containing {aggregate, replacement field}
was_hiddentrue if the aggregate was originally hidden
listthe list of expressions
ref_item_arrayto be kept in sync with any changes in 'list'
Returns
true on error (can not happen currently unless replacement field is empty)

◆ replace_subcondition()

static bool replace_subcondition ( THD thd,
Item **  tree,
Item old_cond,
Item new_cond,
bool  do_fix_fields,
bool *  found_ptr = nullptr 
)
static

Destructively replaces a sub-condition inside a condition tree.

The parse tree is also altered.

Parameters
thdthread handler
treeMust be the handle to the top level condition. This is needed when the top-level condition changes.
old_condThe condition to be replaced.
new_condThe condition to be substituted.
do_fix_fieldsIf true, Item::fix_fields(THD*, Item**) is called for the new condition.
[out]found_ptrPointer 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.
Returns
error status
Return values
trueIf there was an error.
falseIf successful.

◆ replace_subquery_in_expr()

bool Query_block::replace_subquery_in_expr ( THD thd,
Item::Css_info subquery,
TABLE_LIST tr,
Item **  expr 
)
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.

Parameters
thdThe session context
subqueryThe scalar subquery
trThe table reference for the derived table
exprThe expression we are replacing (in)

◆ repoint_contexts_of_join_nests()

void Query_block::repoint_contexts_of_join_nests ( mem_root_deque< TABLE_LIST * >  join_list)
private

Go through a list of tables and join nests, recursively, and repoint its query_block pointer.

Parameters
join_listList of tables and join nests

◆ reset_nj_counters()

void Query_block::reset_nj_counters ( mem_root_deque< TABLE_LIST * > *  join_list = nullptr)

Set NESTED_JOIN::counter=0 in all nested joins in passed list.

Parameters
join_listPass 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.

◆ resolve_limits()

bool Query_block::resolve_limits ( THD thd)

Resolve OFFSET and LIMIT clauses.

Resolve OFFSET and LIMIT clauses for a query block.

Parameters
thdThread handler
Returns
false if success, true if error

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.

◆ resolve_placeholder_tables()

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.

Parameters
thdPointer to THD.
apply_semijoinif true, apply semi-join transform when possible
Returns
false if success, true if error

◆ resolve_rollup()

bool Query_block::resolve_rollup ( THD thd)
private

Resolve items in SELECT list and ORDER BY list for rollup processing.

Parameters
thdsession context
Returns
false if success, true if error

◆ resolve_rollup_item()

Item * Query_block::resolve_rollup_item ( THD thd,
Item item 
)
private

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.

Parameters
thdsession context
itemthe item to be processed
Returns
the new item, or nullptr on error

◆ resolve_rollup_wfs()

bool Query_block::resolve_rollup_wfs ( THD thd)

Replace group by field references inside window functions with references in the presence of ROLLUP.

Parameters
thdsession context
Returns
false if success, true if error

◆ resolve_subquery()

bool Query_block::resolve_subquery ( THD thd)
private

Resolve predicate involving subquery.

Parameters
thdPointer to THD.
Return values
falseSuccess.
trueError.

Perform early unconditional subquery transformations:

  • Convert subquery predicate into semi-join, or
  • Mark the subquery for execution using materialization, or
  • Perform IN->EXISTS transformation, or
  • Perform more/less ALL/ANY -> MIN/MAX rewrite
  • Substitute trivial scalar-context subquery with its value
Note
In this case: IN (SELECT ... UNION SELECT ...), Query_block::prepare() is called for each of the two UNION members, and in those two calls, subq_predicate is the same, not sure this is desired (double work?).

◆ resolve_table_value_constructor_values()

bool Query_block::resolve_table_value_constructor_values ( THD thd)
private

Resolve the rows of a table value constructor and aggregate the type of each column across rows.

Parameters
thdthread handler
Returns
false if success, true if error

◆ setup_conds()

bool Query_block::setup_conds ( THD thd)

Resolve WHERE condition and join conditions.

Parameters
thdthread handler
Returns
false if success, true if error

◆ setup_group()

bool Query_block::setup_group ( THD thd)
private

Resolve and set up the GROUP BY list.

Parameters
thdThread handler
Returns
false if success, true if error

◆ setup_join_cond()

bool Query_block::setup_join_cond ( THD thd,
mem_root_deque< TABLE_LIST * > *  tables,
bool  in_update 
)
private

Resolve join conditions for a join nest.

Parameters
thdthread handler
tablesList of tables with join conditions
in_updateTrue if used in update command that may have CHECK OPTION
Returns
false if success, true if error

◆ setup_order()

bool setup_order ( THD thd,
Ref_item_array  ref_item_array,
TABLE_LIST 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.

Parameters
thdCurrent session.
ref_item_arrayThe Ref_item_array for this query block.
tablesFrom clause of the query.
fieldsAll columns, including hidden ones.
orderThe query block's order clause.
Returns
false if success, true if error.

◆ setup_order_final()

bool Query_block::setup_order_final ( THD thd)
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.

Parameters
thdThread handler
Returns
false if success, true if error

◆ setup_tables()

bool Query_block::setup_tables ( THD thd,
TABLE_LIST tables,
bool  select_insert 
)

Resolve and prepare information about tables for one query block.

Set up table leaves in the query block based on list of tables.

Parameters
thdThread handler
tablesList of tables to handle
select_insertIt is SELECT ... INSERT command
Note
Check also that the 'used keys' and 'ignored keys' exists and set up the table structure accordingly. Create a list of leaf tables.

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.

Returns
False on success, true on error

◆ setup_wild()

bool Query_block::setup_wild ( THD thd)
private

Expand all '*' in list of expressions with the matching column references.

Function should not be called with no wild cards in select list

Parameters
thdthread handler
Returns
false if OK, true if error

◆ simplify_const_condition()

static bool simplify_const_condition ( THD thd,
Item **  cond,
bool  remove_cond,
bool *  ret_cond_value 
)
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 witha a constant TRUE. A false condition is replaced with the constant FALSE.

Parameters
thdThread handler
[in,out]condAddress of condition, may be substituted with a literal
remove_condIf true removes a "true" condition. Else replaces it with a constant TRUE.
ret_cond_valueStore the result of the evaluated const condition
Returns
false if success, true if error

◆ simplify_joins()

bool Query_block::simplify_joins ( THD thd,
mem_root_deque< TABLE_LIST * > *  join_list,
bool  top,
bool  in_sj,
Item **  cond,
uint changelog = nullptr 
)
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:

  1. used_tables
  2. not_null_tables
  3. dep_tables.
  4. join_cond_dep_tables

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.

Note
An outer join can be replaced by an inner join if the where condition or the join condition for an embedding nested join contains a conjunctive predicate rejecting null values for some attribute of the inner tables.

E.g. in the query:

SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a WHERE t2.b < 5
@ LEFT
Definition: sql_yacc.h:316

the predicate t2.b < 5 rejects nulls. The query is converted first to:

SELECT * FROM t1 INNER JOIN t2 ON t2.a=t1.a WHERE t2.b < 5

then to the equivalent form:

SELECT * FROM t1, t2 ON t2.a=t1.a WHERE t2.b < 5 AND t2.a=t1.a

Similarly the following query:

SELECT * from t1 LEFT JOIN (t2, t3) ON t2.a=t1.a t3.b=t1.b
WHERE t2.c < 5

is converted to:

SELECT * FROM t1, (t2, t3) WHERE t2.c < 5 AND t2.a=t1.a t3.b=t1.b

One conversion might trigger another:

SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a
LEFT JOIN t3 ON t3.b=t2.b
WHERE t3 IS NOT NULL =>
SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a, t3
WHERE t3 IS NOT NULL AND t3.b=t2.b =>
SELECT * FROM t1, t2, t3
WHERE t3 IS NOT NULL AND t3.b=t2.b AND t2.a=t1.a
@ IS
Definition: sql_yacc.h:299
#define NULL
Definition: types.h:54

The function removes all unnecessary parentheses from the expression produced by the conversions. E.g.

SELECT * FROM t1, (t2, t3) WHERE t2.c < 5 AND t2.a=t1.a AND t3.b=t1.b

finally is converted to:

SELECT * FROM t1, t2, t3 WHERE t2.c < 5 AND t2.a=t1.a AND t3.b=t1.b

It also will remove parentheses from the following queries:

SELECT * from (t1 LEFT JOIN t2 ON t2.a=t1.a) LEFT JOIN t3 ON t3.b=t2.b
SELECT * from (t1, (t2,t3)) WHERE t1.a=t2.a AND t2.b=t3.b.

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:

SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t3.a LEFT JOIN t3 ON t3.b=t1.b
Parameters
thdthread handler
join_listlist representation of the join to be converted
toptrue <=> cond is the where condition
in_sjtrue <=> processing semi-join nest's children
[in,out]condIn: condition to which the join condition for converted outer joins is to be added; Out: new condition
changelogDon't specify this parameter, it is reserved for recursive calls inside this function
Returns
true for error, false for success

◆ single_visible_field()

Item * Query_block::single_visible_field ( ) const

◆ subquery_allows_materialization()

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.

Parameters
thdTHD
query_blockQuery_block of the subquery
outerParent Query_block (outer to subquery)
Returns
true if subquery allows materialization, false otherwise.

◆ supported_correlated_scalar_subquery()

bool Query_block::supported_correlated_scalar_subquery ( THD thd,
Item::Css_info subquery,
Item **  lifted_where 
)
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.

Parameters
thdsession context
[out]lifted_whereset of predicates lifted out of WHERE
Returns
true for error else false

◆ synthesize_derived()

TABLE_LIST * Query_block::synthesize_derived ( THD thd,
Query_expression unit,
Item join_cond,
bool  left_outer,
bool  use_inner_join 
)
private

Create a new TABLE_LIST 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").

Parameters
thdthe session context
unitthe 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_outertrue for case (1), false for (2)
use_inner_joinfor case (1): if true/false use INNER/LEFT JOIN
Returns
the derived table object, or nullptr on error.

◆ transform_grouped_to_derived()

bool Query_block::transform_grouped_to_derived ( THD thd,
bool *  break_off 
)
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
  FROM t2
  WHERE expr-2;

is transformed to ->

  SELECT derived_1_1.`COUNT(*)` AS tot,
         (IFNULL(derived_1_0.`MAX(t2.b)`,0) + 6) AS mx
  FROM (SELECT MAX(t2.b) AS `MAX(t2.b)`
        FROM t2
        WHERE expr-2) derived_1_0
       LEFT JOIN
       (SELECT COUNT(0) AS `COUNT(*)`
        FROM t1) derived_1_1
       ON TRUE;

Create a new query expression object and query block object to represent the contents of a derived table ("new_derived" in the code below, "derived1" in the example above), with a select list which only contains the aggregate functions lifted out of the transformed query block ("MAX(b) AS mx" above) and any fields referenced ("i" above).

The transformed query block retains the original select list except aggregates and fields are replaced by fields ("derived1.mx", "derived1.i" above) from the new subquery, but it loses its FROM list, replaced by the new derived table ("derived1" 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.

Parameters
thdsession context
[out]break_offset to true of transformation could not be performed
Returns
true on error

◆ transform_scalar_subqueries_to_join_with_derived()

bool Query_block::transform_scalar_subqueries_to_join_with_derived ( THD thd)
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:

  • only implicitly grouped subqueries (guaranteed to have cardinality one) are identified as scalar subqueries. _ Correlated subqueries are not handled
Parameters
[in,out]thdthe session context
Returns
true on error

◆ transform_subquery_to_derived()

bool Query_block::transform_subquery_to_derived ( THD thd,
TABLE_LIST **  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 
)
private

Converts a subquery to a derived table and inserts it into the FROM clause of the owning query block.

Parameters
thdConnection handle
[out]out_tlThe created derived table will be stored in this.
subs_query_expressionUnit for the subquery
subqItem for the subquery
use_inner_joinInsert with INNER JOIN, or with LEFT JOIN
reject_multiple_rowsFor scalar subqueries where we need run-time cardinality check: true, else false
join_conditionSee join_cond in synthesize_derived()
lifted_where_condThe subquery's where condition, moving to JOIN cond of JOIN with the derived table

◆ transform_table_subquery_to_join_with_derived()

bool Query_block::transform_table_subquery_to_join_with_derived ( THD thd,
Item_exists_subselect subq 
)
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.

Parameters
thdConnection handle
subqItem for subquery
Returns
true if error

◆ update_context_to_derived()

static bool update_context_to_derived ( Item expr,
Query_block new_derived 
)
static

A minion of transform_grouped_to_derived.

This updates the name resolution contexts in expr to that of new_derived permanently.

Parameters
exprthe expression to be updated
new_derivedthe query block of the new derived table which now holds the expression after it has been moved down.
Returns
true on error

◆ update_used_tables()

void Query_block::update_used_tables ( )

Update used tables information for all local expressions.

◆ update_used_tables_for_join()

static void update_used_tables_for_join ( mem_root_deque< TABLE_LIST * > *  tables)
static

Update used tables information for a JOIN expression.

◆ validate_gc_assignment()

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.

Parameters
fieldsItem_fields list to be filled
valuesvalues to fill with
tabletable to be checked
Returns
Operation status
Return values
falseOK
trueError occurred
Note
This function must be called after table->write_set has been filled.

◆ walk_join_conditions()

static bool walk_join_conditions ( mem_root_deque< TABLE_LIST * > &  list,
std::function< bool(Item **expr_p)>  action,
Item::Collect_scalar_subquery_info info 
)
static

◆ walk_join_list()

bool walk_join_list ( mem_root_deque< TABLE_LIST * > &  list,
std::function< bool(TABLE_LIST *)>  action 
)

◆ WalkAndReplace()

bool WalkAndReplace ( THD thd,
Item item,
const function< ReplaceResult(Item *item, Item *parent, unsigned argument_idx)> &  get_new_item 
)

◆ wrap_grouped_expressions_for_rollup()

static ReplaceResult wrap_grouped_expressions_for_rollup ( Query_block select,
Item item,
Item parent,
unsigned  argument_idx 
)
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.