fix_groups_mode_window_func.patch
application/octet-stream
Filename: fix_groups_mode_window_func.patch
Type: application/octet-stream
Part: 0
Patch
Format: unified
| File | + | − |
|---|---|---|
| src/backend/executor/nodeWindowAgg.c | 1 | 2 |
| src/test/regress/expected/window.out | 221 | 0 |
| src/test/regress/sql/window.sql | 52 | 0 |
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 968d5d3..947f802 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -1190,8 +1190,7 @@ begin_partition(WindowAggState *winstate)
*/
winstate->framehead_ptr = winstate->frametail_ptr = -1; /* if not used */
- if ((winstate->frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS)) &&
- node->ordNumCols != 0)
+ if (winstate->frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS))
{
if (!(winstate->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING))
winstate->framehead_ptr =
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 85d81e7..7c21a86 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -2491,6 +2491,227 @@ FROM tenk1 WHERE unique1 < 10;
30 | 7 | 3
(10 rows)
+SELECT sum(unique1) over (partition by four groups between unbounded preceding and current row),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 12 | 0 | 0
+ 12 | 8 | 0
+ 12 | 4 | 0
+ 15 | 5 | 1
+ 15 | 9 | 1
+ 15 | 1 | 1
+ 8 | 6 | 2
+ 8 | 2 | 2
+ 10 | 3 | 3
+ 10 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between unbounded preceding and unbounded following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 12 | 0 | 0
+ 12 | 8 | 0
+ 12 | 4 | 0
+ 15 | 5 | 1
+ 15 | 9 | 1
+ 15 | 1 | 1
+ 8 | 6 | 2
+ 8 | 2 | 2
+ 10 | 3 | 3
+ 10 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between current row and unbounded following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 12 | 0 | 0
+ 12 | 8 | 0
+ 12 | 4 | 0
+ 15 | 5 | 1
+ 15 | 9 | 1
+ 15 | 1 | 1
+ 8 | 6 | 2
+ 8 | 2 | 2
+ 10 | 3 | 3
+ 10 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between 1 preceding and unbounded following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 12 | 0 | 0
+ 12 | 8 | 0
+ 12 | 4 | 0
+ 15 | 5 | 1
+ 15 | 9 | 1
+ 15 | 1 | 1
+ 8 | 6 | 2
+ 8 | 2 | 2
+ 10 | 3 | 3
+ 10 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between 1 following and unbounded following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ | 0 | 0
+ | 8 | 0
+ | 4 | 0
+ | 5 | 1
+ | 9 | 1
+ | 1 | 1
+ | 6 | 2
+ | 2 | 2
+ | 3 | 3
+ | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between unbounded preceding and 2 following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 12 | 0 | 0
+ 12 | 8 | 0
+ 12 | 4 | 0
+ 15 | 5 | 1
+ 15 | 9 | 1
+ 15 | 1 | 1
+ 8 | 6 | 2
+ 8 | 2 | 2
+ 10 | 3 | 3
+ 10 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 preceding),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ | 0 | 0
+ | 8 | 0
+ | 4 | 0
+ | 5 | 1
+ | 9 | 1
+ | 1 | 1
+ | 6 | 2
+ | 2 | 2
+ | 3 | 3
+ | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 12 | 0 | 0
+ 12 | 8 | 0
+ 12 | 4 | 0
+ 15 | 5 | 1
+ 15 | 9 | 1
+ 15 | 1 | 1
+ 8 | 6 | 2
+ 8 | 2 | 2
+ 10 | 3 | 3
+ 10 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between 0 preceding and 0 following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 12 | 0 | 0
+ 12 | 8 | 0
+ 12 | 4 | 0
+ 15 | 5 | 1
+ 15 | 9 | 1
+ 15 | 1 | 1
+ 8 | 6 | 2
+ 8 | 2 | 2
+ 10 | 3 | 3
+ 10 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 following
+ exclude current row), unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 12 | 0 | 0
+ 4 | 8 | 0
+ 8 | 4 | 0
+ 10 | 5 | 1
+ 6 | 9 | 1
+ 14 | 1 | 1
+ 2 | 6 | 2
+ 6 | 2 | 2
+ 7 | 3 | 3
+ 3 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 following
+ exclude group), unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ | 0 | 0
+ | 8 | 0
+ | 4 | 0
+ | 5 | 1
+ | 9 | 1
+ | 1 | 1
+ | 6 | 2
+ | 2 | 2
+ | 3 | 3
+ | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 following
+ exclude ties), unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 0 | 0 | 0
+ 8 | 8 | 0
+ 4 | 4 | 0
+ 5 | 5 | 1
+ 9 | 9 | 1
+ 1 | 1 | 1
+ 6 | 6 | 2
+ 2 | 2 | 2
+ 3 | 3 | 3
+ 7 | 7 | 3
+(10 rows)
+
+SELECT sum(unique1) over (groups between 2 preceding and 1 following
+ exclude ties), unique1, four
+FROM tenk1 WHERE unique1 < 10;
+ sum | unique1 | four
+-----+---------+------
+ 4 | 4 | 0
+ 2 | 2 | 2
+ 1 | 1 | 1
+ 6 | 6 | 2
+ 9 | 9 | 1
+ 8 | 8 | 0
+ 5 | 5 | 1
+ 3 | 3 | 3
+ 7 | 7 | 3
+ 0 | 0 | 0
+(10 rows)
+
SELECT sum(unique1) over (partition by ten
order by four groups between 0 preceding and 0 following),unique1, four, ten
FROM tenk1 WHERE unique1 < 10;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 051b50b..4abb352 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -709,6 +709,58 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
exclude ties), unique1, four
FROM tenk1 WHERE unique1 < 10;
+SELECT sum(unique1) over (partition by four groups between unbounded preceding and current row),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between unbounded preceding and unbounded following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between current row and unbounded following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between 1 preceding and unbounded following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between 1 following and unbounded following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between unbounded preceding and 2 following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 preceding),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between 0 preceding and 0 following),
+ unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 following
+ exclude current row), unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 following
+ exclude group), unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (partition by four groups between 2 preceding and 1 following
+ exclude ties), unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
+SELECT sum(unique1) over (groups between 2 preceding and 1 following
+ exclude ties), unique1, four
+FROM tenk1 WHERE unique1 < 10;
+
SELECT sum(unique1) over (partition by ten
order by four groups between 0 preceding and 0 following),unique1, four, ten
FROM tenk1 WHERE unique1 < 10;