WL#461: Derived tables dependent of outer select.

WHAT
====

If we have a top query, which contains a subquery, and a derived table is
contained in the subquery, this WL wants to allow the definition of the derived
table to reference the tables used by the top query.
This has been allowed by the SQL standard for long (since SQL92?), but not by MySQL.
It is not the same thing as LATERAL derived tables (WL#8652): in WL#8652,
references are to the tables in the same FROM clause as the derived table; in
this WL, they are to tables outer to that clause.

EXAMPLE
=======

Here "derived table" means "derived table or CTE (recursive or not)".

Let's define "outer reference" with an example: below, t1.b is an outer reference:

SELECT * FROM t1
WHERE t1.e>(SELECT avg(tt.a) + t1.b
                FROM (SELECT sum(t3.a) FROM 
                      t3 GROUP BY t3.d) tt
            WHERE tt.a>10))

This query is valid in MySQL.
But if the subquery contains a derived table in its FROM clause, and the outer
reference t1.b is in that derived table, MySQL rejects it:

SELECT * FROM t1
WHERE t1.e>(SELECT avg(tt.a)
                FROM (SELECT sum(t3.a) FROM 
                      t3 WHERE t3.b=t1.b GROUP BY t3.d) tt
                                     ^^
            WHERE tt.a>10))
->error

The SQL standard allows this query.

References:
http://stackoverflow.com/questions/21011270/mysql-reference-outer-table-alias-in-subquery-error?rq=1
http://stackoverflow.com/questions/28889171/mysql-derived-table-outer-reference

WARNING
=======

For simplification purposes, this WL will be designed, described, implemented
and documented as a piece of WL#8652 (LATERAL). Because the necessary code
changes are very linked together.

F-1 a derived table or CTE (recursive or not) should allow outer references in
its definition.

F-2 if a derived table contains outer references, it is re-evaluated
so that its content is always up-to-date with the referenced outer columns. For
example, in the query in the HLD, the content of 'tt' has to change every time a
new row of t1 is processed by the top query. Re-evaluation happens when the
subquery containing the derived table is evaluated, and only once per evaluation
of the said subquery.

F-3 Likewise, if a CTE contains outer references, it is re-evaluated so that its
content is always up-to-date with the referenced outer columns. Re-evaluation
happens when the subquery containing the CTE's _definition_ is evaluated, and
only once per evaluation of the said subquery. For example:
select (with cte as (select t1.a) select (select * from cte) ...) from t1;
"cte" is evaluated once per evaluation of the outermost subquery; it doesn't
matter where it's referenced (here, in the innermost subquery).

F-4 This feature should not affect views.

NF-1 depending on its definition, a derived table / CTE with outer references
may be merged in outer query or materialized into a tmp table.

NF-2 if a derived table or CTE does not contain outer references, the query
should execute as fast as in MySQL 8.0.

see WL#8652 for all sections missing here.