A patch for this task that can be considered as a 'proof of concept' was sent for evaluation to Andrii.

This patch introduced two-phase optimization for some queries.
It also checked that the derived tables with outer references can be easily executed within the current architecture.

Alvin,
Bar planned to add the patch to bb-10.2-compatibility on August 4.
The comments of the patch mention mdev-13369.
I personally work with bb-10.2-ext and I did not push it there.

Cost aware implementation of MDEV-13369.

Design.

1. Every materialized derived is checked for being potentially splittable:
a) the specification must contain only 1 select
b) if this select is with GROUP BY then check that there is an index whose major components cover major components of the group by list (in any order)
c) if this select is with window functions then check that all window functions use only 1 partition;
there is an index whose major components cover some components of the partition.

If all the conditions above are satisfied then the materialized derived is considered as potentially splittable and a structure of the type SPLITTABLE_DERIVED_INFO is attached
to the TABLE_LIST created for this derived.
All info about the indexes that potentially can be used for splitting is stored in this structure.

2. The join specifying a potentially splittable derived is subject to two-phase optimization. The first phase ends when the cheapest join order is found.

When the optimization of the main query asks for the optimization of the materialized derived tables only the first phase of the optimization is performed for each derived that is optimized in two phases.
The check whether a join is subject to two phase optimization is performed when the first phase finishes.

3. Let v be a potentially derived used in the main query.
When the function choose plan() called for the main query tries to expand the current partial join order with v1 using an index access that employs the index idx (v.a,v.b,…) the
following is checked. It is checked that some prefix of this index consists only of the fields that are selected into v directly from the fields of an underlying table (vt.a, vt.b,…) and vt.a,vt.b...are the major components of the indexes remembered in the SPLITTABLE_DERIVED_INFO structure created for v. If so, v can be effectively split.

4. Let assume that v can be accessed via a split technique after tables t1, t2 in a partial order for the main query. Let assume that the split is performed using the equality conditions v.a=f(t1.a), v(b)=g(t2.b). When in the partial join order t1,t2,v the table v is accessed the values of f(t1.a) and g(t2.b) are fixed (considered as constants). The equalities vt.a=f(t1.a), vt.b=g(t2.b) must be added to the WHERE condition of v to get the partition of v that is determined by the conditions v.a=f(t1.a), v(b)=g(t2.b). Now the best plan is built for v with extended WHERE that actually can produce any partition p from v. At this point the cost of this plan C(p) must be evaluated . Let C(v) be the cost of producing rows of v. Let in(v) be the expected row count for the partial join t1,t2. If in(v)*C(p) < C(v) then accessing v with a splitting technique is more beneficial with this partial order.

5. Adding conditions to existing one is not so cheap. Besides it's clear that when looking for the best plan for the main query the optimizer has not only to add conditions that can be used, but also has to remove the ones that are not valid for the current partial join order. For example when the partial order t1,v is evaluated the condition vt(b)=g(t2.b) must be removed from consideration.
With a certain technique we can add all usable equalities at once and then invalidate usage of those of them that are not valid in the current partial join order. The technique works as follows. When conditions the vt.a=f(t1.a), vt.b=g(t2.b) are built from the conditions v.a=f(t1.a), v(b)=g(t2.b) each of items f(t1.a), g(t2.b) is cloned and each clone is provided with pointer to the parent. A clone is resolved against v, while the parent is resolved against the main query. Now if the partial join order is t1,v then vt.a=f(t1.a) can be used, while vt.b=g(t2.b) cannot because t2 is not in t1,v.

6. The array of extended KEYUSEs is built only once when the first usage of the split technique is evaluated.
The new variant of best_access_path() must take into account that some KEYUSEs can be invalidated for some partial join orders.

A full cost-base solution was pushed into the 10.3 tree