Ok, I've updated the patch to handle every function that inputs or outputs intervals, as well as added some tests. In the process I noticed that some of the existing date/timestamp/timestamptz don't handle infinite values properly. For example, postgres=# SELECT age('infinity'::timestamp); age -------------------------------------------------- -292253 years -11 mons -26 days -04:00:54.775807 (1 row)
It might be worth going through all those functions separately and making sure they are correct. I also added some overflow handling to make_interval. I also added handling of infinite timestamp subtraction. At this point the patch is ready for review again except for the one outstanding question of: Should finite checks on intervals only look at months or all three fields? - Joe
From 23868228ad2c0be57408b38db76bced85ab83cb1 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 | 32 + src/backend/utils/adt/datetime.c | 39 +- src/backend/utils/adt/formatting.c | 2 +- src/backend/utils/adt/selfuncs.c | 12 +- src/backend/utils/adt/timestamp.c | 695 ++++++++++++++++++---- src/include/common/int.h | 18 + src/include/datatype/timestamp.h | 21 + src/include/utils/timestamp.h | 3 + src/test/regress/expected/horology.out | 6 +- src/test/regress/expected/interval.out | 503 ++++++++++++++-- src/test/regress/expected/timestamp.out | 62 ++ src/test/regress/expected/timestamptz.out | 62 ++ src/test/regress/sql/horology.sql | 6 +- src/test/regress/sql/interval.sql | 149 ++++- src/test/regress/sql/timestamp.sql | 19 + src/test/regress/sql/timestamptz.sql | 18 + 18 files changed, 1461 insertions(+), 193 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 467b49b199..d782d23574 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 b8dac9ef46..36b31f7163 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -9393,7 +9393,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> @@ -10280,7 +10280,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..dc271f663c 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -2023,6 +2023,11 @@ interval_time(PG_FUNCTION_ARGS) TimeADT result; int64 days; + if (INTERVAL_NOT_FINITE(span)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("time out of range"))); + result = span->time; if (result >= USECS_PER_DAY) { @@ -2067,6 +2072,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 +2095,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 +2614,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 +2641,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; @@ -3140,6 +3165,13 @@ timetz_izone(PG_FUNCTION_ARGS) TimeTzADT *result; int tz; + if (INTERVAL_NOT_FINITE(zone)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("interval time zone \"%s\" must be finite", + DatumGetCString(DirectFunctionCall1(interval_out, + PointerGetDatum(zone)))))); + if (zone->month != 0 || zone->day != 0) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index d166613895..608d562b48 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -50,7 +50,6 @@ static int DecodeDate(char *str, int fmask, int *tmask, bool *is2digits, struct pg_tm *tm); static char *AppendSeconds(char *cp, int sec, fsec_t fsec, int precision, bool fillzeros); -static bool int64_multiply_add(int64 val, int64 multiplier, int64 *sum); static bool AdjustFractMicroseconds(double frac, int64 scale, struct pg_itm_in *itm_in); static bool AdjustFractDays(double frac, int scale, @@ -70,7 +69,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] = @@ -514,22 +513,6 @@ AppendTimestampSeconds(char *cp, struct pg_tm *tm, fsec_t fsec) return AppendSeconds(cp, tm->tm_sec, fsec, MAX_TIMESTAMP_PRECISION, true); } - -/* - * Add val * multiplier to *sum. - * Returns true if successful, false on overflow. - */ -static bool -int64_multiply_add(int64 val, int64 multiplier, int64 *sum) -{ - int64 product; - - if (pg_mul_s64_overflow(val, multiplier, &product) || - pg_add_s64_overflow(*sum, product, sum)) - return false; - return true; -} - /* * Multiply frac by scale (to produce microseconds) and add to itm_in->tm_usec. * Returns true if successful, false if itm_in overflows. @@ -620,7 +603,7 @@ AdjustMicroseconds(int64 val, double fval, int64 scale, struct pg_itm_in *itm_in) { /* Handle the integer part */ - if (!int64_multiply_add(val, scale, &itm_in->tm_usec)) + if (pg_mul_add_s64_overflow(val, scale, &itm_in->tm_usec)) return false; /* Handle the float part */ return AdjustFractMicroseconds(fval, scale, itm_in); @@ -978,7 +961,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 +1911,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, @@ -2853,9 +2836,9 @@ DecodeTimeForInterval(char *str, int fmask, int range, return dterr; itm_in->tm_usec = itm.tm_usec; - if (!int64_multiply_add(itm.tm_hour, USECS_PER_HOUR, &itm_in->tm_usec) || - !int64_multiply_add(itm.tm_min, USECS_PER_MINUTE, &itm_in->tm_usec) || - !int64_multiply_add(itm.tm_sec, USECS_PER_SEC, &itm_in->tm_usec)) + if (pg_mul_add_s64_overflow(itm.tm_hour, USECS_PER_HOUR, &itm_in->tm_usec) || + pg_mul_add_s64_overflow(itm.tm_min, USECS_PER_MINUTE, &itm_in->tm_usec) || + pg_mul_add_s64_overflow(itm.tm_sec, USECS_PER_SEC, &itm_in->tm_usec)) return DTERR_FIELD_OVERFLOW; return 0; @@ -3233,7 +3216,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 +3618,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 +4025,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 +4904,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/formatting.c b/src/backend/utils/adt/formatting.c index a4b524ea3a..d9dca76f96 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -4145,7 +4145,7 @@ interval_to_char(PG_FUNCTION_ARGS) struct pg_itm tt, *itm = &tt; - if (VARSIZE_ANY_EXHDR(fmt) <= 0) + if (VARSIZE_ANY_EXHDR(fmt) <= 0 || INTERVAL_NOT_FINITE(it)) PG_RETURN_NULL(); ZERO_tmtc(&tmtc); diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 57de51f0db..ab80812ae5 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -4795,17 +4795,7 @@ convert_timevalue_to_scalar(Datum value, Oid typid, bool *failure) case DATEOID: return date2timestamp_no_overflow(DatumGetDateADT(value)); case INTERVALOID: - { - Interval *interval = DatumGetIntervalP(value); - - /* - * Convert the month part of Interval to days using assumed - * average month length of 365.25/12.0 days. Not too - * accurate, but plenty good enough for our purposes. - */ - return interval->time + interval->day * (double) USECS_PER_DAY + - interval->month * ((DAYS_PER_YEAR / (double) MONTHS_PER_YEAR) * USECS_PER_DAY); - } + return interval2timestamp_no_overflow(DatumGetIntervalP(value)); case TIMEOID: return DatumGetTimeADT(value); case TIMETZOID: diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index 928c330897..6d406d5763 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -18,6 +18,7 @@ #include <ctype.h> #include <math.h> #include <limits.h> +#include <float.h> #include <sys/time.h> #include "access/xact.h" @@ -79,6 +80,7 @@ static bool AdjustIntervalForTypmod(Interval *interval, int32 typmod, static TimestampTz timestamp2timestamptz(Timestamp timestamp); static Timestamp timestamptz2timestamp(TimestampTz timestamp); +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. @@ -1520,13 +1542,35 @@ make_interval(PG_FUNCTION_ARGS) errmsg("interval out of range"))); result = (Interval *) palloc(sizeof(Interval)); - result->month = years * MONTHS_PER_YEAR + months; - result->day = weeks * 7 + days; + result->month = months; + if (pg_mul_add_s32_overflow(years, MONTHS_PER_YEAR, &result->month)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("interval out of range"))); + + result->day = days; + if (pg_mul_add_s32_overflow(weeks, 7, &result->day)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("interval out of range"))); secs = rint(secs * USECS_PER_SEC); - result->time = hours * ((int64) SECS_PER_HOUR * USECS_PER_SEC) + - mins * ((int64) SECS_PER_MINUTE * USECS_PER_SEC) + - (int64) secs; + + result->time = secs; + if (pg_mul_add_s64_overflow(mins, ((int64) SECS_PER_MINUTE * USECS_PER_SEC), &result->time)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("interval out of range"))); + if (pg_mul_add_s64_overflow(hours, ((int64) SECS_PER_HOUR * USECS_PER_SEC), &result->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); } @@ -1545,6 +1589,17 @@ EncodeSpecialTimestamp(Timestamp dt, char *str) elog(ERROR, "invalid argument for EncodeSpecialTimestamp"); } +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 +2082,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 +2101,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 +2136,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)); } @@ -2208,6 +2269,29 @@ timestamp_fastcmp(Datum x, Datum y, SortSupport ssup) } #endif +double +interval2timestamp_no_overflow(Interval *interval) +{ + double result; + + if (INTERVAL_IS_NOBEGIN(interval)) + result = -DBL_MAX; + else if (INTERVAL_IS_NOEND(interval)) + result = DBL_MAX; + else + { + /* + * Convert the month part of Interval to days using assumed average + * month length of 365.25/12.0 days. Not too accurate, but plenty + * good enough for our purposes. + */ + return interval->time + interval->day * (double) USECS_PER_DAY + + interval->month * ((DAYS_PER_YEAR / (double) MONTHS_PER_YEAR) * USECS_PER_DAY); + } + + return result; +} + Datum timestamp_sortsupport(PG_FUNCTION_ARGS) { @@ -2703,43 +2787,70 @@ timestamp_mi(PG_FUNCTION_ARGS) result = (Interval *) palloc(sizeof(Interval)); - if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2)) - ereport(ERROR, - (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), - errmsg("cannot subtract infinite timestamps"))); - - result->time = dt1 - dt2; + /* + * Subtracting two infinite timestamps with different signs results in an + * infinite interval with the same sign as the left operand. Subtracting + * two infinte timestamps with the same sign results in an error. + */ + if (TIMESTAMP_IS_NOBEGIN(dt1)) + { + if TIMESTAMP_IS_NOBEGIN + (dt2) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + else + INTERVAL_NOBEGIN(result); + } + else if (TIMESTAMP_IS_NOEND(dt1)) + { + if TIMESTAMP_IS_NOEND + (dt2) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + else + INTERVAL_NOEND(result); + } + else if (TIMESTAMP_IS_NOBEGIN(dt2)) + INTERVAL_NOEND(result); + else if (TIMESTAMP_IS_NOEND(dt2)) + INTERVAL_NOBEGIN(result); + else + { + result->time = dt1 - dt2; - result->month = 0; - result->day = 0; + result->month = 0; + result->day = 0; - /*---------- - * This is wrong, but removing it breaks a lot of regression tests. - * For example: - * - * test=> SET timezone = 'EST5EDT'; - * test=> SELECT - * test-> ('2005-10-30 13:22:00-05'::timestamptz - - * test(> '2005-10-29 13:22:00-04'::timestamptz); - * ?column? - * ---------------- - * 1 day 01:00:00 - * (1 row) - * - * so adding that to the first timestamp gets: - * - * test=> SELECT - * test-> ('2005-10-29 13:22:00-04'::timestamptz + - * test(> ('2005-10-30 13:22:00-05'::timestamptz - - * test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST'; - * timezone - * -------------------- - * 2005-10-30 14:22:00 - * (1 row) - *---------- - */ - result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours, - IntervalPGetDatum(result))); + /*---------- + * This is wrong, but removing it breaks a lot of regression tests. + * For example: + * + * test=> SET timezone = 'EST5EDT'; + * test=> SELECT + * test-> ('2005-10-30 13:22:00-05'::timestamptz - + * test(> '2005-10-29 13:22:00-04'::timestamptz); + * ?column? + * ---------------- + * 1 day 01:00:00 + * (1 row) + * + * so adding that to the first timestamp gets: + * + * test=> SELECT + * test-> ('2005-10-29 13:22:00-04'::timestamptz + + * test(> ('2005-10-30 13:22:00-05'::timestamptz - + * test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST'; + * timezone + * -------------------- + * 2005-10-30 14:22:00 + * (1 row) + *---------- + */ + result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours, + IntervalPGetDatum(result))); + } PG_RETURN_INTERVAL_P(result); } @@ -2769,6 +2880,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 +2958,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 +2999,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 +3040,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 +3144,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 +3169,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 +3277,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 +3316,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 +3360,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 +3428,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 +3509,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 +3587,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 +3623,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 +3667,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); } @@ -3664,8 +3959,37 @@ timestamp_age(PG_FUNCTION_ARGS) result = (Interval *) palloc(sizeof(Interval)); - if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 && - timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0) + /* + * Subtracting two infinite timestamps with different signs results in an + * infinite interval with the same sign as the left operand. Subtracting + * two infinte timestamps with the same sign results in an error. + */ + if (TIMESTAMP_IS_NOBEGIN(dt1)) + { + if TIMESTAMP_IS_NOBEGIN + (dt2) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + else + INTERVAL_NOBEGIN(result); + } + else if (TIMESTAMP_IS_NOEND(dt1)) + { + if TIMESTAMP_IS_NOEND + (dt2) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + else + INTERVAL_NOEND(result); + } + else if (TIMESTAMP_IS_NOBEGIN(dt2)) + INTERVAL_NOEND(result); + else if (TIMESTAMP_IS_NOEND(dt2)) + INTERVAL_NOBEGIN(result); + else if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 && + timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0) { /* form the symbolic difference */ tm->tm_usec = fsec1 - fsec2; @@ -3784,8 +4108,37 @@ timestamptz_age(PG_FUNCTION_ARGS) result = (Interval *) palloc(sizeof(Interval)); - if (timestamp2tm(dt1, &tz1, tm1, &fsec1, NULL, NULL) == 0 && - timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0) + /* + * Subtracting two infinite timestamps with different signs results in an + * infinite interval with the same sign as the left operand. Subtracting + * two infinte timestamps with the same sign results in an error. + */ + if (TIMESTAMP_IS_NOBEGIN(dt1)) + { + if TIMESTAMP_IS_NOBEGIN + (dt2) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + else + INTERVAL_NOBEGIN(result); + } + else if (TIMESTAMP_IS_NOEND(dt1)) + { + if TIMESTAMP_IS_NOEND + (dt2) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("timestamp out of range"))); + else + INTERVAL_NOEND(result); + } + else if (TIMESTAMP_IS_NOBEGIN(dt2)) + INTERVAL_NOEND(result); + else if (TIMESTAMP_IS_NOEND(dt2)) + INTERVAL_NOBEGIN(result); + else if (timestamp2tm(dt1, &tz1, tm1, &fsec1, NULL, NULL) == 0 && + timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0) { /* form the symbolic difference */ tm->tm_usec = fsec1 - fsec2; @@ -3910,6 +4263,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), @@ -4093,6 +4451,11 @@ timestamptz_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 +4728,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 +5593,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 +5670,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); @@ -5516,6 +5967,13 @@ timestamp_izone(PG_FUNCTION_ARGS) if (TIMESTAMP_NOT_FINITE(timestamp)) PG_RETURN_TIMESTAMPTZ(timestamp); + if (INTERVAL_NOT_FINITE(zone)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("interval time zone \"%s\" must be finite", + DatumGetCString(DirectFunctionCall1(interval_out, + PointerGetDatum(zone)))))); + if (zone->month != 0 || zone->day != 0) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), @@ -5773,6 +6231,13 @@ timestamptz_izone(PG_FUNCTION_ARGS) if (TIMESTAMP_NOT_FINITE(timestamp)) PG_RETURN_TIMESTAMP(timestamp); + if (INTERVAL_NOT_FINITE(zone)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("interval time zone \"%s\" must be finite", + DatumGetCString(DirectFunctionCall1(interval_out, + PointerGetDatum(zone)))))); + if (zone->month != 0 || zone->day != 0) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), @@ -5839,6 +6304,11 @@ generate_series_timestamp(PG_FUNCTION_ARGS) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("step size cannot equal zero"))); + if (INTERVAL_NOT_FINITE((&fctx->step))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("step size cannot be infinite"))); + funcctx->user_fctx = fctx; MemoryContextSwitchTo(oldcontext); } @@ -5918,6 +6388,11 @@ generate_series_timestamptz(PG_FUNCTION_ARGS) (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("step size cannot equal zero"))); + if (INTERVAL_NOT_FINITE((&fctx->step))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("step size cannot be infinite"))); + funcctx->user_fctx = fctx; MemoryContextSwitchTo(oldcontext); } diff --git a/src/include/common/int.h b/src/include/common/int.h index 450800894e..53cc4ce4ce 100644 --- a/src/include/common/int.h +++ b/src/include/common/int.h @@ -154,6 +154,15 @@ pg_mul_s32_overflow(int32 a, int32 b, int32 *result) #endif } +static inline bool +pg_mul_add_s32_overflow(int32 val, int32 multiplier, int32 *sum) +{ + int32 product; + + return pg_mul_s32_overflow(val, multiplier, &product) || + pg_add_s32_overflow(*sum, product, sum); +} + /* * INT64 */ @@ -254,6 +263,15 @@ pg_mul_s64_overflow(int64 a, int64 b, int64 *result) #endif } +static inline bool +pg_mul_add_s64_overflow(int64 val, int64 multiplier, int64 *sum) +{ + int64 product; + + return pg_mul_s64_overflow(val, multiplier, &product) || + pg_add_s64_overflow(*sum, product, sum); +} + /*------------------------------------------------------------------------ * Overflow routines for unsigned integers *------------------------------------------------------------------------ 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/include/utils/timestamp.h b/src/include/utils/timestamp.h index 42f802bb9d..20eb024dc8 100644 --- a/src/include/utils/timestamp.h +++ b/src/include/utils/timestamp.h @@ -112,6 +112,7 @@ extern int timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm, fsec_t *fsec, const char **tzn, pg_tz *attimezone); extern void dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec); +extern void EncodeSpecialInterval(Interval *interval, char *str); extern void interval2itm(Interval span, struct pg_itm *itm); extern int itm2interval(struct pg_itm *itm, Interval *span); extern int itmin2interval(struct pg_itm_in *itm_in, Interval *span); @@ -121,6 +122,8 @@ extern void GetEpochTime(struct pg_tm *tm); extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2); +extern double interval2timestamp_no_overflow(Interval *interval); + /* timestamp comparison works for timestamptz also */ #define timestamptz_cmp_internal(dt1,dt2) timestamp_cmp_internal(dt1, dt2) 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..686394c25b 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; @@ -1576,33 +1623,38 @@ select interval '-2147483648 months -2147483648 days -9223372036854775808 micros ERROR: interval field value out of range: "-2147483648 months -2147483648 days -9223372036854775808 microseconds ago" LINE 1: select interval '-2147483648 months -2147483648 days -922337... ^ +-- overflowing using make_interval +select make_interval(1, 2147483647, 2147483647, 1, 1, 1, 9223372036854.7759); +ERROR: interval out of range +select make_interval(-1, -2147483648, -2147483648, -1, -1, -1, -9223372036854.7759); +ERROR: interval out of range -- test that INT_MIN number is formatted properly SET IntervalStyle to postgres; -select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; +select interval '-2147483647 months -2147483648 days -9223372036854775808 us'; interval -------------------------------------------------------------------- - -178956970 years -8 mons -2147483648 days -2562047788:00:54.775808 + -178956970 years -7 mons -2147483648 days -2562047788:00:54.775808 (1 row) SET IntervalStyle to sql_standard; -select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; +select interval '-2147483647 months -2147483648 days -9223372036854775808 us'; interval --------------------------------------------------- - -178956970-8 -2147483648 -2562047788:00:54.775808 + -178956970-7 -2147483648 -2562047788:00:54.775808 (1 row) SET IntervalStyle to iso_8601; -select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; +select interval '-2147483647 months -2147483648 days -9223372036854775808 us'; interval ----------------------------------------------------- - P-178956970Y-8M-2147483648DT-2562047788H-54.775808S + P-178956970Y-7M-2147483648DT-2562047788H-54.775808S (1 row) SET IntervalStyle to postgres_verbose; -select interval '-2147483648 months -2147483648 days -9223372036854775808 us'; +select interval '-2147483647 months -2147483648 days -9223372036854775808 us'; interval ------------------------------------------------------------------------------ - @ 178956970 years 8 mons 2147483648 days 2562047788 hours 54.775808 secs ago + @ 178956970 years 7 mons 2147483648 days 2562047788 hours 54.775808 secs ago (1 row) -- check that '30 days' equals '1 month' according to the hash function @@ -1681,19 +1733,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 +1821,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 +1832,382 @@ 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) + +SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamp); +ERROR: interval time zone "infinity" must be finite +SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamp); +ERROR: interval time zone "-infinity" must be finite +SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamptz); +ERROR: interval time zone "infinity" must be finite +SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamptz); +ERROR: interval time zone "-infinity" must be finite +SELECT timezone('infinity'::interval, '12:12:12'::time); +ERROR: interval time zone "infinity" must be finite +SELECT timezone('-infinity'::interval, '12:12:12'::time); +ERROR: interval time zone "-infinity" must be finite +SELECT timezone('infinity'::interval, '12:12:12'::timetz); +ERROR: interval time zone "infinity" must be finite +SELECT timezone('-infinity'::interval, '12:12:12'::timetz); +ERROR: interval time zone "-infinity" must be finite +SELECT 'infinity'::interval::time; +ERROR: time out of range +SELECT '-infinity'::interval::time; +ERROR: time out of range +SELECT to_char('infinity'::interval, 'YYYY'); + to_char +--------- + +(1 row) + +SELECT to_char('-infinity'::interval, 'YYYY'); + to_char +--------- + +(1 row) + diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index edc6912e7a..d85359b28f 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -2116,3 +2116,65 @@ select * from generate_series('2020-01-01 00:00'::timestamp, '2020-01-02 03:00'::timestamp, '0 hour'::interval); ERROR: step size cannot equal zero +select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval 'infinity'); +ERROR: step size cannot be infinite +select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval '-infinity'); +ERROR: step size cannot be infinite +-- test arithmetic with infinite timestamps +select timestamp 'infinity' - timestamp 'infinity'; +ERROR: timestamp out of range +select timestamp 'infinity' - timestamp '-infinity'; + ?column? +---------- + infinity +(1 row) + +select timestamp '-infinity' - timestamp 'infinity'; + ?column? +----------- + -infinity +(1 row) + +select timestamp '-infinity' - timestamp '-infinity'; +ERROR: timestamp out of range +select timestamp 'infinity' - timestamp '1995-08-06 12:12:12'; + ?column? +---------- + infinity +(1 row) + +select timestamp '-infinity' - timestamp '1995-08-06 12:12:12'; + ?column? +----------- + -infinity +(1 row) + +-- test age() with infinite timestamps +select age(timestamp 'infinity'); + age +----------- + -infinity +(1 row) + +select age(timestamp '-infinity'); + age +---------- + infinity +(1 row) + +select age(timestamp 'infinity', timestamp 'infinity'); +ERROR: timestamp out of range +select age(timestamp 'infinity', timestamp '-infinity'); + age +---------- + infinity +(1 row) + +select age(timestamp '-infinity', timestamp 'infinity'); + age +----------- + -infinity +(1 row) + +select age(timestamp '-infinity', timestamp '-infinity'); +ERROR: timestamp out of range diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index 00379fd0fd..97230af014 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -2459,6 +2459,10 @@ select * from generate_series('2020-01-01 00:00'::timestamptz, '2020-01-02 03:00'::timestamptz, '0 hour'::interval); ERROR: step size cannot equal zero +select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval 'infinity'); +ERROR: step size cannot be infinite +select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval '-infinity'); +ERROR: step size cannot be infinite -- -- Test behavior with a dynamic (time-varying) timezone abbreviation. -- These tests rely on the knowledge that MSK (Europe/Moscow standard time) @@ -3091,3 +3095,61 @@ select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; Tue Jan 17 16:00:00 2017 PST (1 row) +-- test arithmetic with infinite timestamps +SELECT timestamptz 'infinity' - timestamptz 'infinity'; +ERROR: timestamp out of range +SELECT timestamptz 'infinity' - timestamptz '-infinity'; + ?column? +---------- + infinity +(1 row) + +SELECT timestamptz '-infinity' - timestamptz 'infinity'; + ?column? +----------- + -infinity +(1 row) + +SELECT timestamptz '-infinity' - timestamptz '-infinity'; +ERROR: timestamp out of range +SELECT timestamptz 'infinity' - timestamptz '1995-08-06 12:12:12'; + ?column? +---------- + infinity +(1 row) + +SELECT timestamptz '-infinity' - timestamptz '1995-08-06 12:12:12'; + ?column? +----------- + -infinity +(1 row) + +-- test age() with infinite timestamps +SELECT age(timestamptz 'infinity'); + age +----------- + -infinity +(1 row) + +SELECT age(timestamptz '-infinity'); + age +---------- + infinity +(1 row) + +SELECT age(timestamptz 'infinity', timestamptz 'infinity'); +ERROR: timestamp out of range +SELECT age(timestamptz 'infinity', timestamptz '-infinity'); + age +---------- + infinity +(1 row) + +SELECT age(timestamptz '-infinity', timestamptz 'infinity'); + age +----------- + -infinity +(1 row) + +SELECT age(timestamptz '-infinity', timestamptz '-infinity'); +ERROR: timestamp out of range 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..ed563666b0 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; @@ -507,15 +511,19 @@ select interval '-2147483648 days ago'; select interval '-9223372036854775808 microseconds ago'; select interval '-2147483648 months -2147483648 days -9223372036854775808 microseconds ago'; +-- overflowing using make_interval +select make_interval(1, 2147483647, 2147483647, 1, 1, 1, 9223372036854.7759); +select make_interval(-1, -2147483648, -2147483648, -1, -1, -1, -9223372036854.7759); + -- 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 +586,134 @@ 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); + +SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamp); +SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamp); +SELECT timezone('infinity'::interval, '1995-08-06 12:12:12'::timestamptz); +SELECT timezone('-infinity'::interval, '1995-08-06 12:12:12'::timestamptz); +SELECT timezone('infinity'::interval, '12:12:12'::time); +SELECT timezone('-infinity'::interval, '12:12:12'::time); +SELECT timezone('infinity'::interval, '12:12:12'::timetz); +SELECT timezone('-infinity'::interval, '12:12:12'::timetz); + +SELECT 'infinity'::interval::time; +SELECT '-infinity'::interval::time; + +SELECT to_char('infinity'::interval, 'YYYY'); +SELECT to_char('-infinity'::interval, 'YYYY'); diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index 1d580f77f1..e0fdfd7d74 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -393,3 +393,22 @@ select generate_series('2022-01-01 00:00'::timestamp, select * from generate_series('2020-01-01 00:00'::timestamp, '2020-01-02 03:00'::timestamp, '0 hour'::interval); +select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval 'infinity'); +select generate_series(timestamp '1995-08-06 12:12:12', timestamp '1996-08-06 12:12:12', interval '-infinity'); + + +-- test arithmetic with infinite timestamps +select timestamp 'infinity' - timestamp 'infinity'; +select timestamp 'infinity' - timestamp '-infinity'; +select timestamp '-infinity' - timestamp 'infinity'; +select timestamp '-infinity' - timestamp '-infinity'; +select timestamp 'infinity' - timestamp '1995-08-06 12:12:12'; +select timestamp '-infinity' - timestamp '1995-08-06 12:12:12'; + +-- test age() with infinite timestamps +select age(timestamp 'infinity'); +select age(timestamp '-infinity'); +select age(timestamp 'infinity', timestamp 'infinity'); +select age(timestamp 'infinity', timestamp '-infinity'); +select age(timestamp '-infinity', timestamp 'infinity'); +select age(timestamp '-infinity', timestamp '-infinity'); diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index 4905dd0831..598c07f064 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -454,6 +454,8 @@ select generate_series('2022-01-01 00:00'::timestamptz, select * from generate_series('2020-01-01 00:00'::timestamptz, '2020-01-02 03:00'::timestamptz, '0 hour'::interval); +select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval 'infinity'); +select generate_series(timestamptz '1995-08-06 12:12:12', timestamptz '1996-08-06 12:12:12', interval '-infinity'); -- -- Test behavior with a dynamic (time-varying) timezone abbreviation. @@ -596,3 +598,19 @@ insert into tmptz values ('2017-01-18 00:00+00'); explain (costs off) select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; + +-- test arithmetic with infinite timestamps +SELECT timestamptz 'infinity' - timestamptz 'infinity'; +SELECT timestamptz 'infinity' - timestamptz '-infinity'; +SELECT timestamptz '-infinity' - timestamptz 'infinity'; +SELECT timestamptz '-infinity' - timestamptz '-infinity'; +SELECT timestamptz 'infinity' - timestamptz '1995-08-06 12:12:12'; +SELECT timestamptz '-infinity' - timestamptz '1995-08-06 12:12:12'; + +-- test age() with infinite timestamps +SELECT age(timestamptz 'infinity'); +SELECT age(timestamptz '-infinity'); +SELECT age(timestamptz 'infinity', timestamptz 'infinity'); +SELECT age(timestamptz 'infinity', timestamptz '-infinity'); +SELECT age(timestamptz '-infinity', timestamptz 'infinity'); +SELECT age(timestamptz '-infinity', timestamptz '-infinity'); -- 2.34.1