On Sat, Jan 7, 2023 at 3:04 PM Joseph Koshakow <kosh...@gmail.com> wrote:
>
> On Thu, Jan 5, 2023 at 11:30 PM jian he <jian.universal...@gmail.com> wrote:
> >
> >
> >
> > On Fri, Jan 6, 2023 at 6:54 AM Joseph Koshakow <kosh...@gmail.com> wrote:
> >>
> >> Looks like some of the error messages have changed and we
> >> have some issues with parsing "+infinity" after rebasing.
> >
> >
> > There is a commit 2ceea5adb02603ef52579b568ca2c5aebed87358
> > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2ceea5adb02603ef52579b568ca2c5aebed87358
> > if you pull this commit then you can do select interval '+infinity', even
> > though I don't know why.
>
> It turns out that I was just misreading the error. The test was
> expecting us to fail on "+infinity" but we succeeded. I just removed
> that test case.
>
> >> pgindent. Looks like some of the error messages have changed
>
> The conditions for checking valid addition/subtraction between infinite
> values were missing some cases which explains the change in error
> messages. I've updated the logic and removed duplicate checks.
>
> I removed the extract/date_part tests since they were duplicated in a
> test above. I also converted the DO command tests to using SQL with
> joins so it more closely matches the existing tests.
>
> I've updated the extract/date_part logic for infinite intervals. Fields
> that are monotonically increasing should return +/-infinity and all
> others should return NULL. For Intervals, the fields are the same as
> timestamps plus the hour and day fields since those don't overflow into
> the next highest field.
>
> I think this patch is just about ready for review, except for the
> following two questions:
> 1. Should finite checks on intervals only look at months or all three
> fields?
> 2. Should we make the error messages for adding/subtracting infinite
> values more generic or leave them as is?
>
> My opinions are
> 1. We should only look at months.
> 2. We should make the errors more generic.
>
> Anyone else have any thoughts?
>
> - Joe
Oops I forgot the actual patch. Please see attached.
From 4ea7c98d47dcbff1313a5013572cc79839e4417e 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.
TODOs
1. Should we just use the months field to test for infinity?
2. Should the error messages for adding different sign infinties be "interval out of range"?
Ashutosh Bapat and Joe Koshakow and Jian He
---
doc/src/sgml/datatype.sgml | 2 +-
doc/src/sgml/func.sgml | 5 +-
src/backend/utils/adt/date.c | 20 ++
src/backend/utils/adt/datetime.c | 14 +-
src/backend/utils/adt/timestamp.c | 448 ++++++++++++++++++++----
src/include/datatype/timestamp.h | 21 ++
src/test/regress/expected/horology.out | 6 +-
src/test/regress/expected/interval.out | 466 +++++++++++++++++++++++--
src/test/regress/sql/horology.sql | 6 +-
src/test/regress/sql/interval.sql | 130 ++++++-
10 files changed, 1002 insertions(+), 116 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index fdffba4442..2bcf959f70 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2316,7 +2316,7 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
</row>
<row>
<entry><literal>infinity</literal></entry>
- <entry><type>date</type>, <type>timestamp</type></entry>
+ <entry><type>date</type>, <type>timestamp</type>, <type>interval</type></entry>
<entry>later than all other time stamps</entry>
</row>
<row>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3bf8d021c3..7ddf76da4a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9369,7 +9369,7 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<returnvalue>boolean</returnvalue>
</para>
<para>
- Test for finite interval (currently always true)
+ Test for finite interval (not +/-infinity)
</para>
<para>
<literal>isfinite(interval '4 hours')</literal>
@@ -10256,7 +10256,8 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
When the input value is +/-Infinity, <function>extract</function> returns
+/-Infinity for monotonically-increasing fields (<literal>epoch</literal>,
<literal>julian</literal>, <literal>year</literal>, <literal>isoyear</literal>,
- <literal>decade</literal>, <literal>century</literal>, and <literal>millennium</literal>).
+ <literal>decade</literal>, <literal>century</literal>, and <literal>millennium</literal>
+ for all types and <literal>hour</literal> and <literal>day</literal> just for <type>interval</type>).
For other fields, NULL is returned. <productname>PostgreSQL</productname>
versions before 9.6 returned zero for all cases of infinite input.
</para>
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 99171d9c92..8334b9053f 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -2067,6 +2067,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("cannot add infinite interval to time")));
+
result = time + span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2085,6 +2090,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("cannot subtract infinite interval from time")));
+
result = time - span->time;
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
@@ -2599,6 +2609,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("cannot add infinite interval to time")));
+
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
result->time = time->time + span->time;
@@ -2621,6 +2636,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("cannot subtract infinite interval from time")));
+
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 d166613895..4192e7a74b 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -70,7 +70,7 @@ static bool DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t,
const char *abbr, pg_tz *tzp,
int *offset, int *isdst);
static pg_tz *FetchDynamicTimeZone(TimeZoneAbbrevTable *tbl, const datetkn *tp,
- DateTimeErrorExtra *extra);
+ DateTimeErrorExtra * extra);
const int day_tab[2][13] =
@@ -978,7 +978,7 @@ ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
int
DecodeDateTime(char **field, int *ftype, int nf,
int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp,
- DateTimeErrorExtra *extra)
+ DateTimeErrorExtra * extra)
{
int fmask = 0,
tmask,
@@ -1928,7 +1928,7 @@ DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t, const char *abbr, pg_tz *tzp,
int
DecodeTimeOnly(char **field, int *ftype, int nf,
int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp,
- DateTimeErrorExtra *extra)
+ DateTimeErrorExtra * extra)
{
int fmask = 0,
tmask,
@@ -3233,7 +3233,7 @@ DecodeTimezone(const char *str, int *tzp)
int
DecodeTimezoneAbbrev(int field, const char *lowtoken,
int *ftype, int *offset, pg_tz **tz,
- DateTimeErrorExtra *extra)
+ DateTimeErrorExtra * extra)
{
const datetkn *tp;
@@ -3635,6 +3635,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;
@@ -4040,7 +4042,7 @@ DecodeUnits(int field, const char *lowtoken, int *val)
* separate SQLSTATE codes, so ...
*/
void
-DateTimeParseError(int dterr, DateTimeErrorExtra *extra,
+DateTimeParseError(int dterr, DateTimeErrorExtra * extra,
const char *str, const char *datatype,
Node *escontext)
{
@@ -4919,7 +4921,7 @@ InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl)
*/
static pg_tz *
FetchDynamicTimeZone(TimeZoneAbbrevTable *tbl, const datetkn *tp,
- DateTimeErrorExtra *extra)
+ DateTimeErrorExtra * extra)
{
DynamicZoneAbbrev *dtza;
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 928c330897..189b7cbfea 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 interval_um_internal(Interval *interval, Interval *result);
/* 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)
{
@@ -2027,6 +2060,8 @@ itm2interval(struct pg_itm *itm, Interval *span)
if (pg_add_s64_overflow(span->time, itm->tm_usec,
&span->time))
return -1;
+ if (INTERVAL_NOT_FINITE(span))
+ return -1;
return 0;
}
@@ -2044,6 +2079,8 @@ itmin2interval(struct pg_itm_in *itm_in, Interval *span)
span->month = (int32) total_months;
span->day = itm_in->tm_mday;
span->time = itm_in->tm_usec;
+ if (INTERVAL_NOT_FINITE(span))
+ return -1;
return 0;
}
@@ -2077,7 +2114,9 @@ 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));
}
@@ -2769,6 +2808,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))
@@ -2844,6 +2886,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,
@@ -2882,6 +2927,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))
@@ -2920,7 +2968,32 @@ 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.
+ */
+ if (INTERVAL_IS_NOBEGIN(span))
+ {
+ if TIMESTAMP_IS_NOEND
+ (timestamp)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
+ else
+ TIMESTAMP_NOBEGIN(result);
+ }
+ else if (INTERVAL_IS_NOEND(span))
+ {
+ if TIMESTAMP_IS_NOBEGIN
+ (timestamp)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
+ else
+ TIMESTAMP_NOEND(result);
+ }
+ else if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
@@ -2999,9 +3072,7 @@ timestamp_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Interval tspan;
- tspan.month = -span->month;
- tspan.day = -span->day;
- tspan.time = -span->time;
+ interval_um_internal(span, &tspan);
return DirectFunctionCall2(timestamp_pl_interval,
TimestampGetDatum(timestamp),
@@ -3026,7 +3097,32 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
TimestampTz result;
int tz;
- 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.
+ */
+ if (INTERVAL_IS_NOBEGIN(span))
+ {
+ if TIMESTAMP_IS_NOEND
+ (timestamp)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
+ else
+ TIMESTAMP_NOBEGIN(result);
+ }
+ else if (INTERVAL_IS_NOEND(span))
+ {
+ if TIMESTAMP_IS_NOBEGIN
+ (timestamp)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
+ else
+ TIMESTAMP_NOEND(result);
+ }
+ else if (TIMESTAMP_NOT_FINITE(timestamp))
result = timestamp;
else
{
@@ -3109,15 +3205,37 @@ timestamptz_mi_interval(PG_FUNCTION_ARGS)
Interval *span = PG_GETARG_INTERVAL_P(1);
Interval tspan;
- tspan.month = -span->month;
- tspan.day = -span->day;
- tspan.time = -span->time;
+ interval_um_internal(span, &tspan);
return DirectFunctionCall2(timestamptz_pl_interval,
TimestampGetDatum(timestamp),
PointerGetDatum(&tspan));
}
+/* Negates the given interval */
+static void
+interval_um_internal(Interval *interval, Interval *result)
+{
+ if (INTERVAL_IS_NOBEGIN(interval))
+ INTERVAL_NOEND(result);
+ else if (INTERVAL_IS_NOEND(interval))
+ INTERVAL_NOBEGIN(result);
+ else if (interval->time == PG_INT64_MIN || interval->day == PG_INT32_MIN || interval->month == PG_INT32_MIN)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ else
+ {
+ result->time = -interval->time;
+ result->day = -interval->day;
+ result->month = -interval->month;
+
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ }
+}
Datum
interval_um(PG_FUNCTION_ARGS)
@@ -3126,23 +3244,7 @@ interval_um(PG_FUNCTION_ARGS)
Interval *result;
result = (Interval *) palloc(sizeof(Interval));
-
- result->time = -interval->time;
- /* overflow check copied from int4um */
- if (interval->time != 0 && SAMESIGN(result->time, interval->time))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
- result->day = -interval->day;
- if (interval->day != 0 && SAMESIGN(result->day, interval->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
- result->month = -interval->month;
- if (interval->month != 0 && SAMESIGN(result->month, interval->month))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ interval_um_internal(interval, result);
PG_RETURN_INTERVAL_P(result);
}
@@ -3186,28 +3288,62 @@ interval_pl(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- result->month = span1->month + span2->month;
- /* overflow check copied from int4pl */
- if (SAMESIGN(span1->month, span2->month) &&
- !SAMESIGN(result->month, span1->month))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ /*
+ * 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))
+ {
+ if INTERVAL_IS_NOEND
+ (span2)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
+ else
+ INTERVAL_NOBEGIN(result);
+ }
+ else if (INTERVAL_IS_NOEND(span1))
+ {
+ if INTERVAL_IS_NOBEGIN
+ (span2)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
+ else
+ INTERVAL_NOEND(result);
+ }
+ else if (INTERVAL_NOT_FINITE(span2))
+ memcpy(result, span2, sizeof(Interval));
+ else
+ {
+ result->month = span1->month + span2->month;
+ /* overflow check copied from int4pl */
+ if (SAMESIGN(span1->month, span2->month) &&
+ !SAMESIGN(result->month, span1->month))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->day = span1->day + span2->day;
- if (SAMESIGN(span1->day, span2->day) &&
- !SAMESIGN(result->day, span1->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->day = span1->day + span2->day;
+ if (SAMESIGN(span1->day, span2->day) &&
+ !SAMESIGN(result->day, span1->day))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->time = span1->time + span2->time;
- if (SAMESIGN(span1->time, span2->time) &&
- !SAMESIGN(result->time, span1->time))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->time = span1->time + span2->time;
+ if (SAMESIGN(span1->time, span2->time) &&
+ !SAMESIGN(result->time, span1->time))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ }
PG_RETURN_INTERVAL_P(result);
}
@@ -3220,28 +3356,64 @@ interval_mi(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
- result->month = span1->month - span2->month;
- /* overflow check copied from int4mi */
- if (!SAMESIGN(span1->month, span2->month) &&
- !SAMESIGN(result->month, span1->month))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ /*
+ * 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))
+ {
+ if INTERVAL_IS_NOBEGIN
+ (span2)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot subtract infinite values with the same signs")));
+ else
+ INTERVAL_NOBEGIN(result);
+ }
+ else if (INTERVAL_IS_NOEND(span1))
+ {
+ if INTERVAL_IS_NOEND
+ (span2)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot subtract infinite values with the same signs")));
+ else
+ INTERVAL_NOEND(result);
+ }
+ else if (INTERVAL_IS_NOBEGIN(span2))
+ INTERVAL_NOEND(result);
+ else if (INTERVAL_IS_NOEND(span2))
+ INTERVAL_NOBEGIN(result);
+ else
+ {
+ result->month = span1->month - span2->month;
+ /* overflow check copied from int4mi */
+ if (!SAMESIGN(span1->month, span2->month) &&
+ !SAMESIGN(result->month, span1->month))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->day = span1->day - span2->day;
- if (!SAMESIGN(span1->day, span2->day) &&
- !SAMESIGN(result->day, span1->day))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->day = span1->day - span2->day;
+ if (!SAMESIGN(span1->day, span2->day) &&
+ !SAMESIGN(result->day, span1->day))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
- result->time = span1->time - span2->time;
- if (!SAMESIGN(span1->time, span2->time) &&
- !SAMESIGN(result->time, span1->time))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("interval out of range")));
+ result->time = span1->time - span2->time;
+ if (!SAMESIGN(span1->time, span2->time) &&
+ !SAMESIGN(result->time, span1->time))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+ }
PG_RETURN_INTERVAL_P(result);
}
@@ -3265,6 +3437,24 @@ interval_mul(PG_FUNCTION_ARGS)
result = (Interval *) palloc(sizeof(Interval));
+ if (isnan(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ /*
+ * Multiplying infinite interval by finite number keeps it infinite but
+ * may change the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ if (factor < 0.0)
+ interval_um_internal(span, result);
+ else
+ memcpy(result, span, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result_double = span->month * factor;
if (isnan(result_double) ||
result_double > INT_MAX || result_double < INT_MIN)
@@ -3325,6 +3515,11 @@ interval_mul(PG_FUNCTION_ARGS)
errmsg("interval out of range")));
result->time = (int64) result_double;
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -3356,6 +3551,29 @@ interval_div(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
+ if (isnan(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ /*
+ * Dividing infinite interval by finite number keeps it infinite but may
+ * change the sign.
+ */
+ if (INTERVAL_NOT_FINITE(span))
+ {
+ if (isinf(factor))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
+ if (factor < 0.0)
+ interval_um_internal(span, result);
+ else
+ memcpy(result, span, sizeof(Interval));
+ PG_RETURN_INTERVAL_P(result);
+ }
+
result->month = (int32) (span->month / factor);
result->day = (int32) (span->day / factor);
@@ -3377,6 +3595,11 @@ interval_div(PG_FUNCTION_ARGS)
result->day += (int32) month_remainder_days;
result->time = rint(span->time / factor + sec_remainder * USECS_PER_SEC);
+ if (INTERVAL_NOT_FINITE(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("interval out of range")));
+
PG_RETURN_INTERVAL_P(result);
}
@@ -3910,6 +4133,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("timestamps cannot be binned into infinite intervals")));
+
if (stride->month != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4365,6 +4593,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);
@@ -5224,6 +5458,60 @@ extract_timestamptz(PG_FUNCTION_ARGS)
}
+/*
+ * NonFiniteIntervalPart
+ *
+ * Used by interval_part when extracting from infinite
+ * interval. Returns +/-Infinity if that is the appropriate result,
+ * otherwise returns zero (which should be taken as meaning to return NULL).
+ *
+ * Errors thrown here for invalid units should exactly match those that
+ * would be thrown in the calling functions, else there will be unexpected
+ * discrepancies between finite- and infinite-input cases.
+ */
+static float8
+NonFiniteIntervalPart(int type, int unit, char *lowunits,
+ bool isNegative, bool isTz)
+{
+ if ((type != UNITS) && (type != RESERV))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unit \"%s\" not recognized for type %s",
+ lowunits, format_type_be(INTERVALOID))));
+
+ switch (unit)
+ {
+ /* Oscillating units */
+ case DTK_MICROSEC:
+ case DTK_MILLISEC:
+ case DTK_SECOND:
+ case DTK_MINUTE:
+ case DTK_MONTH:
+ case DTK_QUARTER:
+ return 0.0;
+
+ /* Monotonically-increasing units */
+ case DTK_HOUR:
+ case DTK_DAY:
+ case DTK_YEAR:
+ case DTK_DECADE:
+ case DTK_CENTURY:
+ case DTK_MILLENNIUM:
+ case DTK_EPOCH:
+ if (isNegative)
+ return -get_float8_infinity();
+ else
+ return get_float8_infinity();
+
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unit \"%s\" not supported for type %s",
+ lowunits, format_type_be(INTERVALOID))));
+ return 0.0; /* keep compiler quiet */
+ }
+}
+
/* interval_part() and extract_interval()
* Extract specified field from interval.
*/
@@ -5247,6 +5535,34 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
if (type == UNKNOWN_FIELD)
type = DecodeSpecial(0, lowunits, &val);
+ if (INTERVAL_NOT_FINITE(interval))
+ {
+ double r = NonFiniteIntervalPart(type, val, lowunits,
+ INTERVAL_IS_NOBEGIN(interval),
+ false);
+
+ if (r)
+ {
+ if (retnumeric)
+ {
+ if (r < 0)
+ return DirectFunctionCall3(numeric_in,
+ CStringGetDatum("-Infinity"),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+ else if (r > 0)
+ return DirectFunctionCall3(numeric_in,
+ CStringGetDatum("Infinity"),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+ }
+ else
+ PG_RETURN_FLOAT8(r);
+ }
+ else
+ PG_RETURN_NULL();
+ }
+
if (type == UNITS)
{
interval2itm(*interval, tm);
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 21a37e21e9..333103efb1 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -160,6 +160,27 @@ struct pg_itm_in
#define TIMESTAMP_NOT_FINITE(j) (TIMESTAMP_IS_NOBEGIN(j) || TIMESTAMP_IS_NOEND(j))
+#define INTERVAL_NOBEGIN(i) \
+ do { \
+ (i->time) = PG_INT64_MIN; \
+ (i->day) = PG_INT32_MIN; \
+ (i->month) = PG_INT32_MIN; \
+ } while (0)
+
+#define INTERVAL_IS_NOBEGIN(i) \
+ ((i->time) == PG_INT64_MIN && (i->day) == PG_INT32_MIN && (i->month) == PG_INT32_MIN)
+
+#define INTERVAL_NOEND(i) \
+ do { \
+ (i->time) = PG_INT64_MAX; \
+ (i->day) = PG_INT32_MAX; \
+ (i->month) = PG_INT32_MAX; \
+ } while (0)
+
+#define INTERVAL_IS_NOEND(i) \
+ ((i->time) == PG_INT64_MAX && (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/horology.out b/src/test/regress/expected/horology.out
index de73683690..10b86963ee 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -939,6 +939,7 @@ SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract"
FROM TIMESTAMP_TBL t, INTERVAL_TBL i
WHERE t.d1 BETWEEN '1990-01-01' AND '2001-01-01'
AND i.f1 BETWEEN '00:00' AND '23:00'
+ AND isfinite(i.f1)
ORDER BY 1,2;
t | i | add | subtract
----------------------------+-----------+----------------------------+----------------------------
@@ -1050,6 +1051,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
-------------+-------------------------------+-------------+-------------
@@ -1157,6 +1159,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
----------------+-------------------------------+----------------+----------------
@@ -1432,6 +1435,7 @@ SELECT f1 AS "timestamp"
SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
+ WHERE isfinite(t.f1)
ORDER BY plus, "timestamp", "interval";
timestamp | interval | plus
------------------------------+-------------------------------+------------------------------
@@ -1599,7 +1603,7 @@ 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)
+ WHERE isfinite(t.f1)
ORDER BY minus, "timestamp", "interval";
timestamp | interval | minus
------------------------------+-------------------------------+------------------------------
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 579e92e7b3..2aa9b59ef2 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,7 +268,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 +429,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 +865,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 +877,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;
@@ -1578,31 +1625,31 @@ LINE 1: select interval '-2147483648 months -2147483648 days -922337...
^
-- 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
@@ -1681,19 +1728,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
@@ -1767,7 +1816,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');
@@ -1776,3 +1827,350 @@ SELECT extract(epoch from interval '1000000000 days');
86400000000000.000000
(1 row)
+-- infinite intervals
+SELECT interval '-2147483648 months -2147483648 days -9223372036854775808 us';
+ERROR: interval out of range
+LINE 1: SELECT interval '-2147483648 months -2147483648 days -922337...
+ ^
+SELECT interval '2147483647 months 2147483647 days 9223372036854775807 us';
+ERROR: interval out of range
+LINE 1: SELECT interval '2147483647 months 2147483647 days 922337203...
+ ^
+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)
+
+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: cannot add infinite values with opposite signs
+SELECT date '-infinity' + interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT date '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date 'infinity' - interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT date 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT date '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT date '-infinity' - interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT interval 'infinity' + interval 'infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval 'infinity' + interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT interval '-infinity' + interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+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 '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 'infinity' - interval 'infinity';
+ERROR: cannot subtract infinite values with the same signs
+SELECT interval 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '-infinity';
+ERROR: cannot subtract infinite values with the same signs
+SELECT interval 'infinity' - interval '10 days';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT interval '-infinity' - interval '10 days';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+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 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: cannot add infinite values with opposite signs
+SELECT timestamp '-infinity' + interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamp '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp 'infinity' - interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamp 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamp '-infinity' - interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+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: cannot add infinite values with opposite signs
+SELECT timestamptz '-infinity' + interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamptz '-infinity' + interval '-infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz 'infinity' - interval 'infinity';
+ERROR: cannot add infinite values with opposite signs
+SELECT timestamptz 'infinity' - interval '-infinity';
+ ?column?
+----------
+ infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval 'infinity';
+ ?column?
+-----------
+ -infinity
+(1 row)
+
+SELECT timestamptz '-infinity' - interval '-infinity';
+ERROR: cannot add infinite values with opposite signs
+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' / '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)
+
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 2724a2bbc7..2494d852a6 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -173,14 +173,17 @@ SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract"
FROM TIMESTAMP_TBL t, INTERVAL_TBL i
WHERE t.d1 BETWEEN '1990-01-01' AND '2001-01-01'
AND i.f1 BETWEEN '00:00' AND '23:00'
+ AND 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 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
@@ -253,11 +256,12 @@ SELECT f1 AS "timestamp"
SELECT d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus
FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
+ WHERE isfinite(t.f1)
ORDER BY plus, "timestamp", "interval";
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)
+ WHERE isfinite(t.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 0517b5b82b..742095382a 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');
@@ -140,7 +144,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;
@@ -509,13 +513,13 @@ select interval '-2147483648 months -2147483648 days -9223372036854775808 micros
-- 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;
@@ -578,3 +582,119 @@ SELECT f1,
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
+
+-- 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;
+
+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 '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 '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 '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 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 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' / '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);
--
2.34.1
