Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Implement physical plan for IN Subquery #1835

Closed
Tracked by #5483 ...
yahoNanJing opened this issue Feb 15, 2022 · 2 comments
Closed
Tracked by #5483 ...

Implement physical plan for IN Subquery #1835

yahoNanJing opened this issue Feb 15, 2022 · 2 comments
Labels
enhancement New feature or request

Comments

@yahoNanJing
Copy link
Contributor

yahoNanJing commented Feb 15, 2022

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

Implement subqueries like

SELECT f.x  FROM foo as f WHERE f.x IN  (select a from bar) as sq;

Describe the solution you'd like
It would be nice to use a SEMI join for this, something like

Projection(f.x, sq.a)
  --> Join(Semi f.x = sq.a)
    --> TableScan(foo)
    --> Projection(a)  <--- Here is where the "subquery"'s LogicalPlan is attached 
      --> TableScan(bar)

Describe alternatives you've considered

Additional context

@yahoNanJing yahoNanJing added the enhancement New feature or request label Feb 15, 2022
@alamb
Copy link
Contributor

alamb commented Apr 8, 2022

Probably a dupe of #1209

@alamb alamb closed this as completed Apr 8, 2022
@alamb alamb reopened this Apr 8, 2022
@andygrove andygrove mentioned this issue Apr 16, 2022
15 tasks
@andygrove andygrove changed the title Implement In Subquery Implement physical plan for IN Subquery Apr 27, 2022
@ygf11
Copy link
Contributor

ygf11 commented Mar 6, 2023

I think this has already done.

❯ explain select t1_id from t1 where t1_id in (select t2_id from t2);
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                                                            |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan  | LeftSemi Join: t1.t1_id = __correlated_sq_1.t2_id                                                                                               |
|               |   TableScan: t1 projection=[t1_id]                                                                                                              |
|               |   SubqueryAlias: __correlated_sq_1                                                                                                              |
|               |     Projection: t2.t2_id AS t2_id                                                                                                               |
|               |       TableScan: t2 projection=[t2_id]                                                                                                          |
...
❯ explain select t1_id from t1 where t1_id in (select t2_id from t2 where t2_int > t1_int);
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                                                                                                                                                                       |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan  | Projection: t1.t1_id                                                                                                                                                                                                                                       |
|               |   LeftSemi Join: t1.t1_id = __correlated_sq_2.t2_id Filter: __correlated_sq_2.t2_int > t1.t1_int                                                                                                                                                           |
|               |     TableScan: t1 projection=[t1_id, t1_int]                                                                                                                                                                                                               |
|               |     SubqueryAlias: __correlated_sq_2                                                                                                                                                                                                                       |
|               |       Projection: t2.t2_id AS t2_id, t2.t2_int                                                                                                                                                                                                             |
|               |         TableScan: t2 projection=[t2_id, t2_int]

@alamb alamb closed this as completed Mar 6, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

3 participants