From 12d0603a3aa94b033b4111d1944f2b054f00628d Mon Sep 17 00:00:00 2001 From: John Naylor 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