Jimexist commented on a change in pull request #463:
URL: https://github.com/apache/arrow-datafusion/pull/463#discussion_r644407945
##########
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\
+ \n TableScan: orders projection=None";
+ quick_test(sql, expected);
+ }
+
+ /// psql result
+ /// ```
+ /// QUERY PLAN
+ ///
----------------------------------------------------------------------------------
+ /// WindowAgg (cost=69.83..117.33 rows=1000 width=24)
+ /// -> WindowAgg (cost=69.83..104.83 rows=1000 width=16)
+ /// -> 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)
+ /// ```
+ ///
+ /// FIXME: for now we are not detecting prefix of sorting keys in order to
save one sort exec phase
Review comment:
lots of room for optimization. there are several considerations when it
comes to:
1. how to compute order by and partition by and re-order them to optimize
2. how to compute window aggregations given a possibility of either a
ever-growing window or a shifting window (that can shrink and expand, depending
on # of rows or the absolute values)
--
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:
us...@infra.apache.org
