Optimization for equi-joins of derived tables with GROUP BY

Consider the following tables

create table t1 (a int);

insert into t1 values (5), (1), (2), (9), (7), (2), (7);

create table t2 (a int, b int, index idx(a));

insert into t2 values (7,10), (1,20), (2,23), (7,18), (1,30), (4,71), (3,15), (7,82);

and the query

select t1.a,t.max,t.min from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t on t1.a=t.a;

The following re-writing could be applied to this query:

=>

select t1.a,tl.max,tl.min from t1 left join (select a, max(t2.b) max, min(t2.b) min from t2 where  t1.a=t2.a) tl on 1=1;

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.