Additional ideas for optimizing subqueries

Originally from @jon-chuang on https://github.com/apache/arrow-datafusion/issues/2248#issuecomment-1120280963, copied to new ticket so it didn't get lost:

Some additional ideas for subquery optimizations:
- If rest of subquery is not correlated, push up correlated filter (in particular, correlated equality filter) into a left join on the filter.
- More here: https://www.alibabacloud.com/blog/query-optimization-technology-for-correlated-subqueries_597644

More generally, one can attempt this approach, which is a lot less messy than relying on countless rewriting heuristics (which is also highly error-prone):
- [Unnesting arbitrary queries](https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf)
- Implementation in DuckDB here: https://github.com/duckdb/duckdb/blob/bee8017bdcc5e652aee26ce8cfb260990cf6a369/src/planner/subquery/flatten_dependent_join.cpp#L72

Non è stato indicato alcun file o test di DataFusion. Inizia esaminando la gestione esistente delle subquery correlate, il paper collegato sull’unnesting e src/planner/subquery/flatten_dependent_join.cpp di DuckDB; definisci innanzitutto un’ottimizzazione circoscritta. Il lavoro è completato quando l’optimizer implementa la trasformazione in modo sicuro e test mirati del planner dimostrano i piani di query attesi.

Scritto dal modello di indicizzazione a partire dal testo della issue.