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/06/03 16:13:06 UTC
[GitHub] [arrow-datafusion] Dandandan commented on a change in pull request #463: add `order by` construct in window function and logical plans
Dandandan commented on a change in pull request #463:
URL: https://github.com/apache/arrow-datafusion/pull/463#discussion_r644934936
##########
File path: datafusion/src/sql/planner.rs
##########
@@ -2749,14 +2772,139 @@ mod tests {
);
}
+ /// psql result
+ /// ```
+ /// QUERY PLAN
+ /// ----------------------------------------------------------------------------------
+ /// WindowAgg (cost=137.16..154.66 rows=1000 width=12)
+ /// -> Sort (cost=137.16..139.66 rows=1000 width=12)
+ /// Sort Key: order_id
+ /// -> WindowAgg (cost=69.83..87.33 rows=1000 width=12)
+ /// -> Sort (cost=69.83..72.33 rows=1000 width=8)
+ /// Sort Key: order_id DESC
+ /// -> Seq Scan on orders (cost=0.00..20.00 rows=1000 width=8)
+ /// ```
+ #[test]
+ fn over_order_by() {
+ let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY order_id), MIN(qty) OVER (ORDER BY order_id DESC) from orders";
+ let expected = "\
+ Projection: #order_id, #MAX(qty), #MIN(qty)\
+ \n WindowAggr: windowExpr=[[MAX(#qty)]] partitionBy=[]\
+ \n Sort: #order_id ASC NULLS FIRST\
+ \n WindowAggr: windowExpr=[[MIN(#qty)]] partitionBy=[]\
+ \n Sort: #order_id DESC NULLS FIRST\
+ \n TableScan: orders projection=None";
+ quick_test(sql, expected);
+ }
+
+ /// psql result
+ /// ```
+ /// QUERY PLAN
+ /// -----------------------------------------------------------------------------------
+ /// WindowAgg (cost=142.16..162.16 rows=1000 width=16)
+ /// -> Sort (cost=142.16..144.66 rows=1000 width=16)
+ /// Sort Key: order_id
+ /// -> WindowAgg (cost=72.33..92.33 rows=1000 width=16)
+ /// -> Sort (cost=72.33..74.83 rows=1000 width=12)
+ /// Sort Key: ((order_id + 1))
+ /// -> Seq Scan on orders (cost=0.00..22.50 rows=1000 width=12)
+ /// ```
+ #[test]
+ fn over_order_by_two_sort_keys() {
+ let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY order_id), MIN(qty) OVER (ORDER BY (order_id + 1)) from orders";
+ let expected = "\
+ Projection: #order_id, #MAX(qty), #MIN(qty)\
+ \n WindowAggr: windowExpr=[[MAX(#qty)]] partitionBy=[]\
+ \n Sort: #order_id ASC NULLS FIRST\
+ \n WindowAggr: windowExpr=[[MIN(#qty)]] partitionBy=[]\
+ \n Sort: #order_id Plus Int64(1) ASC NULLS FIRST\
+ \n TableScan: orders projection=None";
+ quick_test(sql, expected);
+ }
+
+ /// psql result
+ /// ```
+ /// QUERY PLAN
+ /// ----------------------------------------------------------------------------------------
+ /// WindowAgg (cost=139.66..172.16 rows=1000 width=24)
+ /// -> WindowAgg (cost=139.66..159.66 rows=1000 width=16)
+ /// -> Sort (cost=139.66..142.16 rows=1000 width=12)
+ /// Sort Key: qty, order_id
+ /// -> WindowAgg (cost=69.83..89.83 rows=1000 width=12)
+ /// -> Sort (cost=69.83..72.33 rows=1000 width=8)
+ /// Sort Key: order_id, qty
+ /// -> Seq Scan on orders (cost=0.00..20.00 rows=1000 width=8)
+ /// ```
+ #[test]
+ fn over_order_by_sort_keys_sorting() {
+ let sql = "SELECT order_id, MAX(qty) OVER (ORDER BY qty, order_id), SUM(qty) OVER (), MIN(qty) OVER (ORDER BY order_id, qty) from orders";
+ let expected = "\
+ Projection: #order_id, #MAX(qty), #SUM(qty), #MIN(qty)\
+ \n WindowAggr: windowExpr=[[SUM(#qty)]] partitionBy=[]\
+ \n WindowAggr: windowExpr=[[MAX(#qty)]] partitionBy=[]\
+ \n Sort: #qty ASC NULLS FIRST, #order_id ASC NULLS FIRST\
+ \n WindowAggr: windowExpr=[[MIN(#qty)]] partitionBy=[]\
+ \n Sort: #order_id ASC NULLS FIRST, #qty ASC NULLS FIRST\
Review comment:
Might be good to remove the defaults (i.e. nulls first)
--
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