From 09a8fef224a3682059fe1adf42957f693a41d242 Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Thu, 30 Apr 2020 10:06:49 +0530 Subject: [PATCH] Support infinite interval This is WIP. Following things are supported 1. Accepts '+/-infinity' as a valid string input for interval type. 2. Support interval_pl, interval_div 3. Tests in interval.sql for comparison operators working fine. TODOs 1. Various TODOs in code 2. interval_pl: how to handle infinite values with opposite signs 3. timestamp, timestamptz, date and time arithmetic 4. Fix horology test. Ashutosh Bapat --- src/backend/utils/adt/datetime.c | 2 + src/backend/utils/adt/timestamp.c | 166 ++++++++++++++++++++++++- src/test/regress/expected/interval.out | 80 ++++++++++-- src/test/regress/sql/interval.sql | 8 ++ 4 files changed, 242 insertions(+), 14 deletions(-) diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index b5b117a8ca..1e98c6dc78 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -3634,6 +3634,8 @@ DecodeInterval(char **field, int *ftype, int nf, int range, case DTK_STRING: case DTK_SPECIAL: type = DecodeUnits(i, field[i], &uval); + if (type == UNKNOWN_FIELD) + type = DecodeSpecial(i, field[i], &uval); if (type == IGNORE_DTF) continue; diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index 3f2508c0c4..0c7286b06e 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -79,6 +79,12 @@ 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_noend(Interval *interval); +static bool interval_is_noend(Interval *interval); +static void interval_nobegin(Interval *interval); +static bool interval_is_nobegin(Interval *interval); +static bool interval_not_finite(Interval *interval); /* common code for timestamptypmodin and timestamptztypmodin */ static int32 @@ -943,6 +949,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 +979,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 +1371,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod, INT64CONST(0) }; + /* + * Infinite interval after being subjected to typmod conversion remains + * infinite. + */ + if (interval_not_finite(interval)) + return; + /* * Unspecified range and precision? Then not necessary to adjust. Setting * typmod to -1 is the convention for all data types. @@ -1545,6 +1571,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) { @@ -2080,10 +2117,12 @@ timestamp_finite(PG_FUNCTION_ARGS) PG_RETURN_BOOL(!TIMESTAMP_NOT_FINITE(timestamp)); } +/* TODO: modify this to check finite-ness */ Datum interval_finite(PG_FUNCTION_ARGS) { - PG_RETURN_BOOL(true); + Interval *interval = PG_GETARG_INTERVAL_P(0); + PG_RETURN_BOOL(!interval_not_finite(interval)); } @@ -2926,8 +2965,27 @@ timestamp_pl_interval(PG_FUNCTION_ARGS) Interval *span = PG_GETARG_INTERVAL_P(1); Timestamp result; + /* + * Adding finite interval to an infinite time is going to be infinite in + * the same direction. Adding infinte interval to infinite timestamp in the + * same direction results in an infinite timestamp in the same direction. + * Adding infinite interval to an infinite timestamp with opposite + * direction is not going to yield 0 but some infinity. Since we are adding + * interval to the timestamp the resultant timestamp is an infinity + * preserving the direction. + */ if (TIMESTAMP_NOT_FINITE(timestamp)) result = timestamp; + else if (interval_not_finite(span)) + { + if (interval_is_nobegin(span)) + TIMESTAMP_NOBEGIN(result); + else + { + Assert(interval_is_noend(span)); + TIMESTAMP_NOEND(result); + } + } else { if (span->month != 0) @@ -3032,8 +3090,27 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS) TimestampTz result; int tz; + /* + * Adding finite interval to an infinite time is going to be infinite in + * the same direction. Adding infinte interval to infinite timestamp in the + * same direction results in an infinite timestamp in the same direction. + * Adding infinite interval to an infinite timestamp with opposite + * direction is not going to yield 0 but some infinity. Since we are adding + * interval to the timestamp the resultant timestamp is an infinity + * preserving the direction. + */ if (TIMESTAMP_NOT_FINITE(timestamp)) result = timestamp; + else if (interval_not_finite(span)) + { + if (interval_is_nobegin(span)) + TIMESTAMP_NOBEGIN(result); + else + { + Assert(interval_is_noend(span)); + TIMESTAMP_NOEND(result); + } + } else { if (span->month != 0) @@ -3192,6 +3269,21 @@ interval_pl(PG_FUNCTION_ARGS) result = (Interval *) palloc(sizeof(Interval)); + /* + * TODO: What if both span1 and span2 are infinite and in oppposite + * direction? + */ + if (interval_not_finite(span1)) + { + memcpy(result, span1, sizeof(Interval)); + PG_RETURN_INTERVAL_P(result); + } + else if (interval_not_finite(span2)) + { + memcpy(result, span2, sizeof(Interval)); + PG_RETURN_INTERVAL_P(result); + } + result->month = span1->month + span2->month; /* overflow check copied from int4pl */ if (SAMESIGN(span1->month, span2->month) && @@ -3362,6 +3454,14 @@ interval_div(PG_FUNCTION_ARGS) (errcode(ERRCODE_DIVISION_BY_ZERO), errmsg("division by zero"))); + /* Dividing infinite interval by finite number keeps it infinite. */ + /* TODO: Do we change the sign of infinity if factor is negative? */ + if (interval_not_finite(span)) + { + memcpy(result, span, sizeof(Interval)); + PG_RETURN_INTERVAL_P(result); + } + result->month = (int32) (span->month / factor); result->day = (int32) (span->day / factor); @@ -5955,3 +6055,63 @@ generate_series_timestamptz(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } } + +/* + * TODO: possibly we should move these to a place along with other interval_* + * functions. + */ + +/* Set the given interval to indicate infinite interval in the future. */ +static void +interval_noend(Interval *interval) +{ + interval->time = DT_NOEND; + interval->day = PG_INT32_MAX; + interval->month = PG_INT32_MAX; +} + +/* Does the given interval indicate infinite interval in the future? */ +static bool +interval_is_noend(Interval *interval) +{ + /* + * TODO: Possibly it makes sense to just check one of the fields to reduce + * the number of instructions here. But it's safer to check all the three + * fields. + */ + return interval->time == DT_NOEND && + interval->day == PG_INT32_MAX && + interval->month == PG_INT32_MAX; +} + +/* Set the given interval to indicate infinite interval in the past. */ +static void +interval_nobegin(Interval *interval) +{ + interval->time = DT_NOBEGIN; + interval->day = PG_INT32_MIN; + interval->month = PG_INT32_MIN; +} + +/* Does the given interval indicate infinite interval in the past? */ +static bool +interval_is_nobegin(Interval *interval) +{ + /* + * TODO: Possibly it makes sense to just check one of the fields to reduce + * the number of instructions here. But it's safer to check all the three + * fields. + */ + return interval->time == DT_NOBEGIN && + interval->day == PG_INT32_MIN && + interval->month == PG_INT32_MIN; +} + +/* Is the given interval infinite? */ +/* TODO: probably a material for a macro. */ +static bool +interval_not_finite(Interval *interval) +{ + return interval_is_nobegin(interval) || interval_is_noend(interval); +} + diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out index 579e92e7b3..8242d28945 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -52,6 +52,19 @@ SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years..."; 9 years 1 mon -12 days +13:14:00 (1 row) +SELECT INTERVAL 'infinity' AS "eternity"; + eternity +---------- + infinity +(1 row) + +SELECT INTERVAL '-infinity' AS "beginning of time"; + beginning of time +------------------- + -infinity +(1 row) + +--TODO: Add tests for operators etc. by looking at the other tests below CREATE TABLE INTERVAL_TBL (f1 interval); INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute'); INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour'); @@ -63,6 +76,8 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds'); INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years'); INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months'); INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours'); +INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity'); +INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity'); -- badly formatted interval INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval'); ERROR: invalid input syntax for type interval: "badly formatted interval" @@ -72,6 +87,10 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago'); ERROR: invalid input syntax for type interval: "@ 30 eons ago" LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago'); ^ +INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity'); +ERROR: invalid input syntax for type interval: "+infinity" +LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity'); + ^ -- Test non-error-throwing API SELECT pg_input_is_valid('1.5 weeks', 'interval'); pg_input_is_valid @@ -117,7 +136,9 @@ SELECT * FROM INTERVAL_TBL; 6 years 5 mons 5 mons 12:00:00 -(10 rows) + infinity + -infinity +(12 rows) SELECT * FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 <> interval '@ 10 days'; @@ -132,7 +153,9 @@ SELECT * FROM INTERVAL_TBL 6 years 5 mons 5 mons 12:00:00 -(9 rows) + infinity + -infinity +(11 rows) SELECT * FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours'; @@ -141,7 +164,8 @@ SELECT * FROM INTERVAL_TBL 00:01:00 05:00:00 -00:00:14 -(3 rows) + -infinity +(4 rows) SELECT * FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 < interval '@ 1 day'; @@ -150,7 +174,8 @@ SELECT * FROM INTERVAL_TBL 00:01:00 05:00:00 -00:00:14 -(3 rows) + -infinity +(4 rows) SELECT * FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 = interval '@ 34 years'; @@ -168,7 +193,8 @@ SELECT * FROM INTERVAL_TBL 6 years 5 mons 5 mons 12:00:00 -(5 rows) + infinity +(6 rows) SELECT * FROM INTERVAL_TBL WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago'; @@ -183,7 +209,8 @@ SELECT * FROM INTERVAL_TBL 6 years 5 mons 5 mons 12:00:00 -(9 rows) + infinity +(10 rows) SELECT r1.*, r2.* FROM INTERVAL_TBL r1, INTERVAL_TBL r2 @@ -191,27 +218,35 @@ SELECT r1.*, r2.* ORDER BY r1.f1, r2.f1; f1 | f1 -----------------+----------------- + -00:00:14 | -infinity + 00:01:00 | -infinity 00:01:00 | -00:00:14 + 05:00:00 | -infinity 05:00:00 | -00:00:14 05:00:00 | 00:01:00 + 1 day 02:03:04 | -infinity 1 day 02:03:04 | -00:00:14 1 day 02:03:04 | 00:01:00 1 day 02:03:04 | 05:00:00 + 10 days | -infinity 10 days | -00:00:14 10 days | 00:01:00 10 days | 05:00:00 10 days | 1 day 02:03:04 + 3 mons | -infinity 3 mons | -00:00:14 3 mons | 00:01:00 3 mons | 05:00:00 3 mons | 1 day 02:03:04 3 mons | 10 days + 5 mons | -infinity 5 mons | -00:00:14 5 mons | 00:01:00 5 mons | 05:00:00 5 mons | 1 day 02:03:04 5 mons | 10 days 5 mons | 3 mons + 5 mons 12:00:00 | -infinity 5 mons 12:00:00 | -00:00:14 5 mons 12:00:00 | 00:01:00 5 mons 12:00:00 | 05:00:00 @@ -219,6 +254,7 @@ SELECT r1.*, r2.* 5 mons 12:00:00 | 10 days 5 mons 12:00:00 | 3 mons 5 mons 12:00:00 | 5 mons + 6 years | -infinity 6 years | -00:00:14 6 years | 00:01:00 6 years | 05:00:00 @@ -227,6 +263,7 @@ SELECT r1.*, r2.* 6 years | 3 mons 6 years | 5 mons 6 years | 5 mons 12:00:00 + 34 years | -infinity 34 years | -00:00:14 34 years | 00:01:00 34 years | 05:00:00 @@ -236,7 +273,18 @@ SELECT r1.*, r2.* 34 years | 5 mons 34 years | 5 mons 12:00:00 34 years | 6 years -(45 rows) + infinity | -infinity + infinity | -00:00:14 + infinity | 00:01:00 + infinity | 05:00:00 + infinity | 1 day 02:03:04 + infinity | 10 days + infinity | 3 mons + infinity | 5 mons + infinity | 5 mons 12:00:00 + infinity | 6 years + infinity | 34 years +(66 rows) -- Test intervals that are large enough to overflow 64 bits in comparisons CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval); @@ -386,12 +434,20 @@ SELECT * FROM INTERVAL_TBL; @ 6 years @ 5 mons @ 5 mons 12 hours -(10 rows) + infinity + -infinity +(12 rows) -- test avg(interval), which is somewhat fragile since people have been -- known to change the allowed input syntax for type interval without -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; + avg +---------- + infinity +(1 row) + +select avg(f1) from interval_tbl where isfinite(f1); avg ------------------------------------------------- @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs @@ -820,8 +876,8 @@ SELECT interval '1 2:03:04.5678' minute to second(2); SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes", (f1 + INTERVAL '1 month')::INTERVAL MONTH::INTERVAL YEAR AS "years" FROM interval_tbl; - f1 | minutes | years ------------------+-----------------+---------- + f1 | minutes | years +-----------------+-----------------+----------- 00:01:00 | 00:01:00 | 00:00:00 05:00:00 | 05:00:00 | 00:00:00 10 days | 10 days | 00:00:00 @@ -832,7 +888,9 @@ SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes", 6 years | 6 years | 6 years 5 mons | 5 mons | 00:00:00 5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00 -(10 rows) + infinity | infinity | infinity + -infinity | -infinity | -infinity +(12 rows) -- test inputting and outputting SQL standard interval literals SET IntervalStyle TO sql_standard; diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index 0517b5b82b..92f60ad005 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -14,6 +14,10 @@ SELECT INTERVAL '-1 days +02:03' AS "22 hours ago..."; SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours"; SELECT INTERVAL '1.5 months' AS "One month 15 days"; SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years..."; +SELECT INTERVAL 'infinity' AS "eternity"; +SELECT INTERVAL '-infinity' AS "beginning of time"; + +--TODO: Add tests for operators etc. by looking at the other tests below CREATE TABLE INTERVAL_TBL (f1 interval); @@ -27,10 +31,13 @@ INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds'); INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years'); INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months'); INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours'); +INSERT INTO INTERVAL_TBL (f1) VALUES ('infinity'); +INSERT INTO INTERVAL_TBL (f1) VALUES ('-infinity'); -- badly formatted interval INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval'); INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago'); +INSERT INTO INTERVAL_TBL (f1) VALUES ('+infinity'); -- Test non-error-throwing API SELECT pg_input_is_valid('1.5 weeks', 'interval'); @@ -141,6 +148,7 @@ SELECT * FROM INTERVAL_TBL; -- updating pg_aggregate.agginitval select avg(f1) from interval_tbl; +select avg(f1) from interval_tbl where isfinite(f1); -- test long interval input select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval; -- 2.25.1