Currently MariaDB does not allow to use lateral derived tables in queries though it
uses builds them internally for when employing split optimization.
Consider the following query
Here the reference t1.a in the WHERE condition of derived table t refers to the field a
of the table t1 used in the same FROM list as t.
SQL standard allows lateral derived tables and MySQL 8.0 supports them.
In MySQL the table can depend only on other tables in the same SELECT. If record in dependent table changes it invalidate table.
My idea is use Item_cache to store external references values on which current content of the table was generated.
Also maintain table map of dependence (including OUTER_REF_TABLE_BIT) and put check outer values/invalidate of the tables as we get and approved value executing the step in JOIN_TAB array processing (all dependent tables defined and got values in the records )
In case of only outer dependence do not forget "join->outer_ref_cond->val_int()" in do_select()