WL#461: Derived tables dependent of outer select.
Affects: Server-8.0 — Status: Complete
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.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.