v4-0002-WIP-Adjust-regression-tests.patch
text/x-patch
Filename: v4-0002-WIP-Adjust-regression-tests.patch
Type: text/x-patch
Part: 1
Message:
Re: tuple radix sort
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v4-0002
Subject: WIP Adjust regression tests
| File | + | − |
|---|---|---|
| contrib/pg_stat_statements/expected/dml.out | 3 | 3 |
| contrib/postgres_fdw/expected/postgres_fdw.out | 4 | 4 |
| src/backend/utils/sort/tuplesort.c | 0 | 1 |
| src/test/regress/expected/groupingsets.out | 1 | 1 |
| src/test/regress/expected/inet.out | 2 | 2 |
| src/test/regress/expected/join.out | 10 | 10 |
| src/test/regress/expected/plancache.out | 4 | 4 |
| src/test/regress/expected/sqljson.out | 4 | 4 |
| src/test/regress/expected/tsrf.out | 19 | 19 |
| src/test/regress/expected/tuplesort.out | 3 | 3 |
| src/test/regress/expected/window.out | 250 | 250 |
From 12d0603a3aa94b033b4111d1944f2b054f00628d Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Tue, 11 Nov 2025 17:41:26 +0700
Subject: [PATCH v4 2/4] WIP Adjust regression tests
Regression tests don't pass for underspecified queries; this
is expected since both qsort and in-place radix sort are
unstable sorts. For the query
SELECT a, b from mytable ORDER BY a;
...a stable sort would guarantee the relative position of 'b'
for each group of 'a', compared to the input.
This is separated out since the relative order changes with
the qsort threshold, same as it would for qsort and its
insertion sort threshold. Assert builds always do radix
sort regardless of input size, if the data type allows it.
The final commit will have the same threshold for all builds.
---
contrib/pg_stat_statements/expected/dml.out | 6 +-
.../postgres_fdw/expected/postgres_fdw.out | 8 +-
src/backend/utils/sort/tuplesort.c | 1 -
src/test/regress/expected/groupingsets.out | 2 +-
src/test/regress/expected/inet.out | 4 +-
src/test/regress/expected/join.out | 20 +-
src/test/regress/expected/plancache.out | 8 +-
src/test/regress/expected/sqljson.out | 8 +-
src/test/regress/expected/tsrf.out | 38 +-
src/test/regress/expected/tuplesort.out | 6 +-
src/test/regress/expected/window.out | 500 +++++++++---------
11 files changed, 300 insertions(+), 301 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/dml.out b/contrib/pg_stat_statements/expected/dml.out
index 347cb8699e4..aa6e91e1c7f 100644
--- a/contrib/pg_stat_statements/expected/dml.out
+++ b/contrib/pg_stat_statements/expected/dml.out
@@ -44,12 +44,12 @@ SELECT *
SELECT * FROM pgss_dml_tab ORDER BY a;
a | b
---+----------------------
- 1 | a
1 | 111
- 2 | b
+ 1 | a
2 | 222
- 3 | c
+ 2 | b
3 | 333
+ 3 | c
4 | 444
5 | 555
6 | 666
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index cd28126049d..95f8fd388e6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2626,15 +2626,15 @@ SELECT * FROM ft1, ft4, ft5, local_tbl WHERE ft1.c1 = ft4.c1 AND ft1.c1 = ft5.c1
12 | 2 | 00012 | Tue Jan 13 00:00:00 1970 PST | Tue Jan 13 00:00:00 1970 | 2 | 2 | foo | 12 | 13 | AAA012 | 12 | 13 | AAA012 | 2 | 2 | 0002
42 | 2 | 00042 | Thu Feb 12 00:00:00 1970 PST | Thu Feb 12 00:00:00 1970 | 2 | 2 | foo | 42 | 43 | AAA042 | 42 | 43 | AAA042 | 2 | 2 | 0002
72 | 2 | 00072 | Sat Mar 14 00:00:00 1970 PST | Sat Mar 14 00:00:00 1970 | 2 | 2 | foo | 72 | 73 | AAA072 | 72 | 73 | | 2 | 2 | 0002
- 24 | 4 | 00024 | Sun Jan 25 00:00:00 1970 PST | Sun Jan 25 00:00:00 1970 | 4 | 4 | foo | 24 | 25 | AAA024 | 24 | 25 | AAA024 | 4 | 4 | 0004
54 | 4 | 00054 | Tue Feb 24 00:00:00 1970 PST | Tue Feb 24 00:00:00 1970 | 4 | 4 | foo | 54 | 55 | AAA054 | 54 | 55 | | 4 | 4 | 0004
+ 24 | 4 | 00024 | Sun Jan 25 00:00:00 1970 PST | Sun Jan 25 00:00:00 1970 | 4 | 4 | foo | 24 | 25 | AAA024 | 24 | 25 | AAA024 | 4 | 4 | 0004
84 | 4 | 00084 | Thu Mar 26 00:00:00 1970 PST | Thu Mar 26 00:00:00 1970 | 4 | 4 | foo | 84 | 85 | AAA084 | 84 | 85 | AAA084 | 4 | 4 | 0004
- 96 | 6 | 00096 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo | 96 | 97 | AAA096 | 96 | 97 | AAA096 | 6 | 6 | 0006
+ 6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006 | 6 | 7 | AAA006 | 6 | 6 | 0006
36 | 6 | 00036 | Fri Feb 06 00:00:00 1970 PST | Fri Feb 06 00:00:00 1970 | 6 | 6 | foo | 36 | 37 | AAA036 | 36 | 37 | | 6 | 6 | 0006
66 | 6 | 00066 | Sun Mar 08 00:00:00 1970 PST | Sun Mar 08 00:00:00 1970 | 6 | 6 | foo | 66 | 67 | AAA066 | 66 | 67 | AAA066 | 6 | 6 | 0006
- 6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006 | 6 | 7 | AAA006 | 6 | 6 | 0006
- 48 | 8 | 00048 | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo | 48 | 49 | AAA048 | 48 | 49 | AAA048 | 8 | 8 | 0008
+ 96 | 6 | 00096 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo | 96 | 97 | AAA096 | 96 | 97 | AAA096 | 6 | 6 | 0006
18 | 8 | 00018 | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo | 18 | 19 | AAA018 | 18 | 19 | | 8 | 8 | 0008
+ 48 | 8 | 00048 | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo | 48 | 49 | AAA048 | 48 | 49 | AAA048 | 8 | 8 | 0008
78 | 8 | 00078 | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo | 78 | 79 | AAA078 | 78 | 79 | AAA078 | 8 | 8 | 0008
(13 rows)
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index c2b9625ae88..3efc22de24e 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -104,7 +104,6 @@
#include "commands/tablespace.h"
#include "miscadmin.h"
#include "pg_trace.h"
-#include "port/pg_bitutils.h"
#include "storage/shmem.h"
#include "utils/guc.h"
#include "utils/memutils.h"
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 398cf6965e0..02abebdb0d7 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -94,8 +94,8 @@ select a, b, grouping(a,b), sum(v), count(*), max(v)
1 | | 1 | 60 | 5 | 14
1 | 1 | 0 | 21 | 2 | 11
2 | | 1 | 15 | 1 | 15
- 3 | | 1 | 33 | 2 | 17
1 | 2 | 0 | 25 | 2 | 13
+ 3 | | 1 | 33 | 2 | 17
1 | 3 | 0 | 14 | 1 | 14
4 | | 1 | 37 | 2 | 19
4 | 1 | 0 | 37 | 2 | 19
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index 1705bff4dd3..85a3a6a7de5 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -465,9 +465,9 @@ SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
- 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
+ 10.0.0.0/32 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
10.1.2.3/32 | 10.1.2.3
@@ -613,9 +613,9 @@ SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i;
c | i
-------------+-------------
10.0.0.0/8 | 9.1.2.3/8
- 10.0.0.0/32 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
10.0.0.0/8 | 10.1.2.3/8
+ 10.0.0.0/32 | 10.1.2.3/8
10.1.0.0/16 | 10.1.2.3/16
10.1.2.0/24 | 10.1.2.3/24
10.1.2.3/32 | 10.1.2.3
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 0e82ca1867a..9b6a9f536d3 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -220,8 +220,8 @@ SELECT t1.a, t2.e
---+----
0 |
1 | -1
- 2 | 2
2 | 4
+ 2 | 2
3 | -3
5 | -5
5 | -5
@@ -1575,8 +1575,8 @@ SELECT *
---+---+-------+----
0 | | zero |
1 | 4 | one | -1
- 2 | 3 | two | 2
2 | 3 | two | 4
+ 2 | 3 | two | 2
3 | 2 | three | -3
5 | 0 | five | -5
5 | 0 | five | -5
@@ -1589,8 +1589,8 @@ SELECT *
---+---+-------+----
0 | | zero |
1 | 4 | one | -1
- 2 | 3 | two | 2
2 | 3 | two | 4
+ 2 | 3 | two | 2
3 | 2 | three | -3
5 | 0 | five | -5
5 | 0 | five | -5
@@ -1683,8 +1683,8 @@ SELECT *
---+---+-------+----
0 | | zero |
1 | 4 | one | -1
- 2 | 3 | two | 2
2 | 3 | two | 4
+ 2 | 3 | two | 2
3 | 2 | three | -3
5 | 0 | five | -5
5 | 0 | five | -5
@@ -1696,8 +1696,8 @@ SELECT *
---+---+-------+----
0 | | zero |
1 | 4 | one | -1
- 2 | 3 | two | 2
2 | 3 | two | 4
+ 2 | 3 | two | 2
3 | 2 | three | -3
5 | 0 | five | -5
5 | 0 | five | -5
@@ -1720,8 +1720,8 @@ SELECT *
---+---+-------+----
0 | | zero |
1 | 4 | one | -1
- 2 | 3 | two | 2
2 | 3 | two | 4
+ 2 | 3 | two | 2
3 | 2 | three | -3
5 | 0 | five | -5
5 | 0 | five | -5
@@ -1736,8 +1736,8 @@ SELECT *
---+---+-------+---+----
0 | | zero | 0 |
1 | 4 | one | 1 | -1
- 2 | 3 | two | 2 | 2
2 | 3 | two | 2 | 4
+ 2 | 3 | two | 2 | 2
3 | 2 | three | 3 | -3
5 | 0 | five | 5 | -5
5 | 0 | five | 5 | -5
@@ -1820,8 +1820,8 @@ SELECT *
---+---+-------+----
0 | | zero |
1 | 4 | one | -1
- 2 | 3 | two | 2
2 | 3 | two | 4
+ 2 | 3 | two | 2
3 | 2 | three | -3
5 | 0 | five | -5
5 | 0 | five | -5
@@ -1835,8 +1835,8 @@ SELECT *
---+---+-------+----
0 | | zero |
1 | 4 | one | -1
- 2 | 3 | two | 2
2 | 3 | two | 4
+ 2 | 3 | two | 2
3 | 2 | three | -3
5 | 0 | five | -5
5 | 0 | five | -5
@@ -2776,8 +2776,8 @@ select * from
---+---+-------+---+----
| | | | 0
| | | |
- | 0 | zero | |
| | null | |
+ | 0 | zero | |
8 | 8 | eight | |
7 | 7 | seven | |
6 | 6 | six | |
diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out
index 4e59188196c..41440c10cdd 100644
--- a/src/test/regress/expected/plancache.out
+++ b/src/test/regress/expected/plancache.out
@@ -38,8 +38,8 @@ EXECUTE prepstmt;
4567890123456789 | -4567890123456789
4567890123456789 | 123
123 | 456
- 123 | 4567890123456789
4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
(5 rows)
EXECUTE prepstmt2(123);
@@ -64,8 +64,8 @@ EXECUTE prepstmt;
4567890123456789 | -4567890123456789
4567890123456789 | 123
123 | 456
- 123 | 4567890123456789
4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
(5 rows)
EXECUTE prepstmt2(123);
@@ -86,8 +86,8 @@ EXECUTE vprep;
4567890123456789 | -4567890123456789
4567890123456789 | 123
123 | 456
- 123 | 4567890123456789
4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
(5 rows)
CREATE OR REPLACE TEMP VIEW pcacheview AS
@@ -98,8 +98,8 @@ EXECUTE vprep;
4567890123456789 | -2283945061728394
4567890123456789 | 61
123 | 228
- 123 | 2283945061728394
4567890123456789 | 2283945061728394
+ 123 | 2283945061728394
(5 rows)
-- Check basic SPI plan invalidation
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index c7b9e575445..13fa4f2262d 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -870,10 +870,10 @@ FROM
4 | [4, 4]
4 | [4, 4]
2 | [4, 4]
- 5 | [5, 3, 5]
- 3 | [5, 3, 5]
- 1 | [5, 3, 5]
- 5 | [5, 3, 5]
+ 3 | [3, 5, 5]
+ 1 | [3, 5, 5]
+ 5 | [3, 5, 5]
+ 5 | [3, 5, 5]
|
|
|
diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out
index c4f7b187f5b..fd3914b0fad 100644
--- a/src/test/regress/expected/tsrf.out
+++ b/src/test/regress/expected/tsrf.out
@@ -354,18 +354,18 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
a | foo | 1 | 1
a | | 1 | 2
b | bar | 1 | 1
- b | | 1 | 1
- | | 1 | 3
| bar | 1 | 2
| foo | 1 | 1
- | foo | 2 | 1
+ | | 1 | 3
+ b | | 1 | 1
a | bar | 2 | 1
- b | | 2 | 1
a | foo | 2 | 1
- | bar | 2 | 2
a | | 2 | 2
- | | 2 | 3
b | bar | 2 | 1
+ | bar | 2 | 2
+ | foo | 2 | 1
+ b | | 2 | 1
+ | | 2 | 3
(16 rows)
SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g);
@@ -433,26 +433,26 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
a | foo | 1 | 1
b | bar | 1 | 1
| bar | 1 | 2
- | foo | 1 | 1
+ | | 1 | 3
a | | 1 | 2
b | | 1 | 1
- | | 1 | 3
+ | foo | 1 | 1
+ a | bar | 2 | 1
+ a | foo | 2 | 1
+ b | bar | 2 | 1
+ | bar | 2 | 2
a | | 2 | 2
b | | 2 | 1
- | bar | 2 | 2
| | 2 | 3
| foo | 2 | 1
- a | bar | 2 | 1
- a | foo | 2 | 1
- b | bar | 2 | 1
- a | | | 4
- b | bar | | 2
- b | | | 2
+ a | bar | | 2
+ | foo | | 2
| | | 6
a | foo | | 2
- a | bar | | 2
+ a | | | 4
| bar | | 4
- | foo | | 2
+ b | bar | | 2
+ b | | | 2
(24 rows)
reset enable_hashagg;
@@ -600,8 +600,8 @@ FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b);
a | b | g
---+---+---
3 | 2 | 1
- 5 | 1 | 2
- 3 | 1 | 3
+ 3 | 2 | 2
+ 3 | 2 | 3
(3 rows)
-- LIMIT / OFFSET is evaluated after SRF evaluation
diff --git a/src/test/regress/expected/tuplesort.out b/src/test/regress/expected/tuplesort.out
index 6dd97e7427a..fc1321bf443 100644
--- a/src/test/regress/expected/tuplesort.out
+++ b/src/test/regress/expected/tuplesort.out
@@ -304,9 +304,9 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 0 | | | |
20002 | | | |
20003 | | | |
+ 0 | | | |
10009 | 00000000-0000-0000-0000-000000010008 | 00000000-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992
10008 | 00000000-0000-0000-0000-000000010007 | 00000000-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993
(5 rows)
@@ -335,9 +335,9 @@ FROM abbrev_abort_uuids
ORDER BY ctid DESC LIMIT 5;
id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing
-------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------
- 0 | | | |
- 20003 | | | |
20002 | | | |
+ 20003 | | | |
+ 0 | | | |
9993 | 00000000-0000-0000-0000-000000009992 | 00000000-0000-0000-0000-000000010008 | 00009992-0000-0000-0000-000000009992 | 00010008-0000-0000-0000-000000010008
9994 | 00000000-0000-0000-0000-000000009993 | 00000000-0000-0000-0000-000000010007 | 00009993-0000-0000-0000-000000009993 | 00010007-0000-0000-0000-000000010007
(5 rows)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 9e2f53726f5..b3cdeaea4b3 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -95,13 +95,13 @@ SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITI
-----------+-------+--------+------
develop | 7 | 4200 | 1
personnel | 5 | 3500 | 1
- sales | 3 | 4800 | 1
sales | 4 | 4800 | 1
- personnel | 2 | 3900 | 2
+ sales | 3 | 4800 | 1
develop | 9 | 4500 | 2
- sales | 1 | 5000 | 3
+ personnel | 2 | 3900 | 2
develop | 11 | 5200 | 3
develop | 10 | 5200 | 3
+ sales | 1 | 5000 | 3
develop | 8 | 6000 | 5
(10 rows)
@@ -394,12 +394,12 @@ SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM te
SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
last_value | ten | four
------------+-----+------
- 0 | 0 | 0
- 0 | 0 | 2
- 0 | 0 | 0
- 1 | 1 | 1
+ 2 | 0 | 0
+ 2 | 0 | 0
+ 2 | 0 | 2
1 | 1 | 3
1 | 1 | 1
+ 1 | 1 | 1
3 | 3 | 3
0 | 4 | 0
1 | 7 | 1
@@ -821,14 +821,14 @@ SELECT sum(unique1) over (order by four range between current row and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -940,14 +940,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- 8 | 0 | 0
- 4 | 8 | 0
- 5 | 4 | 0
- 9 | 5 | 1
- 1 | 9 | 1
- 6 | 1 | 1
- 2 | 6 | 2
- 3 | 2 | 2
+ 8 | 4 | 0
+ 0 | 8 | 0
+ 1 | 0 | 0
+ 9 | 1 | 1
+ 5 | 9 | 1
+ 2 | 5 | 1
+ 6 | 2 | 2
+ 3 | 6 | 2
7 | 3 | 3
| 7 | 3
(10 rows)
@@ -957,14 +957,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- | 0 | 0
- 5 | 8 | 0
- 5 | 4 | 0
- | 5 | 1
- 6 | 9 | 1
- 6 | 1 | 1
- 3 | 6 | 2
+ | 4 | 0
+ 1 | 8 | 0
+ 1 | 0 | 0
+ | 1 | 1
+ 2 | 9 | 1
+ 2 | 5 | 1
3 | 2 | 2
+ 3 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -974,14 +974,14 @@ SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 f
FROM tenk1 WHERE unique1 < 10;
first_value | unique1 | four
-------------+---------+------
- 0 | 0 | 0
- 8 | 8 | 0
4 | 4 | 0
- 5 | 5 | 1
- 9 | 9 | 1
+ 8 | 8 | 0
+ 0 | 0 | 0
1 | 1 | 1
- 6 | 6 | 2
+ 9 | 9 | 1
+ 5 | 5 | 1
2 | 2 | 2
+ 6 | 6 | 2
3 | 3 | 3
7 | 7 | 3
(10 rows)
@@ -991,14 +991,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- 4 | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- 1 | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ 0 | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ 5 | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
7 | 3 | 3
| 7 | 3
(10 rows)
@@ -1008,14 +1008,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -1025,14 +1025,14 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
FROM tenk1 WHERE unique1 < 10;
last_value | unique1 | four
------------+---------+------
- 0 | 0 | 0
- 5 | 8 | 0
- 9 | 4 | 0
- 5 | 5 | 1
- 6 | 9 | 1
- 2 | 1 | 1
- 3 | 6 | 2
- 7 | 2 | 2
+ 4 | 4 | 0
+ 1 | 8 | 0
+ 9 | 0 | 0
+ 1 | 1 | 1
+ 2 | 9 | 1
+ 6 | 5 | 1
+ 3 | 2 | 2
+ 7 | 6 | 2
3 | 3 | 3
7 | 7 | 3
(10 rows)
@@ -1093,14 +1093,14 @@ SELECT sum(unique1) over (w range between current row and unbounded following),
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -1110,14 +1110,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 4 | 8 | 0
8 | 4 | 0
- 22 | 5 | 1
- 18 | 9 | 1
+ 4 | 8 | 0
+ 12 | 0 | 0
26 | 1 | 1
- 29 | 6 | 2
+ 18 | 9 | 1
+ 22 | 5 | 1
33 | 2 | 2
+ 29 | 6 | 2
42 | 3 | 3
38 | 7 | 3
(10 rows)
@@ -1127,14 +1127,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
35 | 3 | 3
35 | 7 | 3
(10 rows)
@@ -1144,14 +1144,14 @@ SELECT sum(unique1) over (w range between unbounded preceding and current row ex
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);
sum | unique1 | four
-----+---------+------
- 0 | 0 | 0
- 8 | 8 | 0
4 | 4 | 0
- 17 | 5 | 1
- 21 | 9 | 1
+ 8 | 8 | 0
+ 0 | 0 | 0
13 | 1 | 1
- 33 | 6 | 2
+ 21 | 9 | 1
+ 17 | 5 | 1
29 | 2 | 2
+ 33 | 6 | 2
38 | 3 | 3
42 | 7 | 3
(10 rows)
@@ -1163,14 +1163,14 @@ FROM tenk1 WHERE unique1 < 10
WINDOW w AS (order by four range between current row and unbounded following);
first_value | nth_2 | last_value | unique1 | four
-------------+-------+------------+---------+------
- 0 | 8 | 7 | 0 | 0
- 0 | 8 | 7 | 8 | 0
- 0 | 8 | 7 | 4 | 0
- 5 | 9 | 7 | 5 | 1
- 5 | 9 | 7 | 9 | 1
- 5 | 9 | 7 | 1 | 1
- 6 | 2 | 7 | 6 | 2
- 6 | 2 | 7 | 2 | 2
+ 4 | 8 | 7 | 4 | 0
+ 4 | 8 | 7 | 8 | 0
+ 4 | 8 | 7 | 0 | 0
+ 1 | 9 | 7 | 1 | 1
+ 1 | 9 | 7 | 9 | 1
+ 1 | 9 | 7 | 5 | 1
+ 2 | 6 | 7 | 2 | 2
+ 2 | 6 | 7 | 6 | 2
3 | 7 | 7 | 3 | 3
3 | 7 | 7 | 7 | 3
(10 rows)
@@ -1367,14 +1367,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1386,14 +1386,14 @@ FROM tenk1 WHERE unique1 < 10;
-----+---------+------
| 3 | 3
| 7 | 3
- 10 | 6 | 2
10 | 2 | 2
+ 10 | 6 | 2
18 | 9 | 1
18 | 5 | 1
18 | 1 | 1
- 23 | 0 | 0
- 23 | 8 | 0
23 | 4 | 0
+ 23 | 8 | 0
+ 23 | 0 | 0
(10 rows)
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others),
@@ -1401,14 +1401,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1418,14 +1418,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1435,14 +1435,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1452,14 +1452,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1469,14 +1469,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 41 | 8 | 0
37 | 4 | 0
- 35 | 5 | 1
- 39 | 9 | 1
+ 41 | 8 | 0
+ 33 | 0 | 0
31 | 1 | 1
- 43 | 6 | 2
+ 39 | 9 | 1
+ 35 | 5 | 1
39 | 2 | 2
+ 43 | 6 | 2
26 | 3 | 3
30 | 7 | 3
(10 rows)
@@ -1486,14 +1486,14 @@ SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::i
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 33 | 8 | 0
33 | 4 | 0
- 30 | 5 | 1
- 30 | 9 | 1
+ 33 | 8 | 0
+ 33 | 0 | 0
30 | 1 | 1
- 37 | 6 | 2
+ 30 | 9 | 1
+ 30 | 5 | 1
37 | 2 | 2
+ 37 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -1539,13 +1539,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
34900 | 5000 | 10-01-2006
34900 | 6000 | 10-01-2006
38400 | 3900 | 12-23-2006
- 47100 | 4800 | 08-01-2007
47100 | 5200 | 08-01-2007
+ 47100 | 4800 | 08-01-2007
47100 | 4800 | 08-08-2007
47100 | 5200 | 08-15-2007
36100 | 3500 | 12-10-2007
- 32200 | 4500 | 01-01-2008
32200 | 4200 | 01-01-2008
+ 32200 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following),
@@ -1557,8 +1557,8 @@ select sum(salary) over (order by enroll_date desc range between '1 year'::inter
36100 | 3500 | 12-10-2007
47100 | 5200 | 08-15-2007
47100 | 4800 | 08-08-2007
- 47100 | 4800 | 08-01-2007
47100 | 5200 | 08-01-2007
+ 47100 | 4800 | 08-01-2007
38400 | 3900 | 12-23-2006
34900 | 5000 | 10-01-2006
34900 | 6000 | 10-01-2006
@@ -1573,8 +1573,8 @@ select sum(salary) over (order by enroll_date desc range between '1 year'::inter
| 3500 | 12-10-2007
| 5200 | 08-15-2007
| 4800 | 08-08-2007
- | 4800 | 08-01-2007
| 5200 | 08-01-2007
+ | 4800 | 08-01-2007
| 3900 | 12-23-2006
| 5000 | 10-01-2006
| 6000 | 10-01-2006
@@ -1587,13 +1587,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
29900 | 5000 | 10-01-2006
28900 | 6000 | 10-01-2006
34500 | 3900 | 12-23-2006
- 42300 | 4800 | 08-01-2007
41900 | 5200 | 08-01-2007
+ 42300 | 4800 | 08-01-2007
42300 | 4800 | 08-08-2007
41900 | 5200 | 08-15-2007
32600 | 3500 | 12-10-2007
- 27700 | 4500 | 01-01-2008
28000 | 4200 | 01-01-2008
+ 27700 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
@@ -1603,13 +1603,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
23900 | 5000 | 10-01-2006
23900 | 6000 | 10-01-2006
34500 | 3900 | 12-23-2006
- 37100 | 4800 | 08-01-2007
37100 | 5200 | 08-01-2007
+ 37100 | 4800 | 08-01-2007
42300 | 4800 | 08-08-2007
41900 | 5200 | 08-15-2007
32600 | 3500 | 12-10-2007
- 23500 | 4500 | 01-01-2008
23500 | 4200 | 01-01-2008
+ 23500 | 4500 | 01-01-2008
(10 rows)
select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
@@ -1619,13 +1619,13 @@ select sum(salary) over (order by enroll_date range between '1 year'::interval p
28900 | 5000 | 10-01-2006
29900 | 6000 | 10-01-2006
38400 | 3900 | 12-23-2006
- 41900 | 4800 | 08-01-2007
42300 | 5200 | 08-01-2007
+ 41900 | 4800 | 08-01-2007
47100 | 4800 | 08-08-2007
47100 | 5200 | 08-15-2007
36100 | 3500 | 12-10-2007
- 28000 | 4500 | 01-01-2008
27700 | 4200 | 01-01-2008
+ 28000 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by salary range between 1000 preceding and 1000 following),
@@ -1710,13 +1710,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
5000 | 5200 | 5000 | 10-01-2006
6000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4200 | 4500 | 01-01-2008
- 5000 | 4200 | 4200 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
+ 5000 | 4500 | 4200 | 01-01-2008
+ 5000 | 4500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1729,13 +1729,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
5000 | 5200 | 5000 | 10-01-2006
6000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4500 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 4200 | 4200 | 01-01-2008
+ 5000 | 4500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1748,13 +1748,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
3900 | 5200 | 5000 | 10-01-2006
3900 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 3500 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 3500 | 4200 | 01-01-2008
+ 5000 | 3500 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
@@ -1767,13 +1767,13 @@ select first_value(salary) over(order by enroll_date range between unbounded pre
6000 | 5200 | 5000 | 10-01-2006
5000 | 5200 | 6000 | 10-01-2006
5000 | 3500 | 3900 | 12-23-2006
- 5000 | 4200 | 4800 | 08-01-2007
- 5000 | 4200 | 5200 | 08-01-2007
- 5000 | 4200 | 4800 | 08-08-2007
- 5000 | 4200 | 5200 | 08-15-2007
- 5000 | 4200 | 3500 | 12-10-2007
- 5000 | 4200 | 4500 | 01-01-2008
+ 5000 | 4500 | 5200 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-01-2007
+ 5000 | 4500 | 4800 | 08-08-2007
+ 5000 | 4500 | 5200 | 08-15-2007
+ 5000 | 4500 | 3500 | 12-10-2007
5000 | 4500 | 4200 | 01-01-2008
+ 5000 | 4200 | 4500 | 01-01-2008
(10 rows)
-- RANGE offset PRECEDING/FOLLOWING with null values
@@ -1828,8 +1828,8 @@ window w as
(order by x desc nulls first range between 2 preceding and 2 following);
x | y | first_value | last_value
---+----+-------------+------------
- | 43 | 43 | 42
- | 42 | 43 | 42
+ | 42 | 42 | 43
+ | 43 | 42 | 43
5 | 5 | 5 | 3
4 | 4 | 5 | 2
3 | 3 | 5 | 1
@@ -2751,10 +2751,10 @@ window w as (order by f_timestamptz desc range between
7 | Wed Oct 19 02:23:54 2005 PDT | 8 | 6
6 | Tue Oct 19 02:23:54 2004 PDT | 7 | 5
5 | Sun Oct 19 02:23:54 2003 PDT | 6 | 4
- 4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 2
- 3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
+ 4 | Sat Oct 19 02:23:54 2002 PDT | 5 | 3
2 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
- 1 | Thu Oct 19 02:23:54 2000 PDT | 3 | 1
+ 3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
+ 1 | Thu Oct 19 02:23:54 2000 PDT | 2 | 1
0 | -infinity | 0 | 0
(12 rows)
@@ -2862,10 +2862,10 @@ window w as (order by f_timestamp desc range between
7 | Wed Oct 19 10:23:54 2005 | 8 | 6
6 | Tue Oct 19 10:23:54 2004 | 7 | 5
5 | Sun Oct 19 10:23:54 2003 | 6 | 4
- 4 | Sat Oct 19 10:23:54 2002 | 5 | 2
- 3 | Fri Oct 19 10:23:54 2001 | 4 | 1
+ 4 | Sat Oct 19 10:23:54 2002 | 5 | 3
2 | Fri Oct 19 10:23:54 2001 | 4 | 1
- 1 | Thu Oct 19 10:23:54 2000 | 3 | 1
+ 3 | Fri Oct 19 10:23:54 2001 | 4 | 1
+ 1 | Thu Oct 19 10:23:54 2000 | 2 | 1
0 | -infinity | 0 | 0
(12 rows)
@@ -2983,14 +2983,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and c
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 12 | 8 | 0
12 | 4 | 0
- 27 | 5 | 1
- 27 | 9 | 1
+ 12 | 8 | 0
+ 12 | 0 | 0
27 | 1 | 1
- 35 | 6 | 2
+ 27 | 9 | 1
+ 27 | 5 | 1
35 | 2 | 2
+ 35 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -3000,14 +3000,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and u
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
45 | 1 | 1
- 45 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -3017,14 +3017,14 @@ SELECT sum(unique1) over (order by four groups between current row and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 33 | 5 | 1
- 33 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
33 | 1 | 1
- 18 | 6 | 2
+ 33 | 9 | 1
+ 33 | 5 | 1
18 | 2 | 2
+ 18 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -3034,14 +3034,14 @@ SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 45 | 0 | 0
- 45 | 8 | 0
45 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 45 | 8 | 0
+ 45 | 0 | 0
45 | 1 | 1
- 33 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
33 | 2 | 2
+ 33 | 6 | 2
18 | 3 | 3
18 | 7 | 3
(10 rows)
@@ -3051,14 +3051,14 @@ SELECT sum(unique1) over (order by four groups between 1 following and unbounded
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 33 | 0 | 0
- 33 | 8 | 0
33 | 4 | 0
- 18 | 5 | 1
- 18 | 9 | 1
+ 33 | 8 | 0
+ 33 | 0 | 0
18 | 1 | 1
- 10 | 6 | 2
+ 18 | 9 | 1
+ 18 | 5 | 1
10 | 2 | 2
+ 10 | 6 | 2
| 3 | 3
| 7 | 3
(10 rows)
@@ -3068,14 +3068,14 @@ SELECT sum(unique1) over (order by four groups between unbounded preceding and 2
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 35 | 0 | 0
- 35 | 8 | 0
35 | 4 | 0
- 45 | 5 | 1
- 45 | 9 | 1
+ 35 | 8 | 0
+ 35 | 0 | 0
45 | 1 | 1
- 45 | 6 | 2
+ 45 | 9 | 1
+ 45 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
45 | 3 | 3
45 | 7 | 3
(10 rows)
@@ -3085,14 +3085,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 precedi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- | 0 | 0
- | 8 | 0
| 4 | 0
- 12 | 5 | 1
- 12 | 9 | 1
+ | 8 | 0
+ | 0 | 0
12 | 1 | 1
- 27 | 6 | 2
+ 12 | 9 | 1
+ 12 | 5 | 1
27 | 2 | 2
+ 27 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -3102,14 +3102,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 27 | 0 | 0
- 27 | 8 | 0
27 | 4 | 0
- 35 | 5 | 1
- 35 | 9 | 1
+ 27 | 8 | 0
+ 27 | 0 | 0
35 | 1 | 1
- 45 | 6 | 2
+ 35 | 9 | 1
+ 35 | 5 | 1
45 | 2 | 2
+ 45 | 6 | 2
33 | 3 | 3
33 | 7 | 3
(10 rows)
@@ -3119,14 +3119,14 @@ SELECT sum(unique1) over (order by four groups between 0 preceding and 0 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 12 | 0 | 0
- 12 | 8 | 0
12 | 4 | 0
- 15 | 5 | 1
- 15 | 9 | 1
+ 12 | 8 | 0
+ 12 | 0 | 0
15 | 1 | 1
- 8 | 6 | 2
+ 15 | 9 | 1
+ 15 | 5 | 1
8 | 2 | 2
+ 8 | 6 | 2
10 | 3 | 3
10 | 7 | 3
(10 rows)
@@ -3136,14 +3136,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 27 | 0 | 0
- 19 | 8 | 0
23 | 4 | 0
- 30 | 5 | 1
- 26 | 9 | 1
+ 19 | 8 | 0
+ 27 | 0 | 0
34 | 1 | 1
- 39 | 6 | 2
+ 26 | 9 | 1
+ 30 | 5 | 1
43 | 2 | 2
+ 39 | 6 | 2
30 | 3 | 3
26 | 7 | 3
(10 rows)
@@ -3153,14 +3153,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 15 | 0 | 0
- 15 | 8 | 0
15 | 4 | 0
- 20 | 5 | 1
- 20 | 9 | 1
+ 15 | 8 | 0
+ 15 | 0 | 0
20 | 1 | 1
- 37 | 6 | 2
+ 20 | 9 | 1
+ 20 | 5 | 1
37 | 2 | 2
+ 37 | 6 | 2
23 | 3 | 3
23 | 7 | 3
(10 rows)
@@ -3170,14 +3170,14 @@ SELECT sum(unique1) over (order by four groups between 2 preceding and 1 followi
FROM tenk1 WHERE unique1 < 10;
sum | unique1 | four
-----+---------+------
- 15 | 0 | 0
- 23 | 8 | 0
19 | 4 | 0
- 25 | 5 | 1
- 29 | 9 | 1
+ 23 | 8 | 0
+ 15 | 0 | 0
21 | 1 | 1
- 43 | 6 | 2
+ 29 | 9 | 1
+ 25 | 5 | 1
39 | 2 | 2
+ 43 | 6 | 2
26 | 3 | 3
30 | 7 | 3
(10 rows)
@@ -3258,14 +3258,14 @@ select first_value(salary) over(order by enroll_date groups between 1 preceding
-------------+------+-----------+--------+-------------
5000 | 6000 | 5000 | 5000 | 10-01-2006
5000 | 3900 | 5000 | 6000 | 10-01-2006
- 5000 | 4800 | 5000 | 3900 | 12-23-2006
- 3900 | 5200 | 3900 | 4800 | 08-01-2007
+ 5000 | 5200 | 5000 | 3900 | 12-23-2006
3900 | 4800 | 3900 | 5200 | 08-01-2007
- 4800 | 5200 | 4800 | 4800 | 08-08-2007
+ 3900 | 4800 | 3900 | 4800 | 08-01-2007
+ 5200 | 5200 | 5200 | 4800 | 08-08-2007
4800 | 3500 | 4800 | 5200 | 08-15-2007
- 5200 | 4500 | 5200 | 3500 | 12-10-2007
- 3500 | 4200 | 3500 | 4500 | 01-01-2008
- 3500 | | 3500 | 4200 | 01-01-2008
+ 5200 | 4200 | 5200 | 3500 | 12-10-2007
+ 3500 | 4500 | 3500 | 4200 | 01-01-2008
+ 3500 | | 3500 | 4500 | 01-01-2008
(10 rows)
select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following),
@@ -3275,14 +3275,14 @@ select last_value(salary) over(order by enroll_date groups between 1 preceding a
------------+------+--------+-------------
3900 | | 5000 | 10-01-2006
3900 | 5000 | 6000 | 10-01-2006
- 5200 | 6000 | 3900 | 12-23-2006
- 4800 | 3900 | 4800 | 08-01-2007
- 4800 | 4800 | 5200 | 08-01-2007
- 5200 | 5200 | 4800 | 08-08-2007
+ 4800 | 6000 | 3900 | 12-23-2006
+ 4800 | 3900 | 5200 | 08-01-2007
+ 4800 | 5200 | 4800 | 08-01-2007
+ 5200 | 4800 | 4800 | 08-08-2007
3500 | 4800 | 5200 | 08-15-2007
- 4200 | 5200 | 3500 | 12-10-2007
- 4200 | 3500 | 4500 | 01-01-2008
- 4200 | 4500 | 4200 | 01-01-2008
+ 4500 | 5200 | 3500 | 12-10-2007
+ 4500 | 3500 | 4200 | 01-01-2008
+ 4500 | 4200 | 4500 | 01-01-2008
(10 rows)
select first_value(salary) over(order by enroll_date groups between 1 following and 3 following
@@ -3295,14 +3295,14 @@ select first_value(salary) over(order by enroll_date groups between 1 following
-------------+------+-----------+--------+-------------
3900 | 6000 | 3900 | 5000 | 10-01-2006
3900 | 3900 | 3900 | 6000 | 10-01-2006
- 4800 | 4800 | 4800 | 3900 | 12-23-2006
- 4800 | 5200 | 4800 | 4800 | 08-01-2007
+ 5200 | 5200 | 5200 | 3900 | 12-23-2006
4800 | 4800 | 4800 | 5200 | 08-01-2007
+ 4800 | 4800 | 4800 | 4800 | 08-01-2007
5200 | 5200 | 5200 | 4800 | 08-08-2007
3500 | 3500 | 3500 | 5200 | 08-15-2007
- 4500 | 4500 | 4500 | 3500 | 12-10-2007
- | 4200 | | 4500 | 01-01-2008
- | | | 4200 | 01-01-2008
+ 4200 | 4200 | 4200 | 3500 | 12-10-2007
+ | 4500 | | 4200 | 01-01-2008
+ | | | 4500 | 01-01-2008
(10 rows)
select last_value(salary) over(order by enroll_date groups between 1 following and 3 following
@@ -3314,13 +3314,13 @@ select last_value(salary) over(order by enroll_date groups between 1 following a
4800 | | 5000 | 10-01-2006
4800 | 5000 | 6000 | 10-01-2006
5200 | 6000 | 3900 | 12-23-2006
- 3500 | 3900 | 4800 | 08-01-2007
- 3500 | 4800 | 5200 | 08-01-2007
- 4200 | 5200 | 4800 | 08-08-2007
- 4200 | 4800 | 5200 | 08-15-2007
- 4200 | 5200 | 3500 | 12-10-2007
- | 3500 | 4500 | 01-01-2008
- | 4500 | 4200 | 01-01-2008
+ 3500 | 3900 | 5200 | 08-01-2007
+ 3500 | 5200 | 4800 | 08-01-2007
+ 4500 | 4800 | 4800 | 08-08-2007
+ 4500 | 4800 | 5200 | 08-15-2007
+ 4500 | 5200 | 3500 | 12-10-2007
+ | 3500 | 4200 | 01-01-2008
+ | 4200 | 4500 | 01-01-2008
(10 rows)
-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS
--
2.51.1