On Sat, Jan 7, 2023 at 3:05 PM Joseph Koshakow <kosh...@gmail.com> wrote: > > On Sat, Jan 7, 2023 at 3:04 PM Joseph Koshakow <kosh...@gmail.com> wrote: > > > > 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?
Here's a patch with the more generic error messages. - Joe
From 6ed93bc20db57cea2d692e9288d97b66f4a526dc 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? 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..d08696de97 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("interval out of range"))); + 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("interval out of range"))); + 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("interval out of range"))); + 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("interval out of range"))); + 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("interval out of range"))); + 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("interval out of range"))); + 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("interval out of range"))); + 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("interval out of range"))); + 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..fdad2def16 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: interval out of range +SELECT date '-infinity' + interval 'infinity'; +ERROR: interval out of range +SELECT date '-infinity' + interval '-infinity'; + ?column? +----------- + -infinity +(1 row) + +SELECT date 'infinity' - interval 'infinity'; +ERROR: interval out of range +SELECT date 'infinity' - interval '-infinity'; + ?column? +---------- + infinity +(1 row) + +SELECT date '-infinity' - interval 'infinity'; + ?column? +----------- + -infinity +(1 row) + +SELECT date '-infinity' - interval '-infinity'; +ERROR: interval out of range +SELECT interval 'infinity' + interval 'infinity'; + ?column? +---------- + infinity +(1 row) + +SELECT interval 'infinity' + interval '-infinity'; +ERROR: interval out of range +SELECT interval '-infinity' + interval 'infinity'; +ERROR: interval out of range +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: interval out of range +SELECT interval 'infinity' - interval '-infinity'; + ?column? +---------- + infinity +(1 row) + +SELECT interval '-infinity' - interval 'infinity'; + ?column? +----------- + -infinity +(1 row) + +SELECT interval '-infinity' - interval '-infinity'; +ERROR: interval out of range +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: interval out of range +SELECT timestamp '-infinity' + interval 'infinity'; +ERROR: interval out of range +SELECT timestamp '-infinity' + interval '-infinity'; + ?column? +----------- + -infinity +(1 row) + +SELECT timestamp 'infinity' - interval 'infinity'; +ERROR: interval out of range +SELECT timestamp 'infinity' - interval '-infinity'; + ?column? +---------- + infinity +(1 row) + +SELECT timestamp '-infinity' - interval 'infinity'; + ?column? +----------- + -infinity +(1 row) + +SELECT timestamp '-infinity' - interval '-infinity'; +ERROR: interval out of range +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: interval out of range +SELECT timestamptz '-infinity' + interval 'infinity'; +ERROR: interval out of range +SELECT timestamptz '-infinity' + interval '-infinity'; + ?column? +----------- + -infinity +(1 row) + +SELECT timestamptz 'infinity' - interval 'infinity'; +ERROR: interval out of range +SELECT timestamptz 'infinity' - interval '-infinity'; + ?column? +---------- + infinity +(1 row) + +SELECT timestamptz '-infinity' - interval 'infinity'; + ?column? +----------- + -infinity +(1 row) + +SELECT timestamptz '-infinity' - interval '-infinity'; +ERROR: interval out of range +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