Item_in_subselect::select_transformer is divided into two parts, for the scalar left part and the row left part.
Scalar IN Subquery
To rewrite a scalar IN subquery, the method used is Item_ in_ subselect :: single_ value_ transformer. Scalar IN subquery will be replaced with Item_in_optimizer.
Item_in_optimizer item is a special boolean function. On a value request (one of val, val_int, or val_str methods) it evaluates left expression of IN by storing its value in cache item (one of Item_cache* items), then it tests the cache to see whether it is NULL. If left expression (cache) is NULL, then Item_in_optimizer returns NULL, else it evaluates Item_in_subselect.
Item_ref is used to point to <left_expression cache>, because at the time of transformation we know only the address of variable where the cache pointer will be stored.
If the select statement has an ORDER BY clause, it will be wiped out, because there is no sense in ORDER BY without LIMIT here.
If IN subquery union, the condition of every select in the UNION will be changed individually.
If a condition needs to be added to the WHERE clause, it will be presented as (item OR item IS NULL) and Item_is_not_null_test(item) will be added to the HAVING clause. Item_is_not_null_test registers NULL value the way Item_ref_null_helper does it, and returns FALSE if argument is NULL. With the above trick, we will register NULL value of Item even for the case of index optimization of a WHERE clause (case 'a' in the following example).
The following are examples of IN transformations:
Now conditions (WHERE (a) or HAVING (b)) will be changed, depending on the select, in the following way:
If subquery contains a HAVING clause, SUM() function or GROUP BY (example 1), then the item list will be unchanged and Item_ ref_ null_ helper reference will be created on item list element. A condition will be added to the HAVING.
If the subquery does not contain HAVING, SUM() function or GROUP BY (example 2), then:
A single select without a FROM clause will be reduced to just <left_expression> = <item> without use of Item_in_optimizer.
Row IN Subquery
To rewrite a row IN subquery, the method used is Item_ in_ subselect :: row_ value_ transformer. It works in almost the same way as the scalar analog, but works with Item_cache_row for caching left expression and uses references for elements of Item_cache_row. To refer to item list it uses Item_ ref_ null_ helper(ref_ array+i).
Subquery with HAVING, SUM() function, or GROUP BY will transformed in the following way:
ROW(l1, l2, ... lN) IN (SELECT i1, i2, ... iN FROM t HAVING <having_expr>)will become:
SELECT without FROM will be transformed in this way, too.
It will be the same for other subqueries, except for the WHERE clause.
MySQL Related Interview Questions
|PHP Interview Questions||MySQL Interview Questions|
|PHP+MySQL Interview Questions||Drupal Interview Questions|
|MYSQL DBA Interview Questions||PHP5 Interview Questions|
|WordPress Interview Questions||Joomla Interview Questions|
|CakePHP Interview Questions||CodeIgniter Interview Questions|
|PHP7 Interview Questions|
A Guided Tour Of The Mysql Source Code
Important Algorithms And Structures
How Mysql Performs Different Selects
How Mysql Transforms Subqueries
Mysql Client/server Protocol
Prepared Statements And Stored Routines
Myisam Storage Engine
Innodb Storage Engine
Writing A Custom Storage Engine
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.