--
-- WINDOW FUNCTIONS
--
CREATE TEMPORARY TABLE empsalary (
    depname varchar,
    empno bigint,
    salary int,
    enroll_date date
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'depname' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO empsalary VALUES
('develop', 10, 5200, '2007-08-01'),
('sales', 1, 5000, '2006-10-01'),
('personnel', 5, 3500, '2007-12-10'),
('sales', 4, 4800, '2007-08-08'),
('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),
('develop', 9, 4500, '2008-01-01'),
('sales', 3, 4800, '2007-08-01'),
('develop', 8, 6000, '2006-10-01'),
('develop', 11, 5200, '2007-08-15');
SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;
  depname  | empno | salary |  sum  
-----------+-------+--------+-------
 develop   |     7 |   4200 | 25100
 develop   |     9 |   4500 | 25100
 develop   |    11 |   5200 | 25100
 develop   |    10 |   5200 | 25100
 develop   |     8 |   6000 | 25100
 personnel |     5 |   3500 |  7400
 personnel |     2 |   3900 |  7400
 sales     |     4 |   4800 | 14600
 sales     |     3 |   4800 | 14600
 sales     |     1 |   5000 | 14600
(10 rows)

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 personnel |     5 |   3500 |    1
 personnel |     2 |   3900 |    2
 sales     |     3 |   4800 |    1
 sales     |     4 |   4800 |    1
 sales     |     1 |   5000 |    3
 develop   |     7 |   4200 |    1
 develop   |     9 |   4500 |    2
 develop   |    10 |   5200 |    3
 develop   |    11 |   5200 |    3
 develop   |     8 |   6000 |    5
(10 rows)

-- with GROUP BY
SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1
GROUP BY four, ten ORDER BY four, ten;
 four | ten | sum  |          avg           
------+-----+------+------------------------
    0 |   0 |    0 | 0.00000000000000000000
    0 |   2 |    0 |     2.0000000000000000
    0 |   4 |    0 |     4.0000000000000000
    0 |   6 |    0 |     6.0000000000000000
    0 |   8 |    0 |     8.0000000000000000
    1 |   1 | 2500 | 1.00000000000000000000
    1 |   3 | 2500 |     3.0000000000000000
    1 |   5 | 2500 |     5.0000000000000000
    1 |   7 | 2500 |     7.0000000000000000
    1 |   9 | 2500 |     9.0000000000000000
    2 |   0 | 5000 | 0.00000000000000000000
    2 |   2 | 5000 |     2.0000000000000000
    2 |   4 | 5000 |     4.0000000000000000
    2 |   6 | 5000 |     6.0000000000000000
    2 |   8 | 5000 |     8.0000000000000000
    3 |   1 | 7500 | 1.00000000000000000000
    3 |   3 | 7500 |     3.0000000000000000
    3 |   5 | 7500 |     5.0000000000000000
    3 |   7 | 7500 |     7.0000000000000000
    3 |   9 | 7500 |     9.0000000000000000
(20 rows)

SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
  depname  | empno | salary |  sum  
-----------+-------+--------+-------
 personnel |     5 |   3500 |  7400
 personnel |     2 |   3900 |  7400
 sales     |     1 |   5000 | 14600
 sales     |     4 |   4800 | 14600
 sales     |     3 |   4800 | 14600
 develop   |    10 |   5200 | 25100
 develop   |     7 |   4200 | 25100
 develop   |     9 |   4500 | 25100
 develop   |     8 |   6000 | 25100
 develop   |    11 |   5200 | 25100
(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;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     7 |   4200 |    1
 sales     |     4 |   4800 |    1
 sales     |     3 |   4800 |    1
 personnel |     5 |   3500 |    1
 personnel |     2 |   3900 |    2
 develop   |     9 |   4500 |    2
 develop   |    10 |   5200 |    3
 develop   |    11 |   5200 |    3
 sales     |     1 |   5000 |    3
 develop   |     8 |   6000 |    5
(10 rows)

-- empty window specification
SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
 count 
-------
    10
    10
    10
    10
    10
    10
    10
    10
    10
    10
(10 rows)

SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
 count 
-------
    10
    10
    10
    10
    10
    10
    10
    10
    10
    10
(10 rows)

-- no window operation
SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
 four 
------
(0 rows)

-- cumulative aggregate
SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
 sum_1 | ten | four 
-------+-----+------
     1 |   9 |    1
     0 |   0 |    0
     0 |   0 |    0
     2 |   0 |    2
     3 |   1 |    3
     4 |   1 |    1
     5 |   1 |    1
     3 |   3 |    3
     0 |   4 |    0
     1 |   7 |    1
(10 rows)

SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
 row_number 
------------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
(10 rows)

SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
 rank_1 | ten | four 
--------+-----+------
      1 |   0 |    0
      1 |   0 |    0
      3 |   4 |    0
      1 |   1 |    1
      1 |   1 |    1
      3 |   7 |    1
      4 |   9 |    1
      1 |   0 |    2
      1 |   1 |    3
      2 |   3 |    3
(10 rows)

SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 dense_rank | ten | four 
------------+-----+------
          1 |   1 |    3
          2 |   3 |    3
          1 |   0 |    0
          1 |   0 |    0
          2 |   4 |    0
          1 |   1 |    1
          1 |   1 |    1
          2 |   7 |    1
          3 |   9 |    1
          1 |   0 |    2
(10 rows)

SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
   percent_rank    | ten | four 
-------------------+-----+------
                 0 |   1 |    3
                 1 |   3 |    3
                 0 |   0 |    0
                 0 |   0 |    0
                 1 |   4 |    0
                 0 |   1 |    1
                 0 |   1 |    1
 0.666666666666667 |   7 |    1
                 1 |   9 |    1
                 0 |   0 |    2
(10 rows)

SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
     cume_dist     | ten | four 
-------------------+-----+------
               0.5 |   1 |    3
                 1 |   3 |    3
 0.666666666666667 |   0 |    0
 0.666666666666667 |   0 |    0
                 1 |   4 |    0
               0.5 |   1 |    1
               0.5 |   1 |    1
              0.75 |   7 |    1
                 1 |   9 |    1
                 1 |   0 |    2
(10 rows)

SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10;
 ntile | ten | four 
-------+-----+------
     1 |   0 |    0
     1 |   0 |    0
     1 |   0 |    2
     1 |   1 |    1
     2 |   1 |    1
     2 |   1 |    3
     2 |   3 |    3
     3 |   4 |    0
     3 |   7 |    1
     3 |   9 |    1
(10 rows)

SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2;
 ntile | ten | four 
-------+-----+------
       |   0 |    0
       |   0 |    0
(2 rows)

SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lag | ten | four 
-----+-----+------
     |   1 |    3
   1 |   3 |    3
     |   0 |    0
   0 |   0 |    0
   0 |   4 |    0
     |   1 |    1
   1 |   1 |    1
   1 |   7 |    1
   7 |   9 |    1
     |   0 |    2
(10 rows)

SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lag | ten | four 
-----+-----+------
     |   1 |    3
     |   3 |    3
   0 |   0 |    0
   0 |   0 |    0
   4 |   4 |    0
     |   1 |    1
   1 |   1 |    1
   1 |   7 |    1
   7 |   9 |    1
     |   0 |    2
(10 rows)

SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lag | ten | four 
-----+-----+------
   0 |   0 |    0
   0 |   0 |    0
   4 |   4 |    0
   0 |   1 |    1
   1 |   1 |    1
   1 |   7 |    1
   7 |   9 |    1
   0 |   0 |    2
   0 |   1 |    3
   0 |   3 |    3
(10 rows)

SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lead | ten | four 
------+-----+------
    3 |   1 |    3
      |   3 |    3
    0 |   0 |    0
    4 |   0 |    0
      |   4 |    0
    1 |   1 |    1
    7 |   1 |    1
    9 |   7 |    1
      |   9 |    1
      |   0 |    2
(10 rows)

SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lead | ten | four 
------+-----+------
    6 |   1 |    3
      |   3 |    3
    0 |   0 |    0
    8 |   0 |    0
      |   4 |    0
    2 |   1 |    1
   14 |   1 |    1
   18 |   7 |    1
      |   9 |    1
      |   0 |    2
(10 rows)

SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 lead | ten | four 
------+-----+------
    6 |   1 |    3
   -1 |   3 |    3
    0 |   0 |    0
    8 |   0 |    0
   -1 |   4 |    0
    2 |   1 |    1
   14 |   1 |    1
   18 |   7 |    1
   -1 |   9 |    1
   -1 |   0 |    2
(10 rows)

SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 first_value | ten | four 
-------------+-----+------
           1 |   1 |    3
           1 |   3 |    3
           0 |   0 |    0
           0 |   0 |    0
           0 |   4 |    0
           1 |   1 |    1
           1 |   1 |    1
           1 |   7 |    1
           1 |   9 |    1
           0 |   0 |    2
(10 rows)

-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window.
-- the column `ten` is ordered, so we should call last_value on this
-- column. Using other cols the result is flaky because there are
-- tuples with the same `ten` while different other col values.
SELECT last_value(ten) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
 last_value | ten | four 
------------+-----+------
          0 |   0 |    2
          0 |   0 |    0
          0 |   0 |    0
          1 |   1 |    1
          1 |   1 |    3
          1 |   1 |    1
          3 |   3 |    3
          4 |   4 |    0
          7 |   7 |    1
          9 |   9 |    1
(10 rows)

set search_path=singleseg, public;
SELECT last_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM
	(SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
	ORDER BY four, ten;
 last_value | ten | four 
------------+-----+------
          0 |   0 |    0
          0 |   0 |    0
          4 |   4 |    0
          1 |   1 |    1
          1 |   1 |    1
          7 |   7 |    1
          9 |   9 |    1
          0 |   0 |    2
          1 |   1 |    3
          3 |   3 |    3
(10 rows)

SELECT nth_value(ten, four + 1) OVER (PARTITION BY four ORDER BY ten), ten, four
	FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s order by four,ten;
 nth_value | ten | four 
-----------+-----+------
         0 |   0 |    0
         0 |   0 |    0
         0 |   4 |    0
         1 |   1 |    1
         1 |   1 |    1
         1 |   7 |    1
         1 |   9 |    1
           |   0 |    2
           |   1 |    3
           |   3 |    3
(10 rows)

reset search_path;
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
FROM tenk1 GROUP BY ten, two;
 ten | two | gsum  |  wsum  
-----+-----+-------+--------
   0 |   0 | 45000 |  45000
   2 |   0 | 47000 |  92000
   4 |   0 | 49000 | 141000
   6 |   0 | 51000 | 192000
   8 |   0 | 53000 | 245000
   1 |   1 | 46000 |  46000
   3 |   1 | 48000 |  94000
   5 |   1 | 50000 | 144000
   7 |   1 | 52000 | 196000
   9 |   1 | 54000 | 250000
(10 rows)

SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
 count | four 
-------+------
     2 |    3
     2 |    3
     4 |    1
     4 |    1
     4 |    1
     4 |    1
(6 rows)

SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) +
  sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum
  FROM tenk1 WHERE unique2 < 10;
 cntsum 
--------
 92
 136
 22
 22
 87
 24
 24
 82
 92
 51
(10 rows)

-- opexpr with different windows evaluation.
SELECT * FROM(
  SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
    sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
    count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
    sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
    FROM tenk1
)sub
WHERE total <> fourcount + twosum;
 total | fourcount | twosum 
-------+-----------+--------
(0 rows)

SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
          avg           
------------------------
 0.00000000000000000000
 0.00000000000000000000
 0.00000000000000000000
 1.00000000000000000000
 1.00000000000000000000
 1.00000000000000000000
 1.00000000000000000000
     3.0000000000000000
     3.0000000000000000
     2.0000000000000000
(10 rows)

SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);
 ten | two | gsum  |  wsum  
-----+-----+-------+--------
   0 |   0 | 45000 |  45000
   2 |   0 | 47000 |  92000
   4 |   0 | 49000 | 141000
   6 |   0 | 51000 | 192000
   8 |   0 | 53000 | 245000
   1 |   1 | 46000 |  46000
   3 |   1 | 48000 |  94000
   5 |   1 | 50000 | 144000
   7 |   1 | 52000 | 196000
   9 |   1 | 54000 | 250000
(10 rows)

-- more than one window with GROUP BY
SELECT sum(salary),
	row_number() OVER (ORDER BY depname),
	sum(sum(salary)) OVER (ORDER BY depname DESC)
FROM empsalary GROUP BY depname;
  sum  | row_number |  sum  
-------+------------+-------
 25100 |          1 | 47100
  7400 |          2 | 22000
 14600 |          3 | 14600
(3 rows)

-- identical windows with different names
SELECT sum(salary) OVER w1, count(*) OVER w2
FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary);
  sum  | count 
-------+-------
  3500 |     1
  7400 |     2
 11600 |     3
 16100 |     4
 25700 |     6
 25700 |     6
 30700 |     7
 41100 |     9
 41100 |     9
 47100 |    10
(10 rows)

-- subplan
SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten)
FROM tenk1 s WHERE unique2 < 10;
 lead 
