Consider the following tables
and the query
The following re-writing could be applied to this query:
The result of this re-writing is a query with so-called lateral derived table. This query requires refilling of the temporary table created the derived table for every new value t1.a. As the size of the derived table tl usually is much smaller than the size of the derived table t this transformation will be always beneficial. Especially this transformation will be beneficial when the join operation that joins derived table is used in a multi-way join query where only a few records of t1 are selected.
Unfortunately now we do not support lateral derived tables on SQL level. This task will allow to use them internally for this transformation.
The difference of this task from the task
MDEV-13225 that in this task the execution plan chosen for the original derived table will be just transformed into the one that uses a lateral derived table. As a result it might happen that the optimizer chooses not the best join order for the main query. The cost of using lateral derived table will be taken into account by the optimizer later.