On Sat, Dec 17, 2022 at 2:34 PM Joseph Koshakow <kosh...@gmail.com> wrote:
>
> Hi Ashutosh,
>
> I've added tests for all the operators and functions involving
> intervals and what I think the expected behaviors to be. The
> formatting might be slightly off and I've left the contents of the
> error messages as TODOs. Hopefully it's a good reference for the
> implementation.
>
> > Adding infinite interval to an infinite timestamp with opposite
> > direction is not going to yield 0 but some infinity. Since we are adding
> > interval to the timestamp the resultant timestamp is an infinity
> > preserving the direction.
>
> I think I disagree with this. Tom Lane in one of the previous threads
> said:
> > tl;dr: we should model it after the behavior of IEEE float infinities,
> > except we'll want to throw errors where those produce NaNs.
> and I agree with this opinion. I believe that means that adding an
> infinite interval to an infinite timestamp with opposite directions
> should yield an error instead of some infinity. Since with floats this
> would yield a NaN.
>
> > Dividing infinite interval by finite number keeps it infinite.
> > TODO: Do we change the sign of infinity if factor is negative?
> Again if we model this after the IEEE float behavior, then the answer
> is yes, we do change the sign of infinity.
>
> - Joe Koshakow
I ended up doing some more work in the attached patch. Here are some
updates:
- I modified the arithmetic operators to more closely match IEEE
floats. Error messages are still all TODO, and they may have the wrong
error code.
- I implemented some more operators and functions.
- I moved the helper functions you created into macros in timestamp.h
to more closely match the implementation of infinite timestamps and
dates. Also so dates.c could access them.
- There seems to be an existing overflow error with interval
subtraction. Many of the arithmetic operators of the form
`X - Interval` are converted to `X + (-Interval)`. This will overflow
in the case that some interval field is INT32_MIN or INT64_MIN.
Additionally, negating a positive infinity interval won't result in a
negative infinity interval and vice versa. We'll have to come up with
an efficient solution for this.
- Joe Koshakow
From e6e764dd8f8423f2aec0fb3782f170c59557adf6 Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <kosh...@gmail.com>
Date: Sat, 17 Dec 2022 14:21:26 -0500
Subject: [PATCH] This is WIP.
Following things are supported
1. Accepts '+/-infinity' as a valid string input for interval type.
2. Support interval_pl, interval_div
3. Tests in interval.sql for comparison operators working fine.
TODOs
1. Various TODOs in code
2. interval_pl: how to handle infinite values with opposite signs
3. timestamp, timestamptz, date and time arithmetic
4. Fix horology test.
Ashutosh Bapat
---
src/backend/utils/adt/date.c | 20 +
src/backend/utils/adt/datetime.c | 2 +
src/backend/utils/adt/timestamp.c | 188 +++++++-
src/include/datatype/timestamp.h | 22 +
src/test/regress/expected/interval.out | 613 ++++++++++++++++++++++++-
src/test/regress/sql/interval.sql | 121 +++++
6 files changed, 949 insertions(+), 17 deletions(-)
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 1cf7c7652d..a2c9214bcf 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -2073,6 +2073,11 @@ time_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeADT result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+
result = time + span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2091,6 +2096,11 @@ time_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeADT result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+
result = time - span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2605,6 +2615,11 @@ timetz_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeTzADT *result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time + span->time;
@@ -2627,6 +2642,11 @@ timetz_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
TimeTzADT *result;
+ if (INTERVAL_NOT_FINITE(span))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time - span->time;
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index b5b117a8ca..1e98c6dc78 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -3634,6 +3634,8 @@ DecodeInterval(char **field, int *ftype, int nf, int range,
case DTK_STRING:
case DTK_SPECIAL:
type = DecodeUnits(i, field[i], &uval);
+ if (type == UNKNOWN_FIELD)
+ type = DecodeSpecial(i, field[i], &uval);
if (type == IGNORE_DTF)
continue;
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 3f2508c0c4..e501e253a6 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -79,6 +79,8 @@ static bool AdjustIntervalForTypmod(Interval *interval, int32 typmod,
static TimestampTz timestamp2timestamptz(Timestamp timestamp);
static Timestamp timestamptz2timestamp(TimestampTz timestamp);
+static void EncodeSpecialInterval(Interval *interval, char *str);
+static void neg_interval_infinite(Interval *interval);
/* common code for timestamptypmodin and timestamptztypmodin */
static int32
@@ -943,6 +945,14 @@ interval_in(PG_FUNCTION_ARGS)
errmsg("interval out of range")));
break;
+ case DTK_LATE:
+ INTERVAL_NOEND(result);
+ break;
+
+ case DTK_EARLY:
+ INTERVAL_NOBEGIN(result);
+ break;
+
default:
elog(ERROR, "unexpected dtype %d while parsing interval \"%s\"",
dtype, str);
@@ -965,8 +975,13 @@ interval_out(PG_FUNCTION_ARGS)
*itm = &tt;
char buf[MAXDATELEN + 1];
- interval2itm(*span, itm);
- EncodeInterval(itm, IntervalStyle, buf);
+ if (INTERVAL_NOT_FINITE(span))
+ EncodeSpecialInterval(span, buf);
+ else
+ {
+ interval2itm(*span, itm);
+ EncodeInterval(itm, IntervalStyle, buf);
+ }
result = pstrdup(buf);
PG_RETURN_CSTRING(result);
@@ -1352,6 +1367,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod,
INT64CONST(0)
};
+ /*
+ * Infinite interval after being subjected to typmod conversion remains
+ * infinite.
+ */
+ if (INTERVAL_NOT_FINITE(interval))
+ return true;
+
/*
* Unspecified range and precision? Then not necessary to adjust. Setting
* typmod to -1 is the convention for all data types.
@@ -1545,6 +1567,17 @@ EncodeSpecialTimestamp(Timestamp dt, char *str)
elog(ERROR, "invalid argument for EncodeSpecialTimestamp");
}
+static void
+EncodeSpecialInterval(Interval *interval, char *str)
+{
+ if (INTERVAL_IS_NOBEGIN(interval))
+ strcpy(str, EARLY);
+ else if (INTERVAL_IS_NOEND(interval))
+ strcpy(str, LATE);
+ else /* shouldn't happen */
+ elog(ERROR, "invalid argument for EncodeSpecialInterval");
+}
+
Datum
now(PG_FUNCTION_ARGS)
{
@@ -2083,7 +2116,8 @@ timestamp_finite(PG_FUNCTION_ARGS)
Datum
interval_finite(PG_FUNCTION_ARGS)
{
- PG_RETURN_BOOL(true);
+ Interval *interval = PG_GETARG_INTERVAL_P(0);
+ PG_RETURN_BOOL(!INTERVAL_NOT_FINITE(interval));
}
@@ -2775,6 +2809,9 @@ interval_justify_interval(PG_FUNCTION_ARGS)
result->day = span->day;
result->time = span->time;
+ if (INTERVAL_NOT_FINITE(result))
+ PG_RETURN_INTERVAL_P(result);
+
/* pre-justify days if it might prevent overflow */
if ((result->day > 0 && result->time > 0) ||
(result->day < 0 && result->time < 0))
@@ -2850,6 +2887,9 @@ interval_justify_hours(PG_FUNCTION_ARGS)
result->day = span->day;
result->time = span->time;
+ if (INTERVAL_NOT_FINITE(result))
+ PG_RETURN_INTERVAL_P(result);
+
TMODULO(result->time, wholeday, USECS_PER_DAY);
if (pg_add_s32_overflow(result->day, wholeday, &result->day))
ereport(ERROR,
@@ -2888,6 +2928,9 @@ interval_justify_days(PG_FUNCTION_ARGS)
result->day = span->day;
result->time = span->time;
+ if (INTERVAL_NOT_FINITE(result))
+ PG_RETURN_INTERVAL_P(result);
+
wholemonth = result->day / DAYS_PER_MONTH;
result->day -= wholemonth * DAYS_PER_MONTH;
if (pg_add_s32_overflow(result->month, wholemonth, &result->month))
@@ -2926,10 +2969,29 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Timestamp result;
- if (TIMESTAMP_NOT_FINITE(timestamp))
+ /*
+ * Adding two infinites with the same sign results in an infinite
+ * timestamp with the same sign. Adding two infintes with
+ * different signs results in an error.
+ */
+ // TODO this logic can probably be combined and cleaned up.
+ if (INTERVAL_IS_NOBEGIN(span) && TIMESTAMP_IS_NOBEGIN(timestamp))
+ TIMESTAMP_NOBEGIN(result);
+ else if (INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
+ TIMESTAMP_NOEND(result);
+ else if (INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+ else if (INTERVAL_IS_NOBEGIN(span))
+ TIMESTAMP_NOBEGIN(result);
+ else if (INTERVAL_IS_NOEND(span))
+ TIMESTAMP_NOEND(result);
+ else if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
+ elog(INFO, "Went to main branch. time: %ld, days: %d, months: %d", span->time, span->day, span->month);
if (span->month != 0)
{
struct pg_tm tt,
@@ -3032,8 +3094,19 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
TimestampTz result;
int tz;
- if (TIMESTAMP_NOT_FINITE(timestamp))
- result = timestamp;
+ /*
+ * Adding two infinites with the same sign results in an infinite
+ * timestamp with the same sign. Adding two infintes with
+ * different signs results in an error.
+ */
+ if (INTERVAL_IS_NOBEGIN(span) && TIMESTAMP_IS_NOBEGIN(timestamp))
+ TIMESTAMP_NOBEGIN(result);
+ else if (INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
+ TIMESTAMP_NOEND(result);
+ else if (INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
else
{
if (span->month != 0)
@@ -3133,6 +3206,13 @@ interval_um(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ if (INTERVAL_NOT_FINITE(interval))
+ {
+ memcpy(result, interval, sizeof(Interval));
+ neg_interval_infinite(result);
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->time = -interval->time;
/* overflow check copied from int4um */
if (interval->time != 0 && SAMESIGN(result->time, interval->time))
@@ -3192,6 +3272,24 @@ interval_pl(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ * Adding two infinite intervals with the same signs results
+ * in an infinite interval with the same sign. Adding two
+ * infinte intervals with different signs results in an error.
+ */
+ if ((INTERVAL_IS_NOBEGIN(span1) && INTERVAL_IS_NOBEGIN(span2)) ||
+ (INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOEND(span2)))
+ {
+ memcpy(result, span1, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (INTERVAL_NOT_FINITE(span1) || INTERVAL_NOT_FINITE(span2))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+ }
+
result->month = span1->month + span2->month;
/* overflow check copied from int4pl */
if (SAMESIGN(span1->month, span2->month) &&
@@ -3226,6 +3324,25 @@ interval_mi(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ * Subtracting two infinite intervals with different signs results
+ * in an infinite interval with the same sign as the left operand.
+ * Subtracting two infinte intervals with the same sign results in
+ * an error.
+ */
+ if ((INTERVAL_IS_NOBEGIN(span1) && INTERVAL_IS_NOEND(span2)) ||
+ (INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOBEGIN(span2)))
+ {
+ memcpy(result, span1, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+ else if (INTERVAL_NOT_FINITE(span1) || INTERVAL_NOT_FINITE(span2))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+ }
+
result->month = span1->month - span2->month;
/* overflow check copied from int4mi */
if (!SAMESIGN(span1->month, span2->month) &&
@@ -3271,6 +3388,18 @@ interval_mul(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ /*
+ * Multiplying infinite interval by finite number keeps it infinite but may change
+ * the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ memcpy(result, span, sizeof(Interval));
+ if (factor < 0.0)
+ neg_interval_infinite(result);
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result_double = span->month * factor;
if (isnan(result_double) ||
result_double > INT_MAX || result_double < INT_MIN)
@@ -3362,6 +3491,18 @@ interval_div(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
+ /*
+ * Dividing infinite interval by finite number keeps it infinite but may change
+ * the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ memcpy(result, span, sizeof(Interval));
+ if (factor < 0.0)
+ neg_interval_infinite(result);
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = (int32) (span->month / factor);
result->day = (int32) (span->day / factor);
@@ -3916,6 +4057,11 @@ timestamp_bin(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("origin out of range")));
+ if (INTERVAL_NOT_FINITE(stride))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("TODO")));
+
if (stride->month != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4371,6 +4517,12 @@ interval_trunc(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ if (INTERVAL_NOT_FINITE(interval))
+ {
+ memcpy(result, interval, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
VARSIZE_ANY_EXHDR(units),
false);
@@ -5253,6 +5405,11 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
if (type == UNKNOWN_FIELD)
type = DecodeSpecial(0, lowunits, &val);
+ if (INTERVAL_NOT_FINITE(interval))
+ {
+ // TODO: copy logic from timestamp_part_common
+ }
+
if (type == UNITS)
{
interval2itm(*interval, tm);
@@ -5955,3 +6112,22 @@ generate_series_timestamptz(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * TODO: possibly we should move these to a place along with other interval_*
+ * functions.
+ */
+
+/* Negates the given interval if it's infinite */
+static void
+neg_interval_infinite(Interval *interval)
+{
+ if (INTERVAL_IS_NOBEGIN(interval))
+ {
+ INTERVAL_NOEND(interval);
+ }
+ else if (INTERVAL_IS_NOEND(interval))
+ {
+ INTERVAL_NOBEGIN(interval);
+ }
+}
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index d155f1b03b..4c281c9112 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -160,6 +160,28 @@ struct pg_itm_in
#define TIMESTAMP_NOT_FINITE(j) (TIMESTAMP_IS_NOBEGIN(j) || TIMESTAMP_IS_NOEND(j))
+// TODO: Should we make custom NOBEGIN and NOEND constants for Interval?
+#define INTERVAL_NOBEGIN(i) \
+ do { \
+ (i->time) = DT_NOBEGIN; \
+ (i->day) = PG_INT32_MIN; \
+ (i->month) = PG_INT32_MIN; \
+ } while (0)
+
+#define INTERVAL_IS_NOBEGIN(i) \
+ ((i->time) == DT_NOBEGIN && (i->day) == PG_INT32_MIN && (i->month) == PG_INT32_MIN)
+
+#define INTERVAL_NOEND(i) \
+ do { \
+ (i->time) = DT_NOEND; \
+ (i->day) = PG_INT32_MAX; \
+ (i->month) = PG_INT32_MAX; \
+ } while (0)
+
+#define INTERVAL_IS_NOEND(i) \
+ ((i->time) == DT_NOEND && (i->day) == PG_INT32_MAX && (i->month) == PG_INT32_MAX)
+
+#define INTERVAL_NOT_FINITE(i) (INTERVAL_IS_NOBEGIN(i) || INTERVAL_IS_NOEND(i))
/*
* Julian date support.
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 579e92e7b3..2a3e9eaf52 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -52,6 +52,19 @@ 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)
+
+--TODO: Add tests for operators etc. by looking at the other tests below
CREATE TABLE INTERVAL_TBL (f1 interval);
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
@@ -63,6 +76,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"
@@ -72,6 +87,10 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
ERROR: invalid input syntax for type interval: "@ 30 eons ago"
LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
^
+INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
+ERROR: invalid input syntax for type interval: "+infinity"
+LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
+ ^
-- Test non-error-throwing API
SELECT pg_input_is_valid('1.5 weeks', 'interval');
pg_input_is_valid
@@ -117,7 +136,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 +153,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 +164,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 +174,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 +193,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 +209,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 +218,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 +254,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 +263,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,7 +273,18 @@ 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 intervals that are large enough to overflow 64 bits in comparisons
CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
@@ -386,12 +434,20 @@ 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;
+ avg
+----------
+ infinity
+(1 row)
+
+select avg(f1) from interval_tbl where isfinite(f1);
avg
-------------------------------------------------
@ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
@@ -820,8 +876,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 +888,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;
@@ -1776,3 +1834,536 @@ SELECT extract(epoch from interval '1000000000 days');
86400000000000.000000
(1 row)
+-- infinite intervals
+SELECT isfinite(interval 'infinity');
+ isfinite
+----------
+ f
+(1 row)
+
+SELECT isfinite(interval '-infinity');
+ isfinite
+----------
+ f
+(1 row)
+
+SELECT date '1995-08-06' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date '1995-08-06' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date '1995-08-06' - interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date '1995-08-06' - interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date 'infinity' + interval '-infinity';
+ERROR: TODO
+SELECT date '-infinity' + interval 'infinity';
+ERROR: TODO
+SELECT date '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date 'infinity' - interval 'infinity';
+ERROR: TODO
+SELECT date 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date '-infinity' - interval '-infinity';
+ERROR: TODO
+SELECT interval 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' + interval '-infinity';
+ERROR: TODO
+SELECT interval '-infinity' + interval 'infinity';
+ERROR: TODO
+SELECT interval '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval 'infinity' + interval '10 days';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' + interval '10 days';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval 'infinity' - interval 'infinity';
+ERROR: TODO
+SELECT interval 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '-infinity';
+ERROR: TODO
+SELECT interval 'infinity' - interval '10 days';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '10 days';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' - interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '1995-08-06 12:30:15' - interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp 'infinity' + interval 'infinity'
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp 'infinity' + interval '-infinity'
+ERROR: TODO
+SELECT timestamp '-infinity' + interval 'infinity'
+ERROR: TODO
+SELECT timestamp '-infinity' + interval '-infinity'
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp 'infinity' - interval 'infinity'
+ERROR: TODO
+SELECT timestamp 'infinity' - interval '-infinity'
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval 'infinity'
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval '-infinity'
+ERROR: TODO
+SELECT timestamptz '1995-08-06 12:30:15' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '1995-08-06 12:30:15' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '1995-08-06 12:30:15' - interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '1995-08-06 12:30:15' - interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz 'infinity' + interval 'infinity'
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz 'infinity' + interval '-infinity'
+ERROR: TODO
+SELECT timestamptz '-infinity' + interval 'infinity'
+ERROR: TODO
+SELECT timestamptz '-infinity' + interval '-infinity'
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz 'infinity' - interval 'infinity'
+ERROR: TODO
+SELECT timestamptz 'infinity' - interval '-infinity'
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval 'infinity'
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval '-infinity'
+ERROR: TODO
+SELECT time '11:27:42' + interval 'infinity';
+ERROR: TODO
+SELECT time '11:27:42' + interval '-infinity';
+ERROR: TODO
+SELECT time '11:27:42' - interval 'infinity';
+ERROR: TODO
+SELECT time '11:27:42' - interval '-infinity';
+ERROR: TODO
+SELECT timetz '11:27:42' + interval 'infinity';
+ERROR: TODO
+SELECT timetz '11:27:42' + interval '-infinity';
+ERROR: TODO
+SELECT timetz '11:27:42' - interval 'infinity';
+ERROR: TODO
+SELECT timetz '11:27:42' - interval '-infinity';
+ERROR: TODO
+SELECT interval 'infinity' < interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' < interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' < interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' < interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' <= interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' <= interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' <= interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' <= interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' > interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' > interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' > interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' > interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' >= interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' >= interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' >= interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' >= interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' = interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' = interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' = interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval '-infinity' = interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval 'infinity' <> interval 'infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT interval 'infinity' <> interval '-infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' <> interval 'infinity';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT interval '-infinity' <> interval '-infinity';
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT -interval 'infinity';
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT -interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' * 2;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' * -2;
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' * 2;
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' * -2;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' / 3;
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' / -3;
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' / 3;
+ interval
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' / -3;
+ ?column?
+----------
+ infinity
+(1 row)
+
+
+SELECT date_bin('infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: TODO
+SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00');
+ERROR: TODO
+SELECT date_part('month', interval 'infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_part('month', interval '-infinity');
+ date_part
+-----------
+
+(1 row)
+
+SELECT date_trunc('hour', interval 'infinity');
+ date_trunc
+------------
+ infinity
+(1 row)
+
+SELECT date_trunc('hour', interval '-infinity');
+ date_trunc
+------------
+ -infinity
+(1 row)
+
+SELECT extract(month from interval 'infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT extract(month from interval '-infinity');
+ extract
+---------
+
+(1 row)
+
+SELECT justify_days(interval 'infinity');
+ justify_interval
+------------------
+ infinity
+(1 row)
+
+SELECT justify_days(interval '-infinity');
+ justify_interval
+------------------
+ -infinity
+(1 row)
+
+SELECT justify_hours(interval 'infinity');
+ justify_interval
+------------------
+ infinity
+(1 row)
+
+SELECT justify_hours(interval '-infinity');
+ justify_interval
+------------------
+ -infinity
+(1 row)
+
+SELECT justify_interval(interval 'infinity');
+ justify_interval
+------------------
+ infinity
+(1 row)
+
+SELECT justify_interval(interval '-infinity');
+ justify_interval
+------------------
+ -infinity
+(1 row)
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 0517b5b82b..85b8007454 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -14,6 +14,10 @@ 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";
+
+--TODO: Add tests for operators etc. by looking at the other tests below
CREATE TABLE INTERVAL_TBL (f1 interval);
@@ -27,10 +31,13 @@ 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');
INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity');
-- Test non-error-throwing API
SELECT pg_input_is_valid('1.5 weeks', 'interval');
@@ -141,6 +148,7 @@ SELECT * FROM INTERVAL_TBL;
-- 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;
@@ -578,3 +586,116 @@ SELECT f1,
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
+
+-- infinite intervals
+SELECT isfinite(interval 'infinity');
+SELECT isfinite(interval '-infinity');
+
+SELECT date '1995-08-06' + interval 'infinity';
+SELECT date '1995-08-06' + interval '-infinity';
+SELECT date '1995-08-06' - interval 'infinity';
+SELECT date '1995-08-06' - interval '-infinity';
+SELECT date 'infinity' + interval 'infinity';
+SELECT date 'infinity' + interval '-infinity';
+SELECT date '-infinity' + interval 'infinity';
+SELECT date '-infinity' + interval '-infinity';
+SELECT date 'infinity' - interval 'infinity';
+SELECT date 'infinity' - interval '-infinity';
+SELECT date '-infinity' - interval 'infinity';
+SELECT date '-infinity' - interval '-infinity';
+SELECT interval 'infinity' + interval 'infinity';
+SELECT interval 'infinity' + interval '-infinity';
+SELECT interval '-infinity' + interval 'infinity';
+SELECT interval '-infinity' + interval '-infinity';
+SELECT interval 'infinity' + interval '10 days';
+SELECT interval '-infinity' + interval '10 days';
+SELECT interval 'infinity' - interval 'infinity';
+SELECT interval 'infinity' - interval '-infinity';
+SELECT interval '-infinity' - interval 'infinity';
+SELECT interval '-infinity' - interval '-infinity';
+SELECT interval 'infinity' - interval '10 days';
+SELECT interval '-infinity' - interval '10 days';
+SELECT timestamp '1995-08-06 12:30:15' + interval 'infinity';
+SELECT timestamp '1995-08-06 12:30:15' + interval '-infinity';
+SELECT timestamp '1995-08-06 12:30:15' - interval 'infinity';
+SELECT timestamp '1995-08-06 12:30:15' - interval '-infinity';
+SELECT timestamp 'infinity' + interval 'infinity'
+SELECT timestamp 'infinity' + interval '-infinity'
+SELECT timestamp '-infinity' + interval 'infinity'
+SELECT timestamp '-infinity' + interval '-infinity'
+SELECT timestamp 'infinity' - interval 'infinity'
+SELECT timestamp 'infinity' - interval '-infinity'
+SELECT timestamp '-infinity' - interval 'infinity'
+SELECT timestamp '-infinity' - interval '-infinity'
+SELECT timestamptz '1995-08-06 12:30:15' + interval 'infinity';
+SELECT timestamptz '1995-08-06 12:30:15' + interval '-infinity';
+SELECT timestamptz '1995-08-06 12:30:15' - interval 'infinity';
+SELECT timestamptz '1995-08-06 12:30:15' - interval '-infinity';
+SELECT timestamptz 'infinity' + interval 'infinity'
+SELECT timestamptz 'infinity' + interval '-infinity'
+SELECT timestamptz '-infinity' + interval 'infinity'
+SELECT timestamptz '-infinity' + interval '-infinity'
+SELECT timestamptz 'infinity' - interval 'infinity'
+SELECT timestamptz 'infinity' - interval '-infinity'
+SELECT timestamptz '-infinity' - interval 'infinity'
+SELECT timestamptz '-infinity' - interval '-infinity'
+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 interval 'infinity' < interval 'infinity';
+SELECT interval 'infinity' < interval '-infinity';
+SELECT interval '-infinity' < interval 'infinity';
+SELECT interval '-infinity' < interval '-infinity';
+SELECT interval 'infinity' <= interval 'infinity';
+SELECT interval 'infinity' <= interval '-infinity';
+SELECT interval '-infinity' <= interval 'infinity';
+SELECT interval '-infinity' <= interval '-infinity';
+SELECT interval 'infinity' > interval 'infinity';
+SELECT interval 'infinity' > interval '-infinity';
+SELECT interval '-infinity' > interval 'infinity';
+SELECT interval '-infinity' > interval '-infinity';
+SELECT interval 'infinity' >= interval 'infinity';
+SELECT interval 'infinity' >= interval '-infinity';
+SELECT interval '-infinity' >= interval 'infinity';
+SELECT interval '-infinity' >= interval '-infinity';
+SELECT interval 'infinity' = interval 'infinity';
+SELECT interval 'infinity' = interval '-infinity';
+SELECT interval '-infinity' = interval 'infinity';
+SELECT interval '-infinity' = interval '-infinity';
+SELECT interval 'infinity' <> interval 'infinity';
+SELECT interval 'infinity' <> interval '-infinity';
+SELECT interval '-infinity' <> interval 'infinity';
+SELECT interval '-infinity' <> interval '-infinity';
+
+SELECT -interval 'infinity';
+SELECT -interval '-infinity';
+SELECT interval 'infinity' * 2;
+SELECT interval 'infinity' * -2;
+SELECT interval '-infinity' * 2;
+SELECT interval '-infinity' * -2;
+SELECT interval 'infinity' / 3;
+SELECT interval 'infinity' / -3;
+SELECT interval '-infinity' / 3;
+SELECT interval '-infinity' / -3;
+
+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 date_part('month', interval 'infinity');
+SELECT date_part('month', interval '-infinity');
+SELECT date_trunc('hour', interval 'infinity');
+SELECT date_trunc('hour', interval '-infinity');
+SELECT extract(month from interval 'infinity');
+SELECT extract(month from interval '-infinity');
+
+SELECT justify_days(interval 'infinity');
+SELECT justify_days(interval '-infinity');
+SELECT justify_hours(interval 'infinity');
+SELECT justify_hours(interval '-infinity');
+SELECT justify_interval(interval 'infinity');
+SELECT justify_interval(interval '-infinity');
--
2.34.1