------
    3
     
    0
    0
    4
    1
    7
    9
     
    0
(10 rows)

-- empty table
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
 count 
-------
(0 rows)

-- mixture of agg/wfunc in the same window
SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
  sum  | rank 
-------+------
  6000 |    1
 16400 |    2
 16400 |    2
 20900 |    4
 25100 |    5
  3900 |    1
  7400 |    2
  5000 |    1
 14600 |    2
 14600 |    2
(10 rows)

-- strict aggs
SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM(
	SELECT *,
		CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus,
		CASE WHEN
			AVG(salary) OVER (PARTITION BY depname) < salary
		THEN 200 END AS depadj FROM empsalary
)s;
 empno |  depname  | salary | bonus | depadj | min  | max 
-------+-----------+--------+-------+--------+------+-----
     1 | sales     |   5000 |  1000 |    200 | 1000 | 200
     2 | personnel |   3900 |  1000 |    200 | 1000 | 200
     3 | sales     |   4800 |   500 |        |  500 | 200
     4 | sales     |   4800 |   500 |        |  500 | 200
     5 | personnel |   3500 |   500 |        |  500 | 200
     7 | develop   |   4200 |       |        |  500 | 200
     8 | develop   |   6000 |  1000 |    200 |  500 | 200
     9 | develop   |   4500 |       |        |  500 | 200
    10 | develop   |   5200 |   500 |    200 |  500 | 200
    11 | develop   |   5200 |   500 |    200 |  500 | 200
