You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2021/05/13 10:52:36 UTC
[GitHub] [arrow-datafusion] Jimexist opened a new pull request #334: Add window expr
Jimexist opened a new pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334
# Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
-->
Closes #.
# Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
-->
# What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
-->
# Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be updated before approving the PR.
-->
<!--
If there are any breaking changes to public APIs, please add the `breaking change` label.
-->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb merged pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
alamb merged pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist edited a comment on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist edited a comment on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845184684
> I haven't had the time to go through yet;
>
> Since this is a large decomposable change, a suggestion here is to create a branch and merge this on that branch, so that we do not have incomplete features in master and allow PRs to that branch without an issue. Then merge the branch into master once its first iteration is ready (e.g. logical and 1 physical plan).
>
> (This is something that I wished to have when implementing large features; it gives the time to review in parts without the risk of having incomplete code in master)
>
> With that said, I am also fine risk it and merg to master; @Jimexist do you have any preference how would you prefer to work on this here? :)
I wish to:
1. add unit tests
2. fix some small comments, per @alamb, like
1. adding filter clause to leave room for future room without API disruption,
2. adding nth_value and tile and leaving them unimplemented and leave room for good first PR
3. merge it into master as it
3 because although I agree with having a feature branch, the maintenance of continuous rebasing would be troublesome, and I can estimate the whole series of window function implementations take > 1 month. if I can keep the merged code isolated and structurally correct then there's little worry about breaking changes in future: I believe this PR:
1. is _isolated_ because the _explain_ works, while actually executing the window SQL returns zero rows, due to unimplemented exec plan, which I intend to do next
2. is structurally correct if reviewers can focus on the phase and timing of the window clause in the query planner. future changes of e.g. adding one or more sort phases requires only modifications within the `.window` `fn` and thus can be non-intrusive
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
alamb commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-844384440
I will try to review this later today but I may not get to it until tomorrow
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on pull request #334: Add window expr
Posted by GitBox <gi...@apache.org>.
Jimexist commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-841844817
I wonder which approach makes more sense:
- To implement proto and serde as an API first and leave a lot of unimplemented stubs
- To try to cover a minimal working end to end feature and leave advanced use cases like window frames for later
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845567201
> 🤔 it looks like there is one test failure now:
>
> ```
> ---- physical_plan::window_functions::tests::test_cume_dist_return_type stdout ----
> Error: Plan("The function expected 0 arguments but received 1")
> thread 'physical_plan::window_functions::tests::test_cume_dist_return_type' panicked at 'assertion failed: `(left == right)`
> left: `1`,
> right: `0`: the test returned a termination value with a non-zero status code (1) which indicates a failure', /rustc/9bc8c42bb2f19e745a63f3445f1ac248fb015e53/library/test/src/lib.rs:193:5
> note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
>
> ---- physical_plan::window_functions::tests::test_window_function_from_str stdout ----
> Error: Plan("There is no built-in function named cum_dist")
> thread 'physical_plan::window_functions::tests::test_window_function_from_str' panicked at 'assertion failed: `(left == right)`
> left: `1`,
> right: `0`: the test returned a termination value with a non-zero status code (1) which indicates a failure', /rustc/9bc8c42bb2f19e745a63f3445f1ac248fb015e53/library/test/src/lib.rs:193:5
> ```
@alamb sorry I did try to add more tests, but then forgot to push.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist edited a comment on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist edited a comment on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845184684
> I haven't had the time to go through yet;
>
> Since this is a large decomposable change, a suggestion here is to create a branch and merge this on that branch, so that we do not have incomplete features in master and allow PRs to that branch without an issue. Then merge the branch into master once its first iteration is ready (e.g. logical and 1 physical plan).
>
> (This is something that I wished to have when implementing large features; it gives the time to review in parts without the risk of having incomplete code in master)
>
> With that said, I am also fine risk it and merg to master; @Jimexist do you have any preference how would you prefer to work on this here? :)
I wish to:
1. add unit tests
2. fix some small comments, per @alamb, like
1. adding filter clause to leave room for future room without API disruption,
2. adding nth_value and tile and leaving them unimplemented and leave room for good first PR
3. merge it into master as it
3 because although I agree with having a feature branch, the maintenance of continuous rebasing would be troublesome, and I can estimate the whole series of window function implementations take > 1 month. if I can keep the merged code _isolated_ and _structurally complete_ then there's little worry about breaking changes in future: I believe this PR:
1. is _isolated_ because the _explain_ works, while actually executing the window SQL returns zero rows, due to unimplemented exec plan, which I intend to do next
2. is _structurally complete_ if reviewers can focus on the phase and timing of the window clause in the query planner. future changes of e.g. adding one or more sort phases requires only modifications within the `.window` `fn` and thus can be non-intrusive
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
alamb commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845489982
🤔 it looks like there is one test failure now:
```
---- physical_plan::window_functions::tests::test_cume_dist_return_type stdout ----
Error: Plan("The function expected 0 arguments but received 1")
thread 'physical_plan::window_functions::tests::test_cume_dist_return_type' panicked at 'assertion failed: `(left == right)`
left: `1`,
right: `0`: the test returned a termination value with a non-zero status code (1) which indicates a failure', /rustc/9bc8c42bb2f19e745a63f3445f1ac248fb015e53/library/test/src/lib.rs:193:5
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
---- physical_plan::window_functions::tests::test_window_function_from_str stdout ----
Error: Plan("There is no built-in function named cum_dist")
thread 'physical_plan::window_functions::tests::test_window_function_from_str' panicked at 'assertion failed: `(left == right)`
left: `1`,
right: `0`: the test returned a termination value with a non-zero status code (1) which indicates a failure', /rustc/9bc8c42bb2f19e745a63f3445f1ac248fb015e53/library/test/src/lib.rs:193:5
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636612578
##########
File path: datafusion/src/logical_plan/builder.rs
##########
@@ -289,6 +288,52 @@ impl LogicalPlanBuilder {
}))
}
+ /// Apply a window
+ ///
+ /// NOTE: this feature is under development and this API will be changing
+ ///
+ /// - https://github.com/apache/arrow-datafusion/issues/359 basic structure
+ /// - https://github.com/apache/arrow-datafusion/issues/298 empty over clause
+ /// - https://github.com/apache/arrow-datafusion/issues/299 with partition clause
+ /// - https://github.com/apache/arrow-datafusion/issues/360 with order by
+ /// - https://github.com/apache/arrow-datafusion/issues/361 with window frame
+ pub fn window(
+ &self,
+ window_expr: impl IntoIterator<Item = Expr>,
Review comment:
tracked in https://github.com/apache/arrow-datafusion/issues/372
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Dandandan commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r635803983
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
My comment above was more about re-using the same functions over here - as I thought we might not want to support every aggregation function here too. But for me it sounds like a good idea to reuse them. Maybe @alamb has some ideas about it as well
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845184684
> I haven't had the time to go through yet;
>
> Since this is a large decomposable change, a suggestion here is to create a branch and merge this on that branch, so that we do not have incomplete features in master and allow PRs to that branch without an issue. Then merge the branch into master once its first iteration is ready (e.g. logical and 1 physical plan).
>
> (This is something that I wished to have when implementing large features; it gives the time to review in parts without the risk of having incomplete code in master)
>
> With that said, I am also fine risk it and merg to master; @Jimexist do you have any preference how would you prefer to work on this here? :)
I wish to:
1. add unit tests
2. fix some small comments like
1. adding filter clause to leave room for future room without API disruption,
2. adding nth_value and tile
3. merge it into master as it
3 because although I agree with having a feature branch, the maintenance of continuous rebasing is trouble some. if I can keep the merged code isolated and structurally correct then there's little worry about breaking changes in future: I believe this PR:
1. is _isolated_ because the _explain_ works, while actually executing the window SQL returns zero rows, due to unimplemented exec plan, which I intend to do next
2. is structurally correct if reviewers can focus on the phase and timing of the window clause in the query planner. future changes of e.g. adding one or more sort phases requires only modifications within the `.window` `fn` and thus can be non-intrusive
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Dandandan commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r635802863
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
I believe `count(..) over order by ..` also needs to be sorted, it will do a count over the window, which means a running count by default.
But yeah very useful for analytics indeed :+1:
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on a change in pull request #334: Add window expr
Posted by GitBox <gi...@apache.org>.
Dandandan commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r631928204
##########
File path: datafusion/src/physical_plan/planner.rs
##########
@@ -136,7 +136,13 @@ impl DefaultPhysicalPlanner {
limit,
..
} => source.scan(projection, batch_size, filters, *limit),
- LogicalPlan::Aggregate {
+ LogicalPlan::Window {
+ input,
+ group_expr,
Review comment:
I would use partition by / order by here
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Dandandan commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r635802863
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
I believe `count(..) over order by ..` also needs to be sorted, it will do a count over the window, which means a running count (over sorted rows) by default.
But yeah very useful for analytics indeed :+1:
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-844188559
@Dandandan @alamb PTAL
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r635675124
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
also fun thing to notice:
```
[postgres] # explain analyze select c1, sum(c3) over (partition by c1 order by c3), avg(c3) over (partition by c1 order by c3 desc) from test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=11.64..13.64 rows=100 width=44) (actual time=1.287..1.373 rows=100 loops=1)
-> Sort (cost=11.64..11.89 rows=100 width=36) (actual time=1.281..1.292 rows=100 loops=1)
Sort Key: c1, c3
Sort Method: quicksort Memory: 31kB
-> WindowAgg (cost=6.32..8.32 rows=100 width=36) (actual time=1.051..1.174 rows=100 loops=1)
-> Sort (cost=6.32..6.57 rows=100 width=4) (actual time=0.221..0.231 rows=100 loops=1)
Sort Key: c1, c3 DESC
Sort Method: quicksort Memory: 29kB
-> Seq Scan on test (cost=0.00..3.00 rows=100 width=4) (actual time=0.010..0.028 rows=100 loops=1)
Planning Time: 0.087 ms
Execution Time: 1.437 ms
(11 rows)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r635673607
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
```
[postgres] # explain select c1, count(c3) over (partition by c1 order by c3) from test;
QUERY PLAN
------------------------------------------------------------------
WindowAgg (cost=6.32..8.32 rows=100 width=12)
-> Sort (cost=6.32..6.57 rows=100 width=4)
Sort Key: c1, c3
-> Seq Scan on test (cost=0.00..3.00 rows=100 width=4)
(4 rows)
```
```
[postgres] # explain select c1, first_value(c3) over (partition by c1 order by c3) from test;
QUERY PLAN
------------------------------------------------------------------
WindowAgg (cost=6.32..8.32 rows=100 width=6)
-> Sort (cost=6.32..6.57 rows=100 width=4)
Sort Key: c1, c3
-> Seq Scan on test (cost=0.00..3.00 rows=100 width=4)
(4 rows)
```
IMO only the second time we'll really need to sort by `c3`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636164538
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
> I believe count(..) over order by .. also needs to be sorted, it will do a count over the window, which means a running count (over sorted rows) by default.
Good point! Indeed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist edited a comment on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist edited a comment on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845184684
> I haven't had the time to go through yet;
>
> Since this is a large decomposable change, a suggestion here is to create a branch and merge this on that branch, so that we do not have incomplete features in master and allow PRs to that branch without an issue. Then merge the branch into master once its first iteration is ready (e.g. logical and 1 physical plan).
>
> (This is something that I wished to have when implementing large features; it gives the time to review in parts without the risk of having incomplete code in master)
>
> With that said, I am also fine risk it and merg to master; @Jimexist do you have any preference how would you prefer to work on this here? :)
I wish to:
1. add unit tests
2. fix some small comments, per @alamb, like
1. adding filter clause to leave room for future room without API disruption,
2. adding nth_value and tile and leaving them unimplemented and leave room for good first PR
3. merge it into master as it
3 because although I agree with having a feature branch, the maintenance of continuous rebasing is trouble some. if I can keep the merged code isolated and structurally correct then there's little worry about breaking changes in future: I believe this PR:
1. is _isolated_ because the _explain_ works, while actually executing the window SQL returns zero rows, due to unimplemented exec plan, which I intend to do next
2. is structurally correct if reviewers can focus on the phase and timing of the window clause in the query planner. future changes of e.g. adding one or more sort phases requires only modifications within the `.window` `fn` and thus can be non-intrusive
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist edited a comment on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist edited a comment on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845237253
@alamb @jorgecarleitao updated
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on pull request #334: Add window expr
Posted by GitBox <gi...@apache.org>.
Jimexist commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-841844817
I wonder which approach makes more sense:
- To implement proto and serde as an API first and leave a lot of unimplemented stubs
- To try to cover a minimal working end to end feature and leave advanced use cases like window frames for later
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist edited a comment on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist edited a comment on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845184684
> I haven't had the time to go through yet;
>
> Since this is a large decomposable change, a suggestion here is to create a branch and merge this on that branch, so that we do not have incomplete features in master and allow PRs to that branch without an issue. Then merge the branch into master once its first iteration is ready (e.g. logical and 1 physical plan).
>
> (This is something that I wished to have when implementing large features; it gives the time to review in parts without the risk of having incomplete code in master)
>
> With that said, I am also fine risk it and merg to master; @Jimexist do you have any preference how would you prefer to work on this here? :)
I wish to:
1. add unit tests
2. fix some small comments like
1. adding filter clause to leave room for future room without API disruption,
2. adding nth_value and tile
3. merge it into master as it
3 because although I agree with having a feature branch, the maintenance of continuous rebasing is trouble some. if I can keep the merged code isolated and structurally correct then there's little worry about breaking changes in future: I believe this PR:
1. is _isolated_ because the _explain_ works, while actually executing the window SQL returns zero rows, due to unimplemented exec plan, which I intend to do next
2. is structurally correct if reviewers can focus on the phase and timing of the window clause in the query planner. future changes of e.g. adding one or more sort phases requires only modifications within the `.window` `fn` and thus can be non-intrusive
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] jorgecarleitao commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
jorgecarleitao commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845151947
I haven't had the time to go through yet;
Since this is a large decomposable change, a suggestion here is to create a branch and merge this on that branch, so that we do not have incomplete features in master and allow PRs to that branch without an issue. Then merge the branch into master once its first iteration is ready (e.g. logical and 1 physical plan).
(This is something that I wished to have when implementing large features; it gives the time to review in parts without the risk of having incomplete code in master)
With that said, I am also fine risk it and merg to master; @Jimexist do you have any preference how would you prefer to work on this here? :)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on a change in pull request #334: Add window expr
Posted by GitBox <gi...@apache.org>.
Dandandan commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r631927655
##########
File path: datafusion/src/optimizer/hash_build_probe_order.rs
##########
@@ -53,8 +53,8 @@ fn get_num_rows(logical_plan: &LogicalPlan) -> Option<usize> {
let num_rows_input = get_num_rows(input);
num_rows_input.map(|rows| std::cmp::min(*limit, rows))
}
- LogicalPlan::Aggregate { .. } => {
- // we cannot yet predict how many rows will be produced by an aggregate because
+ LogicalPlan::Window { .. } | LogicalPlan::Aggregate { .. } => {
Review comment:
Window has the same nr of rows as the input!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636212377
##########
File path: datafusion/src/logical_plan/builder.rs
##########
@@ -289,6 +288,37 @@ impl LogicalPlanBuilder {
}))
}
+ /// Apply a window
+ pub fn window(
+ &self,
+ window_expr: impl IntoIterator<Item = Expr>,
+ // partition_by_expr: impl IntoIterator<Item = Expr>,
+ // order_by_expr: impl IntoIterator<Item = Expr>,
+ // window_frame: Option<WindowFrame>,
+ ) -> Result<Self> {
+ let window_expr = window_expr.into_iter().collect::<Vec<Expr>>();
+ // let partition_by_expr = partition_by_expr.into_iter().collect::<Vec<Expr>>();
+ // let order_by_expr = order_by_expr.into_iter().collect::<Vec<Expr>>();
+ let all_expr = window_expr.iter();
+ validate_unique_names("Windows", all_expr.clone(), self.plan.schema())?;
+
+ let mut window_fields: Vec<DFField> =
+ exprlist_to_fields(all_expr, self.plan.schema())?;
+ window_fields.extend_from_slice(self.plan.schema().fields());
+
+ Ok(Self::from(&LogicalPlan::Window {
+ input: Arc::new(self.plan.clone()),
+ // FIXME implement next
+ // partition_by_expr,
+ // FIXME implement next
+ // order_by_expr,
+ // FIXME implement next
+ // window_frame,
+ window_expr,
Review comment:
added
##########
File path: datafusion/src/physical_plan/window_functions.rs
##########
@@ -0,0 +1,149 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+//! Window functions provide the ability to perform calculations across
+//! sets of rows that are related to the current query row.
+//!
+//! see also https://www.postgresql.org/docs/current/functions-window.html
+
+use crate::error::{DataFusionError, Result};
+use crate::physical_plan::{
+ aggregates, aggregates::AggregateFunction, functions::Signature,
+ type_coercion::data_types,
+};
+use arrow::datatypes::DataType;
+use std::{fmt, str::FromStr};
+
+/// WindowFunction
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum WindowFunction {
+ /// window function that leverages an aggregate function
+ AggregateFunction(AggregateFunction),
+ /// window function that leverages a built-in window function
+ BuiltInWindowFunction(BuiltInWindowFunction),
+}
+
+impl FromStr for WindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<WindowFunction> {
+ if let Ok(aggregate) = AggregateFunction::from_str(name) {
+ Ok(WindowFunction::AggregateFunction(aggregate))
+ } else if let Ok(built_in_function) = BuiltInWindowFunction::from_str(name) {
+ Ok(WindowFunction::BuiltInWindowFunction(built_in_function))
+ } else {
+ Err(DataFusionError::Plan(format!(
+ "There is no built-in function named {}",
+ name
+ )))
+ }
+ }
+}
+
+impl fmt::Display for BuiltInWindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ // uppercase of the debug.
+ write!(f, "{}", format!("{:?}", self).to_uppercase())
+ }
+}
+
+impl fmt::Display for WindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ WindowFunction::AggregateFunction(fun) => fun.fmt(f),
+ WindowFunction::BuiltInWindowFunction(fun) => fun.fmt(f),
+ }
+ }
+}
+
+/// An aggregate function that is part of a built-in window function
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum BuiltInWindowFunction {
+ /// row number
+ RowNumber,
+ /// rank
+ Rank,
+ /// dense rank
+ DenseRank,
+ /// lag
+ Lag,
+ /// lead
+ Lead,
+ /// first value
+ FirstValue,
+ /// last value
+ LastValue,
+}
Review comment:
added
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] andygrove commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
andygrove commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636129329
##########
File path: ballista/rust/core/src/serde/logical_plan/from_proto.rs
##########
@@ -75,6 +76,33 @@ impl TryInto<LogicalPlan> for &protobuf::LogicalPlanNode {
.build()
.map_err(|e| e.into())
}
+ LogicalPlanType::Window(window) => {
+ let input: LogicalPlan = convert_box_required!(window.input)?;
+ let window_expr = window
+ .window_expr
+ .iter()
+ .map(|expr| expr.try_into())
+ .collect::<Result<Vec<_>, _>>()?;
+
+ // let partition_by_expr = window
+ // .partition_by_expr
+ // .iter()
+ // .map(|expr| expr.try_into())
+ // .collect::<Result<Vec<_>, _>>()?;
+ // let order_by_expr = window
+ // .order_by_expr
+ // .iter()
+ // .map(|expr| expr.try_into())
+ // .collect::<Result<Vec<_>, _>>()?;
+ // // FIXME parse the window_frame data
Review comment:
It looks this is still WIP? Is the plan to complete this as part of this PR?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r635672736
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
yes in general three types of things can be used:
1. aggregation
2. UDAF
3. built in window function
for both 1. and 2. they are not order sensitive, but for 3 we'll have to take sort into account
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] codecov-commenter commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
codecov-commenter commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845724345
# [Codecov](https://codecov.io/gh/apache/arrow-datafusion/pull/334?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) Report
> Merging [#334](https://codecov.io/gh/apache/arrow-datafusion/pull/334?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) (6941151) into [master](https://codecov.io/gh/apache/arrow-datafusion/commit/913bf86147773863ec96b76f1f7cfc1cfbb84a6e?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) (913bf86) will **decrease** coverage by `0.95%`.
> The diff coverage is `36.84%`.
[](https://codecov.io/gh/apache/arrow-datafusion/pull/334?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation)
```diff
@@ Coverage Diff @@
## master #334 +/- ##
==========================================
- Coverage 75.89% 74.94% -0.96%
==========================================
Files 144 146 +2
Lines 23771 24314 +543
==========================================
+ Hits 18040 18221 +181
- Misses 5731 6093 +362
```
| [Impacted Files](https://codecov.io/gh/apache/arrow-datafusion/pull/334?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) | Coverage Δ | |
|---|---|---|
| [...lista/rust/core/src/serde/logical\_plan/to\_proto.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-YmFsbGlzdGEvcnVzdC9jb3JlL3NyYy9zZXJkZS9sb2dpY2FsX3BsYW4vdG9fcHJvdG8ucnM=) | `62.37% <0.00%> (-5.46%)` | :arrow_down: |
| [datafusion/src/optimizer/constant\_folding.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi9zcmMvb3B0aW1pemVyL2NvbnN0YW50X2ZvbGRpbmcucnM=) | `91.63% <0.00%> (-0.30%)` | :arrow_down: |
| [datafusion/src/optimizer/hash\_build\_probe\_order.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi9zcmMvb3B0aW1pemVyL2hhc2hfYnVpbGRfcHJvYmVfb3JkZXIucnM=) | `60.50% <0.00%> (-1.57%)` | :arrow_down: |
| [datafusion/src/optimizer/projection\_push\_down.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi9zcmMvb3B0aW1pemVyL3Byb2plY3Rpb25fcHVzaF9kb3duLnJz) | `91.11% <0.00%> (-7.63%)` | :arrow_down: |
| [datafusion/src/optimizer/utils.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi9zcmMvb3B0aW1pemVyL3V0aWxzLnJz) | `47.76% <0.00%> (-2.05%)` | :arrow_down: |
| [datafusion/src/physical\_plan/mod.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi9zcmMvcGh5c2ljYWxfcGxhbi9tb2QucnM=) | `82.75% <0.00%> (-1.95%)` | :arrow_down: |
| [datafusion/src/physical\_plan/planner.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi9zcmMvcGh5c2ljYWxfcGxhbi9wbGFubmVyLnJz) | `76.51% <0.00%> (-4.11%)` | :arrow_down: |
| [datafusion/src/physical\_plan/sort.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi9zcmMvcGh5c2ljYWxfcGxhbi9zb3J0LnJz) | `92.07% <ø> (ø)` | |
| [datafusion/src/physical\_plan/windows.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi9zcmMvcGh5c2ljYWxfcGxhbi93aW5kb3dzLnJz) | `0.00% <0.00%> (ø)` | |
| [datafusion/tests/sql.rs](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation#diff-ZGF0YWZ1c2lvbi90ZXN0cy9zcWwucnM=) | `99.89% <ø> (ø)` | |
| ... and [12 more](https://codecov.io/gh/apache/arrow-datafusion/pull/334/diff?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation) | |
------
[Continue to review full report at Codecov](https://codecov.io/gh/apache/arrow-datafusion/pull/334?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/apache/arrow-datafusion/pull/334?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation). Last update [913bf86...6941151](https://codecov.io/gh/apache/arrow-datafusion/pull/334?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=The+Apache+Software+Foundation).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on pull request #334: Add window expr
Posted by GitBox <gi...@apache.org>.
Dandandan commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-841865589
@Jimexist I think it would be perfectly fine if more "advanced" features like window frames etc. are not supported, just as not all window functions have to be supported.
If we could just support e.g. `ROW_NUMBER() OVER ()` or `MAX(x) OVER ()` with default spec that would be great already.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on pull request #334: Add window expr
Posted by GitBox <gi...@apache.org>.
Dandandan commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-841865589
@Jimexist I think it would be perfectly fine if more "advanced" features like window frames etc. are not supported, just as not all window functions have to be supported.
If we could just support e.g. `ROW_NUMBER() OVER ()` or `MAX(x) OVER ()` with default spec that would be great already.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r635676450
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
> I checked whether this makes sense to reuse aggregate functions for window expressions - I think it does! E.g. PostgreSQL also says:
> https://www.postgresql.org/docs/9.3/functions-window.html
>
> > In addition to these functions, any built-in or user-defined aggregate function can be used as a window function (see Section 9.20 for a list of the built-in aggregates). Aggregate functions act as window functions only when an OVER clause follows the call; otherwise they act as regular aggregates.
it is very useful for analytics, e.g. if you want to know (in an employee table with name, department, and salary) the list of employees in each department with salary above average.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
alamb commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845183672
I am supportive of putting this directly on master -- we have various other "not yet implemented" functionality in DataFusion and I think we can handle any potential confusion with additional documentation
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636612889
##########
File path: datafusion/src/physical_plan/aggregates.rs
##########
@@ -183,7 +182,7 @@ static TIMESTAMPS: &[DataType] = &[
];
/// the signatures supported by the function `fun`.
-fn signature(fun: &AggregateFunction) -> Signature {
+pub fn signature(fun: &AggregateFunction) -> Signature {
Review comment:
tracked in https://github.com/apache/arrow-datafusion/issues/373
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636557518
##########
File path: datafusion/src/sql/planner.rs
##########
@@ -2641,13 +2701,23 @@ mod tests {
}
#[test]
- fn over_not_supported() {
+ fn empty_over() {
let sql = "SELECT order_id, MAX(order_id) OVER () from orders";
- let err = logical_plan(sql).expect_err("query should have failed");
- assert_eq!(
- "NotImplemented(\"Unsupported OVER clause ()\")",
- format!("{:?}", err)
- );
+ let expected = "\
+ Projection: #order_id, #MAX(order_id)\
+ \n WindowAggr: windowExpr=[[MAX(#order_id)]] partitionBy=[], orderBy=[]\
+ \n TableScan: orders projection=None";
+ quick_test(sql, expected);
+ }
+
+ #[test]
+ fn empty_over_plus() {
Review comment:
done
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636187884
##########
File path: datafusion/src/logical_plan/builder.rs
##########
@@ -289,6 +288,37 @@ impl LogicalPlanBuilder {
}))
}
+ /// Apply a window
+ pub fn window(
+ &self,
+ window_expr: impl IntoIterator<Item = Expr>,
+ // partition_by_expr: impl IntoIterator<Item = Expr>,
+ // order_by_expr: impl IntoIterator<Item = Expr>,
+ // window_frame: Option<WindowFrame>,
+ ) -> Result<Self> {
+ let window_expr = window_expr.into_iter().collect::<Vec<Expr>>();
+ // let partition_by_expr = partition_by_expr.into_iter().collect::<Vec<Expr>>();
+ // let order_by_expr = order_by_expr.into_iter().collect::<Vec<Expr>>();
+ let all_expr = window_expr.iter();
+ validate_unique_names("Windows", all_expr.clone(), self.plan.schema())?;
+
+ let mut window_fields: Vec<DFField> =
+ exprlist_to_fields(all_expr, self.plan.schema())?;
+ window_fields.extend_from_slice(self.plan.schema().fields());
+
+ Ok(Self::from(&LogicalPlan::Window {
+ input: Arc::new(self.plan.clone()),
+ // FIXME implement next
+ // partition_by_expr,
+ // FIXME implement next
+ // order_by_expr,
+ // FIXME implement next
+ // window_frame,
+ window_expr,
Review comment:
I'll leave a comment for now. btw `function.filter` needs to be added in the upstream `sqlparser` crate.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
alamb commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636098205
##########
File path: datafusion/src/logical_plan/builder.rs
##########
@@ -289,6 +288,37 @@ impl LogicalPlanBuilder {
}))
}
+ /// Apply a window
+ pub fn window(
Review comment:
```suggestion
///
/// NOTE: this feature is under development and this API will be changing
pub fn window(
```
##########
File path: datafusion/src/physical_plan/window_functions.rs
##########
@@ -0,0 +1,149 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+//! Window functions provide the ability to perform calculations across
+//! sets of rows that are related to the current query row.
+//!
+//! see also https://www.postgresql.org/docs/current/functions-window.html
+
+use crate::error::{DataFusionError, Result};
+use crate::physical_plan::{
+ aggregates, aggregates::AggregateFunction, functions::Signature,
+ type_coercion::data_types,
+};
+use arrow::datatypes::DataType;
+use std::{fmt, str::FromStr};
+
+/// WindowFunction
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum WindowFunction {
+ /// window function that leverages an aggregate function
+ AggregateFunction(AggregateFunction),
+ /// window function that leverages a built-in window function
+ BuiltInWindowFunction(BuiltInWindowFunction),
+}
+
+impl FromStr for WindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<WindowFunction> {
+ if let Ok(aggregate) = AggregateFunction::from_str(name) {
+ Ok(WindowFunction::AggregateFunction(aggregate))
+ } else if let Ok(built_in_function) = BuiltInWindowFunction::from_str(name) {
+ Ok(WindowFunction::BuiltInWindowFunction(built_in_function))
+ } else {
+ Err(DataFusionError::Plan(format!(
+ "There is no built-in function named {}",
+ name
+ )))
+ }
+ }
+}
+
+impl fmt::Display for BuiltInWindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ // uppercase of the debug.
+ write!(f, "{}", format!("{:?}", self).to_uppercase())
+ }
+}
+
+impl fmt::Display for WindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ WindowFunction::AggregateFunction(fun) => fun.fmt(f),
+ WindowFunction::BuiltInWindowFunction(fun) => fun.fmt(f),
+ }
+ }
+}
+
+/// An aggregate function that is part of a built-in window function
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum BuiltInWindowFunction {
+ /// row number
+ RowNumber,
+ /// rank
+ Rank,
+ /// dense rank
+ DenseRank,
+ /// lag
+ Lag,
+ /// lead
+ Lead,
+ /// first value
+ FirstValue,
+ /// last value
+ LastValue,
+}
+
+impl FromStr for BuiltInWindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<BuiltInWindowFunction> {
Review comment:
I wonder if this should be a case insensitive comparison (aka I think `RANK` should also match to `BuiltInWindowFunction::Rank`)? I can't remember if that is handled higher up in the planner or not -- it may be worth a test in any case.
##########
File path: datafusion/src/logical_plan/builder.rs
##########
@@ -289,6 +288,37 @@ impl LogicalPlanBuilder {
}))
}
+ /// Apply a window
+ pub fn window(
+ &self,
+ window_expr: impl IntoIterator<Item = Expr>,
+ // partition_by_expr: impl IntoIterator<Item = Expr>,
+ // order_by_expr: impl IntoIterator<Item = Expr>,
+ // window_frame: Option<WindowFrame>,
Review comment:
I think SQL also offers a "FILTER" clause for window aggregates as well, so we may want to leave room for that in the API
```suggestion
// filter: impl IntoIterator<Item = Expr>,
// partition_by_expr: impl IntoIterator<Item = Expr>,
// order_by_expr: impl IntoIterator<Item = Expr>,
// window_frame: Option<WindowFrame>,
```
This page https://sqlite.org/windowfunctions.html does a pretty good job with the syntax
##########
File path: datafusion/src/sql/planner.rs
##########
@@ -2641,13 +2701,23 @@ mod tests {
}
#[test]
- fn over_not_supported() {
+ fn empty_over() {
let sql = "SELECT order_id, MAX(order_id) OVER () from orders";
- let err = logical_plan(sql).expect_err("query should have failed");
- assert_eq!(
- "NotImplemented(\"Unsupported OVER clause ()\")",
- format!("{:?}", err)
- );
+ let expected = "\
+ Projection: #order_id, #MAX(order_id)\
+ \n WindowAggr: windowExpr=[[MAX(#order_id)]] partitionBy=[], orderBy=[]\
+ \n TableScan: orders projection=None";
+ quick_test(sql, expected);
+ }
+
+ #[test]
+ fn empty_over_plus() {
Review comment:
I would like to see some additional tests to increase the coverage:
1. Multiple window functions in the same query (as the frontend code supports this)
2. Built in window functions (e.g `ROW_NUMBER()` and `row_number()` and `lead(col)`)
3. A test in `tests/sql.rs` that runs (and throw as "NotYetImplemented") error for queries that have an `OVER()` clause (to exercise the physical planning logic)
##########
File path: datafusion/src/physical_plan/window_functions.rs
##########
@@ -0,0 +1,149 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+//! Window functions provide the ability to perform calculations across
+//! sets of rows that are related to the current query row.
+//!
+//! see also https://www.postgresql.org/docs/current/functions-window.html
+
+use crate::error::{DataFusionError, Result};
+use crate::physical_plan::{
+ aggregates, aggregates::AggregateFunction, functions::Signature,
+ type_coercion::data_types,
+};
+use arrow::datatypes::DataType;
+use std::{fmt, str::FromStr};
+
+/// WindowFunction
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum WindowFunction {
+ /// window function that leverages an aggregate function
+ AggregateFunction(AggregateFunction),
+ /// window function that leverages a built-in window function
+ BuiltInWindowFunction(BuiltInWindowFunction),
+}
+
+impl FromStr for WindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<WindowFunction> {
+ if let Ok(aggregate) = AggregateFunction::from_str(name) {
+ Ok(WindowFunction::AggregateFunction(aggregate))
+ } else if let Ok(built_in_function) = BuiltInWindowFunction::from_str(name) {
+ Ok(WindowFunction::BuiltInWindowFunction(built_in_function))
+ } else {
+ Err(DataFusionError::Plan(format!(
+ "There is no built-in function named {}",
+ name
+ )))
+ }
+ }
+}
+
+impl fmt::Display for BuiltInWindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ // uppercase of the debug.
+ write!(f, "{}", format!("{:?}", self).to_uppercase())
+ }
+}
+
+impl fmt::Display for WindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ WindowFunction::AggregateFunction(fun) => fun.fmt(f),
+ WindowFunction::BuiltInWindowFunction(fun) => fun.fmt(f),
+ }
+ }
+}
+
+/// An aggregate function that is part of a built-in window function
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum BuiltInWindowFunction {
+ /// row number
+ RowNumber,
+ /// rank
+ Rank,
+ /// dense rank
+ DenseRank,
+ /// lag
+ Lag,
+ /// lead
+ Lead,
+ /// first value
+ FirstValue,
+ /// last value
+ LastValue,
+}
+
+impl FromStr for BuiltInWindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<BuiltInWindowFunction> {
+ Ok(match name {
+ "row_number" => BuiltInWindowFunction::RowNumber,
+ "rank" => BuiltInWindowFunction::Rank,
+ "dense_rank" => BuiltInWindowFunction::DenseRank,
+ "first_value" => BuiltInWindowFunction::FirstValue,
+ "last_value" => BuiltInWindowFunction::LastValue,
+ "lag" => BuiltInWindowFunction::Lag,
+ "lead" => BuiltInWindowFunction::Lead,
+ _ => {
+ return Err(DataFusionError::Plan(format!(
+ "There is no built-in function named {}",
Review comment:
```suggestion
"There is no built-in window function named {}",
```
##########
File path: datafusion/src/logical_plan/builder.rs
##########
@@ -289,6 +288,37 @@ impl LogicalPlanBuilder {
}))
}
+ /// Apply a window
+ pub fn window(
+ &self,
+ window_expr: impl IntoIterator<Item = Expr>,
+ // partition_by_expr: impl IntoIterator<Item = Expr>,
+ // order_by_expr: impl IntoIterator<Item = Expr>,
+ // window_frame: Option<WindowFrame>,
+ ) -> Result<Self> {
+ let window_expr = window_expr.into_iter().collect::<Vec<Expr>>();
+ // let partition_by_expr = partition_by_expr.into_iter().collect::<Vec<Expr>>();
+ // let order_by_expr = order_by_expr.into_iter().collect::<Vec<Expr>>();
+ let all_expr = window_expr.iter();
+ validate_unique_names("Windows", all_expr.clone(), self.plan.schema())?;
+
+ let mut window_fields: Vec<DFField> =
+ exprlist_to_fields(all_expr, self.plan.schema())?;
+ window_fields.extend_from_slice(self.plan.schema().fields());
+
+ Ok(Self::from(&LogicalPlan::Window {
+ input: Arc::new(self.plan.clone()),
+ // FIXME implement next
+ // partition_by_expr,
+ // FIXME implement next
+ // order_by_expr,
+ // FIXME implement next
+ // window_frame,
+ window_expr,
Review comment:
likewise here it may make sense to have a filter clause (FIXME) too
##########
File path: datafusion/src/physical_plan/window_functions.rs
##########
@@ -0,0 +1,149 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+//! Window functions provide the ability to perform calculations across
+//! sets of rows that are related to the current query row.
+//!
+//! see also https://www.postgresql.org/docs/current/functions-window.html
+
+use crate::error::{DataFusionError, Result};
+use crate::physical_plan::{
+ aggregates, aggregates::AggregateFunction, functions::Signature,
+ type_coercion::data_types,
+};
+use arrow::datatypes::DataType;
+use std::{fmt, str::FromStr};
+
+/// WindowFunction
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum WindowFunction {
+ /// window function that leverages an aggregate function
+ AggregateFunction(AggregateFunction),
+ /// window function that leverages a built-in window function
+ BuiltInWindowFunction(BuiltInWindowFunction),
+}
+
+impl FromStr for WindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<WindowFunction> {
+ if let Ok(aggregate) = AggregateFunction::from_str(name) {
+ Ok(WindowFunction::AggregateFunction(aggregate))
+ } else if let Ok(built_in_function) = BuiltInWindowFunction::from_str(name) {
+ Ok(WindowFunction::BuiltInWindowFunction(built_in_function))
+ } else {
+ Err(DataFusionError::Plan(format!(
+ "There is no built-in function named {}",
+ name
+ )))
+ }
+ }
+}
+
+impl fmt::Display for BuiltInWindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ // uppercase of the debug.
+ write!(f, "{}", format!("{:?}", self).to_uppercase())
+ }
+}
+
+impl fmt::Display for WindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ WindowFunction::AggregateFunction(fun) => fun.fmt(f),
+ WindowFunction::BuiltInWindowFunction(fun) => fun.fmt(f),
+ }
+ }
+}
+
+/// An aggregate function that is part of a built-in window function
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum BuiltInWindowFunction {
+ /// row number
+ RowNumber,
+ /// rank
+ Rank,
+ /// dense rank
+ DenseRank,
+ /// lag
+ Lag,
+ /// lead
+ Lead,
+ /// first value
+ FirstValue,
+ /// last value
+ LastValue,
+}
Review comment:
Would it be possible to include all the built in window functions (and generate errors for those that are not supported)?
Comparing this list and https://www.postgresql.org/docs/9.1/functions-window.html it seems like `percent_rank`, `cume_dist`, `ntile`, `nth_value` are not present.
I am not suggesting you need to implement those functions, merely add space for them in the enum. Once you have set the pattern of how to implement such functions, those additional functions would make for good "first time contributor" type tickets for people who want to help out.
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
I think SQL is confusing in this area -- as @Jimexist says, all "normal" aggregate functions (e.g. sum, count, etc) are also valid window functions, but the reverse is not true. You can't use window functions (e.g. LAG, LEAD, etc) outside of a window clause.
Thus I think representing window functions as a new type of function, as this PR does, makes the most sense. They are different enough (e.g. require information on the incoming windows) that trying to wrangle them into the same structures as normal aggregates seems like it will get messy. Long term I would expect we have a UDWF (user defined window function) api as well.
Ideally the physical implementation for `sum` / `count` / etc can be mostly reused but in the plans I think they are different enough to warrant different plan structures.
##########
File path: datafusion/src/sql/planner.rs
##########
@@ -2641,13 +2701,23 @@ mod tests {
}
#[test]
- fn over_not_supported() {
+ fn empty_over() {
Review comment:
👍 Great
##########
File path: datafusion/src/physical_plan/windows.rs
##########
@@ -0,0 +1,195 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+//! Execution plan for window functions
+
+use crate::error::{DataFusionError, Result};
+use crate::physical_plan::{
+ aggregates, window_functions::WindowFunction, AggregateExpr, Distribution,
+ ExecutionPlan, Partitioning, PhysicalExpr, SendableRecordBatchStream, WindowExpr,
+};
+use arrow::datatypes::{Field, Schema, SchemaRef};
+use async_trait::async_trait;
+use std::any::Any;
+use std::sync::Arc;
+
+/// Window execution plan
+#[derive(Debug)]
+pub struct WindowAggExec {
+ /// Input plan
+ input: Arc<dyn ExecutionPlan>,
+ /// Window function expression
+ window_expr: Vec<Arc<dyn WindowExpr>>,
+ /// Schema after the window is run
+ schema: SchemaRef,
+ /// Schema before the window
+ input_schema: SchemaRef,
+}
+
+/// Create a physical expression for window function
+pub fn create_window_expr(
+ fun: &WindowFunction,
+ args: &[Arc<dyn PhysicalExpr>],
+ input_schema: &Schema,
+ name: String,
+) -> Result<Arc<dyn WindowExpr>> {
+ match fun {
+ WindowFunction::AggregateFunction(fun) => Ok(Arc::new(AggregateWindowExpr {
+ aggregate: aggregates::create_aggregate_expr(
+ fun,
+ false,
+ args,
+ input_schema,
+ name,
+ )?,
+ })),
+ WindowFunction::BuiltInWindowFunction(fun) => {
+ Err(DataFusionError::NotImplemented(format!(
+ "window funtion with {:?} not implemented",
+ fun
+ )))
+ }
+ }
+}
+
+/// A window expr that takes the form of a built in window function
+#[derive(Debug)]
+pub struct BuiltInWindowExpr {}
+
+/// A window expr that takes the form of an aggregate function
+#[derive(Debug)]
+pub struct AggregateWindowExpr {
+ aggregate: Arc<dyn AggregateExpr>,
+}
+
+impl WindowExpr for AggregateWindowExpr {
+ /// Return a reference to Any that can be used for downcasting
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn name(&self) -> &str {
+ &self.aggregate.name()
+ }
+
+ fn field(&self) -> Result<Field> {
+ self.aggregate.field()
+ }
+}
+
+fn create_schema(
+ input_schema: &Schema,
+ window_expr: &[Arc<dyn WindowExpr>],
+) -> Result<Schema> {
+ let mut fields = Vec::with_capacity(input_schema.fields().len() + window_expr.len());
+ for expr in window_expr {
+ fields.push(expr.field()?);
+ }
+ fields.extend_from_slice(input_schema.fields());
+ Ok(Schema::new(fields))
+}
+
+impl WindowAggExec {
+ /// Create a new execution plan for window aggregates
+ pub fn try_new(
+ window_expr: Vec<Arc<dyn WindowExpr>>,
+ input: Arc<dyn ExecutionPlan>,
+ input_schema: SchemaRef,
+ ) -> Result<Self> {
+ let schema = create_schema(&input.schema(), &window_expr)?;
+ let schema = Arc::new(schema);
+ Ok(WindowAggExec {
+ input,
+ window_expr,
+ schema,
+ input_schema,
+ })
+ }
+
+ /// Input plan
+ pub fn input(&self) -> &Arc<dyn ExecutionPlan> {
+ &self.input
+ }
+
+ /// Get the input schema before any aggregates are applied
+ pub fn input_schema(&self) -> SchemaRef {
+ self.input_schema.clone()
+ }
+}
+
+#[async_trait]
+impl ExecutionPlan for WindowAggExec {
+ /// Return a reference to Any that can be used for downcasting
+ fn as_any(&self) -> &dyn Any {
+ self
+ }
+
+ fn schema(&self) -> SchemaRef {
+ self.schema.clone()
+ }
+
+ fn children(&self) -> Vec<Arc<dyn ExecutionPlan>> {
+ vec![self.input.clone()]
+ }
+
+ /// Get the output partitioning of this plan
+ fn output_partitioning(&self) -> Partitioning {
+ Partitioning::UnknownPartitioning(1)
+ }
+
+ fn required_child_distribution(&self) -> Distribution {
+ Distribution::SinglePartition
+ }
+
+ fn with_new_children(
+ &self,
+ children: Vec<Arc<dyn ExecutionPlan>>,
+ ) -> Result<Arc<dyn ExecutionPlan>> {
+ match children.len() {
+ 1 => Ok(Arc::new(WindowAggExec::try_new(
+ self.window_expr.clone(),
+ children[0].clone(),
+ children[0].schema(),
+ )?)),
+ _ => Err(DataFusionError::Internal(
+ "WindowAggExec wrong number of children".to_owned(),
+ )),
+ }
+ }
+
+ async fn execute(&self, partition: usize) -> Result<SendableRecordBatchStream> {
+ if 0 != partition {
+ return Err(DataFusionError::Internal(format!(
+ "WindowAggExec invalid partition {}",
+ partition
+ )));
+ }
+
+ // window needs to operate on a single partition currently
Review comment:
👍
Eventually it would be cool to push the partitioning expressions into a `RepartitionExec` so that we can execute the window functions in parallel on different windows but that is definitely an optimization for the future (not this initial PR) :)
##########
File path: datafusion/src/physical_plan/window_functions.rs
##########
@@ -0,0 +1,149 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+//! Window functions provide the ability to perform calculations across
+//! sets of rows that are related to the current query row.
+//!
+//! see also https://www.postgresql.org/docs/current/functions-window.html
+
+use crate::error::{DataFusionError, Result};
+use crate::physical_plan::{
+ aggregates, aggregates::AggregateFunction, functions::Signature,
+ type_coercion::data_types,
+};
+use arrow::datatypes::DataType;
+use std::{fmt, str::FromStr};
+
+/// WindowFunction
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum WindowFunction {
+ /// window function that leverages an aggregate function
+ AggregateFunction(AggregateFunction),
+ /// window function that leverages a built-in window function
+ BuiltInWindowFunction(BuiltInWindowFunction),
+}
+
+impl FromStr for WindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<WindowFunction> {
+ if let Ok(aggregate) = AggregateFunction::from_str(name) {
+ Ok(WindowFunction::AggregateFunction(aggregate))
+ } else if let Ok(built_in_function) = BuiltInWindowFunction::from_str(name) {
+ Ok(WindowFunction::BuiltInWindowFunction(built_in_function))
+ } else {
+ Err(DataFusionError::Plan(format!(
+ "There is no built-in function named {}",
+ name
+ )))
+ }
+ }
+}
+
+impl fmt::Display for BuiltInWindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ // uppercase of the debug.
+ write!(f, "{}", format!("{:?}", self).to_uppercase())
+ }
+}
+
+impl fmt::Display for WindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ WindowFunction::AggregateFunction(fun) => fun.fmt(f),
+ WindowFunction::BuiltInWindowFunction(fun) => fun.fmt(f),
+ }
+ }
+}
+
+/// An aggregate function that is part of a built-in window function
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum BuiltInWindowFunction {
+ /// row number
+ RowNumber,
+ /// rank
+ Rank,
+ /// dense rank
+ DenseRank,
+ /// lag
+ Lag,
+ /// lead
+ Lead,
+ /// first value
+ FirstValue,
+ /// last value
+ LastValue,
+}
+
+impl FromStr for BuiltInWindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<BuiltInWindowFunction> {
+ Ok(match name {
+ "row_number" => BuiltInWindowFunction::RowNumber,
+ "rank" => BuiltInWindowFunction::Rank,
+ "dense_rank" => BuiltInWindowFunction::DenseRank,
+ "first_value" => BuiltInWindowFunction::FirstValue,
+ "last_value" => BuiltInWindowFunction::LastValue,
+ "lag" => BuiltInWindowFunction::Lag,
+ "lead" => BuiltInWindowFunction::Lead,
+ _ => {
+ return Err(DataFusionError::Plan(format!(
+ "There is no built-in function named {}",
+ name
+ )))
+ }
+ })
+ }
+}
+
+/// Returns the datatype of the scalar function
Review comment:
```suggestion
/// Returns the datatype of the window function
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845188351
> I went over this PR quite carefully. Thank you very much @Jimexist for the contribution! ❤️
> ❤️ -- this PR looks to be in great shape.
>
> All I think this PR needs is a few more tests and it could be merged in.
>
> I am not familiar with the ballista code, but it looked ok to me. @andygrove do you have any suggestions of who might be interested in those changes?
thanks for the comment! @andygrove I am not 100% sure of the `oneof` usage in the `window_frame` field, so I guess that's one place to look at.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
alamb commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845107623
Checking it out...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] jorgecarleitao commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
jorgecarleitao commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636596927
##########
File path: datafusion/src/logical_plan/builder.rs
##########
@@ -289,6 +288,52 @@ impl LogicalPlanBuilder {
}))
}
+ /// Apply a window
+ ///
+ /// NOTE: this feature is under development and this API will be changing
+ ///
+ /// - https://github.com/apache/arrow-datafusion/issues/359 basic structure
+ /// - https://github.com/apache/arrow-datafusion/issues/298 empty over clause
+ /// - https://github.com/apache/arrow-datafusion/issues/299 with partition clause
+ /// - https://github.com/apache/arrow-datafusion/issues/360 with order by
+ /// - https://github.com/apache/arrow-datafusion/issues/361 with window frame
+ pub fn window(
+ &self,
+ window_expr: impl IntoIterator<Item = Expr>,
Review comment:
I am not sure we should use `IntoIterator<Item = Expr>` for every field with 6 fields. This will produce a version of the compiled function for every combination, which IMO adds an unnecessary compile time and binary size.
`IntoIterator` is more relevant when we want to avoid an extra allocation; these are very small vectors.
##########
File path: datafusion/src/physical_plan/aggregates.rs
##########
@@ -183,7 +182,7 @@ static TIMESTAMPS: &[DataType] = &[
];
/// the signatures supported by the function `fun`.
-fn signature(fun: &AggregateFunction) -> Signature {
+pub fn signature(fun: &AggregateFunction) -> Signature {
Review comment:
`pub(super)` or `pub(crate)` instead?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636212671
##########
File path: datafusion/src/physical_plan/window_functions.rs
##########
@@ -0,0 +1,149 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+//! Window functions provide the ability to perform calculations across
+//! sets of rows that are related to the current query row.
+//!
+//! see also https://www.postgresql.org/docs/current/functions-window.html
+
+use crate::error::{DataFusionError, Result};
+use crate::physical_plan::{
+ aggregates, aggregates::AggregateFunction, functions::Signature,
+ type_coercion::data_types,
+};
+use arrow::datatypes::DataType;
+use std::{fmt, str::FromStr};
+
+/// WindowFunction
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum WindowFunction {
+ /// window function that leverages an aggregate function
+ AggregateFunction(AggregateFunction),
+ /// window function that leverages a built-in window function
+ BuiltInWindowFunction(BuiltInWindowFunction),
+}
+
+impl FromStr for WindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<WindowFunction> {
+ if let Ok(aggregate) = AggregateFunction::from_str(name) {
+ Ok(WindowFunction::AggregateFunction(aggregate))
+ } else if let Ok(built_in_function) = BuiltInWindowFunction::from_str(name) {
+ Ok(WindowFunction::BuiltInWindowFunction(built_in_function))
+ } else {
+ Err(DataFusionError::Plan(format!(
+ "There is no built-in function named {}",
+ name
+ )))
+ }
+ }
+}
+
+impl fmt::Display for BuiltInWindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ // uppercase of the debug.
+ write!(f, "{}", format!("{:?}", self).to_uppercase())
+ }
+}
+
+impl fmt::Display for WindowFunction {
+ fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
+ match self {
+ WindowFunction::AggregateFunction(fun) => fun.fmt(f),
+ WindowFunction::BuiltInWindowFunction(fun) => fun.fmt(f),
+ }
+ }
+}
+
+/// An aggregate function that is part of a built-in window function
+#[derive(Debug, Clone, PartialEq, Eq)]
+pub enum BuiltInWindowFunction {
+ /// row number
+ RowNumber,
+ /// rank
+ Rank,
+ /// dense rank
+ DenseRank,
+ /// lag
+ Lag,
+ /// lead
+ Lead,
+ /// first value
+ FirstValue,
+ /// last value
+ LastValue,
+}
+
+impl FromStr for BuiltInWindowFunction {
+ type Err = DataFusionError;
+ fn from_str(name: &str) -> Result<BuiltInWindowFunction> {
Review comment:
done
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845253719
> Thanks @Jimexist -- I still think adding coverage for the cases I mentioned in https://github.com/apache/arrow-datafusion/pull/334/files#r636122381 would be good, but I am also comfortable with you doing so as a follow on pR.
>
> Thanks again -- I vote to merge this on green CI
yeah let me add test to sql.rs when the actual physical exec and result stream is done, in next PR
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist edited a comment on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist edited a comment on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845184684
> I haven't had the time to go through yet;
>
> Since this is a large decomposable change, a suggestion here is to create a branch and merge this on that branch, so that we do not have incomplete features in master and allow PRs to that branch without an issue. Then merge the branch into master once its first iteration is ready (e.g. logical and 1 physical plan).
>
> (This is something that I wished to have when implementing large features; it gives the time to review in parts without the risk of having incomplete code in master)
>
> With that said, I am also fine risk it and merg to master; @Jimexist do you have any preference how would you prefer to work on this here? :)
I wish to:
1. add unit tests
2. fix some small comments, per @alamb, like
1. adding filter clause to leave room for future room without API disruption,
2. adding nth_value and tile and leaving them unimplemented and leave room for good first PR
3. merge it into master as it
3 because although I agree with having a feature branch, the maintenance of continuous rebasing would be troublesome, and I can estimate the whole series of window function implementations take > 1 month. if I can keep the merged code _isolated_ and _structurally complete_ then there's little worry about breaking changes in future: I believe this PR:
1. is _isolated_ because the _explain_ works (thus mostly the API is determined), while actually executing the window SQL returns zero rows, due to unimplemented exec plan, which I intend to do next
2. is _structurally complete_ if reviewers can focus on the phase and timing of the window clause in the query planner. future changes of e.g. adding one or more sort phases requires only modifications within the `.window` `fn` and thus can be non-intrusive
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Dandandan commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r635518750
##########
File path: ballista/rust/core/proto/ballista.proto
##########
@@ -151,6 +153,25 @@ message AggregateExprNode {
LogicalExprNode expr = 2;
}
+enum BuiltInWindowFunction {
+ ROW_NUMBER = 0;
+ RANK = 1;
+ DENSE_RANK = 2;
+ LAG = 3;
+ LEAD = 4;
+ FIRST_VALUE = 5;
+ LAST_VALUE = 6;
+}
+
+message WindowExprNode {
+ oneof window_function {
+ AggregateFunction aggr_function = 1;
Review comment:
I checked whether this makes sense to reuse aggregate functions for window expressions - I think it does! E.g. PostgreSQL also says:
https://www.postgresql.org/docs/9.3/functions-window.html
> In addition to these functions, any built-in or user-defined aggregate function can be used as a window function (see Section 9.20 for a list of the built-in aggregates). Aggregate functions act as window functions only when an OVER clause follows the call; otherwise they act as regular aggregates.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on a change in pull request #334: Add window expr
Posted by GitBox <gi...@apache.org>.
alamb commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r632720978
##########
File path: datafusion/src/logical_plan/builder.rs
##########
@@ -289,6 +289,14 @@ impl LogicalPlanBuilder {
}))
}
+ pub fn window(
+ &self,
+ group_expr: impl IntoIterator<Item = Expr>,
+ aggr_expr: impl IntoIterator<Item = Expr>,
Review comment:
would eventually need sort_exprs too to handle the various window clauses
Maybe worth taking a peek at how the `sqlparser` crate structures things so the API on the builder is "future proof" (aka we don't have to break backwards compatibility when we add support for non empty over clause)
https://github.com/ballista-compute/sqlparser-rs/blob/35ef0eee3857c59827b2587bc813218a00dce264/src/ast/mod.rs#L372-L376
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Jimexist commented on pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
Jimexist commented on pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#issuecomment-845237253
@alamb @jorgecarleitao updated
```
> explain select c1, max(c3) over (), avg(c3) over () from test;
+--------------+----------------------------------------------------------------------------+
| plan_type | plan |
+--------------+----------------------------------------------------------------------------+
| logical_plan | Projection: #c1, #MAX(c3), #AVG(c3) |
| | WindowAggr: windowExpr=[[MAX(#c3), AVG(#c3)]] partitionBy=[], orderBy=[] |
| | TableScan: test projection=None |
+--------------+----------------------------------------------------------------------------+
1 row in set. Query took 0 seconds.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on a change in pull request #334: Add window expression part 1 - logical and physical planning, structure, to/from proto, and explain, for empty over clause only
Posted by GitBox <gi...@apache.org>.
alamb commented on a change in pull request #334:
URL: https://github.com/apache/arrow-datafusion/pull/334#discussion_r636132473
##########
File path: ballista/rust/core/src/serde/logical_plan/from_proto.rs
##########
@@ -75,6 +76,33 @@ impl TryInto<LogicalPlan> for &protobuf::LogicalPlanNode {
.build()
.map_err(|e| e.into())
}
+ LogicalPlanType::Window(window) => {
+ let input: LogicalPlan = convert_box_required!(window.input)?;
+ let window_expr = window
+ .window_expr
+ .iter()
+ .map(|expr| expr.try_into())
+ .collect::<Result<Vec<_>, _>>()?;
+
+ // let partition_by_expr = window
+ // .partition_by_expr
+ // .iter()
+ // .map(|expr| expr.try_into())
+ // .collect::<Result<Vec<_>, _>>()?;
+ // let order_by_expr = window
+ // .order_by_expr
+ // .iter()
+ // .map(|expr| expr.try_into())
+ // .collect::<Result<Vec<_>, _>>()?;
+ // // FIXME parse the window_frame data
Review comment:
I think @Jimexist plans to implement the feature in several PRs as outlined in the PR description
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org