On Wed, Jan 4, 2023 at 10:13 PM jian he <[email protected]> wrote:
>
>
> On Tue, Jan 3, 2023 at 6:14 AM Joseph Koshakow <[email protected]> wrote:
>
>> I have another patch, this one adds validations to operations that
>> return intervals and updated error messages. I tried to give all of the
>> error messages meaningful text, but I'm starting to think that almost all
>> of them should just say "interval out of range". The current approach
>> may reveal some implementation details and lead to confusion. For
>> example, some subtractions are converted to additions which would lead
>> to an error message about addition.
>>
>> SELECT date 'infinity' - interval 'infinity';
>> ERROR: cannot add infinite values with opposite signs
>>
>> I've also updated the commit message to include the remaining TODOs,
>> which I've copied below
>>
>> 1. Various TODOs in code.
>> 2. Correctly implement interval_part for infinite intervals.
>> 3. Test consolidation.
>> 4. Should we just use the months field to test for infinity?
>>
>
>
> 3. Test consolidation.
> I used the DO command, reduced a lot of test sql code.
> I don't know how to generate an interval.out file.
> I hope the format is ok. I use https://sqlformat.darold.net/ format the
> sql code.
> Then I saw on the internet that one line should be no more than 80 chars.
> so I slightly changed the format.
>
> --
> I recommend David Deutsch's <<The Beginning of Infinity>>
>
> Jian
>
>
>
1. Various TODOs in code.
logic combine and clean up for functions in backend/utils/adt/timestamp.c
(timestamp_pl_interval,timestamptz_pl_interval, interval_pl, interval_mi).
3. Test consolidation in /regress/sql/interval.sql
For 1. I don't know how to format the code. I have a problem installing
pg_indent. If the format is wrong, please reformat.
3. As the previous email thread.
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 9484b29ec4..350363b9ad 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -2973,21 +2973,21 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
* timestamp with the same sign. Adding two infintes with different signs
* results in an error.
*/
- /* TODO this logic can probably be combined and cleaned up. */
- if (INTERVAL_IS_NOBEGIN(span) && TIMESTAMP_IS_NOBEGIN(timestamp))
- TIMESTAMP_NOBEGIN(result);
- else if (INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
- TIMESTAMP_NOEND(result);
- else if (INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("cannot add infinite values with opposite signs")));
- else if (INTERVAL_IS_NOBEGIN(span))
- TIMESTAMP_NOBEGIN(result);
- else if (INTERVAL_IS_NOEND(span))
- TIMESTAMP_NOEND(result);
- else if (TIMESTAMP_NOT_FINITE(timestamp))
- result = timestamp;
+ if ( (INTERVAL_IS_NOBEGIN(span) && TIMESTAMP_IS_NOBEGIN(timestamp))
+ || (INTERVAL_IS_NOBEGIN(span) && !TIMESTAMP_NOT_FINITE(timestamp))
+ || (!INTERVAL_NOT_FINITE(span) && TIMESTAMP_IS_NOBEGIN(timestamp)))
+ TIMESTAMP_NOBEGIN(result);
+
+ else if ((INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
+ || (INTERVAL_IS_NOEND(span) && !TIMESTAMP_NOT_FINITE(timestamp))
+ ||(!INTERVAL_NOT_FINITE(span) && TIMESTAMP_IS_NOEND(timestamp)))
+ TIMESTAMP_NOEND(result);
+
+ else if ((!INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
+ || (INTERVAL_NOT_FINITE(span) && !TIMESTAMP_NOT_FINITE(timestamp)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
else
{
if (span->month != 0)
@@ -3095,21 +3095,21 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS)
* timestamp with the same sign. Adding two infintes with different signs
* results in an error.
*/
- /* TODO this logic can probably be combined and cleaned up. */
- if (INTERVAL_IS_NOBEGIN(span) && TIMESTAMP_IS_NOBEGIN(timestamp))
- TIMESTAMP_NOBEGIN(result);
- else if (INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
- TIMESTAMP_NOEND(result);
- else if (INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
- ereport(ERROR,
- (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
- errmsg("cannot add infinite values with opposite signs")));
- else if (INTERVAL_IS_NOBEGIN(span))
- TIMESTAMP_NOBEGIN(result);
- else if (INTERVAL_IS_NOEND(span))
- TIMESTAMP_NOEND(result);
- else if (TIMESTAMP_NOT_FINITE(timestamp))
- result = timestamp;
+ if ((INTERVAL_IS_NOBEGIN(span) && TIMESTAMP_IS_NOBEGIN(timestamp))
+ || (INTERVAL_IS_NOBEGIN(span) && !TIMESTAMP_NOT_FINITE(timestamp))
+ || (!INTERVAL_NOT_FINITE(span) && TIMESTAMP_IS_NOBEGIN(timestamp)))
+ TIMESTAMP_NOBEGIN(result);
+
+ else if ((INTERVAL_IS_NOEND(span) && TIMESTAMP_IS_NOEND(timestamp))
+ || (INTERVAL_IS_NOEND(span) && !TIMESTAMP_NOT_FINITE(timestamp))
+ || (!INTERVAL_NOT_FINITE(span) && TIMESTAMP_IS_NOEND(timestamp)))
+ TIMESTAMP_NOEND(result);
+
+ else if ((!INTERVAL_NOT_FINITE(span) && TIMESTAMP_NOT_FINITE(timestamp))
+ || (INTERVAL_NOT_FINITE(span) && !TIMESTAMP_NOT_FINITE(timestamp)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("cannot add infinite values with opposite signs")));
else
{
if (span->month != 0)
@@ -3255,61 +3255,52 @@ interval_pl(PG_FUNCTION_ARGS)
* infinite interval with the same sign. Adding two infinte intervals with
* different signs results in an error.
*/
- /* TODO can be combined and simplified */
- if (INTERVAL_IS_NOBEGIN(span1) && INTERVAL_IS_NOBEGIN(span2))
- {
- INTERVAL_NOBEGIN(result);
- PG_RETURN_INTERVAL_P(result);
- }
- else if (INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOEND(span2))
- {
- INTERVAL_NOEND(result);
- PG_RETURN_INTERVAL_P(result);
- }
+ if ((INTERVAL_IS_NOBEGIN(span1) && INTERVAL_IS_NOBEGIN(span2))
+ ||(INTERVAL_IS_NOBEGIN(span1) && !INTERVAL_NOT_FINITE(span2))
+ ||(!INTERVAL_NOT_FINITE(span1) && INTERVAL_IS_NOBEGIN(span2)))
+ INTERVAL_NOBEGIN(result);
+
+ else if
+ ((INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOEND(span2))
+ ||(INTERVAL_IS_NOEND(span1) && !INTERVAL_NOT_FINITE(span2))
+ ||(!INTERVAL_NOT_FINITE(span1) && INTERVAL_IS_NOEND(span2)))
+ INTERVAL_NOEND(result);
+
else if (INTERVAL_NOT_FINITE(span1) && INTERVAL_NOT_FINITE(span2))
{
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("cannot add infinite values with opposite signs")));
}
- else if (INTERVAL_NOT_FINITE(span1))
- {
- memcpy(result, span1, sizeof(Interval));
- PG_RETURN_INTERVAL_P(result);
- }
- else if (INTERVAL_NOT_FINITE(span2))
+ else
{
- 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) &&
- !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->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->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->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")));
- if (INTERVAL_NOT_FINITE(result))
- 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);
}
@@ -3327,61 +3318,51 @@ interval_mi(PG_FUNCTION_ARGS)
* infinite interval with the same sign as the left operand. Subtracting
* two infinte intervals with the same sign results in an error.
*/
- /* TODO can be simplified and cleaned up */
- if (INTERVAL_IS_NOBEGIN(span1) && INTERVAL_IS_NOEND(span2))
- {
- INTERVAL_NOBEGIN(result);
- PG_RETURN_INTERVAL_P(result);
- }
- else if (INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOBEGIN(span2))
- {
- INTERVAL_NOEND(result);
- PG_RETURN_INTERVAL_P(result);
- }
+ if ((INTERVAL_IS_NOBEGIN(span1) && INTERVAL_IS_NOEND(span2))
+ ||(INTERVAL_IS_NOBEGIN(span1) && !INTERVAL_NOT_FINITE(span2))
+ ||(!INTERVAL_NOT_FINITE(span1) && INTERVAL_IS_NOEND(span2)))
+ INTERVAL_NOBEGIN(result);
+
+ else if ((INTERVAL_IS_NOEND(span1) && INTERVAL_IS_NOBEGIN(span2))
+ ||(INTERVAL_IS_NOEND(span1) && !INTERVAL_NOT_FINITE(span2))
+ ||(!INTERVAL_NOT_FINITE(span1) && INTERVAL_IS_NOBEGIN(span2)))
+ INTERVAL_NOEND(result);
+
else if (INTERVAL_NOT_FINITE(span1) && INTERVAL_NOT_FINITE(span2))
{
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("cannot subtract infinite values with the same signs")));
- }
- else if (INTERVAL_NOT_FINITE(span1))
- {
- memcpy(result, span1, sizeof(Interval));
- PG_RETURN_INTERVAL_P(result);
- }
- else if (INTERVAL_NOT_FINITE(span2))
+ }
+ else
{
- memcpy(result, span2, sizeof(Interval));
- PG_RETURN_INTERVAL_P(result);
- }
-
- 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->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->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->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")));
- if (INTERVAL_NOT_FINITE(result))
- 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);
}
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 82f3180221..1fd99c53d4 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -652,30 +652,31 @@ SELECT timetz '11:27:42' + interval '-infinity';
SELECT timetz '11:27:42' - interval 'infinity';
SELECT timetz '11:27:42' - interval '-infinity';
-SELECT interval 'infinity' < interval 'infinity';
-SELECT interval 'infinity' < interval '-infinity';
-SELECT interval '-infinity' < interval 'infinity';
-SELECT interval '-infinity' < interval '-infinity';
-SELECT interval 'infinity' <= interval 'infinity';
-SELECT interval 'infinity' <= interval '-infinity';
-SELECT interval '-infinity' <= interval 'infinity';
-SELECT interval '-infinity' <= interval '-infinity';
-SELECT interval 'infinity' > interval 'infinity';
-SELECT interval 'infinity' > interval '-infinity';
-SELECT interval '-infinity' > interval 'infinity';
-SELECT interval '-infinity' > interval '-infinity';
-SELECT interval 'infinity' >= interval 'infinity';
-SELECT interval 'infinity' >= interval '-infinity';
-SELECT interval '-infinity' >= interval 'infinity';
-SELECT interval '-infinity' >= interval '-infinity';
-SELECT interval 'infinity' = interval 'infinity';
-SELECT interval 'infinity' = interval '-infinity';
-SELECT interval '-infinity' = interval 'infinity';
-SELECT interval '-infinity' = interval '-infinity';
-SELECT interval 'infinity' <> interval 'infinity';
-SELECT interval 'infinity' <> interval '-infinity';
-SELECT interval '-infinity' <> interval 'infinity';
-SELECT interval '-infinity' <> interval '-infinity';
+DO $$
+DECLARE
+ intv interval;
+ intv1 interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ OPERATOR text[] := '{<,<=,=, >,>=,<>}';
+ opr text;
+ result boolean;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH opr IN ARRAY OPERATOR LOOP
+ FOREACH intv1 IN ARRAY intvs LOOP
+ EXECUTE 'select interval ' || quote_literal(intv) || ' '
+ || opr || ' interval ' || quote_literal(intv1) INTO result;
+ RAISE NOTICE '%'
+ ,(format('%10s %2s %10s %2s'
+ ,intv
+ ,opr
+ ,intv1
+ ,result));
+ END LOOP;
+ END LOOP;
+ END LOOP;
+END
+$$;
SELECT -interval 'infinity';
SELECT -interval '-infinity';
@@ -709,58 +710,48 @@ SELECT date_bin('-infinity', timestamp '2001-02-16 20:38:40', timestamp '2001-02
SELECT date_trunc('hour', interval 'infinity');
SELECT date_trunc('hour', interval '-infinity');
-SELECT date_part('us', interval 'infinity');
-SELECT date_part('us', interval '-infinity');
-SELECT date_part('ms', interval 'infinity');
-SELECT date_part('ms', interval '-infinity');
-SELECT date_part('second', interval 'infinity');
-SELECT date_part('second', interval '-infinity');
-SELECT date_part('minute', interval 'infinity');
-SELECT date_part('minute', interval '-infinity');
-SELECT date_part('hour', interval 'infinity');
-SELECT date_part('hour', interval '-infinity');
-SELECT date_part('day', interval 'infinity');
-SELECT date_part('day', interval '-infinity');
-SELECT date_part('month', interval 'infinity');
-SELECT date_part('month', interval '-infinity');
-SELECT date_part('quarter', interval 'infinity');
-SELECT date_part('quarter', interval '-infinity');
-SELECT date_part('year', interval 'infinity');
-SELECT date_part('year', interval '-infinity');
-SELECT date_part('decade', interval 'infinity');
-SELECT date_part('decade', interval '-infinity');
-SELECT date_part('century', interval 'infinity');
-SELECT date_part('century', interval '-infinity');
-SELECT date_part('millennium', interval 'infinity');
-SELECT date_part('millennium', interval '-infinity');
-SELECT date_part('epoch', interval 'infinity');
-SELECT date_part('epoch', interval '-infinity');
-SELECT extract(us from interval 'infinity');
-SELECT extract(us from interval '-infinity');
-SELECT extract(ms from interval 'infinity');
-SELECT extract(ms from interval '-infinity');
-SELECT extract(second from interval 'infinity');
-SELECT extract(second from interval '-infinity');
-SELECT extract(minute from interval 'infinity');
-SELECT extract(minute from interval '-infinity');
-SELECT extract(hour from interval 'infinity');
-SELECT extract(hour from interval '-infinity');
-SELECT extract(day from interval 'infinity');
-SELECT extract(day from interval '-infinity');
-SELECT extract(month from interval 'infinity');
-SELECT extract(month from interval '-infinity');
-SELECT extract(quarter from interval 'infinity');
-SELECT extract(quarter from interval '-infinity');
-SELECT extract(year from interval 'infinity');
-SELECT extract(year from interval '-infinity');
-SELECT extract(decade from interval 'infinity');
-SELECT extract(decade from interval '-infinity');
-SELECT extract(century from interval 'infinity');
-SELECT extract(century from interval '-infinity');
-SELECT extract(millennium from interval 'infinity');
-SELECT extract(millennium from interval '-infinity');
-SELECT extract(epoch from interval 'infinity');
-SELECT extract(epoch from interval '-infinity');
+DO $$
+DECLARE
+ intv interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ units text[] := '{us,ms,second,minute,hour,day,month
+ ,quarter,year,decade,century,millennium,epoch}';
+ unit text;
+ result double precision;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH unit IN ARRAY units LOOP
+ EXECUTE 'select date_part( ' || quote_literal(unit) || ' ,interval '
+ || quote_literal(intv) || ' )' INTO result;
+ RAISE NOTICE '% %'
+ ,(format('date_part(%10s, interval %10s) yiled', unit, intv))
+ ,result;
+ END LOOP;
+ END LOOP;
+END
+$$;
+
+
+DO $$
+DECLARE
+ intv interval;
+ intvs interval[] := '{+infinity,-infinity}';
+ units text[] := '{us,ms,second,minute,hour,day,month
+ ,quarter,year,decade,century,millennium,epoch}';
+ unit text;
+ result numeric;
+BEGIN
+ FOREACH intv IN ARRAY intvs LOOP
+ FOREACH unit IN ARRAY units LOOP
+ EXECUTE 'select extract( ' || quote_literal(unit)
+ || ' from interval ' || quote_literal(intv) || ' )' INTO result;
+ RAISE NOTICE '% %'
+ ,(format('extract(%10s from interval %9s) yiled', unit, intv))
+ ,result;
+ END LOOP;
+ END LOOP;
+END
+$$;
SELECT justify_days(interval 'infinity');
SELECT justify_days(interval '-infinity');