(10 rows)

-- window function over ungrouped agg over empty row set (bug before 9.1)
SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42;
 sum 
-----
   0
(1 row)

-- window function with ORDER BY an expression involving aggregates (9.1 bug)
select ten,
  sum(unique1) + sum(unique2) as res,
  rank() over (order by sum(unique1) + sum(unique2)) as rank
from tenk1
group by ten order by ten;
 ten |   res    | rank 
-----+----------+------
   0 |  9976146 |    4
   1 | 10114187 |    9
   2 | 10059554 |    8
   3 |  9878541 |    1
   4 |  9881005 |    2
   5 |  9981670 |    5
   6 |  9947099 |    3
   7 | 10120309 |   10
   8 |  9991305 |    6
   9 | 10040184 |    7
(10 rows)

-- window and aggregate with GROUP BY expression (9.2 bug)
explain (costs off)
select first_value(max(x)) over (), y
  from (select unique1 as x, ten+four as y from tenk1) ss
  group by y;
                               QUERY PLAN                               
------------------------------------------------------------------------
 WindowAgg
   ->  Gather Motion 3:1  (slice2; segments: 3)
         ->  Subquery Scan on "Window"
               ->  HashAggregate
                     Group Key: ((tenk1.ten + tenk1.four))
                     ->  Redistribute Motion 3:3  (slice1; segments: 3)
                           Hash Key: ((tenk1.ten + tenk1.four))
                           ->  HashAggregate
                                 Group Key: (tenk1.ten + tenk1.four)
                                 ->  Seq Scan on tenk1
 Optimizer: legacy query optimizer
(11 rows)

-- test non-default frame specifications
SELECT four, ten,
	sum(ten) over (partition by four order by ten),
	last_value(ten) over (partition by four order by ten)
FROM (select distinct ten, four from tenk1) ss;
 four | ten | sum | last_value 
------+-----+-----+------------
    3 |   1 |   1 |          1
    3 |   3 |   4 |          3
    3 |   5 |   9 |          5
    3 |   7 |  16 |          7
    3 |   9 |  25 |          9
    0 |   0 |   0 |          0
    0 |   2 |   2 |          2
    0 |   4 |   6 |          4
    0 |   6 |  12 |          6
    0 |   8 |  20 |          8
    1 |   1 |   1 |          1
    1 |   3 |   4 |          3
    1 |   5 |   9 |          5
    1 |   7 |  16 |          7
    1 |   9 |  25 |          9
    2 |   0 |   0 |          0
    2 |   2 |   2 |          2
    2 |   4 |   6 |          4
    2 |   6 |  12 |          6
    2 |   8 |  20 |          8
(20 rows)

SELECT four, ten,
	sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
	last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
 four | ten | sum | last_value 
------+-----+-----+------------
    0 |   0 |   0 |          0
    0 |   2 |   2 |          2
    0 |   4 |   6 |          4
    0 |   6 |  12 |          6
    0 |   8 |  20 |          8
    1 |   1 |   1 |          1
    1 |   3 |   4 |          3
    1 |   5 |   9 |          5
    1 |   7 |  16 |          7
    1 |   9 |  25 |          9
    2 |   0 |   0 |          0
    2 |   2 |   2 |          2
    2 |   4 |   6 |          4
    2 |   6 |  12 |          6
    2 |   8 |  20 |          8
    3 |   1 |   1 |          1
    3 |   3 |   4 |          3
    3 |   5 |   9 |          5
    3 |   7 |  16 |          7
    3 |   9 |  25 |          9
(20 rows)

SELECT four, ten,
	sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
	last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
FROM (select distinct ten, four from tenk1) ss;
 four | ten | sum | last_value 
------+-----+-----+------------
    3 |   1 |  25 |          9
    3 |   3 |  25 |          9
    3 |   5 |  25 |          9
    3 |   7 |  25 |          9
    3 |   9 |  25 |          9
    0 |   0 |  20 |          8
    0 |   2 |  20 |          8
    0 |   4 |  20 |          8
    0 |   6 |  20 |          8
    0 |   8 |  20 |          8
    1 |   1 |  25 |          9
    1 |   3 |  25 |          9
    1 |   5 |  25 |          9
    1 |   7 |  25 |          9
    1 |   9 |  25 |          9
    2 |   0 |  20 |          8
    2 |   2 |  20 |          8
    2 |   4 |  20 |          8
    2 |   6 |  20 |          8
    2 |   8 |  20 |          8
(20 rows)

SELECT four, ten/4 as two,
	sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
	last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
 four | two | sum | last_value 
------+-----+-----+------------
    3 |   0 |   0 |          0
    3 |   0 |   0 |          0
    3 |   1 |   2 |          1
    3 |   1 |   2 |          1
    3 |   2 |   4 |          2
    0 |   0 |   0 |          0
    0 |   0 |   0 |          0
    0 |   1 |   2 |          1
    0 |   1 |   2 |          1
    0 |   2 |   4 |          2
    1 |   0 |   0 |          0
    1 |   0 |   0 |          0
    1 |   1 |   2 |          1
    1 |   1 |   2 |          1
    1 |   2 |   4 |          2
    2 |   0 |   0 |          0
    2 |   0 |   0 |          0
    2 |   1 |   2 |          1
    2 |   1 |   2 |          1
    2 |   2 |   4 |          2
(20 rows)

SELECT four, ten/4 as two,
	sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
	last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;
 four | two | sum | last_value 
------+-----+-----+------------
    3 |   0 |   0 |          0
    3 |   0 |   0 |          0
    3 |   1 |   1 |          1
    3 |   1 |   2 |          1
    3 |   2 |   4 |          2
    0 |   0 |   0 |          0
    0 |   0 |   0 |          0
    0 |   1 |   1 |          1
    0 |   1 |   2 |          1
    0 |   2 |   4 |          2
    1 |   0 |   0 |          0
    1 |   0 |   0 |          0
    1 |   1 |   1 |          1
    1 |   1 |   2 |          1
    1 |   2 |   4 |          2
    2 |   0 |   0 |          0
    2 |   0 |   0 |          0
    2 |   1 |   1 |          1
    2 |   1 |   2 |          1
    2 |   2 |   4 |          2
(20 rows)

SELECT sum(unique1) over (order by four range between current row and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
  45 |       0 |    0
  45 |       8 |    0
  45 |       4 |    0
  33 |       5 |    1
  33 |       1 |    1
  33 |       9 |    1
  18 |       2 |    2
  18 |       6 |    2
  10 |       7 |    3
  10 |       3 |    3
(10 rows)

set search_path=singleseg, public;
SELECT sum(unique1) over (rows between current row and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
  45 |       4 |    0
  41 |       2 |    2
  39 |       1 |    1
  38 |       6 |    2
  32 |       9 |    1
  23 |       8 |    0
  15 |       5 |    1
  10 |       3 |    3
   7 |       7 |    3
   0 |       0 |    0
(10 rows)

SELECT sum(unique1) over (rows between 2 preceding and 2 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
   7 |       4 |    0
  13 |       2 |    2
  22 |       1 |    1
  26 |       6 |    2
  29 |       9 |    1
  31 |       8 |    0
  32 |       5 |    1
  23 |       3 |    3
  15 |       7 |    3
  10 |       0 |    0
(10 rows)

SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
	unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
     |       4 |    0
   4 |       2 |    2
   6 |       1 |    1
   3 |       6 |    2
   7 |       9 |    1
  15 |       8 |    0
  17 |       5 |    1
  13 |       3 |    3
   8 |       7 |    3
  10 |       0 |    0
(10 rows)

SELECT sum(unique1) over (rows between 1 following and 3 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
   9 |       4 |    0
  16 |       2 |    2
  23 |       1 |    1
  22 |       6 |    2
  16 |       9 |    1
  15 |       8 |    0
  10 |       5 |    1
   7 |       3 |    3
   0 |       7 |    3
     |       0 |    0
(10 rows)

SELECT sum(unique1) over (rows between unbounded preceding and 1 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
   6 |       4 |    0
   7 |       2 |    2
  13 |       1 |    1
  22 |       6 |    2
  30 |       9 |    1
  35 |       8 |    0
  38 |       5 |    1
  45 |       3 |    3
  45 |       7 |    3
  45 |       0 |    0
(10 rows)

SELECT sum(unique1) over (w range between current row and unbounded following),
	unique1, four
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
  33 |       1 |    1
  18 |       2 |    2
  18 |       6 |    2
  10 |       7 |    3
  10 |       3 |    3
(10 rows)

-- fails on PostgreSQL: not implemented yet
-- Has been implemented in GPDB.
SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding),
	unique1, four
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 | four 
-----+---------+------
     |       0 |    0
     |       8 |    0
     |       4 |    0
  12 |       5 |    1
  12 |       9 |    1
  12 |       1 |    1
  27 |       2 |    2
  27 |       6 |    2
  23 |       7 |    3
  23 |       3 |    3
(10 rows)

SELECT first_value(unique1) over w,
	nth_value(unique1, 2) over w AS nth_2,
	last_value(unique1) over w, unique1, four
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 |          3 |       0 |    0
           0 |     8 |          3 |       8 |    0
           0 |     8 |          3 |       4 |    0
           5 |     9 |          3 |       5 |    1
           5 |     9 |          3 |       9 |    1
           5 |     9 |          3 |       1 |    1
           2 |     6 |          3 |       2 |    2
           2 |     6 |          3 |       6 |    2
           7 |     3 |          3 |       7 |    3
           7 |     3 |          3 |       3 |    3
(10 rows)

SELECT sum(unique1) over
	(order by unique1
	 rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING),
	unique1
FROM tenk1 WHERE unique1 < 10;
 sum | unique1 
-----+---------
   0 |       0
   1 |       1
   3 |       2
   5 |       3
   7 |       4
   9 |       5
  11 |       6
  13 |       7
  15 |       8
  17 |       9
(10 rows)

CREATE TEMP VIEW v_window AS
	SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows
	FROM generate_series(1, 10) i;
SELECT * FROM v_window;
 i  | sum_rows 
----+----------
  1 |        3
  2 |        6
  3 |        9
  4 |       12
  5 |       15
  6 |       18
  7 |       21
  8 |       24
  9 |       27
 10 |       19
(10 rows)

SELECT pg_get_viewdef('v_window');
                                    pg_get_viewdef                                     
---------------------------------------------------------------------------------------
  SELECT i.i,                                                                         +
     sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+
    FROM generate_series(1, 10) i(i);
(1 row)

reset search_path;
-- with UNION
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
 count 
-------
(0 rows)

-- ordering by a non-integer constant is allowed
SELECT rank() OVER (ORDER BY length('abc'));
 rank 
------
    1
(1 row)

-- can't order by another window function
SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random()));
ERROR:  window functions are not allowed in window definitions
LINE 1: SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random())...
                                     ^
-- some other errors
SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
ERROR:  window functions are not allowed in WHERE
LINE 1: SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY sa...
                                      ^
SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10;
ERROR:  window functions are not allowed in JOIN conditions
LINE 1: SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVE...
                                                    ^
SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
ERROR:  window functions are not allowed in GROUP BY
LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO...
               ^
SELECT * FROM rank() OVER (ORDER BY random());
ERROR:  syntax error at or near "ORDER"
LINE 1: SELECT * FROM rank() OVER (ORDER BY random());
                                   ^
DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;
ERROR:  window functions are not allowed in WHERE
LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())...
                                     ^
DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
ERROR:  window functions are not allowed in RETURNING
LINE 1: DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random...
                                        ^
SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1);
ERROR:  window "w" is already defined
LINE 1: ...w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY ...
                                                             ^
SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
ERROR:  syntax error at or near "ORDER"
LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
                                               ^
-- Not allowed in PostgreSQL, but is allowed in GPDB for backwards-compatibility.
-- Added LIMIT to reduce the size of the output.
SELECT count() OVER () FROM tenk1 limit 5;
 count 
-------
 10000
 10000
 10000
 10000
 10000
(5 rows)

SELECT generate_series(1, 100) OVER () FROM empsalary;
ERROR:  OVER specified, but generate_series is not a window function nor an aggregate function
LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
               ^
SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR:  argument of ntile must be greater than zero
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR:  argument of nth_value must be greater than zero
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT depname,
          sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
          min(salary) OVER (PARTITION BY depname, empno order by enroll_date) depminsalary
   FROM empsalary) emp
WHERE depname = 'sales';
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)
   ->  Subquery Scan on emp
         ->  WindowAgg
               Order By: empsalary.empno
               ->  Sort
                     Sort Key: empsalary.empno
                     ->  Redistribute Motion 3:3  (slice2; segments: 3)
                           Hash Key: empsalary.depname
                           ->  WindowAgg
                                 Partition By: empsalary.empno
                                 Order By: empsalary.enroll_date
                                 ->  Sort
                                       Sort Key: empsalary.empno, empsalary.enroll_date
                                       ->  Redistribute Motion 3:3  (slice1; segments: 3)
                                             Hash Key: empsalary.depname, empsalary.empno
                                             ->  Seq Scan on empsalary
                                                   Filter: ((depname)::text = 'sales'::text)
 Optimizer: legacy query optimizer
(18 rows)

-- Test Sort node reordering
EXPLAIN (COSTS OFF)
SELECT
  lead(1) OVER (PARTITION BY depname ORDER BY salary, enroll_date),
  lag(1) OVER (PARTITION BY depname ORDER BY salary,enroll_date,empno)
FROM empsalary;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)
   ->  WindowAgg
         Partition By: depname
         Order By: salary, enroll_date
         ->  WindowAgg
               Partition By: depname
               Order By: salary, enroll_date, empno
               ->  Sort
                     Sort Key: depname, salary, enroll_date, empno
                     ->  Redistribute Motion 3:3  (slice1; segments: 3)
                           Hash Key: depname
                           ->  Seq Scan on empsalary
 Optimizer: legacy query optimizer
(13 rows)

-- cleanup
DROP TABLE empsalary;
--
-- Test the basic moving-aggregate machinery
--
-- create aggregates that record the series of transform calls (these are
-- intentionally not true inverses)
CREATE FUNCTION logging_sfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '-' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;
CREATE AGGREGATE logging_agg_nonstrict (anyelement)
(
	stype = text,
	sfunc = logging_sfunc_nonstrict,
	mstype = text,
	msfunc = logging_msfunc_nonstrict,
	minvfunc = logging_minvfunc_nonstrict
);
CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement)
(
	stype = text,
	sfunc = logging_sfunc_nonstrict,
	mstype = text,
	msfunc = logging_msfunc_nonstrict,
	minvfunc = logging_minvfunc_nonstrict,
	initcond = 'I',
	minitcond = 'MI'
);
CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '*' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '+' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '-' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;
CREATE AGGREGATE logging_agg_strict (text)
(
	stype = text,
	sfunc = logging_sfunc_strict,
	mstype = text,
	msfunc = logging_msfunc_strict,
	minvfunc = logging_minvfunc_strict
);
CREATE AGGREGATE logging_agg_strict_initcond (anyelement)
(
	stype = text,
	sfunc = logging_sfunc_strict,
	mstype = text,
	msfunc = logging_msfunc_strict,
	minvfunc = logging_minvfunc_strict,
	initcond = 'I',
	minitcond = 'MI'
);
-- test strict and non-strict cases
SELECT
	p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS row,
	logging_agg_nonstrict(v) over wnd as nstrict,
	logging_agg_nonstrict_initcond(v) over wnd as nstrict_init,
	logging_agg_strict(v::text) over wnd as strict,
	logging_agg_strict_initcond(v) over wnd as strict_init
FROM (VALUES
	(1, 1, NULL),
	(1, 2, 'a'),
	(1, 3, 'b'),
	(1, 4, NULL),
	(1, 5, NULL),
	(1, 6, 'c'),
	(2, 1, NULL),
	(2, 2, 'x'),
	(3, 1, 'z')
) AS t(p, i, v)
WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY p, i;
   row    |                    nstrict                    |                  nstrict_init                   |  strict   |  strict_init   
----------+-----------------------------------------------+-------------------------------------------------+-----------+----------------
 1,1:NULL | +NULL                                         | MI+NULL                                         |           | MI
 1,2:a    | +NULL+'a'                                     | MI+NULL+'a'                                     | a         | MI+'a'
 1,3:b    | +NULL+'a'-NULL+'b'                            | MI+NULL+'a'-NULL+'b'                            | a+'b'     | MI+'a'+'b'
 1,4:NULL | +NULL+'a'-NULL+'b'-'a'+NULL                   | MI+NULL+'a'-NULL+'b'-'a'+NULL                   | a+'b'-'a' | MI+'a'+'b'-'a'
 1,5:NULL | +NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL          | MI+NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL          |           | MI
 1,6:c    | +NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL-NULL+'c' | MI+NULL+'a'-NULL+'b'-'a'+NULL-'b'+NULL-NULL+'c' | c         | MI+'c'
 2,1:NULL | +NULL                                         | MI+NULL                                         |           | MI
 2,2:x    | +NULL+'x'                                     | MI+NULL+'x'                                     | x         | MI+'x'
 3,1:z    | +'z'                                          | MI+'z'                                          | z         | MI+'z'
(9 rows)

-- and again, but with filter
SELECT
	p::text || ',' || i::text || ':' ||
		CASE WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row,
	logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt,
	logging_agg_nonstrict_initcond(v) filter(where f) over wnd as nstrict_init_filt,
	logging_agg_strict(v::text) filter(where f) over wnd as strict_filt,
	logging_agg_strict_initcond(v) filter(where f) over wnd as strict_init_filt
FROM (VALUES
	(1, 1, true,  NULL),
	(1, 2, false, 'a'),
	(1, 3, true,  'b'),
	(1, 4, false, NULL),
	(1, 5, false, NULL),
	(1, 6, false, 'c'),
	(2, 1, false, NULL),
	(2, 2, true,  'x'),
	(3, 1, true,  'z')
) AS t(p, i, f, v)
WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY p, i;
   row    | nstrict_filt | nstrict_init_filt | strict_filt | strict_init_filt 
----------+--------------+-------------------+-------------+------------------
 1,1:NULL | +NULL        | MI+NULL           |             | MI
 1,2:-    | +NULL        | MI+NULL           |             | MI
 1,3:b    | +'b'         | MI+'b'            | b           | MI+'b'
 1,4:-    | +'b'         | MI+'b'            | b           | MI+'b'
 1,5:-    |              | MI                |             | MI
 1,6:-    |              | MI                |             | MI
 2,1:-    |              | MI                |             | MI
 2,2:x    | +'x'         | MI+'x'            | x           | MI+'x'
 3,1:z    | +'z'         | MI+'z'            | z           | MI+'z'
(9 rows)

-- test that volatile arguments disable moving-aggregate mode
SELECT
	i::text || ':' || COALESCE(v::text, 'NULL') as row,
	logging_agg_strict(v::text)
		over wnd as inverse,
	logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END)
		over wnd as noinverse
