Skip to content

Add support for non-correlated subqueries #3266

Closed

Description

@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

Activity

  1. avantgardnerio commented on Aug 25, 2022

    @avantgardnerio
    Contributor

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

  2. kmitchener commented on Aug 25, 2022

    @kmitchener
    Contributor

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

  3. DaltonModlin commented on Aug 25, 2022

    @DaltonModlin
    Contributor

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

  4. avantgardnerio commented on Aug 26, 2022

    @avantgardnerio
    Contributor

    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.

  5. kmitchener commented on Aug 26, 2022

    @kmitchener
    Contributor

    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.

  6. added a commit that references this issue on Aug 29, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or requestsqlSQL Planner

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions