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
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');