FROM (VALUES
	(1, 'a'),
	(2, 'b'),
	(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY i;
 row |    inverse    | noinverse 
-----+---------------+-----------
 1:a | a             | a
 2:b | a+'b'         | a*'b'
 3:c | a+'b'-'a'+'c' | b*'c'
(3 rows)

SELECT
	i::text || ':' || COALESCE(v::text, 'NULL') as row,
	logging_agg_strict(v::text) filter(where true)
		over wnd as inverse,
	logging_agg_strict(v::text) filter(where random() >= 0)
		over wnd as noinverse
FROM (VALUES
	(1, 'a'),
	(2, 'b'),
	(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY i;
 row |    inverse    | noinverse 
-----+---------------+-----------
 1:a | a             | a
 2:b | a+'b'         | a*'b'
 3:c | a+'b'-'a'+'c' | b*'c'
(3 rows)

-- test that non-overlapping windows don't use inverse transitions
SELECT
	logging_agg_strict(v::text) OVER wnd
FROM (VALUES
	(1, 'a'),
	(2, 'b'),
	(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
ORDER BY i;
 logging_agg_strict 
--------------------
 a
 b
 c
(3 rows)

-- test that returning NULL from the inverse transition functions
-- restarts the aggregation from scratch. The second aggregate is supposed
-- to test cases where only some aggregates restart, the third one checks
-- that one aggregate restarting doesn't cause others to restart.
CREATE FUNCTION sum_int_randrestart_minvfunc(int4, int4) RETURNS int4 AS
$$ SELECT CASE WHEN random() < 0.2 THEN NULL ELSE $1 - $2 END $$
LANGUAGE SQL STRICT;
CREATE AGGREGATE sum_int_randomrestart (int4)
(
	stype = int4,
	sfunc = int4pl,
	mstype = int4,
	msfunc = int4pl,
	minvfunc = sum_int_randrestart_minvfunc
);
-- In PostgreSQL, the 'vs' CTE is constructed using random() and
-- generate_series(), but GPDB inlines CTEs even when they contain volatile
-- expressions, causing incorrect results. That's a bug in GPDB, of course,
-- but for the purposes of this test, we work around that by using a
-- non-volatile WITH clause. The list of values below was created by running
-- the original subquery using random() once, and copying the result here.
--
-- See https://github.com/greenplum-db/gpdb/issues/1349
WITH
vs (i, v) AS (
VALUES
 ( 1, 18),
 ( 2, 91),
 ( 3, 62),
 ( 4, 34),
 ( 5, 12),
 ( 6, 99),
 ( 7,  4),
 ( 8, 32),
 ( 9, 75),
 (10, 38),
 (11,  0),
 (12, 43),
 (13, 95),
 (14, 83),
 (15, 99),
 (16, 44),
 (17, 27),
 (18, 11),
 (19, 27),
 (20, 19),
 (21, 71),
 (22, 52),
 (23, 49),
 (24, 58),
 (25, 35),
 (26, 66),
 (27, 12),
 (28, 49),
 (29,  9),
 (30, 89),
 (31,  7),
 (32, 27),
 (33, 80),
 (34, 69),
 (35, 61),
 (36, 92),
 (37, 68),
 (38, 65),
 (39, 23),
 (40, 43),
 (41,  3),
 (42, 24),
 (43, 86),
 (44, 98),
 (45,  6),
 (46, 85),
 (47, 42),
 (48, 33),
 (49, 96),
 (50, 68),
 (51, 52),
 (52, 67),
 (53, 20),
 (54,  1),
 (55, 25),
 (56, 55),
 (57, 67),
 (58, 37),
 (59,  4),
 (60, 76),
 (61, 26),
 (62, 11),
 (63,  3),
 (64,  6),
 (65, 80),
 (66, 64),
 (67, 98),
 (68, 48),
 (69, 29),
 (70, 21),
 (71, 91),
 (72, 31),
 (73, 45),
 (74, 77),
 (75, 29),
 (76, 51),
 (77, 63),
 (78, 71),
 (79, 84),
 (80, 59),
 (81, 39),
 (82, 36),
 (83, 26),
 (84, 60),
 (85, 37),
 (86, 51),
 (87, 15),
 (88,  4),
 (89, 88),
 (90, 19),
 (91, 80),
 (92, 14),
 (93, 30),
 (94, 83),
 (95, 20),
 (96, 10),
 (97, 47),
 (98, 18),
 (99, 58),
(100, 75)
),
sum_following AS (
	SELECT i, SUM(v) OVER
		(ORDER BY i DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s
	FROM vs
)
SELECT DISTINCT
	sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1,
	-sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2,
	100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3
FROM vs
JOIN sum_following ON sum_following.i = vs.i
WINDOW fwd AS (
	ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
 eq1 | eq2 | eq3 
-----+-----+-----
 t   | t   | t
(1 row)

--
-- Test various built-in aggregates that have moving-aggregate support
--
-- test inverse transition functions handle NULLs properly
SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i |        avg         
---+--------------------
 1 | 1.5000000000000000
 2 | 2.0000000000000000
 3 |                   
 4 |                   
(4 rows)

SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i |        avg         
---+--------------------
 1 | 1.5000000000000000
 2 | 2.0000000000000000
 3 |                   
 4 |                   
(4 rows)

SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i |        avg         
---+--------------------
 1 | 1.5000000000000000
 2 | 2.0000000000000000
 3 |                   
 4 |                   
(4 rows)

SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v);
 i |        avg         
---+--------------------
 1 | 2.0000000000000000
 2 | 2.5000000000000000
 3 |                   
 4 |                   
(4 rows)

SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);
 i |    avg     
---+------------
 1 | @ 1.5 secs
 2 | @ 2 secs
 3 | 
 4 | 
(4 rows)

SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i | sum 
---+-----
 1 |   3
 2 |   2
 3 |    
 4 |    
(4 rows)

SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i | sum 
---+-----
 1 |   3
 2 |   2
 3 |    
 4 |    
(4 rows)

SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i | sum 
---+-----
 1 |   3
 2 |   2
 3 |    
 4 |    
(4 rows)

SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v);
 i |  sum  
---+-------
 1 | $3.30
 2 | $2.20
 3 |      
 4 |      
(4 rows)

SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);
 i |   sum    
---+----------
 1 | @ 3 secs
 2 | @ 2 secs
 3 | 
 4 | 
(4 rows)

SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v);
 i | sum 
---+-----
 1 | 3.3
 2 | 2.2
 3 |    
 4 |    
(4 rows)

SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n);
 sum  
------
 6.01
    5
    3
(3 rows)

SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i | count 
---+-------
 1 |     2
 2 |     1
 3 |     0
 4 |     0
(4 rows)

SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i | count 
---+-------
 1 |     4
 2 |     3
 3 |     2
 4 |     1
(4 rows)

SELECT VAR_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
        var_pop        
-----------------------
    21704.000000000000
    13868.750000000000
    11266.666666666667
 4225.0000000000000000
                     0
(5 rows)

SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
        var_pop        
-----------------------
    21704.000000000000
    13868.750000000000
    11266.666666666667
 4225.0000000000000000
                     0
(5 rows)

SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
        var_pop        
-----------------------
    21704.000000000000
    13868.750000000000
    11266.666666666667
 4225.0000000000000000
                     0
(5 rows)

SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
        var_pop        
-----------------------
    21704.000000000000
    13868.750000000000
    11266.666666666667
 4225.0000000000000000
                     0
(5 rows)

SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       var_samp        
-----------------------
    27130.000000000000
    18491.666666666667
    16900.000000000000
 8450.0000000000000000
                      
(5 rows)

SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       var_samp        
-----------------------
    27130.000000000000
    18491.666666666667
    16900.000000000000
 8450.0000000000000000
                      
(5 rows)

SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       var_samp        
-----------------------
    27130.000000000000
    18491.666666666667
    16900.000000000000
 8450.0000000000000000
                      
(5 rows)

SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       var_samp        
-----------------------
    27130.000000000000
    18491.666666666667
    16900.000000000000
 8450.0000000000000000
                      
(5 rows)

SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       variance        
-----------------------
    27130.000000000000
    18491.666666666667
    16900.000000000000
 8450.0000000000000000
                      
(5 rows)

SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       variance        
-----------------------
    27130.000000000000
    18491.666666666667
    16900.000000000000
 8450.0000000000000000
                      
(5 rows)

SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       variance        
-----------------------
    27130.000000000000
    18491.666666666667
    16900.000000000000
 8450.0000000000000000
                      
(5 rows)

SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       variance        
-----------------------
    27130.000000000000
    18491.666666666667
    16900.000000000000
 8450.0000000000000000
                      
(5 rows)

SELECT STDDEV_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
     stddev_pop      
---------------------
    147.322774885623
    147.322774885623
    117.765657133139
    106.144555520604
 65.0000000000000000
                   0
(6 rows)

SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
     stddev_pop      
---------------------
    147.322774885623
    147.322774885623
    117.765657133139
    106.144555520604
 65.0000000000000000
                   0
(6 rows)

SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
     stddev_pop      
---------------------
    147.322774885623
    147.322774885623
    117.765657133139
    106.144555520604
 65.0000000000000000
                   0
(6 rows)

SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
     stddev_pop      
---------------------
    147.322774885623
    147.322774885623
    117.765657133139
    106.144555520604
 65.0000000000000000
                   0
(6 rows)

SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
     stddev_samp     
---------------------
    164.711869639076
    164.711869639076
    135.984067694222
    130.000000000000
 91.9238815542511782
                    
(6 rows)

SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
     stddev_samp     
---------------------
    164.711869639076
    164.711869639076
    135.984067694222
    130.000000000000
 91.9238815542511782
                    
(6 rows)

SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
     stddev_samp     
---------------------
    164.711869639076
    164.711869639076
    135.984067694222
    130.000000000000
 91.9238815542511782
                    
(6 rows)

SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
     stddev_samp     
---------------------
    164.711869639076
    164.711869639076
    135.984067694222
    130.000000000000
 91.9238815542511782
                    
(6 rows)

SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       stddev        
---------------------
    164.711869639076
    164.711869639076
    135.984067694222
    130.000000000000
 91.9238815542511782
                    
(6 rows)

SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       stddev        
---------------------
    164.711869639076
    164.711869639076
    135.984067694222
    130.000000000000
 91.9238815542511782
                    
(6 rows)

SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       stddev        
---------------------
    164.711869639076
    164.711869639076
    135.984067694222
    130.000000000000
 91.9238815542511782
                    
(6 rows)

SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
       stddev        
---------------------
    164.711869639076
    164.711869639076
    135.984067694222
    130.000000000000
 91.9238815542511782
                    
(6 rows)

-- test that inverse transition functions work with various frame options
SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i | sum 
---+-----
 1 |   1
 2 |   2
 3 |    
 4 |    
(4 rows)

SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
 i | sum 
---+-----
 1 |   3
 2 |   2
 3 |    
 4 |    
(4 rows)

SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v);
 i | sum 
---+-----
 1 |   3
 2 |   6
 3 |   9
 4 |   7
(4 rows)

-- ensure aggregate over numeric properly recovers from NaN values
SELECT a, b,
       SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b);
 a |  b  | sum 
---+-----+-----
 1 |   1 |   1
 2 |   2 |   3
 3 | NaN | NaN
 4 |   3 | NaN
 5 |   4 |   7
(5 rows)

-- It might be tempting for someone to add an inverse trans function for
-- float and double precision. This should not be done as it can give incorrect
-- results. This test should fail if anyone ever does this without thinking too
-- hard about it.
SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9')
  FROM (VALUES(1,1e20),(2,1)) n(i,n);
         to_char          
--------------------------
  100000000000000000000
                      1.0
(2 rows)

SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
  FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
  WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);
 i | b | bool_and | bool_or 
---+---+----------+---------
 1 | t | t        | t
 2 | t | f        | t
 3 | f | f        | f
 4 | f | f        | t
 5 | t | t        | t
(5 rows)