diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/window.out | 187 | ||||
| -rw-r--r-- | src/test/regress/parallel_schedule | 7 | ||||
| -rw-r--r-- | src/test/regress/serial_schedule | 4 | ||||
| -rw-r--r-- | src/test/regress/sql/window.sql | 52 |
4 files changed, 245 insertions, 5 deletions
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index c14011ce0e4..0481cc6dd81 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -728,6 +728,193 @@ FROM (select distinct ten, four from tenk1) ss; 3 | 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 | 9 | 1 + 33 | 1 | 1 + 18 | 6 | 2 + 18 | 2 | 2 + 10 | 3 | 3 + 10 | 7 | 3 +(10 rows) + +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 | 6 | 2 + 18 | 2 | 2 + 10 | 3 | 3 + 10 | 7 | 3 +(10 rows) + +-- fail: not implemented yet +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; +ERROR: RANGE PRECEDING is only supported with UNBOUNDED +LINE 1: SELECT sum(unique1) over (order by four range between 2::int... + ^ +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 | 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 + 3 | 7 | 7 | 3 | 3 + 3 | 7 | 7 | 7 | 3 +(10 rows) + +SELECT sum(unique1) over + (rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING), + unique1 +FROM tenk1 WHERE unique1 < 10; + sum | unique1 +-----+--------- + 4 | 4 + 6 | 2 + 3 | 1 + 7 | 6 + 15 | 9 + 17 | 8 + 13 | 5 + 8 | 3 + 10 | 7 + 7 | 0 +(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) + -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; count diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 43794122f56..7cdf872df59 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.60 2010/02/07 22:40:33 tgl Exp $ +# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.61 2010/02/12 17:33:21 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. @@ -78,18 +78,19 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi test: privileges test: misc +# rules cannot run concurrently with any test that creates a view +test: rules # ---------- # Another group of parallel tests # ---------- -test: select_views portals_p2 rules foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap +test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap # ---------- # Another group of parallel tests # NB: temp.sql does a reconnect which transiently uses 2 connections, # so keep this parallel group to at most 19 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 # run stats by itself because its delay may be insufficient under heavy load diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 037abf2341c..c404d54206b 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.55 2010/01/28 23:21:13 petere Exp $ +# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.56 2010/02/12 17:33:21 tgl Exp $ # This should probably be in an order similar to parallel_schedule. test: tablespace test: boolean @@ -89,9 +89,9 @@ test: namespace test: prepared_xacts test: privileges test: misc +test: rules test: select_views test: portals_p2 -test: rules test: foreign_key test: cluster test: dependency diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index fc62a6fd6e9..1cfc64bd8b6 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -161,6 +161,58 @@ SELECT four, ten/4 as two, 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; +SELECT sum(unique1) over (order by four range between current row and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (rows between current row and unbounded following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (rows between 2 preceding and 2 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (rows between 2 preceding and 1 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (rows between 1 following and 3 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +SELECT sum(unique1) over (rows between unbounded preceding and 1 following), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +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); + +-- fail: not implemented yet +SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding), + unique1, four +FROM tenk1 WHERE unique1 < 10; + +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); + +SELECT sum(unique1) over + (rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING), + unique1 +FROM tenk1 WHERE unique1 < 10; + +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; + +SELECT pg_get_viewdef('v_window'); + -- with UNION SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0; |