GitHub user avamingli added a comment to the discussion: Support Parallel Processing of Window Functions.
During develop, I found that the result of Window Agg without Order By clause is unstable. Referring to the SQL 2011 standard, it states that if ORDER BY is omitted, the order of rows in the partition is undefined. While using a window function without ORDER BY is valid, the resulting output seems unpredictable. ```sql SELECT sum(unique1) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), unique1, four FROM tenk1 WHERE unique1 < 10; ``` The case is in window.sql of regression. ```sql explain(costs off) SELECT sum(unique1) over (rows between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; QUERY PLAN -------------------------------------------------------------------- WindowAgg Window: w1 AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) -> Index Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 < 10) (4 rows) regression=# SELECT sum(unique1) over (rows between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; sum | unique1 | four -----+---------+------ 45 | 0 | 0 45 | 1 | 1 44 | 2 | 2 42 | 3 | 3 39 | 4 | 0 35 | 5 | 1 30 | 6 | 2 24 | 7 | 3 17 | 8 | 0 9 | 9 | 1 (10 rows) ``` However, after setting enable_indexscan = off, the results changed: ```sql regression=# set enable_indexscan = off; SET regression=# SELECT sum(unique1) over (rows between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; sum | unique1 | four -----+---------+------ 45 | 4 | 0 41 | 2 | 2 39 | 1 | 1 38 | 6 | 2 32 | 9 | 1 23 | 8 | 0 15 | 5 | 1 10 | 3 | 3 7 | 7 | 3 0 | 0 | 0 (10 rows) regression=# explain(costs off) SELECT sum(unique1) over (rows between current row and unbounded following), unique1, four FROM tenk1 WHERE unique1 < 10; QUERY PLAN -------------------------------------------------------------------- WindowAgg Window: w1 AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) -> Seq Scan on tenk1 Filter: (unique1 < 10) (4 rows) ``` And the parallel process of Window function make it worse. However, both results are correct. To pass parallel test cases, we need to modify the SQL in that case. GitHub link: https://github.com/apache/cloudberry/discussions/1216#discussioncomment-13773413 ---- This is an automatically sent email for dev@cloudberry.apache.org. To unsubscribe, please send an email to: dev-unsubscr...@cloudberry.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@cloudberry.apache.org For additional commands, e-mail: dev-h...@cloudberry.apache.org
