Add support for non-correlated subqueries

[andygrove]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.

I would like to be able to run this query:

CREATE TABLE paintings AS SELECT 'Mona Lisa' as name, 1000000 as listed_price;

SELECT name, listed_price
FROM paintings
WHERE listed_price > (
    SELECT AVG(listed_price)
    FROM paintings
);

It currently fails with:

Skipping optimizer rule decorrelate_scalar_subquery due to unexpected error: scalar subqueries must have a filter to be correlated at /home/andy/git/apache/arrow-datafusion/datafusion/optimizer/src/decorrelate_scalar_subquery.rs:177
caused by
Error during planning: Could not coerce into Filter! at /home/andy/git/apache/arrow-datafusion/datafusion/expr/src/logical_plan/plan.rs:1127
NotImplemented("Physical plan does not support logical expression (<subquery>)")

This is because the existing subquery optimizer rules fail to rewrite the query.

It should be possible to rewrite the query as a join:

SELECT name, listed_price
FROM paintings
CROSS JOIN (SELECT AVG(listed_price) AS avg_price FROM paintings) temp
WHERE listed_price > temp.avg_price;

Describe the solution you'd like
☝️

Describe alternatives you've considered
None

Additional context
None

[avantgardnerio]

FYI this should not be super-hard. I actually started adding uncorrelated support, and there is even an example of it here. CC @DaltonModlin

[kmitchener]

When this works, q15 in the TPCH benchmark should work as well.

[DaltonModlin]

I'll take a look into this issue as I'm currently trying to fix q15 for TPCH benchmarks.

[avantgardnerio]

I think there are three things preventing q15 from working:

1. projections in the view definition (now fixed by @DaltonModlin )
2. a different view-related error @DaltonModlin is working on presently
3. The uncorrelated issue we're chatting on right now

2 & # 3 could be worked on independently I believe.

[kmitchener]

4 things! We need the PR for drop view #3267 to be merged in too for q15. :)

I can take a stab at this issue, but I wouldn't care if someone came up with a competing patch .. plan optimization is totally new to me so I'm not sure how successful I'll be over what timeframe.