summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
authorDean Rasheed2023-11-14 10:58:49 +0000
committerDean Rasheed2023-11-14 10:58:49 +0000
commit519fc1bd9e9d7b408903e44f55f83f6db30742b7 (patch)
treef31e231df8b28ddff5149504e7019b824191353a /src/test
parentb41b1a7f490093643ce2080d1cf9323e24423eb8 (diff)
Support +/- infinity in the interval data type.
This adds support for infinity to the interval data type, using the same input/output representation as the other date/time data types that support infinity. This allows various arithmetic operations on infinite dates, timestamps and intervals. The new values are represented by setting all fields of the interval to INT32/64_MIN for -infinity, and INT32/64_MAX for +infinity. This ensures that they compare as less/greater than all other interval values, without the need for any special-case comparison code. Note that, since those 2 values were formerly accepted as legal finite intervals, pg_upgrade and dump/restore from an old database will turn them from finite to infinite intervals. That seems OK, since those exact values should be extremely rare in practice, and they are outside the documented range supported by the interval type, which gives us a certain amount of leeway. Bump catalog version. Joseph Koshakow, Jian He, and Ashutosh Bapat, reviewed by me. Discussion: https://postgr.es/m/CAAvxfHea4%2BsPybKK7agDYOMo9N-Z3J6ZXf3BOM79pFsFNcRjwA%40mail.gmail.com
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/brin_multi.out28
-rw-r--r--src/test/regress/expected/horology.out71
-rw-r--r--src/test/regress/expected/interval.out499
-rw-r--r--src/test/regress/expected/timestamp.out62
-rw-r--r--src/test/regress/expected/timestamptz.out62
-rw-r--r--src/test/regress/expected/window.out469
-rw-r--r--src/test/regress/sql/brin_multi.sql19
-rw-r--r--src/test/regress/sql/horology.sql3
-rw-r--r--src/test/regress/sql/interval.sql193
-rw-r--r--src/test/regress/sql/timestamp.sql19
-rw-r--r--src/test/regress/sql/timestamptz.sql18
-rw-r--r--src/test/regress/sql/window.sql167
12 files changed, 1543 insertions, 67 deletions
diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index 80801cd4ee7..8f4c95b9e64 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -943,4 +943,32 @@ SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
DROP TABLE brin_interval_test;
RESET enable_seqscan;
+-- test handling of infinite interval values
+CREATE TABLE brin_interval_test(a INTERVAL);
+INSERT INTO brin_interval_test VALUES ('-infinity'), ('infinity');
+INSERT INTO brin_interval_test SELECT (i || ' days')::interval FROM generate_series(100, 140) s(i);
+CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan = off;
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_interval_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '@ 30 years ago'::interval)
+ -> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '@ 30 years ago'::interval)
+(4 rows)
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_interval_test (actual rows=0 loops=1)
+ Recheck Cond: (a = '@ 30 years'::interval)
+ -> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1)
+ Index Cond: (a = '@ 30 years'::interval)
+(4 rows)
+
+DROP TABLE brin_interval_test;
+RESET enable_seqscan;
RESET datestyle;
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 8f52661096f..cfb4b205e4d 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -1171,6 +1171,7 @@ SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract"
SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
FROM TIME_TBL t, INTERVAL_TBL i
+ WHERE isfinite(i.f1)
ORDER BY 1,2;
t | i | add | subtract
-------------+-------------------------------+-------------+-------------
@@ -1278,6 +1279,7 @@ SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
FROM TIMETZ_TBL t, INTERVAL_TBL i
+ WHERE isfinite(i.f1)
ORDER BY 1,2;
t | i | add | subtract
----------------+-------------------------------+----------------+----------------
@@ -1556,6 +1558,22 @@ SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
ORDER BY plus, "timestamp", "interval";
timestamp | interval | plus
------------------------------+-------------------------------+------------------------------
+ Thu Jan 01 00:00:00 1970 PST | -infinity | -infinity
+ Wed Feb 28 17:32:01 1996 PST | -infinity | -infinity
+ Thu Feb 29 17:32:01 1996 PST | -infinity | -infinity
+ Fri Mar 01 17:32:01 1996 PST | -infinity | -infinity
+ Mon Dec 30 17:32:01 1996 PST | -infinity | -infinity
+ Tue Dec 31 17:32:01 1996 PST | -infinity | -infinity
+ Fri Dec 31 17:32:01 1999 PST | -infinity | -infinity
+ Sat Jan 01 17:32:01 2000 PST | -infinity | -infinity
+ Wed Mar 15 02:14:05 2000 PST | -infinity | -infinity
+ Wed Mar 15 03:14:04 2000 PST | -infinity | -infinity
+ Wed Mar 15 08:14:01 2000 PST | -infinity | -infinity
+ Wed Mar 15 12:14:03 2000 PST | -infinity | -infinity
+ Wed Mar 15 13:14:02 2000 PST | -infinity | -infinity
+ Sun Dec 31 17:32:01 2000 PST | -infinity | -infinity
+ Mon Jan 01 17:32:01 2001 PST | -infinity | -infinity
+ Sat Sep 22 18:19:20 2001 PDT | -infinity | -infinity
Thu Jan 01 00:00:00 1970 PST | @ 14 secs ago | Wed Dec 31 23:59:46 1969 PST
Thu Jan 01 00:00:00 1970 PST | @ 1 min | Thu Jan 01 00:01:00 1970 PST
Thu Jan 01 00:00:00 1970 PST | @ 5 hours | Thu Jan 01 05:00:00 1970 PST
@@ -1716,14 +1734,45 @@ SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sun Dec 31 17:32:01 2034 PST
Mon Jan 01 17:32:01 2001 PST | @ 34 years | Mon Jan 01 17:32:01 2035 PST
Sat Sep 22 18:19:20 2001 PDT | @ 34 years | Sat Sep 22 18:19:20 2035 PDT
-(160 rows)
+ Thu Jan 01 00:00:00 1970 PST | infinity | infinity
+ Wed Feb 28 17:32:01 1996 PST | infinity | infinity
+ Thu Feb 29 17:32:01 1996 PST | infinity | infinity
+ Fri Mar 01 17:32:01 1996 PST | infinity | infinity
+ Mon Dec 30 17:32:01 1996 PST | infinity | infinity
+ Tue Dec 31 17:32:01 1996 PST | infinity | infinity
+ Fri Dec 31 17:32:01 1999 PST | infinity | infinity
+ Sat Jan 01 17:32:01 2000 PST | infinity | infinity
+ Wed Mar 15 02:14:05 2000 PST | infinity | infinity
+ Wed Mar 15 03:14:04 2000 PST | infinity | infinity
+ Wed Mar 15 08:14:01 2000 PST | infinity | infinity
+ Wed Mar 15 12:14:03 2000 PST | infinity | infinity
+ Wed Mar 15 13:14:02 2000 PST | infinity | infinity
+ Sun Dec 31 17:32:01 2000 PST | infinity | infinity
+ Mon Jan 01 17:32:01 2001 PST | infinity | infinity
+ Sat Sep 22 18:19:20 2001 PDT | infinity | infinity
+(192 rows)
SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
- WHERE isfinite(d.f1)
ORDER BY minus, "timestamp", "interval";
timestamp | interval | minus
------------------------------+-------------------------------+------------------------------
+ Thu Jan 01 00:00:00 1970 PST | infinity | -infinity
+ Wed Feb 28 17:32:01 1996 PST | infinity | -infinity
+ Thu Feb 29 17:32:01 1996 PST | infinity | -infinity
+ Fri Mar 01 17:32:01 1996 PST | infinity | -infinity
+ Mon Dec 30 17:32:01 1996 PST | infinity | -infinity
+ Tue Dec 31 17:32:01 1996 PST | infinity | -infinity
+ Fri Dec 31 17:32:01 1999 PST | infinity | -infinity
+ Sat Jan 01 17:32:01 2000 PST | infinity | -infinity
+ Wed Mar 15 02:14:05 2000 PST | infinity | -infinity
+ Wed Mar 15 03:14:04 2000 PST | infinity | -infinity
+ Wed Mar 15 08:14:01 2000 PST | infinity | -infinity
+ Wed Mar 15 12:14:03 2000 PST | infinity | -infinity
+ Wed Mar 15 13:14:02 2000 PST | infinity | -infinity
+ Sun Dec 31 17:32:01 2000 PST | infinity | -infinity
+ Mon Jan 01 17:32:01 2001 PST | infinity | -infinity
+ Sat Sep 22 18:19:20 2001 PDT | infinity | -infinity
Thu Jan 01 00:00:00 1970 PST | @ 34 years | Wed Jan 01 00:00:00 1936 PST
Wed Feb 28 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
Thu Feb 29 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
@@ -1884,7 +1933,23 @@ SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus
Sat Sep 22 18:19:20 2001 PDT | @ 5 hours | Sat Sep 22 13:19:20 2001 PDT
Sat Sep 22 18:19:20 2001 PDT | @ 1 min | Sat Sep 22 18:18:20 2001 PDT
Sat Sep 22 18:19:20 2001 PDT | @ 14 secs ago | Sat Sep 22 18:19:34 2001 PDT
-(160 rows)
+ Thu Jan 01 00:00:00 1970 PST | -infinity | infinity
+ Wed Feb 28 17:32:01 1996 PST | -infinity | infinity
+ Thu Feb 29 17:32:01 1996 PST | -infinity | infinity
+ Fri Mar 01 17:32:01 1996 PST | -infinity | infinity
+ Mon Dec 30 17:32:01 1996 PST | -infinity | infinity
+ Tue Dec 31 17:32:01 1996 PST | -infinity | infinity
+ Fri Dec 31 17:32:01 1999 PST | -infinity | infinity
+ Sat Jan 01 17:32:01 2000 PST | -infinity | infinity
+ Wed Mar 15 02:14:05 2000 PST | -infinity | infinity
+ Wed Mar 15 03:14:04 2000 PST | -infinity | infinity
+ Wed Mar 15 08:14:01 2000 PST | -infinity | infinity
+ Wed Mar 15 12:14:03 2000 PST | -infinity | infinity
+ Wed Mar 15 13:14:02 2000 PST | -infinity | infinity
+ Sun Dec 31 17:32:01 2000 PST | -infinity | infinity
+ Mon Jan 01 17:32:01 2001 PST | -infinity | infinity
+ Sat Sep 22 18:19:20 2001 PDT | -infinity | infinity
+(192 rows)
SELECT d.f1 AS "timestamp",
timestamp with time zone '1980-01-06 00:00 GMT' AS gpstime_zero,
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 75d19d65949..a481781475b 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -52,6 +52,18 @@ SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
9 years 1 mon -12 days +13:14:00
(1 row)
+SELECT INTERVAL 'infinity' AS "eternity";
+ eternity
+----------
+ infinity
+(1 row)
+
+SELECT INTERVAL '-infinity' AS "beginning of time";
+ beginning of time
+-------------------
+ -infinity
+(1 row)
+
CREATE TABLE INTERVAL_TBL (f1 interval);
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
@@ -63,6 +75,8 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
-- badly formatted interval
INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
ERROR: invalid input syntax for type interval: "badly formatted interval"
@@ -117,7 +131,9 @@ SELECT * FROM INTERVAL_TBL;
6 years
5 mons
5 mons 12:00:00
-(10 rows)
+ infinity
+ -infinity
+(12 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
@@ -132,7 +148,9 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(9 rows)
+ infinity
+ -infinity
+(11 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
@@ -141,7 +159,8 @@ SELECT * FROM INTERVAL_TBL
00:01:00
05:00:00
-00:00:14
-(3 rows)
+ -infinity
+(4 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
@@ -150,7 +169,8 @@ SELECT * FROM INTERVAL_TBL
00:01:00
05:00:00
-00:00:14
-(3 rows)
+ -infinity
+(4 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
@@ -168,7 +188,8 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(5 rows)
+ infinity
+(6 rows)
SELECT * FROM INTERVAL_TBL
WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
@@ -183,7 +204,8 @@ SELECT * FROM INTERVAL_TBL
6 years
5 mons
5 mons 12:00:00
-(9 rows)
+ infinity
+(10 rows)
SELECT r1.*, r2.*
FROM INTERVAL_TBL r1, INTERVAL_TBL r2
@@ -191,27 +213,35 @@ SELECT r1.*, r2.*
ORDER BY r1.f1, r2.f1;
f1 | f1
-----------------+-----------------
+ -00:00:14 | -infinity
+ 00:01:00 | -infinity
00:01:00 | -00:00:14
+ 05:00:00 | -infinity
05:00:00 | -00:00:14
05:00:00 | 00:01:00
+ 1 day 02:03:04 | -infinity
1 day 02:03:04 | -00:00:14
1 day 02:03:04 | 00:01:00
1 day 02:03:04 | 05:00:00
+ 10 days | -infinity
10 days | -00:00:14
10 days | 00:01:00
10 days | 05:00:00
10 days | 1 day 02:03:04
+ 3 mons | -infinity
3 mons | -00:00:14
3 mons | 00:01:00
3 mons | 05:00:00
3 mons | 1 day 02:03:04
3 mons | 10 days
+ 5 mons | -infinity
5 mons | -00:00:14
5 mons | 00:01:00
5 mons | 05:00:00
5 mons | 1 day 02:03:04
5 mons | 10 days
5 mons | 3 mons
+ 5 mons 12:00:00 | -infinity
5 mons 12:00:00 | -00:00:14
5 mons 12:00:00 | 00:01:00
5 mons 12:00:00 | 05:00:00
@@ -219,6 +249,7 @@ SELECT r1.*, r2.*
5 mons 12:00:00 | 10 days
5 mons 12:00:00 | 3 mons
5 mons 12:00:00 | 5 mons
+ 6 years | -infinity
6 years | -00:00:14
6 years | 00:01:00
6 years | 05:00:00
@@ -227,6 +258,7 @@ SELECT r1.*, r2.*
6 years | 3 mons
6 years | 5 mons
6 years | 5 mons 12:00:00
+ 34 years | -infinity
34 years | -00:00:14
34 years | 00:01:00
34 years | 05:00:00
@@ -236,8 +268,63 @@ SELECT r1.*, r2.*
34 years | 5 mons
34 years | 5 mons 12:00:00
34 years | 6 years
-(45 rows)
+ infinity | -infinity
+ infinity | -00:00:14
+ infinity | 00:01:00
+ infinity | 05:00:00
+ infinity | 1 day 02:03:04
+ infinity | 10 days
+ infinity | 3 mons
+ infinity | 5 mons
+ infinity | 5 mons 12:00:00
+ infinity | 6 years
+ infinity | 34 years
+(66 rows)
+
+-- test unary minus
+SELECT f1, -f1 FROM INTERVAL_TBL;
+ f1 | ?column?
+-----------------+-------------------
+ 00:01:00 | -00:01:00
+ 05:00:00 | -05:00:00
+ 10 days | -10 days
+ 34 years | -34 years
+ 3 mons | -3 mons
+ -00:00:14 | 00:00:14
+ 1 day 02:03:04 | -1 days -02:03:04
+ 6 years | -6 years
+ 5 mons | -5 mons
+ 5 mons 12:00:00 | -5 mons -12:00:00
+ infinity | -infinity
+ -infinity | infinity
+(12 rows)
+
+SELECT -('-2147483648 months'::interval); -- should fail
+ERROR: interval out of range
+SELECT -('-2147483647 months'::interval); -- ok
+ ?column?
+------------------------
+ 178956970 years 7 mons
+(1 row)
+
+SELECT -('-2147483648 days'::interval); -- should fail
+ERROR: interval out of range
+SELECT -('-2147483647 days'::interval); -- ok
+ ?column?
+-----------------
+ 2147483647 days
+(1 row)
+
+SELECT -('-9223372036854775808 us'::interval); -- should fail
+ERROR: interval out of range
+SELECT -('-9223372036854775807 us'::interval); -- ok
+ ?column?
+-------------------------
+ 2562047788:00:54.775807
+(1 row)
+SELECT -('-2147483647 months -2147483647 days -9223372036854775807 us'::interval); -- should fail
+ERROR: interval out of range
-- Test intervals that are large enough to overflow 64 bits in comparisons
CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
INSERT INTO INTERVAL_TBL_OF (f1) VALUES
@@ -304,6 +391,17 @@ SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
(5 rows)
RESET enable_seqscan;
+-- subtracting about-to-overflow values should result in 0
+SELECT f1 - f1 FROM INTERVAL_TBL_OF;
+ ?column?
+----------
+ 00:00:00
+ 00:00:00
+ 00:00:00
+ 00:00:00
+ 00:00:00
+(5 rows)
+
DROP TABLE INTERVAL_TBL_OF;
-- Test multiplication and division with intervals.
-- Floating point arithmetic rounding errors can lead to unexpected results,
@@ -386,12 +484,14 @@ SELECT * FROM INTERVAL_TBL;
@ 6 years
@ 5 mons
@ 5 mons 12 hours
-(10 rows)
+ infinity
+ -infinity
+(12 rows)
-- test avg(interval), which is somewhat fragile since people have been
-- known to change the allowed input syntax for type interval without
-- updating pg_aggregate.agginitval
-select avg(f1) from interval_tbl;
+select avg(f1) from interval_tbl where isfinite(f1);
avg
-------------------------------------------------
@ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
@@ -820,8 +920,8 @@ SELECT interval '1 2:03:04.5678' minute to second(2);
SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
(f1 + INTERVAL '1 month')::INTERVAL MONTH::INTERVAL YEAR AS "years"
FROM interval_tbl;
- f1 | minutes | years
------------------+-----------------+----------
+ f1 | minutes | years
+-----------------+-----------------+-----------
00:01:00 | 00:01:00 | 00:00:00
05:00:00 | 05:00:00 | 00:00:00
10 days | 10 days | 00:00:00
@@ -832,7 +932,9 @@ SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
6 years | 6 years | 6 years
5 mons | 5 mons | 00:00:00
5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00
-(10 rows)
+ infinity | infinity | infinity
+ -infinity | -infinity | -infinity
+(12 rows)
-- test inputting and outputting SQL standard interval literals
SET IntervalStyle TO sql_standard;
@@ -1616,31 +1718,31 @@ select make_interval(mins := -1, secs := -9223372036800.0);
ERROR: interval out of range
-- test that INT_MIN number is formatted properly
SET IntervalStyle to postgres;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
interval
--------------------------------------------------------------------
- -178956970 years -8 mons -2147483648 days -2562047788:00:54.775808
+ -178956970 years -7 mons -2147483648 days -2562047788:00:54.775808
(1 row)
SET IntervalStyle to sql_standard;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
interval
---------------------------------------------------
- -178956970-8 -2147483648 -2562047788:00:54.775808
+ -178956970-7 -2147483648 -2562047788:00:54.775808
(1 row)
SET IntervalStyle to iso_8601;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
interval
-----------------------------------------------------
- P-178956970Y-8M-2147483648DT-2562047788H-54.775808S
+ P-178956970Y-7M-2147483648DT-2562047788H-54.775808S
(1 row)
SET IntervalStyle to postgres_verbose;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
interval
------------------------------------------------------------------------------
- @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775808 secs ago
+ @ 178956970 years 7 mons 2147483648 days 2562047788 hours 54.775808 secs ago
(1 row)
-- check that '30 days' equals '1 month' according to the hash function
@@ -1719,19 +1821,21 @@ SELECT f1,
EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
EXTRACT(EPOCH FROM f1) AS EPOCH
FROM INTERVAL_TBL;
- f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch
--------------------------------+-------------+-------------+------------+--------+------+-----+-------+---------+------+--------+---------+------------+-------------------
- @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
- @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
- @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
- @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400.000000
- @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
- @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
- @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
- @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600.000000
- @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
- @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
-(10 rows)
+ f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch
+-------------------------------+-------------+-------------+------------+--------+-----------+-----------+-------+---------+-----------+-----------+-----------+------------+-------------------
+ @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
+ @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
+ @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
+ @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400.000000
+ @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
+ @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
+ @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
+ @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600.000000
+ @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
+ @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
+ infinity | | | | | Infinity | Infinity | | | Infinity | Infinity | Infinity | Infinity | Infinity
+ -infinity | | | | | -Infinity | -Infinity | | | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
+(12 rows)
SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
ERROR: unit "fortnight" not recognized for type interval
@@ -1805,7 +1909,9 @@ SELECT f1,
@ 6 years | 0 | 0 | 0 | 189345600
@ 5 mons | 0 | 0 | 0 | 12960000
@ 5 mons 12 hours | 0 | 0 | 0 | 13003200
-(10 rows)
+ infinity | | | | Infinity
+ -infinity | | | | -Infinity
+(12 rows)
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
@@ -1814,6 +1920,292 @@ SELECT extract(epoch from interval '1000000000 days');
86400000000000.000000
(1 row)
+--
+-- test infinite intervals
+--
+-- largest finite intervals
+SELECT interval '-2147483648 months -2147483648 days -9223372036854775807 us';
+ interval
+------------------------------------------------------------------------------
+ @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775807 secs ago
+(1 row)
+
+SELECT interval '2147483647 months 2147483647 days 9223372036854775806 us';
+ interval
+--------------------------------------------------------------------------
+ @ 178956970 years 7 mons 2147483647 days 2562047788 hours 54.775806 secs
+(1 row)
+
+-- infinite intervals
+SELECT interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '2147483647 months 2147483647 days 9223372036854775807 us';
+ interval
+----------
+ infinity
+(1 row)
+
+CREATE TABLE INFINITE_INTERVAL_TBL (i interval);
+INSERT INTO INFINITE_INTERVAL_TBL VALUES ('infinity'), ('-infinity'), ('1 year 2 days 3 hours');
+SELECT i, isfinite(i) FROM INFINITE_INTERVAL_TBL;
+ i | isfinite
+-------------------------+----------
+ infinity | f
+ -infinity | f
+ @ 1 year 2 days 3 hours | t
+(3 rows)
+
+-- test basic arithmetic
+CREATE FUNCTION eval(expr text)
+RETURNS text AS
+$$
+DECLARE
+ result text;
+BEGIN
+ EXECUTE 'select '||expr INTO result;
+ RETURN result;
+EXCEPTION WHEN OTHERS THEN
+ RETURN SQLERRM;
+END
+$$
+LANGUAGE plpgsql;
+SELECT d AS date, i AS interval,
+ eval(format('date %L + interval %L', d, i)) AS plus,
+ eval(format('date %L - interval %L', d, i)) AS minus
+FROM (VALUES (date '-infinity'),
+ (date '1995-08-06'),
+ (date 'infinity')) AS t1(d),
+ (VALUES (interval '-infinity'),
+ (interval 'infinity')) AS t2(i);
+ date | interval | plus | minus
+------------+-----------+------------------------+------------------------
+ -infinity | -infinity | -infinity | timestamp out of range
+ -infinity | infinity | timestamp out of range | -infinity
+ 1995-08-06 | -infinity | -infinity | infinity
+ 1995-08-06 | infinity | infinity | -infinity
+ infinity | -infinity | timestamp out of range | infinity
+ infinity | infinity | infinity | timestamp out of range
+(6 rows)
+
+SELECT i1 AS interval1, i2 AS interval2,
+ eval(format('interval %L + interval %L', i1, i2)) AS plus,
+ eval(format('interval %L - interval %L', i1, i2)) AS minus
+FROM (VALUES (interval '-infinity'),
+ (interval '2 months'),
+ (interval 'infinity')) AS t1(i1),
+ (VALUES (interval '-infinity'),
+ (interval '10 days'),
+ (interval 'infinity')) AS t2(i2);
+ interval1 | interval2 | plus | minus
+-----------+-----------+-----------------------+-----------------------
+ -infinity | -infinity | -infinity | interval out of range
+ -infinity | @ 10 days | -infinity | -infinity
+ -infinity | infinity | interval out of range | -infinity
+ @ 2 mons | -infinity | -infinity | infinity
+ @ 2 mons | @ 10 days | @ 2 mons 10 days | @ 2 mons -10 days
+ @ 2 mons | infinity | infinity | -infinity
+ infinity | -infinity | interval out of range | infinity
+ infinity | @ 10 days | infinity | infinity
+ infinity | infinity | infinity | interval out of range
+(9 rows)
+
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' + interval '1 month 1 day 1 us';
+ERROR: interval out of range
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' + interval '-1 month -1 day -1 us';
+ERROR: interval out of range
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' - interval '-1 month -1 day -1 us';
+ERROR: interval out of range
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' - interval '1 month 1 day 1 us';
+ERROR: interval out of range
+SELECT t AS timestamp, i AS interval,
+ eval(format('timestamp %L + interval %L', t, i)) AS plus,
+ eval(format('timestamp %L - interval %L', t, i)) AS minus
+FROM (VALUES (timestamp '-infinity'),
+ (timestamp '1995-08-06 12:30:15'),
+ (timestamp 'infinity')) AS t1(t),
+ (VALUES (interval '-infinity'),
+ (interval 'infinity')) AS t2(i);
+ timestamp | interval | plus | minus
+---------------------+-----------+------------------------+------------------------
+ -infinity | -infinity | -infinity | timestamp out of range
+ -infinity | infinity | timestamp out of range | -infinity
+ 1995-08-06 12:30:15 | -infinity | -infinity | infinity
+ 1995-08-06 12:30:15 | infinity | infinity | -infinity
+ infinity | -infinity | timestamp out of range | infinity
+ infinity | infinity | infinity | timestamp out of range
+(6 rows)
+
+SELECT t AT TIME ZONE 'GMT' AS timestamptz, i AS interval,
+ eval(format('timestamptz %L + interval %L', t, i)) AS plus,
+ eval(format('timestamptz %L - interval %L', t, i)) AS minus
+FROM (VALUES (timestamptz '-infinity'),
+ (timestamptz '1995-08-06 12:30:15 GMT'),
+ (timestamptz 'infinity')) AS t1(t),
+ (VALUES (interval '-infinity'),
+ (interval 'infinity')) AS t2(i);
+ timestamptz | interval | plus | minus
+---------------------+-----------+------------------------+------------------------
+ -infinity | -infinity | -infinity | timestamp out of range
+ -infinity | infinity | timestamp out of range | -infinity
+ 1995-08-06 12:30:15 | -infinity | -infinity | infinity
+ 1995-08-06 12:30:15 | infinity | infinity | -infinity
+ infinity | -infinity | timestamp out of range | infinity
+ infinity | infinity | infinity | timestamp out of range
+(6 rows)
+
+-- time +/- infinite interval not supported
+SELECT time '11:27:42' + interval 'infinity';
+ERROR: cannot add infinite interval to time
+SELECT time '11:27:42' + interval '-infinity';
+ERROR: cannot add infinite interval to time
+SELECT time '11:27:42' - interval 'infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT time '11:27:42' - interval '-infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT timetz '11:27:42' + interval 'infinity';
+ERROR: cannot add infinite interval to time
+SELECT timetz '11:27:42' + interval '-infinity';
+ERROR: cannot add infinite interval to time
+SELECT timetz '11:27:42' - interval 'infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT timetz '11:27:42' - interval '-infinity';
+ERROR: cannot subtract infinite interval from time
+SELECT lhst.i lhs,
+ rhst.i rhs,
+ lhst.i < rhst.i AS lt,
+ lhst.i <= rhst.i AS le,
+ lhst.i = rhst.i AS eq,
+ lhst.i > rhst.i AS gt,
+ lhst.i >= rhst.i AS ge,
+ lhst.i <> rhst.i AS ne
+ FROM INFINITE_INTERVAL_TBL lhst CROSS JOIN INFINITE_INTERVAL_TBL rhst
+ WHERE NOT isfinite(lhst.i);
+ lhs | rhs | lt | le | eq | gt | ge | ne
+-----------+-------------------------+----+----+----+----+----+----
+ infinity | infinity | f | t | t | f | t | f
+ -infinity | infinity | t | t | f | f | f | t
+ infinity | -infinity | f | f | f | t | t | t
+ -infinity | -infinity | f | t | t | f | t | f
+ infinity | @ 1 year 2 days 3 hours | f | f | f | t | t | t
+ -infinity | @ 1 year 2 days 3 hours | t | t | f | f | f | t
+(6 rows)
+
+SELECT i AS interval,
+ -i AS um,
+ i * 2.0 AS mul,
+ i * -2.0 AS mul_neg,
+ i * 'infinity' AS mul_inf,
+ i * '-infinity' AS mul_inf_neg,
+ i / 3.0 AS div,
+ i / -3.0 AS div_neg
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+ interval | um | mul | mul_neg | mul_inf | mul_inf_neg | div | div_neg
+-----------+-----------+-----------+-----------+-----------+-------------+-----------+-----------
+ infinity | -infinity | infinity | -infinity | infinity | -infinity | infinity | -infinity
+ -infinity | infinity | -infinity | infinity | -infinity | infinity | -infinity | infinity
+(2 rows)
+
+SELECT -interval '-2147483647 months -2147483647 days -9223372036854775807 us';
+ERROR: interval out of range
+SELECT interval 'infinity' * 'nan';
+ERROR: interval out of range
+SELECT interval '-infinity' * 'nan';
+ERROR: interval out of range
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' * 2;
+ERROR: interval out of range
+SELECT interval 'infinity' * 0;
+ERROR: interval out of range
+SELECT interval '-infinity' * 0;
+ERROR: interval out of range
+SELECT interval '0 days' * 'infinity'::float;
+ERROR: interval out of range
+SELECT interval '0 days' * '-infinity'::float;
+ERROR: interval out of range
+SELECT interval '5 days' * 'infinity'::float;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '5 days' * '-infinity'::float;
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval 'infinity' / 'infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' / '-infinity';
+ERROR: interval out of range
+SELECT interval 'infinity' / 'nan';
+ERROR: interval out of range
+SELECT interval '-infinity' / 'infinity';
+ERROR: interval out of range
+SELECT interval '-infinity' / '-infinity';
+ERROR: interval out of range
+SELECT interval '-infinity' / 'nan';
+ERROR: interval out of range
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' / 0.5;
+ERROR: interval out of range
+SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: timestamps cannot be binned into infinite intervals
+SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: timestamps cannot be binned into infinite intervals
+SELECT i AS interval, date_trunc('hour', i)
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+ interval | date_trunc
+-----------+------------
+ infinity | infinity
+ -infinity | -infinity
+(2 rows)
+
+SELECT i AS interval, justify_days(i), justify_hours(i), justify_interval(i)
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+ interval | justify_days | justify_hours | justify_interval
+-----------+--------------+---------------+------------------
+ infinity | infinity | infinity | infinity
+ -infinity | -infinity | -infinity | -infinity
+(2 rows)
+
+SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamp);
+ERROR: interval time zone "infinity" must be finite
+SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamp);
+ERROR: interval time zone "-infinity" must be finite
+SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
+ERROR: interval time zone "infinity" must be finite
+SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
+ERROR: interval time zone "-infinity" must be finite
+SELECT timezone('infinity'::interval, '12:12:12'::time);
+ERROR: interval time zone "infinity" must be finite
+SELECT timezone('-infinity'::interval, '12:12:12'::time);
+ERROR: interval time zone "-infinity" must be finite
+SELECT timezone('infinity'::interval, '12:12:12'::timetz);
+ERROR: interval time zone "infinity" must be finite
+SELECT timezone('-infinity'::interval, '12:12:12'::timetz);
+ERROR: interval time zone "-infinity" must be finite
+SELECT 'infinity'::interval::time;
+ERROR: cannot convert infinite interval to time
+SELECT '-infinity'::interval::time;
+ERROR: cannot convert infinite interval to time
+SELECT to_char('infinity'::interval, 'YYYY');
+ to_char
+---------
+
+(1 row)
+
+SELECT to_char('-infinity'::interval, 'YYYY');
+ to_char
+---------
+
+(1 row)
+
-- "ago" can only appear once at the end of an interval.
SELECT INTERVAL '42 days 2 seconds ago ago';
ERROR: invalid input syntax for type interval: "42 days 2 seconds ago ago"
@@ -1832,3 +2224,42 @@ SELECT INTERVAL '1 year months days 5 hours';
ERROR: invalid input syntax for type interval: "1 year months days 5 hours"
LINE 1: SELECT INTERVAL '1 year months days 5 hours';
^
+-- unacceptable reserved words in interval. Only "infinity", "+infinity" and
+-- "-infinity" are allowed.
+SELECT INTERVAL 'now';
+ERROR: invalid input syntax for type interval: "now"
+LINE 1: SELECT INTERVAL 'now';
+ ^
+SELECT INTERVAL 'today';
+ERROR: invalid input syntax for type interval: "today"
+LINE 1: SELECT INTERVAL 'today';
+ ^
+SELECT INTERVAL 'tomorrow';
+ERROR: invalid input syntax for type interval: "tomorrow"
+LINE 1: SELECT INTERVAL 'tomorrow';
+ ^
+SELECT INTERVAL 'allballs';
+ERROR: invalid input syntax for type interval: "allballs"
+LINE 1: SELECT INTERVAL 'allballs';
+ ^
+SELECT INTERVAL 'epoch';
+ERROR: invalid input syntax for type interval: "epoch"
+LINE 1: SELECT INTERVAL 'epoch';
+ ^
+SELECT INTERVAL 'yesterday';
+ERROR: invalid input syntax for type interval: "yesterday"
+LINE 1: SELECT INTERVAL 'yesterday';
+ ^
+-- infinity specification should be the only thing
+SELECT INTERVAL 'infinity years';
+ERROR: invalid input syntax for type interval: "infinity years"
+LINE 1: SELECT INTERVAL 'infinity years';
+ ^
+SELECT INTERVAL 'infinity ago';
+ERROR: invalid input syntax for type interval: "infinity ago"
+LINE 1: SELECT INTERVAL 'infinity ago';
+ ^
+SELECT INTERVAL '+infinity -infinity';
+ERROR: invalid input syntax for type interval: "+infinity -infinity"
+LINE 1: SELECT INTERVAL '+infinity -infinity';
+ ^
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index c64bcb7c127..835f0e5762f 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -2125,3 +2125,65 @@ select * from generate_series('2020-01-01 00:00'::timestamp,
'2020-01-02 03:00'::timestamp,
'0 hour'::interval);
ERROR: step size cannot equal zero
+select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval 'infinity');
+ERROR: step size cannot be infinite
+select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval '-infinity');
+ERROR: step size cannot be infinite
+-- test arithmetic with infinite timestamps
+select timestamp 'infinity' - timestamp 'infinity';
+ERROR: interval out of range
+select timestamp 'infinity' - timestamp '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+select timestamp '-infinity' - timestamp 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+select timestamp '-infinity' - timestamp '-infinity';
+ERROR: interval out of range
+select timestamp 'infinity' - timestamp '1995-08-06 12:12:12';
+ ?column?
+----------
+ infinity
+(1 row)
+
+select timestamp '-infinity' - timestamp '1995-08-06 12:12:12';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+-- test age() with infinite timestamps
+select age(timestamp 'infinity');
+ age
+-----------
+ -infinity
+(1 row)
+
+select age(timestamp '-infinity');
+ age
+----------
+ infinity
+(1 row)
+
+select age(timestamp 'infinity', timestamp 'infinity');
+ERROR: interval out of range
+select age(timestamp 'infinity', timestamp '-infinity');
+ age
+----------
+ infinity
+(1 row)
+
+select age(timestamp '-infinity', timestamp 'infinity');
+ age
+-----------
+ -infinity
+(1 row)
+
+select age(timestamp '-infinity', timestamp '-infinity');
+ERROR: interval out of range
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 2ca2101dd42..a084357480f 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2468,6 +2468,10 @@ select * from generate_series('2020-01-01 00:00'::timestamptz,
'2020-01-02 03:00'::timestamptz,
'0 hour'::interval);
ERROR: step size cannot equal zero
+select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval 'infinity');
+ERROR: step size cannot be infinite
+select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval '-infinity');
+ERROR: step size cannot be infinite
-- Interval crossing time shift for Europe/Warsaw timezone (with DST)
SET TimeZone to 'UTC';
SELECT date_add('2022-10-30 00:00:00+01'::timestamptz,
@@ -3210,3 +3214,61 @@ select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
Tue Jan 17 16:00:00 2017 PST
(1 row)
+-- test arithmetic with infinite timestamps
+SELECT timestamptz 'infinity' - timestamptz 'infinity';
+ERROR: interval out of range
+SELECT timestamptz 'infinity' - timestamptz '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - timestamptz 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - timestamptz '-infinity';
+ERROR: interval out of range
+SELECT timestamptz 'infinity' - timestamptz '1995-08-06 12:12:12';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - timestamptz '1995-08-06 12:12:12';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+-- test age() with infinite timestamps
+SELECT age(timestamptz 'infinity');
+ age
+-----------
+ -infinity
+(1 row)
+
+SELECT age(timestamptz '-infinity');
+ age
+----------
+ infinity
+(1 row)
+
+SELECT age(timestamptz 'infinity', timestamptz 'infinity');
+ERROR: interval out of range
+SELECT age(timestamptz 'infinity', timestamptz '-infinity');
+ age
+----------
+ infinity
+(1 row)
+
+SELECT age(timestamptz '-infinity', timestamptz 'infinity');
+ age
+-----------
+ -infinity
+(1 row)
+
+SELECT age(timestamptz '-infinity', timestamptz '-infinity');
+ERROR: interval out of range
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 69a38df10b6..2201740c185 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -2372,6 +2372,7 @@ create temp table datetimes(
f_timestamp timestamp
);
insert into datetimes values
+(0, '10:00', '10:00 BST', '-infinity', '-infinity', '-infinity'),
(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'),
(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
@@ -2381,14 +2382,16 @@ insert into datetimes values
(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'),
(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'),
(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'),
-(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54');
+(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54'),
+(11, '21:00', '21:00 BST', 'infinity', 'infinity', 'infinity');
select id, f_time, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_time range between
'70 min'::interval preceding and '2 hours'::interval following);
id | f_time | first_value | last_value
----+----------+-------------+------------
- 1 | 11:00:00 | 1 | 3
+ 0 | 10:00:00 | 0 | 2
+ 1 | 11:00:00 | 0 | 3
2 | 12:00:00 | 1 | 4
3 | 13:00:00 | 2 | 6
4 | 14:00:00 | 3 | 6
@@ -2396,9 +2399,10 @@ window w as (order by f_time range between
6 | 15:00:00 | 4 | 7
7 | 17:00:00 | 7 | 9
8 | 18:00:00 | 7 | 10
- 9 | 19:00:00 | 8 | 10
- 10 | 20:00:00 | 9 | 10
-(10 rows)
+ 9 | 19:00:00 | 8 | 11
+ 10 | 20:00:00 | 9 | 11
+ 11 | 21:00:00 | 10 | 11
+(12 rows)
select id, f_time, first_value(id) over w, last_value(id) over w
from datetimes
@@ -2406,7 +2410,8 @@ window w as (order by f_time desc range between
'70 min' preceding and '2 hours' following);
id | f_time | first_value | last_value
----+----------+-------------+------------
- 10 | 20:00:00 | 10 | 8
+ 11 | 21:00:00 | 11 | 9
+ 10 | 20:00:00 | 11 | 8
9 | 19:00:00 | 10 | 7
8 | 18:00:00 | 9 | 7
7 | 17:00:00 | 8 | 5
@@ -2414,17 +2419,90 @@ window w as (order by f_time desc range between
5 | 15:00:00 | 6 | 3
4 | 14:00:00 | 6 | 2
3 | 13:00:00 | 4 | 1
- 2 | 12:00:00 | 3 | 1
- 1 | 11:00:00 | 2 | 1
-(10 rows)
+ 2 | 12:00:00 | 3 | 0
+ 1 | 11:00:00 | 2 | 0
+ 0 | 10:00:00 | 1 | 0
+(12 rows)
+
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time desc range between
+ '-70 min' preceding and '2 hours' following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+ id | f_time | first_value | last_value
+----+----------+-------------+------------
+ 0 | 10:00:00 | 0 | 11
+ 1 | 11:00:00 | 0 | 11
+ 2 | 12:00:00 | 0 | 11
+ 3 | 13:00:00 | 0 | 11
+ 4 | 14:00:00 | 0 | 11
+ 5 | 15:00:00 | 0 | 11
+ 6 | 15:00:00 | 0 | 11
+ 7 | 17:00:00 | 0 | 11
+ 8 | 18:00:00 | 0 | 11
+ 9 | 19:00:00 | 0 | 11
+ 10 | 20:00:00 | 0 | 11
+ 11 | 21:00:00 | 0 | 11
+(12 rows)
+
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+ id | f_time | first_value | last_value
+----+----------+-------------+------------
+ 0 | 10:00:00 | |
+ 1 | 11:00:00 | |
+ 2 | 12:00:00 | |
+ 3 | 13:00:00 | |
+ 4 | 14:00:00 | |
+ 5 | 15:00:00 | |
+ 6 | 15:00:00 | |
+ 7 | 17:00:00 | |
+ 8 | 18:00:00 | |
+ 9 | 19:00:00 | |
+ 10 | 20:00:00 | |
+ 11 | 21:00:00 | |
+(12 rows)
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time range between
+ 'infinity'::interval following and 'infinity'::interval following);
+ id | f_time | first_value | last_value
+----+----------+-------------+------------
+ 0 | 10:00:00 | |
+ 1 | 11:00:00 | |
+ 2 | 12:00:00 | |
+ 3 | 13:00:00 | |
+ 4 | 14:00:00 | |
+ 5 | 15:00:00 | |
+ 6 | 15:00:00 | |
+ 7 | 17:00:00 | |
+ 8 | 18:00:00 | |
+ 9 | 19:00:00 | |
+ 10 | 20:00:00 | |
+ 11 | 21:00:00 | |
+(12 rows)
+
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time range between
+ '-infinity'::interval following and
+ 'infinity'::interval following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
select id, f_timetz, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timetz range between
'70 min'::interval preceding and '2 hours'::interval following);
id | f_timetz | first_value | last_value
----+-------------+-------------+------------
- 1 | 11:00:00+01 | 1 | 3
+ 0 | 10:00:00+01 | 0 | 2
+ 1 | 11:00:00+01 | 0 | 3
2 | 12:00:00+01 | 1 | 4
3 | 13:00:00+01 | 2 | 6
4 | 14:00:00+01 | 3 | 6
@@ -2432,9 +2510,10 @@ window w as (order by f_timetz range between
6 | 15:00:00+01 | 4 | 7
7 | 17:00:00+01 | 7 | 9
8 | 18:00:00+01 | 7 | 10
- 9 | 19:00:00+01 | 8 | 10
- 10 | 20:00:00+01 | 9 | 10
-(10 rows)
+ 9 | 19:00:00+01 | 8 | 11
+ 10 | 20:00:00+01 | 9 | 11
+ 11 | 21:00:00+01 | 10 | 11
+(12 rows)
select id, f_timetz, first_value(id) over w, last_value(id) over w
from datetimes
@@ -2442,7 +2521,8 @@ window w as (order by f_timetz desc range between
'70 min' preceding and '2 hours' following);
id | f_timetz | first_value | last_value
----+-------------+-------------+------------
- 10 | 20:00:00+01 | 10 | 8
+ 11 | 21:00:00+01 | 11 | 9
+ 10 | 20:00:00+01 | 11 | 8
9 | 19:00:00+01 | 10 | 7
8 | 18:00:00+01 | 9 | 7
7 | 17:00:00+01 | 8 | 5
@@ -2450,16 +2530,89 @@ window w as (order by f_timetz desc range between
5 | 15:00:00+01 | 6 | 3
4 | 14:00:00+01 | 6 | 2
3 | 13:00:00+01 | 4 | 1
- 2 | 12:00:00+01 | 3 | 1
- 1 | 11:00:00+01 | 2 | 1
-(10 rows)
+ 2 | 12:00:00+01 | 3 | 0
+ 1 | 11:00:00+01 | 2 | 0
+ 0 | 10:00:00+01 | 1 | 0
+(12 rows)
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz desc range between
+ '70 min' preceding and '-2 hours' following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+ id | f_timetz | first_value | last_value
+----+-------------+-------------+------------
+ 0 | 10:00:00+01 | 0 | 11
+ 1 | 11:00:00+01 | 0 | 11
+ 2 | 12:00:00+01 | 0 | 11
+ 3 | 13:00:00+01 | 0 | 11
+ 4 | 14:00:00+01 | 0 | 11
+ 5 | 15:00:00+01 | 0 | 11
+ 6 | 15:00:00+01 | 0 | 11
+ 7 | 17:00:00+01 | 0 | 11
+ 8 | 18:00:00+01 | 0 | 11
+ 9 | 19:00:00+01 | 0 | 11
+ 10 | 20:00:00+01 | 0 | 11
+ 11 | 21:00:00+01 | 0 | 11
+(12 rows)
+
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+ id | f_timetz | first_value | last_value
+----+-------------+-------------+------------
+ 0 | 10:00:00+01 | |
+ 1 | 11:00:00+01 | |
+ 2 | 12:00:00+01 | |
+ 3 | 13:00:00+01 | |
+ 4 | 14:00:00+01 | |
+ 5 | 15:00:00+01 | |
+ 6 | 15:00:00+01 | |
+ 7 | 17:00:00+01 | |
+ 8 | 18:00:00+01 | |
+ 9 | 19:00:00+01 | |
+ 10 | 20:00:00+01 | |
+ 11 | 21:00:00+01 | |
+(12 rows)
+
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz range between
+ 'infinity'::interval following and 'infinity'::interval following);
+ id | f_timetz | first_value | last_value
+----+-------------+-------------+------------
+ 0 | 10:00:00+01 | |
+ 1 | 11:00:00+01 | |
+ 2 | 12:00:00+01 | |
+ 3 | 13:00:00+01 | |
+ 4 | 14:00:00+01 | |
+ 5 | 15:00:00+01 | |
+ 6 | 15:00:00+01 | |
+ 7 | 17:00:00+01 | |
+ 8 | 18:00:00+01 | |
+ 9 | 19:00:00+01 | |
+ 10 | 20:00:00+01 | |
+ 11 | 21:00:00+01 | |
+(12 rows)
+
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz range between
+ 'infinity'::interval following and
+ '-infinity'::interval following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
select id, f_interval, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_interval range between
'1 year'::interval preceding and '1 year'::interval following);
id | f_interval | first_value | last_value
----+------------+-------------+------------
+ 0 | -infinity | 0 | 0
1 | @ 1 year | 1 | 2
2 | @ 2 years | 1 | 3
3 | @ 3 years | 2 | 4
@@ -2470,7 +2623,8 @@ window w as (order by f_interval range between
8 | @ 8 years | 7 | 9
9 | @ 9 years | 8 | 10
10 | @ 10 years | 9 | 10
-(10 rows)
+ 11 | infinity | 11 | 11
+(12 rows)
select id, f_interval, first_value(id) over w, last_value(id) over w
from datetimes
@@ -2478,6 +2632,7 @@ window w as (order by f_interval desc range between
'1 year' preceding and '1 year' following);
id | f_interval | first_value | last_value
----+------------+-------------+------------
+ 11 | infinity | 11 | 11
10 | @ 10 years | 10 | 9
9 | @ 9 years | 10 | 8
8 | @ 8 years | 9 | 7
@@ -2488,14 +2643,87 @@ window w as (order by f_interval desc range between
3 | @ 3 years | 4 | 2
2 | @ 2 years | 3 | 1
1 | @ 1 year | 2 | 1
-(10 rows)
+ 0 | -infinity | 0 | 0
+(12 rows)
+
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval desc range between
+ '-1 year' preceding and '1 year' following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+ id | f_interval | first_value | last_value
+----+------------+-------------+------------
+ 0 | -infinity | 0 | 11
+ 1 | @ 1 year | 0 | 11
+ 2 | @ 2 years | 0 | 11
+ 3 | @ 3 years | 0 | 11
+ 4 | @ 4 years | 0 | 11
+ 5 | @ 5 years | 0 | 11
+ 6 | @ 5 years | 0 | 11
+ 7 | @ 7 years | 0 | 11
+ 8 | @ 8 years | 0 | 11
+ 9 | @ 9 years | 0 | 11
+ 10 | @ 10 years | 0 | 11
+ 11 | infinity | 0 | 11
+(12 rows)
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+ id | f_interval | first_value | last_value
+----+------------+-------------+------------
+ 0 | -infinity | 0 | 0
+ 1 | @ 1 year | 0 | 0
+ 2 | @ 2 years | 0 | 0
+ 3 | @ 3 years | 0 | 0
+ 4 | @ 4 years | 0 | 0
+ 5 | @ 5 years | 0 | 0
+ 6 | @ 5 years | 0 | 0
+ 7 | @ 7 years | 0 | 0
+ 8 | @ 8 years | 0 | 0
+ 9 | @ 9 years | 0 | 0
+ 10 | @ 10 years | 0 | 0
+ 11 | infinity | 0 | 11
+(12 rows)
+
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval range between
+ 'infinity'::interval following and 'infinity'::interval following);
+ id | f_interval | first_value | last_value
+----+------------+-------------+------------
+ 0 | -infinity | 0 | 11
+ 1 | @ 1 year | 11 | 11
+ 2 | @ 2 years | 11 | 11
+ 3 | @ 3 years | 11 | 11
+ 4 | @ 4 years | 11 | 11
+ 5 | @ 5 years | 11 | 11
+ 6 | @ 5 years | 11 | 11
+ 7 | @ 7 years | 11 | 11
+ 8 | @ 8 years | 11 | 11
+ 9 | @ 9 years | 11 | 11
+ 10 | @ 10 years | 11 | 11
+ 11 | infinity | 11 | 11
+(12 rows)
+
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval range between
+ '-infinity'::interval following and
+ 'infinity'::interval following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
select id, f_timestamptz, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timestamptz range between
'1 year'::interval preceding and '1 year'::interval following);
id | f_timestamptz | first_value | last_value
----+------------------------------+-------------+------------
+ 0 | -infinity | 0 | 0
1 | Thu Oct 19 02:23:54 2000 PDT | 1 | 3
2 | Fri Oct 19 02:23:54 2001 PDT | 1 | 4
3 | Fri Oct 19 02:23:54 2001 PDT | 1 | 4
@@ -2506,7 +2734,8 @@ window w as (order by f_timestamptz range between
8 | Thu Oct 19 02:23:54 2006 PDT | 7 | 9
9 | Fri Oct 19 02:23:54 2007 PDT | 8 | 10
10 | Sun Oct 19 02:23:54 2008 PDT | 9 | 10
-(10 rows)
+ 11 | infinity | 11 | 11
+(12 rows)
select id, f_timestamptz, first_value(id) over w, last_value(id) over w
from datetimes
@@ -2514,6 +2743,7 @@ window w as (order by f_timestamptz desc range between
'1 year' preceding and '1 year' following);
id | f_timestamptz | first_value | last_value
----+------------------------------+-------------+------------
+ 11 | infinity | 11 | 11
10 | Sun Oct 19 02:23:54 2008 PDT | 10 | 9
9 | Fri Oct 19 02:23:54 2007 PDT | 10 | 8
8 | Thu Oct 19 02:23:54 2006 PDT | 9 | 7
@@ -2524,14 +2754,87 @@ window w as (order by f_timestamptz desc range between
3 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
2 | Fri Oct 19 02:23:54 2001 PDT | 4 | 1
1 | Thu Oct 19 02:23:54 2000 PDT | 3 | 1
-(10 rows)
+ 0 | -infinity | 0 | 0
+(12 rows)
+
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz desc range between
+ '1 year' preceding and '-1 year' following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+ id | f_timestamptz | first_value | last_value
+----+------------------------------+-------------+------------
+ 0 | -infinity | 0 | 11
+ 1 | Thu Oct 19 02:23:54 2000 PDT | 0 | 11
+ 2 | Fri Oct 19 02:23:54 2001 PDT | 0 | 11
+ 3 | Fri Oct 19 02:23:54 2001 PDT | 0 | 11
+ 4 | Sat Oct 19 02:23:54 2002 PDT | 0 | 11
+ 5 | Sun Oct 19 02:23:54 2003 PDT | 0 | 11
+ 6 | Tue Oct 19 02:23:54 2004 PDT | 0 | 11
+ 7 | Wed Oct 19 02:23:54 2005 PDT | 0 | 11
+ 8 | Thu Oct 19 02:23:54 2006 PDT | 0 | 11
+ 9 | Fri Oct 19 02:23:54 2007 PDT | 0 | 11
+ 10 | Sun Oct 19 02:23:54 2008 PDT | 0 | 11
+ 11 | infinity | 0 | 11
+(12 rows)
+
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+ id | f_timestamptz | first_value | last_value
+----+------------------------------+-------------+------------
+ 0 | -infinity | 0 | 0
+ 1 | Thu Oct 19 02:23:54 2000 PDT | 0 | 0
+ 2 | Fri Oct 19 02:23:54 2001 PDT | 0 | 0
+ 3 | Fri Oct 19 02:23:54 2001 PDT | 0 | 0
+ 4 | Sat Oct 19 02:23:54 2002 PDT | 0 | 0
+ 5 | Sun Oct 19 02:23:54 2003 PDT | 0 | 0
+ 6 | Tue Oct 19 02:23:54 2004 PDT | 0 | 0
+ 7 | Wed Oct 19 02:23:54 2005 PDT | 0 | 0
+ 8 | Thu Oct 19 02:23:54 2006 PDT | 0 | 0
+ 9 | Fri Oct 19 02:23:54 2007 PDT | 0 | 0
+ 10 | Sun Oct 19 02:23:54 2008 PDT | 0 | 0
+ 11 | infinity | 0 | 11
+(12 rows)
+
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz range between
+ 'infinity'::interval following and 'infinity'::interval following);
+ id | f_timestamptz | first_value | last_value
+----+------------------------------+-------------+------------
+ 0 | -infinity | 0 | 11
+ 1 | Thu Oct 19 02:23:54 2000 PDT | 11 | 11
+ 2 | Fri Oct 19 02:23:54 2001 PDT | 11 | 11
+ 3 | Fri Oct 19 02:23:54 2001 PDT | 11 | 11
+ 4 | Sat Oct 19 02:23:54 2002 PDT | 11 | 11
+ 5 | Sun Oct 19 02:23:54 2003 PDT | 11 | 11
+ 6 | Tue Oct 19 02:23:54 2004 PDT | 11 | 11
+ 7 | Wed Oct 19 02:23:54 2005 PDT | 11 | 11
+ 8 | Thu Oct 19 02:23:54 2006 PDT | 11 | 11
+ 9 | Fri Oct 19 02:23:54 2007 PDT | 11 | 11
+ 10 | Sun Oct 19 02:23:54 2008 PDT | 11 | 11
+ 11 | infinity | 11 | 11
+(12 rows)
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz range between
+ '-infinity'::interval following and
+ 'infinity'::interval following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
select id, f_timestamp, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timestamp range between
'1 year'::interval preceding and '1 year'::interval following);
id | f_timestamp | first_value | last_value
----+--------------------------+-------------+------------
+ 0 | -infinity | 0 | 0
1 | Thu Oct 19 10:23:54 2000 | 1 | 3
2 | Fri Oct 19 10:23:54 2001 | 1 | 4
3 | Fri Oct 19 10:23:54 2001 | 1 | 4
@@ -2542,7 +2845,8 @@ window w as (order by f_timestamp range between
8 | Thu Oct 19 10:23:54 2006 | 7 | 9
9 | Fri Oct 19 10:23:54 2007 | 8 | 10
10 | Sun Oct 19 10:23:54 2008 | 9 | 10
-(10 rows)
+ 11 | infinity | 11 | 11
+(12 rows)
select id, f_timestamp, first_value(id) over w, last_value(id) over w
from datetimes
@@ -2550,6 +2854,7 @@ window w as (order by f_timestamp desc range between
'1 year' preceding and '1 year' following);
id | f_timestamp | first_value | last_value
----+--------------------------+-------------+------------
+ 11 | infinity | 11 | 11
10 | Sun Oct 19 10:23:54 2008 | 10 | 9
9 | Fri Oct 19 10:23:54 2007 | 10 | 8
8 | Thu Oct 19 10:23:54 2006 | 9 | 7
@@ -2560,8 +2865,80 @@ window w as (order by f_timestamp desc range between
3 | Fri Oct 19 10:23:54 2001 | 4 | 1
2 | Fri Oct 19 10:23:54 2001 | 4 | 1
1 | Thu Oct 19 10:23:54 2000 | 3 | 1
-(10 rows)
+ 0 | -infinity | 0 | 0
+(12 rows)
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp desc range between
+ '-1 year' preceding and '1 year' following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+ id | f_timestamp | first_value | last_value
+----+--------------------------+-------------+------------
+ 0 | -infinity | 0 | 11
+ 1 | Thu Oct 19 10:23:54 2000 | 0 | 11
+ 2 | Fri Oct 19 10:23:54 2001 | 0 | 11
+ 3 | Fri Oct 19 10:23:54 2001 | 0 | 11
+ 4 | Sat Oct 19 10:23:54 2002 | 0 | 11
+ 5 | Sun Oct 19 10:23:54 2003 | 0 | 11
+ 6 | Tue Oct 19 10:23:54 2004 | 0 | 11
+ 7 | Wed Oct 19 10:23:54 2005 | 0 | 11
+ 8 | Thu Oct 19 10:23:54 2006 | 0 | 11
+ 9 | Fri Oct 19 10:23:54 2007 | 0 | 11
+ 10 | Sun Oct 19 10:23:54 2008 | 0 | 11
+ 11 | infinity | 0 | 11
+(12 rows)
+
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+ id | f_timestamp | first_value | last_value
+----+--------------------------+-------------+------------
+ 0 | -infinity | 0 | 0
+ 1 | Thu Oct 19 10:23:54 2000 | 0 | 0
+ 2 | Fri Oct 19 10:23:54 2001 | 0 | 0
+ 3 | Fri Oct 19 10:23:54 2001 | 0 | 0
+ 4 | Sat Oct 19 10:23:54 2002 | 0 | 0
+ 5 | Sun Oct 19 10:23:54 2003 | 0 | 0
+ 6 | Tue Oct 19 10:23:54 2004 | 0 | 0
+ 7 | Wed Oct 19 10:23:54 2005 | 0 | 0
+ 8 | Thu Oct 19 10:23:54 2006 | 0 | 0
+ 9 | Fri Oct 19 10:23:54 2007 | 0 | 0
+ 10 | Sun Oct 19 10:23:54 2008 | 0 | 0
+ 11 | infinity | 0 | 11
+(12 rows)
+
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp range between
+ 'infinity'::interval following and 'infinity'::interval following);
+ id | f_timestamp | first_value | last_value
+----+--------------------------+-------------+------------
+ 0 | -infinity | 0 | 11
+ 1 | Thu Oct 19 10:23:54 2000 | 11 | 11
+ 2 | Fri Oct 19 10:23:54 2001 | 11 | 11
+ 3 | Fri Oct 19 10:23:54 2001 | 11 | 11
+ 4 | Sat Oct 19 10:23:54 2002 | 11 | 11
+ 5 | Sun Oct 19 10:23:54 2003 | 11 | 11
+ 6 | Tue Oct 19 10:23:54 2004 | 11 | 11
+ 7 | Wed Oct 19 10:23:54 2005 | 11 | 11
+ 8 | Thu Oct 19 10:23:54 2006 | 11 | 11
+ 9 | Fri Oct 19 10:23:54 2007 | 11 | 11
+ 10 | Sun Oct 19 10:23:54 2008 | 11 | 11
+ 11 | infinity | 11 | 11
+(12 rows)
+
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp range between
+ '-infinity'::interval following and
+ 'infinity'::interval following); -- error, negative offset disallowed
+ERROR: invalid preceding or following size in window function
-- RANGE offset PRECEDING/FOLLOWING error cases
select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_date from empsalary;
@@ -4375,6 +4752,52 @@ SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDE
4 |
(4 rows)
+-- moving aggregates over infinite intervals
+SELECT x
+ ,avg(x) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) as curr_next_avg
+ ,avg(x) OVER(ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as prev_curr_avg
+ ,sum(x) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) as curr_next_sum
+ ,sum(x) OVER(ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as prev_curr_sum
+FROM (VALUES (NULL::interval),
+ ('infinity'::interval),
+ ('-2147483648 days -2147483648 months -9223372036854775807 usecs'), -- extreme interval value
+ ('-infinity'::interval),
+ ('2147483647 days 2147483647 months 9223372036854775806 usecs'), -- extreme interval value
+ ('infinity'::interval),
+ ('6 days'::interval),
+ ('7 days'::interval),
+ (NULL::interval),
+ ('-infinity'::interval)) v(x);
+ x | curr_next_avg | prev_curr_avg | curr_next_sum | prev_curr_sum
+------------------------------------------------------------------------------+-------------------+-------------------+---------------+---------------
+ | infinity | | infinity |
+ infinity | infinity | infinity | infinity | infinity
+ @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775807 secs ago | -infinity | infinity | -infinity | infinity
+ -infinity | -infinity | -infinity | -infinity | -infinity
+ @ 178956970 years 7 mons 2147483647 days 2562047788 hours 54.775806 secs | infinity | -infinity | infinity | -infinity
+ infinity | infinity | infinity | infinity | infinity
+ @ 6 days | @ 6 days 12 hours | infinity | @ 13 days | infinity
+ @ 7 days | @ 7 days | @ 6 days 12 hours | @ 7 days | @ 13 days
+ | -infinity | @ 7 days | -infinity | @ 7 days
+ -infinity | -infinity | -infinity | -infinity | -infinity
+(10 rows)
+
+--should fail.
+SELECT x, avg(x) OVER(ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
+FROM (VALUES (NULL::interval),
+ ('3 days'::interval),
+ ('infinity'::timestamptz - now()),
+ ('6 days'::interval),
+ ('-infinity'::interval)) v(x);
+ERROR: interval out of range.
+--should fail.
+SELECT x, sum(x) OVER(ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
+FROM (VALUES (NULL::interval),
+ ('3 days'::interval),
+ ('infinity'::timestamptz - now()),
+ ('6 days'::interval),
+ ('-infinity'::interval)) v(x);
+ERROR: interval out of range.
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
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index b666dbad670..116106d30ff 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -684,4 +684,23 @@ SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
DROP TABLE brin_interval_test;
RESET enable_seqscan;
+
+-- test handling of infinite interval values
+CREATE TABLE brin_interval_test(a INTERVAL);
+
+INSERT INTO brin_interval_test VALUES ('-infinity'), ('infinity');
+INSERT INTO brin_interval_test SELECT (i || ' days')::interval FROM generate_series(100, 140) s(i);
+
+CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan = off;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
+
+DROP TABLE brin_interval_test;
+RESET enable_seqscan;
RESET datestyle;
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 39a35a6b7ce..252bce4b1ce 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -213,10 +213,12 @@ SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract"
SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
FROM TIME_TBL t, INTERVAL_TBL i
+ WHERE isfinite(i.f1)
ORDER BY 1,2;
SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract"
FROM TIMETZ_TBL t, INTERVAL_TBL i
+ WHERE isfinite(i.f1)
ORDER BY 1,2;
-- SQL9x OVERLAPS operator
@@ -293,7 +295,6 @@ SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
- WHERE isfinite(d.f1)
ORDER BY minus, "timestamp", "interval";
SELECT d.f1 AS "timestamp",
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index a0a373f08bd..caad291890c 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -14,6 +14,8 @@ SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
SELECT INTERVAL '1.5 months' AS "One month 15 days";
SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
+SELECT INTERVAL 'infinity' AS "eternity";
+SELECT INTERVAL '-infinity' AS "beginning of time";
CREATE TABLE INTERVAL_TBL (f1 interval);
@@ -27,6 +29,8 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity');
-- badly formatted interval
INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
@@ -66,6 +70,17 @@ SELECT r1.*, r2.*
WHERE r1.f1 > r2.f1
ORDER BY r1.f1, r2.f1;
+-- test unary minus
+
+SELECT f1, -f1 FROM INTERVAL_TBL;
+SELECT -('-2147483648 months'::interval); -- should fail
+SELECT -('-2147483647 months'::interval); -- ok
+SELECT -('-2147483648 days'::interval); -- should fail
+SELECT -('-2147483647 days'::interval); -- ok
+SELECT -('-9223372036854775808 us'::interval); -- should fail
+SELECT -('-9223372036854775807 us'::interval); -- ok
+SELECT -('-2147483647 months -2147483647 days -9223372036854775807 us'::interval); -- should fail
+
-- Test intervals that are large enough to overflow 64 bits in comparisons
CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
INSERT INTO INTERVAL_TBL_OF (f1) VALUES
@@ -95,6 +110,9 @@ SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
RESET enable_seqscan;
+-- subtracting about-to-overflow values should result in 0
+SELECT f1 - f1 FROM INTERVAL_TBL_OF;
+
DROP TABLE INTERVAL_TBL_OF;
-- Test multiplication and division with intervals.
@@ -140,7 +158,7 @@ SELECT * FROM INTERVAL_TBL;
-- known to change the allowed input syntax for type interval without
-- updating pg_aggregate.agginitval
-select avg(f1) from interval_tbl;
+select avg(f1) from interval_tbl where isfinite(f1);
-- test long interval input
select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
@@ -528,13 +546,13 @@ select make_interval(mins := -1, secs := -9223372036800.0);
-- test that INT_MIN number is formatted properly
SET IntervalStyle to postgres;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
SET IntervalStyle to sql_standard;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
SET IntervalStyle to iso_8601;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
SET IntervalStyle to postgres_verbose;
-select interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+select interval '-2147483647 months -2147483648 days -9223372036854775808 us';
-- check that '30 days' equals '1 month' according to the hash function
select '30 days'::interval = '1 month'::interval as t;
@@ -598,6 +616,157 @@ SELECT f1,
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
+--
+-- test infinite intervals
+--
+
+-- largest finite intervals
+SELECT interval '-2147483648 months -2147483648 days -9223372036854775807 us';
+SELECT interval '2147483647 months 2147483647 days 9223372036854775806 us';
+
+-- infinite intervals
+SELECT interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+SELECT interval '2147483647 months 2147483647 days 9223372036854775807 us';
+
+CREATE TABLE INFINITE_INTERVAL_TBL (i interval);
+INSERT INTO INFINITE_INTERVAL_TBL VALUES ('infinity'), ('-infinity'), ('1 year 2 days 3 hours');
+
+SELECT i, isfinite(i) FROM INFINITE_INTERVAL_TBL;
+
+-- test basic arithmetic
+CREATE FUNCTION eval(expr text)
+RETURNS text AS
+$$
+DECLARE
+ result text;
+BEGIN
+ EXECUTE 'select '||expr INTO result;
+ RETURN result;
+EXCEPTION WHEN OTHERS THEN
+ RETURN SQLERRM;
+END
+$$
+LANGUAGE plpgsql;
+
+SELECT d AS date, i AS interval,
+ eval(format('date %L + interval %L', d, i)) AS plus,
+ eval(format('date %L - interval %L', d, i)) AS minus
+FROM (VALUES (date '-infinity'),
+ (date '1995-08-06'),
+ (date 'infinity')) AS t1(d),
+ (VALUES (interval '-infinity'),
+ (interval 'infinity')) AS t2(i);
+
+SELECT i1 AS interval1, i2 AS interval2,
+ eval(format('interval %L + interval %L', i1, i2)) AS plus,
+ eval(format('interval %L - interval %L', i1, i2)) AS minus
+FROM (VALUES (interval '-infinity'),
+ (interval '2 months'),
+ (interval 'infinity')) AS t1(i1),
+ (VALUES (interval '-infinity'),
+ (interval '10 days'),
+ (interval 'infinity')) AS t2(i2);
+
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' + interval '1 month 1 day 1 us';
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' + interval '-1 month -1 day -1 us';
+SELECT interval '2147483646 months 2147483646 days 9223372036854775806 us' - interval '-1 month -1 day -1 us';
+SELECT interval '-2147483647 months -2147483647 days -9223372036854775807 us' - interval '1 month 1 day 1 us';
+
+SELECT t AS timestamp, i AS interval,
+ eval(format('timestamp %L + interval %L', t, i)) AS plus,
+ eval(format('timestamp %L - interval %L', t, i)) AS minus
+FROM (VALUES (timestamp '-infinity'),
+ (timestamp '1995-08-06 12:30:15'),
+ (timestamp 'infinity')) AS t1(t),
+ (VALUES (interval '-infinity'),
+ (interval 'infinity')) AS t2(i);
+
+SELECT t AT TIME ZONE 'GMT' AS timestamptz, i AS interval,
+ eval(format('timestamptz %L + interval %L', t, i)) AS plus,
+ eval(format('timestamptz %L - interval %L', t, i)) AS minus
+FROM (VALUES (timestamptz '-infinity'),
+ (timestamptz '1995-08-06 12:30:15 GMT'),
+ (timestamptz 'infinity')) AS t1(t),
+ (VALUES (interval '-infinity'),
+ (interval 'infinity')) AS t2(i);
+
+-- time +/- infinite interval not supported
+SELECT time '11:27:42' + interval 'infinity';
+SELECT time '11:27:42' + interval '-infinity';
+SELECT time '11:27:42' - interval 'infinity';
+SELECT time '11:27:42' - interval '-infinity';
+SELECT timetz '11:27:42' + interval 'infinity';
+SELECT timetz '11:27:42' + interval '-infinity';
+SELECT timetz '11:27:42' - interval 'infinity';
+SELECT timetz '11:27:42' - interval '-infinity';
+
+SELECT lhst.i lhs,
+ rhst.i rhs,
+ lhst.i < rhst.i AS lt,
+ lhst.i <= rhst.i AS le,
+ lhst.i = rhst.i AS eq,
+ lhst.i > rhst.i AS gt,
+ lhst.i >= rhst.i AS ge,
+ lhst.i <> rhst.i AS ne
+ FROM INFINITE_INTERVAL_TBL lhst CROSS JOIN INFINITE_INTERVAL_TBL rhst
+ WHERE NOT isfinite(lhst.i);
+
+SELECT i AS interval,
+ -i AS um,
+ i * 2.0 AS mul,
+ i * -2.0 AS mul_neg,
+ i * 'infinity' AS mul_inf,
+ i * '-infinity' AS mul_inf_neg,
+ i / 3.0 AS div,
+ i / -3.0 AS div_neg
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+
+SELECT -interval '-2147483647 months -2147483647 days -9223372036854775807 us';
+SELECT interval 'infinity' * 'nan';
+SELECT interval '-infinity' * 'nan';
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' * 2;
+SELECT interval 'infinity' * 0;
+SELECT interval '-infinity' * 0;
+SELECT interval '0 days' * 'infinity'::float;
+SELECT interval '0 days' * '-infinity'::float;
+SELECT interval '5 days' * 'infinity'::float;
+SELECT interval '5 days' * '-infinity'::float;
+
+SELECT interval 'infinity' / 'infinity';
+SELECT interval 'infinity' / '-infinity';
+SELECT interval 'infinity' / 'nan';
+SELECT interval '-infinity' / 'infinity';
+SELECT interval '-infinity' / '-infinity';
+SELECT interval '-infinity' / 'nan';
+SELECT interval '-1073741824 months -1073741824 days -4611686018427387904 us' / 0.5;
+
+SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+
+SELECT i AS interval, date_trunc('hour', i)
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+
+SELECT i AS interval, justify_days(i), justify_hours(i), justify_interval(i)
+ FROM INFINITE_INTERVAL_TBL
+ WHERE NOT isfinite(i);
+
+SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamp);
+SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamp);
+SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
+SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamptz);
+SELECT timezone('infinity'::interval, '12:12:12'::time);
+SELECT timezone('-infinity'::interval, '12:12:12'::time);
+SELECT timezone('infinity'::interval, '12:12:12'::timetz);
+SELECT timezone('-infinity'::interval, '12:12:12'::timetz);
+
+SELECT 'infinity'::interval::time;
+SELECT '-infinity'::interval::time;
+
+SELECT to_char('infinity'::interval, 'YYYY');
+SELECT to_char('-infinity'::interval, 'YYYY');
+
-- "ago" can only appear once at the end of an interval.
SELECT INTERVAL '42 days 2 seconds ago ago';
SELECT INTERVAL '2 minutes ago 5 days';
@@ -605,3 +774,17 @@ SELECT INTERVAL '2 minutes ago 5 days';
-- consecutive and dangling units are not allowed.
SELECT INTERVAL 'hour 5 months';
SELECT INTERVAL '1 year months days 5 hours';
+
+-- unacceptable reserved words in interval. Only "infinity", "+infinity" and
+-- "-infinity" are allowed.
+SELECT INTERVAL 'now';
+SELECT INTERVAL 'today';
+SELECT INTERVAL 'tomorrow';
+SELECT INTERVAL 'allballs';
+SELECT INTERVAL 'epoch';
+SELECT INTERVAL 'yesterday';
+
+-- infinity specification should be the only thing
+SELECT INTERVAL 'infinity years';
+SELECT INTERVAL 'infinity ago';
+SELECT INTERVAL '+infinity -infinity';
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index b9bcce9cfe3..ea12ffd18d4 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -397,3 +397,22 @@ select generate_series('2022-01-01 00:00'::timestamp,
select * from generate_series('2020-01-01 00:00'::timestamp,
'2020-01-02 03:00'::timestamp,
'0 hour'::interval);
+select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval 'infinity');
+select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval '-infinity');
+
+
+-- test arithmetic with infinite timestamps
+select timestamp 'infinity' - timestamp 'infinity';
+select timestamp 'infinity' - timestamp '-infinity';
+select timestamp '-infinity' - timestamp 'infinity';
+select timestamp '-infinity' - timestamp '-infinity';
+select timestamp 'infinity' - timestamp '1995-08-06 12:12:12';
+select timestamp '-infinity' - timestamp '1995-08-06 12:12:12';
+
+-- test age() with infinite timestamps
+select age(timestamp 'infinity');
+select age(timestamp '-infinity');
+select age(timestamp 'infinity', timestamp 'infinity');
+select age(timestamp 'infinity', timestamp '-infinity');
+select age(timestamp '-infinity', timestamp 'infinity');
+select age(timestamp '-infinity', timestamp '-infinity');
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index cdc57bc1606..a2dcd5f5d8e 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -458,6 +458,8 @@ select generate_series('2022-01-01 00:00'::timestamptz,
select * from generate_series('2020-01-01 00:00'::timestamptz,
'2020-01-02 03:00'::timestamptz,
'0 hour'::interval);
+select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval 'infinity');
+select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval '-infinity');
-- Interval crossing time shift for Europe/Warsaw timezone (with DST)
SET TimeZone to 'UTC';
@@ -642,3 +644,19 @@ insert into tmptz values ('2017-01-18 00:00+00');
explain (costs off)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
+
+-- test arithmetic with infinite timestamps
+SELECT timestamptz 'infinity' - timestamptz 'infinity';
+SELECT timestamptz 'infinity' - timestamptz '-infinity';
+SELECT timestamptz '-infinity' - timestamptz 'infinity';
+SELECT timestamptz '-infinity' - timestamptz '-infinity';
+SELECT timestamptz 'infinity' - timestamptz '1995-08-06 12:12:12';
+SELECT timestamptz '-infinity' - timestamptz '1995-08-06 12:12:12';
+
+-- test age() with infinite timestamps
+SELECT age(timestamptz 'infinity');
+SELECT age(timestamptz '-infinity');
+SELECT age(timestamptz 'infinity', timestamptz 'infinity');
+SELECT age(timestamptz 'infinity', timestamptz '-infinity');
+SELECT age(timestamptz '-infinity', timestamptz 'infinity');
+SELECT age(timestamptz '-infinity', timestamptz '-infinity');
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 9113a92ae0b..437e948d6c2 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -673,6 +673,7 @@ create temp table datetimes(
);
insert into datetimes values
+(0, '10:00', '10:00 BST', '-infinity', '-infinity', '-infinity'),
(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'),
(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
@@ -682,7 +683,8 @@ insert into datetimes values
(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'),
(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'),
(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'),
-(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54');
+(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54'),
+(11, '21:00', '21:00 BST', 'infinity', 'infinity', 'infinity');
select id, f_time, first_value(id) over w, last_value(id) over w
from datetimes
@@ -694,6 +696,32 @@ from datetimes
window w as (order by f_time desc range between
'70 min' preceding and '2 hours' following);
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time desc range between
+ '-70 min' preceding and '2 hours' following); -- error, negative offset disallowed
+
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time range between
+ 'infinity'::interval following and 'infinity'::interval following);
+
+select id, f_time, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_time range between
+ '-infinity'::interval following and
+ 'infinity'::interval following); -- error, negative offset disallowed
+
select id, f_timetz, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timetz range between
@@ -704,6 +732,32 @@ from datetimes
window w as (order by f_timetz desc range between
'70 min' preceding and '2 hours' following);
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz desc range between
+ '70 min' preceding and '-2 hours' following); -- error, negative offset disallowed
+
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz range between
+ 'infinity'::interval following and 'infinity'::interval following);
+
+select id, f_timetz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timetz range between
+ 'infinity'::interval following and
+ '-infinity'::interval following); -- error, negative offset disallowed
+
select id, f_interval, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_interval range between
@@ -714,6 +768,32 @@ from datetimes
window w as (order by f_interval desc range between
'1 year' preceding and '1 year' following);
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval desc range between
+ '-1 year' preceding and '1 year' following); -- error, negative offset disallowed
+
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval range between
+ 'infinity'::interval following and 'infinity'::interval following);
+
+select id, f_interval, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_interval range between
+ '-infinity'::interval following and
+ 'infinity'::interval following); -- error, negative offset disallowed
+
select id, f_timestamptz, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timestamptz range between
@@ -724,6 +804,32 @@ from datetimes
window w as (order by f_timestamptz desc range between
'1 year' preceding and '1 year' following);
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz desc range between
+ '1 year' preceding and '-1 year' following); -- error, negative offset disallowed
+
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz range between
+ 'infinity'::interval following and 'infinity'::interval following);
+
+select id, f_timestamptz, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamptz range between
+ '-infinity'::interval following and
+ 'infinity'::interval following); -- error, negative offset disallowed
+
select id, f_timestamp, first_value(id) over w, last_value(id) over w
from datetimes
window w as (order by f_timestamp range between
@@ -734,6 +840,32 @@ from datetimes
window w as (order by f_timestamp desc range between
'1 year' preceding and '1 year' following);
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp desc range between
+ '-1 year' preceding and '1 year' following); -- error, negative offset disallowed
+
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp range between
+ 'infinity'::interval preceding and 'infinity'::interval following);
+
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp range between
+ 'infinity'::interval preceding and 'infinity'::interval preceding);
+
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp range between
+ 'infinity'::interval following and 'infinity'::interval following);
+
+select id, f_timestamp, first_value(id) over w, last_value(id) over w
+from datetimes
+window w as (order by f_timestamp range between
+ '-infinity'::interval following and
+ 'infinity'::interval following); -- error, negative offset disallowed
+
-- RANGE offset PRECEDING/FOLLOWING error cases
select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following
exclude ties), salary, enroll_date from empsalary;
@@ -1591,6 +1723,39 @@ SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED
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);
+-- moving aggregates over infinite intervals
+SELECT x
+ ,avg(x) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) as curr_next_avg
+ ,avg(x) OVER(ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as prev_curr_avg
+ ,sum(x) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) as curr_next_sum
+ ,sum(x) OVER(ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as prev_curr_sum
+FROM (VALUES (NULL::interval),
+ ('infinity'::interval),
+ ('-2147483648 days -2147483648 months -9223372036854775807 usecs'), -- extreme interval value
+ ('-infinity'::interval),
+ ('2147483647 days 2147483647 months 9223372036854775806 usecs'), -- extreme interval value
+ ('infinity'::interval),
+ ('6 days'::interval),
+ ('7 days'::interval),
+ (NULL::interval),
+ ('-infinity'::interval)) v(x);
+
+--should fail.
+SELECT x, avg(x) OVER(ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
+FROM (VALUES (NULL::interval),
+ ('3 days'::interval),
+ ('infinity'::timestamptz - now()),
+ ('6 days'::interval),
+ ('-infinity'::interval)) v(x);
+
+--should fail.
+SELECT x, sum(x) OVER(ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
+FROM (VALUES (NULL::interval),
+ ('3 days'::interval),
+ ('infinity'::timestamptz - now()),
+ ('6 days'::interval),
+ ('-infinity'::interval)) v(x);
+
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);
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-14 01:53:19 +0000