Regarding v6 patch:
> SELECT company, tdate, price,
> first_value(price) OVER w,
> last_value(price) OVER w,
> max(price) OVER w,
> min(price) OVER w,
> sum(price) OVER w,
> avg(price) OVER w,
> count(price) OVER w
> FROM stock
> WINDOW w AS (
> PARTITION BY company
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> AFTER MATCH SKIP PAST LAST ROW
> INITIAL
> PATTERN (START UP+ DOWN+)
> DEFINE
> START AS TRUE,
> UP AS price > PREV(price),
> DOWN AS price < PREV(price)
> );
> company | tdate | price | first_value | last_value | max | min | sum
> | avg | count
> ----------+------------+-------+-------------+------------+------+-----+------+-----------------------+-------
> company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590
> | 147.5000000000000000 | 4
> company1 | 07-02-2023 | 200 | | | | |
> | |
> company1 | 07-03-2023 | 150 | | | | |
> | |
> company1 | 07-04-2023 | 140 | | | | |
> | |
> company1 | 07-05-2023 | 150 | | | | |
> | |
> company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450
> | 112.5000000000000000 | 4
> company1 | 07-07-2023 | 110 | | | | |
> | |
> company1 | 07-08-2023 | 130 | | | | |
> | |
> company1 | 07-09-2023 | 120 | | | | |
> | |
> company1 | 07-10-2023 | 130 | | | | |
> | |
> company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950
> | 1237.5000000000000000 | 4
> company2 | 07-02-2023 | 2000 | | | | |
> | |
> company2 | 07-03-2023 | 1500 | | | | |
> | |
> company2 | 07-04-2023 | 1400 | | | | |
> | |
> company2 | 07-05-2023 | 1500 | | | | |
> | |
> company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660
> | 915.0000000000000000 | 4
> company2 | 07-07-2023 | 1100 | | | | |
> | |
> company2 | 07-08-2023 | 1300 | | | | |
> | |
> company2 | 07-09-2023 | 1200 | | | | |
> | |
> company2 | 07-10-2023 | 1300 | | | | |
> | |
> (20 rows)
count column for unmatched rows should have been 0, rather than
NULL. i.e.
company | tdate | price | first_value | last_value | max | min | sum |
avg | count
----------+------------+-------+-------------+------------+------+-----+------+-----------------------+-------
company1 | 07-01-2023 | 100 | 100 | 140 | 200 | 100 | 590 |
147.5000000000000000 | 4
company1 | 07-02-2023 | 200 | | | | | |
|
company1 | 07-03-2023 | 150 | | | | | |
|
company1 | 07-04-2023 | 140 | | | | | |
|
company1 | 07-05-2023 | 150 | | | | | |
| 0
company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 |
112.5000000000000000 | 4
company1 | 07-07-2023 | 110 | | | | | |
|
company1 | 07-08-2023 | 130 | | | | | |
|
company1 | 07-09-2023 | 120 | | | | | |
|
company1 | 07-10-2023 | 130 | | | | | |
| 0
company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 |
1237.5000000000000000 | 4
company2 | 07-02-2023 | 2000 | | | | | |
|
company2 | 07-03-2023 | 1500 | | | | | |
|
company2 | 07-04-2023 | 1400 | | | | | |
|
company2 | 07-05-2023 | 1500 | | | | | |
| 0
company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 |
915.0000000000000000 | 4
company2 | 07-07-2023 | 1100 | | | | | |
|
company2 | 07-08-2023 | 1300 | | | | | |
|
company2 | 07-09-2023 | 1200 | | | | | |
|
company2 | 07-10-2023 | 1300 | | | | | |
| 0
(20 rows)
Attached is the fix against v6 patch. I will include this in upcoming v7 patch.
Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 32270d051a..2b78cb6722 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -968,12 +968,12 @@ eval_windowaggregates(WindowAggState *winstate)
{
/*
* If the skip mode is SKIP TO PAST LAST ROW and we already know that
- * current row is a skipped row or an unmatched row, we don't need to
- * accumulate rows, just return NULL.
+ * current row is a skipped row, we don't need to accumulate rows,
+ * just return NULL. Note that for unamtched row, we need to do
+ * aggregation so that count(*) shows 0, rather than NULL.
*/
if (winstate->rpSkipTo == ST_PAST_LAST_ROW &&
- (get_reduced_frame_map(winstate, winstate->currentpos) == RF_SKIPPED ||
- get_reduced_frame_map(winstate, winstate->currentpos) == RF_UNMATCHED))
+ get_reduced_frame_map(winstate, winstate->currentpos) == RF_SKIPPED)
agg_result_isnull = true;
}
@@ -1080,8 +1080,8 @@ next_tuple:
result, isnull);
/*
- * RPR is enabled and we just return NULL. because skip mode is SKIP
- * TO PAST LAST ROW and current row is skipped row or unmatched row.
+ * RPR is defined and we just return NULL because skip mode is SKIP
+ * TO PAST LAST ROW and current row is skipped row.
*/
if (agg_result_isnull)
{
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
index 63bed05f05..97bdc630d1 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -457,22 +457,22 @@ DOWN AS price < PREV(price)
company1 | 07-02-2023 | 200 | | | | | | |
company1 | 07-03-2023 | 150 | | | | | | |
company1 | 07-04-2023 | 140 | | | | | | |
- company1 | 07-05-2023 | 150 | | | | | | |
+ company1 | 07-05-2023 | 150 | | | | | | | 0
company1 | 07-06-2023 | 90 | 90 | 120 | 130 | 90 | 450 | 112.5000000000000000 | 4
company1 | 07-07-2023 | 110 | | | | | | |
company1 | 07-08-2023 | 130 | | | | | | |
company1 | 07-09-2023 | 120 | | | | | | |
- company1 | 07-10-2023 | 130 | | | | | | |
+ company1 | 07-10-2023 | 130 | | | | | | | 0
company2 | 07-01-2023 | 50 | 50 | 1400 | 2000 | 50 | 4950 | 1237.5000000000000000 | 4
company2 | 07-02-2023 | 2000 | | | | | | |
company2 | 07-03-2023 | 1500 | | | | | | |
company2 | 07-04-2023 | 1400 | | | | | | |
- company2 | 07-05-2023 | 1500 | | | | | | |
+ company2 | 07-05-2023 | 1500 | | | | | | | 0
company2 | 07-06-2023 | 60 | 60 | 1200 | 1300 | 60 | 3660 | 915.0000000000000000 | 4
company2 | 07-07-2023 | 1100 | | | | | | |
company2 | 07-08-2023 | 1300 | | | | | | |
company2 | 07-09-2023 | 1200 | | | | | | |
- company2 | 07-10-2023 | 1300 | | | | | | |
+ company2 | 07-10-2023 | 1300 | | | | | | | 0
(20 rows)
-- using AFTER MATCH SKIP TO NEXT ROW
