Additional ideas for optimizing subqueries

[alamb]

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
• Implementation in DuckDB here: https://github.com/duckdb/duckdb/blob/bee8017bdcc5e652aee26ce8cfb260990cf6a369/src/planner/subquery/flatten_dependent_join.cpp#L72

[mingmwang]

https://github.com/apache/arrow-datafusion/issues/5492

[mingmwang]

I will work on it.