You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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;
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.
Activity
avantgardnerio commentedon Aug 25, 2022
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 commentedon Aug 25, 2022
When this works, q15 in the TPCH benchmark should work as well.
DaltonModlin commentedon Aug 25, 2022
I'll take a look into this issue as I'm currently trying to fix q15 for TPCH benchmarks.
avantgardnerio commentedon Aug 26, 2022
I think there are three things preventing q15 from working:
2 & # 3 could be worked on independently I believe.
kmitchener commentedon Aug 26, 2022
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.
initial commit for apache#3266