12.6 Derived Table Execution

Derived tables is the internal name for subqueries in the
FROM clause.

The processing of derived tables is now included in the table
opening process (open_and_lock_tables() call).
Routine of execution derived tables and substituting temporary
table instead of it (mysql_handle_derived())
will be called just after opening and locking all real tables used
in query (including tables used in derived table query).

If lex->derived_tables flag is present, all
SELECT_LEX structures will be scanned (there is
a list of all SELECT_LEX structures in reverse
order named lex->all_selects_list, the first
SELECT in the query will be last in this list).

There is a pointer for the derived table,
SELECT_LEX_UNIT stored in the
TABLE_LIST structure
(TABLE_LIST::derived). For any table that has
this pointer, mysql_derived() will be called.

mysql_derived():

Creates union_result for writing results in
this table (with empty table entry, same as for
UNIONs).

call unit->prepare() to get list of
types of result fields (it work correctly for single
SELECT, and do not create temporary table
for UNION processing in this case).

Creates a temporary table for storing results.

Assign this temporary table to union_result
object.

Calls mysql_select or
mysql_union to execute the query.

If it is not explain, then cleanup JOIN
structures after execution (EXPLAIN needs
data of optimization phase and cleanup them after whole query
processing).

Stores pointer to this temporary table in
TABLE_LIST structure, then this table will
be used by outer query.

Links this temporary table in
thd->derived_tables for removing after
query execution. This table will be closed in
close_thread_tables if its second parameter
(bool skip_derived) is true.