From bc2752902d1187ed8086641b4da1f36836940cb8 Mon Sep 17 00:00:00 2001 From: John Naylor Date: Thu, 30 Jun 2022 16:56:06 +0700 Subject: [PATCH v2 3/3] Implement dual-pivot quicksort Choose pivots by running insertion sort on five candidates and choosing the 2nd and 4th, ("tertile of five"). If any of the five are equal, we assume the input has many duplicates and fall back to B&M since it's optimized for that case. Set default insertion sort threshold to 12, since it seems better for dual pivot. --- .../expected/pg_stat_statements.out | 4 +- src/include/lib/sort_template.h | 219 +++++-- src/test/regress/expected/create_index.out | 2 +- src/test/regress/expected/geometry.out | 4 +- src/test/regress/expected/groupingsets.out | 4 +- src/test/regress/expected/inet.out | 8 +- src/test/regress/expected/join.out | 2 +- src/test/regress/expected/merge.out | 22 +- src/test/regress/expected/sqljson.out | 10 +- src/test/regress/expected/tsrf.out | 60 +- src/test/regress/expected/tuplesort.out | 16 +- src/test/regress/expected/window.out | 544 +++++++++--------- 12 files changed, 515 insertions(+), 380 deletions(-) diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out index 8f7f93172a..5a894a0f8b 100644 --- a/contrib/pg_stat_statements/expected/pg_stat_statements.out +++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out @@ -196,10 +196,10 @@ SELECT * FROM test ORDER BY a; ---+---------------------- 1 | a 1 | 111 - 2 | b 2 | 222 - 3 | c + 2 | b 3 | 333 + 3 | c 4 | 444 5 | 555 6 | 666 diff --git a/src/include/lib/sort_template.h b/src/include/lib/sort_template.h index ea04011ce5..1b70331f05 100644 --- a/src/include/lib/sort_template.h +++ b/src/include/lib/sort_template.h @@ -135,7 +135,7 @@ /* * If the user wants to be able to pass in compare functions at runtime, - * we'll need to make that an argument of the sort and med3 functions. + * we'll need to make that an argument of the sort functions. */ #ifdef ST_COMPARE_RUNTIME_POINTER /* @@ -161,8 +161,8 @@ /* * If the user wants to use a compare function or expression that takes an - * extra argument, we'll need to make that an argument of the sort, compare and - * med3 functions. + * extra argument, we'll need to make that an argument of the sort and compare + * functions. */ #ifdef ST_COMPARE_ARG_TYPE #define ST_SORT_PROTO_ARG , ST_COMPARE_ARG_TYPE *arg @@ -174,7 +174,7 @@ /* Default input size threshold to control algorithm choice. */ #ifndef ST_INSERTION_SORT_THRESHOLD -#define ST_INSERTION_SORT_THRESHOLD 7 +#define ST_INSERTION_SORT_THRESHOLD 12 #endif #ifdef ST_DECLARE @@ -195,7 +195,6 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n #ifdef ST_DEFINE /* sort private helper functions */ -#define ST_MED3 ST_MAKE_NAME(ST_SORT, med3) #define ST_SORT_INTERNAL ST_MAKE_NAME(ST_SORT, internal) #define ST_SWAP ST_MAKE_NAME(ST_SORT, swap) #define ST_SWAPN ST_MAKE_NAME(ST_SORT, swapn) @@ -208,8 +207,8 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n #endif /* - * Create wrapper macros that know how to invoke compare, med3 and sort with - * the right arguments. + * Create wrapper macros that know how to invoke compare and sort with the + * right arguments. */ #ifdef ST_COMPARE_RUNTIME_POINTER #define DO_COMPARE(a_, b_) ST_COMPARE((a_), (b_) ST_SORT_INVOKE_ARG) @@ -218,10 +217,6 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n #else #define DO_COMPARE(a_, b_) ST_COMPARE((a_), (b_)) #endif -#define DO_MED3(a_, b_, c_) \ - ST_MED3((a_), (b_), (c_) \ - ST_SORT_INVOKE_COMPARE \ - ST_SORT_INVOKE_ARG) #define DO_SORT(a_, n_) \ ST_SORT_INTERNAL((a_), (n_) \ ST_SORT_INVOKE_ELEMENT_SIZE \ @@ -245,23 +240,6 @@ ST_SCOPE void ST_SORT(ST_ELEMENT_TYPE * first, size_t n #define DO_SWAP(a_, b_) DO_SWAPN((a_), (b_), element_size) #endif -/* - * Find the median of three values. Currently, performance seems to be best - * if the comparator is inlined here, but the med3 function is not inlined - * in the qsort function. - */ -static pg_noinline ST_ELEMENT_TYPE * -ST_MED3(ST_ELEMENT_TYPE * a, - ST_ELEMENT_TYPE * b, - ST_ELEMENT_TYPE * c - ST_SORT_PROTO_COMPARE - ST_SORT_PROTO_ARG) -{ - return DO_COMPARE(a, b) < 0 ? - (DO_COMPARE(b, c) < 0 ? b : (DO_COMPARE(a, c) < 0 ? c : a)) - : (DO_COMPARE(b, c) > 0 ? b : (DO_COMPARE(a, c) < 0 ? a : c)); -} - static inline void ST_SWAP(ST_POINTER_TYPE * a, ST_POINTER_TYPE * b) { @@ -288,6 +266,18 @@ ST_SORT_INTERNAL(ST_POINTER_TYPE * data, size_t n ST_SORT_PROTO_ARG) { ST_POINTER_TYPE *a = data, + *left, + *right, + *e1, + *e2, + *e3, + *e4, + *e5, + *pivot1, + *pivot2, + *less, + *great, + *k, *pa, *pb, *pc, @@ -296,9 +286,12 @@ ST_SORT_INTERNAL(ST_POINTER_TYPE * data, size_t n *pm, *pn; size_t d1, - d2; + d2, + d3, + seventh; int r, presorted; + bool unique_hint = true; loop: DO_CHECK_FOR_INTERRUPTS(); @@ -324,22 +317,164 @@ loop: } if (presorted) return; - pm = a + (n / 2) * ST_POINTER_STEP; - if (n > 7) + + left = a; + right = a + (n - 1) * ST_POINTER_STEP; + +#ifdef QSORT_DEBUG + elog(NOTICE, "start:"); + + for (ST_POINTER_TYPE *i = left; i <= right; i += ST_POINTER_STEP) { - pl = a; - pn = a + (n - 1) * ST_POINTER_STEP; - if (n > 40) + elog(NOTICE, "%d ", *i); + } +#endif + + /* select five pivot candidates spaced equally around the midpoint */ + seventh = n / 7 * ST_POINTER_STEP; + e3 = a + (n / 2) * ST_POINTER_STEP; + e2 = e3 - seventh; + e1 = e2 - seventh; + e4 = e3 + seventh; + e5 = e4 + seventh; + + /* do insertion sort on pivot candidates */ + for (pm = e2; pm <= e5; pm += seventh) + for (pl = pm; pl > e1 && (r = DO_COMPARE(pl - seventh, pl)) >= 0; + pl -= seventh) + { + if (r == 0) + { + /* found two equal candidates */ + unique_hint = false; + break; + } + else + DO_SWAP(pl, pl - seventh); + } + + /* + * If the pivot candidates were all unique, use dual-pivot quicksort, + * otherwise use B&M quicksort since it is faster on inputs with many + * duplicates. + */ + if (unique_hint) + { + DO_SWAP(e2, left); + DO_SWAP(e4, right); + + pivot1 = left; + pivot2 = right; + less = left + ST_POINTER_STEP; + great = right - ST_POINTER_STEP; + + /* + * dual-pivot partitioning + * + * left part center part right part + * +--------------------------------------------------------------+ + * | < pivot1 | pivot1 <= && <= pivot2 | ? | > pivot2 | + * +--------------------------------------------------------------+ + * ^ ^ ^ + * | | | + * less k great + * + * Invariants: + * + * all in (left, less) < pivot1 + * pivot1 <= all in [less, k) <= pivot2 + * all in (great, right) > pivot2 + * + * k points to the first element in the "?" part + */ + for (k = less; k <= great; k += ST_POINTER_STEP) { - size_t d = (n / 8) * ST_POINTER_STEP; + if (DO_COMPARE(k, pivot1) < 0) + { + if (k > less) + DO_SWAP(k, less); + less += ST_POINTER_STEP; + } + else if (DO_COMPARE(k, pivot2) > 0) + { + while (k < great && DO_COMPARE(great, pivot2) > 0) + { + great-= ST_POINTER_STEP; + DO_CHECK_FOR_INTERRUPTS(); + } + + DO_SWAP(k, great); + great-= ST_POINTER_STEP; - pl = DO_MED3(pl, pl + d, pl + 2 * d); - pm = DO_MED3(pm - d, pm, pm + d); - pn = DO_MED3(pn - 2 * d, pn - d, pn); + if (DO_COMPARE(k, pivot1) < 0) + { + if (k > less) + DO_SWAP(k, less); + less += ST_POINTER_STEP; + } + } + DO_CHECK_FOR_INTERRUPTS(); + } + + DO_SWAP(less - ST_POINTER_STEP, pivot1); + DO_SWAP(great + ST_POINTER_STEP, pivot2); + + d1 = (less - ST_POINTER_STEP) - left; + d2 = (great + ST_POINTER_STEP) - less; + d3 = right - (great + ST_POINTER_STEP); + + /* recurse on shorter subarrays to save stack space */ + if (d1 > d2) + { + /* recurse on d2 */ + DO_SORT(less, d2 / ST_POINTER_STEP); + if (d1 > d3) + { + /* recurse on d3 */ + DO_SORT(great + 2 * ST_POINTER_STEP, d3 / ST_POINTER_STEP); + /* iterate on d1 */ + /* DO_SORT(left, d1 / ST_POINTER_STEP) */ + n = d1 / ST_POINTER_STEP; + goto loop; + } + else + { + goto recurse_d1; + } + } + else + { +recurse_d1: + /* recurse on d1 */ + DO_SORT(left, d1 / ST_POINTER_STEP); + if (d2 > d3) + { + /* recurse on d3 */ + DO_SORT(great + 2 * ST_POINTER_STEP, d3 / ST_POINTER_STEP); + /* iterate on d2 */ + /* DO_SORT(less, d2 / ST_POINTER_STEP) */ + a = less; + n = d2 / ST_POINTER_STEP; + goto loop; + } + else + { + /* recurse on d2 */ + DO_SORT(less, d2 / ST_POINTER_STEP); + /* iterate on d3 */ + /* DO_SORT(great + 2 * ST_POINTER_STEP, d3 / ST_POINTER_STEP) */ + a = great + 2 * ST_POINTER_STEP; + n = d3 / ST_POINTER_STEP; + goto loop; + } } - pm = DO_MED3(pl, pm, pn); } - DO_SWAP(a, pm); + else + /* classic B&M quicksort with a single pivot */ + // WIP: clean up variables to match dual pivot more closely + { + /* use median of five for the pivot */ + DO_SWAP(a, e3); pa = pb = a + ST_POINTER_STEP; pc = pd = a + (n - 1) * ST_POINTER_STEP; for (;;) @@ -404,6 +539,7 @@ loop: goto loop; } } + } } /* @@ -433,7 +569,6 @@ ST_SORT(ST_ELEMENT_TYPE * data, size_t n #undef DO_CHECK_FOR_INTERRUPTS #undef DO_COMPARE -#undef DO_MED3 #undef DO_SORT #undef DO_SWAP #undef DO_SWAPN @@ -444,10 +579,10 @@ ST_SORT(ST_ELEMENT_TYPE * data, size_t n #undef ST_COMPARE_RUNTIME_POINTER #undef ST_ELEMENT_TYPE #undef ST_ELEMENT_TYPE_VOID +#undef ST_INSERTION_SORT_THRESHOLD #undef ST_MAKE_NAME #undef ST_MAKE_NAME_ #undef ST_MAKE_PREFIX -#undef ST_MED3 #undef ST_POINTER_STEP #undef ST_POINTER_TYPE #undef ST_SCOPE diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index d55aec3a1d..9b548fe3e8 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -172,8 +172,8 @@ SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; (10,10) (-5,-12) (5.1,34.5) - (Infinity,1e+300) (1e+300,Infinity) + (Infinity,1e+300) (NaN,NaN) (11 rows) diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out index 3b364d1e3b..5eb0b29b16 100644 --- a/src/test/regress/expected/geometry.out +++ b/src/test/regress/expected/geometry.out @@ -4308,8 +4308,8 @@ SELECT c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance <(100,200),10> | (-5,-12) | 226.577682802 <(3,5),0> | (1e+300,Infinity) | Infinity <(3,5),0> | (Infinity,1e+300) | Infinity - <(1,2),3> | (1e+300,Infinity) | Infinity <(5,1),3> | (1e+300,Infinity) | Infinity + <(1,2),3> | (1e+300,Infinity) | Infinity <(5,1),3> | (Infinity,1e+300) | Infinity <(1,2),3> | (Infinity,1e+300) | Infinity <(1,3),5> | (1e+300,Infinity) | Infinity @@ -4321,8 +4321,8 @@ SELECT c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance <(100,1),115> | (1e+300,Infinity) | Infinity <(100,1),115> | (Infinity,1e+300) | Infinity <(3,5),0> | (NaN,NaN) | NaN - <(1,2),3> | (NaN,NaN) | NaN <(5,1),3> | (NaN,NaN) | NaN + <(1,2),3> | (NaN,NaN) | NaN <(1,3),5> | (NaN,NaN) | NaN <(100,200),10> | (NaN,NaN) | NaN <(1,2),100> | (NaN,NaN) | NaN diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index fcad5c4093..57dbb65a82 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -92,14 +92,14 @@ select a, b, grouping(a,b), sum(v), count(*), max(v) ---+---+----------+-----+-------+----- | | 3 | 145 | 10 | 19 1 | | 1 | 60 | 5 | 14 - 1 | 1 | 0 | 21 | 2 | 11 2 | | 1 | 15 | 1 | 15 + 1 | 1 | 0 | 21 | 2 | 11 3 | | 1 | 33 | 2 | 17 1 | 2 | 0 | 25 | 2 | 13 1 | 3 | 0 | 14 | 1 | 14 4 | | 1 | 37 | 2 | 19 - 4 | 1 | 0 | 37 | 2 | 19 2 | 3 | 0 | 15 | 1 | 15 + 4 | 1 | 0 | 37 | 2 | 19 3 | 3 | 0 | 16 | 1 | 16 3 | 4 | 0 | 17 | 1 | 17 (12 rows) diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out index d5bf9e2aaa..a02ac66298 100644 --- a/src/test/regress/expected/inet.out +++ b/src/test/regress/expected/inet.out @@ -390,8 +390,8 @@ 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/32 | 10.1.2.3/8 10.0.0.0/8 | 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 @@ -451,8 +451,8 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i; --------------------+------------------ 10.0.0.0/8 | 9.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.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 @@ -538,8 +538,8 @@ 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/32 | 10.1.2.3/8 10.0.0.0/8 | 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 @@ -599,8 +599,8 @@ SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i; --------------------+------------------ 10.0.0.0/8 | 9.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.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 2538bd6a79..720d4ca4e8 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2386,8 +2386,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/merge.out b/src/test/regress/expected/merge.out index af670e28e7..a2a4aaaf85 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1554,11 +1554,11 @@ SELECT * FROM pa_target ORDER BY tid; 5 | 500 | initial 5 | 50 | inserted by merge 6 | 60 | inserted by merge - 7 | 700 | initial 7 | 70 | inserted by merge + 7 | 700 | initial 8 | 80 | inserted by merge - 9 | 90 | inserted by merge 9 | 900 | initial + 9 | 90 | inserted by merge 10 | 100 | inserted by merge 11 | 1100 | initial 11 | 110 | inserted by merge @@ -1583,12 +1583,12 @@ SELECT * FROM pa_target ORDER BY tid; -----+---------+-------------------------- 2 | 110 | initial updated by merge 2 | 20 | inserted by merge - 4 | 40 | inserted by merge 4 | 330 | initial updated by merge - 6 | 550 | initial updated by merge + 4 | 40 | inserted by merge 6 | 60 | inserted by merge - 8 | 80 | inserted by merge + 6 | 550 | initial updated by merge 8 | 770 | initial updated by merge + 8 | 80 | inserted by merge 10 | 990 | initial updated by merge 10 | 100 | inserted by merge 12 | 1210 | initial updated by merge @@ -1658,18 +1658,18 @@ SELECT * FROM pa_target ORDER BY tid; -----+---------+-------------------------- 1 | 110 | initial updated by merge 2 | 20 | inserted by merge - 3 | 30 | inserted by merge 3 | 300 | initial + 3 | 30 | inserted by merge 4 | 40 | inserted by merge 6 | 60 | inserted by merge - 7 | 700 | initial 7 | 70 | inserted by merge + 7 | 700 | initial 8 | 80 | inserted by merge 9 | 900 | initial 9 | 90 | inserted by merge 10 | 100 | inserted by merge - 11 | 110 | inserted by merge 11 | 1100 | initial + 11 | 110 | inserted by merge 12 | 120 | inserted by merge 13 | 1300 | initial 13 | 130 | inserted by merge @@ -1691,12 +1691,12 @@ SELECT * FROM pa_target ORDER BY tid; -----+---------+-------------------------- 2 | 110 | initial updated by merge 2 | 20 | inserted by merge - 4 | 40 | inserted by merge 4 | 330 | initial updated by merge - 6 | 550 | initial updated by merge + 4 | 40 | inserted by merge 6 | 60 | inserted by merge - 8 | 80 | inserted by merge + 6 | 550 | initial updated by merge 8 | 770 | initial updated by merge + 8 | 80 | inserted by merge 10 | 990 | initial updated by merge 10 | 100 | inserted by merge 12 | 1210 | initial updated by merge diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index 0883261535..db0f2c08ad 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -873,13 +873,13 @@ FROM (VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL), (5), (4)) foo(bar); bar | json_arrayagg -----+--------------- + 2 | [4, 4] 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 d47b5f6ec5..f4c87d7a17 100644 --- a/src/test/regress/expected/tsrf.out +++ b/src/test/regress/expected/tsrf.out @@ -348,11 +348,11 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g; dataa | b | g | count -------+-----+---+------- - a | bar | 1 | 1 + b | | 1 | 1 a | foo | 1 | 1 a | | 1 | 2 b | bar | 1 | 1 - b | | 1 | 1 + a | bar | 1 | 1 | | 1 | 3 | bar | 1 | 2 | foo | 1 | 1 @@ -398,59 +398,59 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa; dataa | b | g | count -------+-----+---+------- - a | foo | | 2 - a | | | 4 + a | | 1 | 2 + a | foo | 1 | 1 a | | 2 | 2 a | bar | 1 | 1 + a | foo | 2 | 1 + a | foo | | 2 + a | | | 4 a | bar | 2 | 1 a | bar | | 2 - a | foo | 1 | 1 - a | foo | 2 | 1 - a | | 1 | 2 + b | bar | | 2 b | bar | 1 | 1 + b | bar | 2 | 1 b | | | 2 b | | 1 | 1 - b | bar | 2 | 1 - b | bar | | 2 b | | 2 | 1 - | | 2 | 3 - | | | 6 | bar | 1 | 2 - | bar | 2 | 2 - | bar | | 4 - | foo | 1 | 1 | foo | 2 | 1 | foo | | 2 + | foo | 1 | 1 + | | 2 | 3 | | 1 | 3 + | | | 6 + | bar | 2 | 2 + | bar | | 4 (24 rows) SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g; dataa | b | g | count -------+-----+---+------- - a | bar | 1 | 1 + a | | 1 | 2 a | foo | 1 | 1 - b | bar | 1 | 1 | bar | 1 | 2 + b | bar | 1 | 1 | foo | 1 | 1 - a | | 1 | 2 + a | bar | 1 | 1 b | | 1 | 1 | | 1 | 3 + a | foo | 2 | 1 + | | 2 | 3 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 + a | bar | 2 | 1 + b | | 2 | 1 + a | foo | | 2 b | bar | | 2 b | | | 2 | | | 6 - a | foo | | 2 - a | bar | | 2 | bar | | 4 | foo | | 2 + a | bar | | 2 + a | | | 4 (24 rows) reset enable_hashagg; @@ -533,9 +533,9 @@ SELECT DISTINCT ON (a) a, b, generate_series(1,3) g FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b); a | b | g ---+---+--- - 1 | 1 | 1 - 3 | 2 | 1 - 5 | 3 | 1 + 1 | 4 | 3 + 3 | 2 | 3 + 5 | 3 | 3 (3 rows) -- unreferenced in DISTINCT ON or ORDER BY @@ -597,9 +597,9 @@ SELECT DISTINCT ON (g) a, b, generate_series(1,3) g 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 + 1 | 1 | 1 + 5 | 3 | 2 + 1 | 1 | 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 418f296a3f..b3ad4cc5f3 100644 --- a/src/test/regress/expected/tuplesort.out +++ b/src/test/regress/expected/tuplesort.out @@ -242,9 +242,9 @@ FROM abbrev_abort_uuids ORDER BY ctid DESC LIMIT 5; id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing -------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- + 20003 | | | | 0 | | | | 20002 | | | | - 20003 | | | | 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 (5 rows) @@ -260,11 +260,11 @@ FROM abbrev_abort_uuids ORDER BY ctid LIMIT 5; id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing -------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- - 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 + 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 19999 | 00000000-0000-0000-0000-000000019998 | 00000000-0000-0000-0000-000000000002 | 00009989-0000-0000-0000-000000019998 | 00000002-0000-0000-0000-000000000002 - 20009 | 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 + 19998 | 00000000-0000-0000-0000-000000019997 | 00000000-0000-0000-0000-000000000003 | 00009988-0000-0000-0000-000000019997 | 00000003-0000-0000-0000-000000000003 (5 rows) -- tail @@ -273,9 +273,9 @@ FROM abbrev_abort_uuids ORDER BY ctid DESC LIMIT 5; id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing -------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- - 0 | | | | - 20002 | | | | 20003 | | | | + 20002 | | | | + 0 | | | | 1 | 00000000-0000-0000-0000-000000000000 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 2 | 00000000-0000-0000-0000-000000000001 | 00000000-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 (5 rows) @@ -304,8 +304,8 @@ FROM abbrev_abort_uuids ORDER BY ctid DESC LIMIT 5; id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing -------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- - 0 | | | | 20002 | | | | + 0 | | | | 20003 | | | | 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 @@ -322,8 +322,8 @@ FROM abbrev_abort_uuids ORDER BY ctid LIMIT 5; id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing -------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- - 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 20001 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 + 20010 | 00000000-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 | 00009991-0000-0000-0000-000000020000 | 00000000-0000-0000-0000-000000000000 9992 | 00000000-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 | 00009991-0000-0000-0000-000000009991 | 00000000-0000-0000-0000-000000010009 20000 | 00000000-0000-0000-0000-000000019999 | 00000000-0000-0000-0000-000000000001 | 00009990-0000-0000-0000-000000019999 | 00000001-0000-0000-0000-000000000001 9991 | 00000000-0000-0000-0000-000000009990 | 00000000-0000-0000-0000-000000010010 | 00009990-0000-0000-0000-000000009990 | 00000001-0000-0000-0000-000000010010 @@ -335,9 +335,9 @@ FROM abbrev_abort_uuids ORDER BY ctid DESC LIMIT 5; id | abort_increasing | abort_decreasing | noabort_increasing | noabort_decreasing -------+--------------------------------------+--------------------------------------+--------------------------------------+-------------------------------------- + 20002 | | | | 0 | | | | 20003 | | | | - 20002 | | | | 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 433a0bb025..aa173e5318 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -23,13 +23,13 @@ SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM emps -----------+-------+--------+------- develop | 7 | 4200 | 25100 develop | 9 | 4500 | 25100 - develop | 11 | 5200 | 25100 develop | 10 | 5200 | 25100 + develop | 11 | 5200 | 25100 develop | 8 | 6000 | 25100 personnel | 5 | 3500 | 7400 personnel | 2 | 3900 | 7400 - sales | 3 | 4800 | 14600 sales | 4 | 4800 | 14600 + sales | 3 | 4800 | 14600 sales | 1 | 5000 | 14600 (10 rows) @@ -38,13 +38,13 @@ SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary -----------+-------+--------+------ develop | 7 | 4200 | 1 develop | 9 | 4500 | 2 - develop | 11 | 5200 | 3 develop | 10 | 5200 | 3 + develop | 11 | 5200 | 3 develop | 8 | 6000 | 5 personnel | 5 | 3500 | 1 personnel | 2 | 3900 | 2 - sales | 3 | 4800 | 1 sales | 4 | 4800 | 1 + sales | 3 | 4800 | 1 sales | 1 | 5000 | 3 (10 rows) @@ -78,16 +78,16 @@ GROUP BY four, ten ORDER BY four, ten; SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); depname | empno | salary | sum -----------+-------+--------+------- - develop | 11 | 5200 | 25100 + develop | 10 | 5200 | 25100 develop | 7 | 4200 | 25100 develop | 9 | 4500 | 25100 develop | 8 | 6000 | 25100 - develop | 10 | 5200 | 25100 + develop | 11 | 5200 | 25100 personnel | 5 | 3500 | 7400 personnel | 2 | 3900 | 7400 - sales | 3 | 4800 | 14600 sales | 1 | 5000 | 14600 sales | 4 | 4800 | 14600 + sales | 3 | 4800 | 14600 (10 rows) SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; @@ -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 - develop | 11 | 5200 | 3 + personnel | 2 | 3900 | 2 develop | 10 | 5200 | 3 + develop | 11 | 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 @@ -803,14 +803,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) @@ -922,14 +922,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) @@ -939,14 +939,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) @@ -956,14 +956,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) @@ -973,14 +973,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) @@ -990,14 +990,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) @@ -1007,14 +1007,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) @@ -1075,14 +1075,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) @@ -1092,14 +1092,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) @@ -1109,14 +1109,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) @@ -1126,14 +1126,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) @@ -1145,14 +1145,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) @@ -1349,14 +1349,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) @@ -1368,14 +1368,14 @@ FROM tenk1 WHERE unique1 < 10; -----+---------+------ | 3 | 3 | 7 | 3 - 10 | 6 | 2 10 | 2 | 2 + 10 | 6 | 2 + 18 | 1 | 1 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), @@ -1383,14 +1383,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) @@ -1400,14 +1400,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) @@ -1417,14 +1417,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) @@ -1434,14 +1434,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) @@ -1451,14 +1451,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) @@ -1468,14 +1468,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) @@ -1521,13 +1521,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), @@ -1539,8 +1539,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 @@ -1555,8 +1555,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 @@ -1569,13 +1569,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 @@ -1585,13 +1585,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 @@ -1601,13 +1601,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), @@ -1692,13 +1692,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 @@ -1711,13 +1711,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 @@ -1730,13 +1730,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 @@ -1749,13 +1749,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 @@ -1810,8 +1810,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 @@ -2392,10 +2392,10 @@ window w as (order by f_time desc range between 10 | 20:00:00 | 10 | 8 9 | 19:00:00 | 10 | 7 8 | 18:00:00 | 9 | 7 - 7 | 17:00:00 | 8 | 5 - 6 | 15:00:00 | 6 | 3 - 5 | 15:00:00 | 6 | 3 - 4 | 14:00:00 | 6 | 2 + 7 | 17:00:00 | 8 | 6 + 5 | 15:00:00 | 5 | 3 + 6 | 15:00:00 | 5 | 3 + 4 | 14:00:00 | 5 | 2 3 | 13:00:00 | 4 | 1 2 | 12:00:00 | 3 | 1 1 | 11:00:00 | 2 | 1 @@ -2428,10 +2428,10 @@ window w as (order by f_timetz desc range between 10 | 20:00:00+01 | 10 | 8 9 | 19:00:00+01 | 10 | 7 8 | 18:00:00+01 | 9 | 7 - 7 | 17:00:00+01 | 8 | 5 - 6 | 15:00:00+01 | 6 | 3 - 5 | 15:00:00+01 | 6 | 3 - 4 | 14:00:00+01 | 6 | 2 + 7 | 17:00:00+01 | 8 | 6 + 5 | 15:00:00+01 | 5 | 3 + 6 | 15:00:00+01 | 5 | 3 + 4 | 14:00:00+01 | 5 | 2 3 | 13:00:00+01 | 4 | 1 2 | 12:00:00+01 | 3 | 1 1 | 11:00:00+01 | 2 | 1 @@ -2465,9 +2465,9 @@ window w as (order by f_interval desc range between 9 | @ 9 years | 10 | 8 8 | @ 8 years | 9 | 7 7 | @ 7 years | 8 | 7 - 6 | @ 5 years | 6 | 4 - 5 | @ 5 years | 6 | 4 - 4 | @ 4 years | 6 | 3 + 5 | @ 5 years | 5 | 4 + 6 | @ 5 years | 5 | 4 + 4 | @ 4 years | 5 | 3 3 | @ 3 years | 4 | 2 2 | @ 2 years | 3 | 1 1 | @ 1 year | 2 | 1 @@ -2503,10 +2503,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 (10 rows) select id, f_timestamp, first_value(id) over w, last_value(id) over w @@ -2539,10 +2539,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 (10 rows) -- RANGE offset PRECEDING/FOLLOWING error cases @@ -2588,14 +2588,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) @@ -2605,14 +2605,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) @@ -2622,14 +2622,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) @@ -2639,14 +2639,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) @@ -2656,14 +2656,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) @@ -2673,14 +2673,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) @@ -2690,14 +2690,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) @@ -2707,14 +2707,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) @@ -2724,14 +2724,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) @@ -2741,14 +2741,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) @@ -2758,14 +2758,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) @@ -2775,14 +2775,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) @@ -2863,14 +2863,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), @@ -2880,14 +2880,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 @@ -2900,14 +2900,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 @@ -2919,13 +2919,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 @@ -3686,7 +3686,7 @@ SELECT * FROM depname | empno | salary | enroll_date | c1 | rn | c2 | c3 -----------+-------+--------+-------------+----+----+----+---- personnel | 5 | 3500 | 12-10-2007 | 2 | 1 | 2 | 2 - sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3 + sales | 1 | 5000 | 10-01-2006 | 3 | 1 | 3 | 3 (2 rows) -- Tests to ensure we don't push down the run condition when it's not valid to @@ -3824,7 +3824,7 @@ WHERE first_emp = 1 OR last_emp = 1; depname | empno | salary | enroll_date | first_emp | last_emp -----------+-------+--------+-------------+-----------+---------- develop | 8 | 6000 | 10-01-2006 | 1 | 5 - develop | 7 | 4200 | 01-01-2008 | 5 | 1 + develop | 7 | 4200 | 01-01-2008 | 4 | 1 personnel | 2 | 3900 | 12-23-2006 | 1 | 2 personnel | 5 | 3500 | 12-10-2007 | 2 | 1 sales | 1 | 5000 | 10-01-2006 | 1 | 3 -- 2.36.1