Support window functions a la SQL:2008.

Hitoshi Harada, with some kibitzing from Heikki and Tom.

5 files changed, 857 insertions, 5 deletions

diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
new file mode 100644
index 00000000000..ee16087b92c
--- /dev/null
+++ b/src/test/regress/expected/window.out
@@ -0,0 +1,672 @@
+--
+-- WINDOW FUNCTIONS
+--
+CREATE TEMPORARY TABLE empsalary (
+    depname varchar,
+    empno bigint,
+    salary int,
+    enroll_date date
+);
+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     |     3 |   4800 | 14600
+ sales     |     4 |   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 
+-----------+-------+--------+------
+ develop   |     7 |   4200 |    1
+ develop   |     9 |   4500 |    2
+ develop   |    11 |   5200 |    3
+ develop   |    10 |   5200 |    3
+ develop   |     8 |   6000 |    5
+ personnel |     5 |   3500 |    1
+ personnel |     2 |   3900 |    2
+ sales     |     3 |   4800 |    1
+ sales     |     4 |   4800 |    1
+ sales     |     1 |   5000 |    3
+(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  
+-----------+-------+--------+-------
+ develop   |    11 |   5200 | 25100
+ develop   |     7 |   4200 | 25100
+ develop   |     9 |   4500 | 25100
+ develop   |     8 |   6000 | 25100
+ develop   |    10 |   5200 | 25100
+ personnel |     5 |   3500 |  7400
+ personnel |     2 |   3900 |  7400
+ sales     |     3 |   4800 | 14600
+ sales     |     1 |   5000 | 14600
+ sales     |     4 |   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;
+  depname  | empno | salary | rank 
+-----------+-------+--------+------
+ develop   |     7 |   4200 |    1
+ personnel |     5 |   3500 |    1
+ sales     |     3 |   4800 |    1
+ sales     |     4 |   4800 |    1
+ personnel |     2 |   3900 |    2
+ develop   |     9 |   4500 |    2
+ sales     |     1 |   5000 |    3
+ develop   |    11 |   5200 |    3
+ develop   |    10 |   5200 |    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 
+-------+-----+------
+     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
+     1 |   9 |    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 |   0 |    0
+          1 |   0 |    0
+          2 |   4 |    0
+          1 |   1 |    1
+          1 |   1 |    1
+          2 |   7 |    1
+          3 |   9 |    1
+          1 |   0 |    2
+          1 |   1 |    3
+          2 |   3 |    3
+(10 rows)
+
+SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+   percent_rank    | ten | four 
+-------------------+-----+------
+                 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
+                 0 |   1 |    3
+                 1 |   3 |    3
+(10 rows)
+
+SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+     cume_dist     | ten | four 
+-------------------+-----+------
+ 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
+               0.5 |   1 |    3
+                 1 |   3 |    3
+(10 rows)
+
+SELECT ntile(3) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+ ntile | ten | four 
+-------+-----+------
+     1 |   0 |    0
+     1 |   0 |    2
+     1 |   0 |    0
+     1 |   1 |    1
+     2 |   1 |    3
+     2 |   1 |    1
+     2 |   3 |    3
+     3 |   4 |    0
+     3 |   7 |    1
+     3 |   9 |    1
+(10 rows)
+
+SELECT ntile(NULL) OVER (ORDER BY ten), ten, four FROM tenk1 LIMIT 1;
+ ntile | ten | four 
+-------+-----+------
+       |   0 |    0
+(1 row)
+
+SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+ lag | ten | four 
+-----+-----+------
+     |   0 |    0
+   0 |   0 |    0
+   0 |   4 |    0
+     |   1 |    1
+   1 |   1 |    1
+   1 |   7 |    1
+   7 |   9 |    1
+     |   0 |    2
+     |   1 |    3
+   1 |   3 |    3
+(10 rows)
+
+SELECT lag(ten, four) 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
+     |   1 |    1
+   1 |   1 |    1
+   1 |   7 |    1
+   7 |   9 |    1
+     |   0 |    2
+     |   1 |    3
+     |   3 |    3
+(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 
+------+-----+------
+    0 |   0 |    0
+    4 |   0 |    0
+      |   4 |    0
+    1 |   1 |    1
+    7 |   1 |    1
+    9 |   7 |    1
+      |   9 |    1
+      |   0 |    2
+    3 |   1 |    3
+      |   3 |    3
+(10 rows)
+
+SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+ lead | ten | four 
+------+-----+------
+    0 |   0 |    0
+    8 |   0 |    0
+      |   4 |    0
+    2 |   1 |    1
+   14 |   1 |    1
+   18 |   7 |    1
+      |   9 |    1
+      |   0 |    2
+    6 |   1 |    3
+      |   3 |    3
+(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 
+------+-----+------
+    0 |   0 |    0
+    8 |   0 |    0
+   -1 |   4 |    0
+    2 |   1 |    1
+   14 |   1 |    1
+   18 |   7 |    1
+   -1 |   9 |    1
+   -1 |   0 |    2
+    6 |   1 |    3
+   -1 |   3 |    3
+(10 rows)
+
+SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+ first_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 |   0 |    2
+           1 |   1 |    3
+           1 |   3 |    3
+(10 rows)
+
+-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window.
+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
+          1 |   1 |    3
+          1 |   1 |    1
+          3 |   3 |    3
+          0 |   4 |    0
+          1 |   7 |    1
+          1 |   9 |    1
+(10 rows)
+
+SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM
+	(SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
+	ORDER BY four, ten;
+ last_value | ten | four 
+------------+-----+------
+          4 |   0 |    0
+          4 |   0 |    0
+          4 |   4 |    0
+          9 |   1 |    1
+          9 |   1 |    1
+          9 |   7 |    1
+          9 |   9 |    1
+          0 |   0 |    2
+          3 |   1 |    3
+          3 |   3 |    3
+(10 rows)
+
+SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four
+	FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;
+ 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)
+
+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 
+-------+------
+     4 |    1
+     4 |    1
+     4 |    1
+     4 |    1
+     2 |    3
+     2 |    3
+(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 
+--------
+ 22
+ 22
+ 87
+ 24
+ 24
+ 82
+ 92
+ 51
+ 92
+ 136
+(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
+     2.0000000000000000
+     3.0000000000000000
+     3.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  
+-------+------------+-------
+ 14600 |          3 | 14600
+  7400 |          2 | 22000
+ 25100 |          1 | 47100
+(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 
+------
+    0
+    0
+    4
+    1
+    7
+    9
+     
+    0
+    3
+     
+(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)
+
+-- with UNION
+SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
+ count 
+-------
+(0 rows)
+
+-- via a VIEW
+CREATE TEMPORARY VIEW vsumsalary AS
+SELECT SUM(salary) OVER (PARTITION BY depname) FROM empsalary;
+SELECT * FROM vsumsalary;
+  sum  
+-------
+ 25100
+ 25100
+ 25100
+ 25100
+ 25100
+  7400
+  7400
+ 14600
+ 14600
+ 14600
+(10 rows)
+
+-- ordering by a non-integer constant is allowed
+SELECT rank() OVER (ORDER BY length('abc'));
+ rank 
+------
+    1
+(1 row)
+
+-- but this draws an error: "ORDER BY 1" means order by first SELECT column
+SELECT rank() OVER (ORDER BY 1);
+ERROR:  window functions not allowed in window definition
+LINE 1: SELECT rank() OVER (ORDER BY 1);
+               ^
+-- some other errors
+SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
+ERROR:  window functions not allowed in WHERE clause
+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 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 not allowed in GROUP BY clause
+LINE 1: SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GRO...
+               ^
+SELECT * FROM rank() OVER (ORDER BY random());
+ERROR:  cannot use window function in function expression in FROM
+LINE 1: SELECT * FROM rank() OVER (ORDER BY random());
+                      ^
+DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;
+ERROR:  window functions not allowed in WHERE clause
+LINE 1: DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())...
+                                     ^
+DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());
+ERROR:  cannot use window function 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...
+                                               ^
+SELECT count() OVER () FROM tenk1;
+ERROR:  count(*) must be used to call a parameterless aggregate function
+LINE 1: SELECT count() OVER () FROM tenk1;
+               ^
+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
+-- cleanup
+DROP VIEW vsumsalary;
+DROP TABLE empsalary;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 13bbb903254..db4097401cd 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -781,12 +781,12 @@ LINE 2:                           WHERE n IN (SELECT * FROM x))
 -- aggregate functions
 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
   SELECT * FROM x;
-ERROR:  aggregates not allowed in a recursive query's recursive term
+ERROR:  aggregate functions not allowed in a recursive query's recursive term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
                                                           ^
 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
   SELECT * FROM x;
-ERROR:  aggregates not allowed in a recursive query's recursive term
+ERROR:  aggregate functions not allowed in a recursive query's recursive term
 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
                                                           ^
 -- ORDER BY
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 27b79aef256..d2e62948967 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -1,5 +1,5 @@
 # ----------
-# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.51 2008/12/19 16:25:19 petere Exp $
+# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.52 2008/12/28 18:54:01 tgl Exp $
 #
 # By convention, we put no more than twenty tests in any one parallel group;
 # this limits the number of connections needed to run the tests.
@@ -83,7 +83,7 @@ test: select_views portals_p2 rules foreign_key cluster dependency guc bitmapops
 # Another group of parallel tests
 # ----------
 # "plpgsql" cannot run concurrently with "rules", nor can "plancache"
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject window with xml
 
 # run stats by itself because its delay may be insufficient under heavy load
 test: stats
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index da823d84238..a650807f3bb 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -1,4 +1,4 @@
-# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.48 2008/12/19 16:25:19 petere Exp $
+# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.49 2008/12/28 18:54:01 tgl Exp $
 # This should probably be in an order similar to parallel_schedule.
 test: boolean
 test: char
@@ -116,6 +116,7 @@ test: polymorphism
 test: rowtypes
 test: returning
 test: largeobject
+test: window
 test: with
 test: xml
 test: stats
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
new file mode 100644
index 00000000000..1100de5afa4
--- /dev/null
+++ b/src/test/regress/sql/window.sql
@@ -0,0 +1,179 @@
+--
+-- WINDOW FUNCTIONS
+--
+
+CREATE TEMPORARY TABLE empsalary (
+    depname varchar,
+    empno bigint,
+    salary int,
+    enroll_date date
+);
+
+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;
+
+SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;
+
+-- 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;
+
+SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
+
+SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;
+
+-- empty window specification
+SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;
+
+SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();
+
+-- no window operation
+SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);
+
+-- cumulative aggregate
+SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;
+
+SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT ntile(3) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT ntile(NULL) OVER (ORDER BY ten), ten, four FROM tenk1 LIMIT 1;
+
+SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
+
+-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window.
+SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10; 
+
+SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM
+	(SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
+	ORDER BY four, ten;
+
+SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four
+	FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;
+
+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;
+
+SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
+
+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;
+
+-- 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;
+
+SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;
+
+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);
+
+-- 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;
+
+-- 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);
+
+-- 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;
+
+-- empty table
+SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;
+
+-- 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);
+
+-- 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;
+
+-- with UNION
+SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;
+
+-- via a VIEW
+CREATE TEMPORARY VIEW vsumsalary AS
+SELECT SUM(salary) OVER (PARTITION BY depname) FROM empsalary;
+SELECT * FROM vsumsalary;
+
+-- ordering by a non-integer constant is allowed
+SELECT rank() OVER (ORDER BY length('abc'));
+
+-- but this draws an error: "ORDER BY 1" means order by first SELECT column
+SELECT rank() OVER (ORDER BY 1);
+
+-- some other errors
+SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;
+
+SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10;
+
+SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;
+
+SELECT * FROM rank() OVER (ORDER BY random());
+
+DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;
+
+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);
+
+SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;
+
+SELECT count() OVER () FROM tenk1;
+
+SELECT generate_series(1, 100) OVER () FROM empsalary;
+
+SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
+
+SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
+
+-- cleanup
+DROP VIEW vsumsalary;
+DROP TABLE empsalary;