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.