Re: Infinite Interval

On Mon, Apr 3, 2023 at 10:11 AM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:
>
> + infinity | | |
> | | Infinity | Infinity | | | Infinity |
> Infinity | Infinity | Infinity | Infinity
> + -infinity | | |
> | | -Infinity | -Infinity | | | -Infinity |
> -Infinity | -Infinity | -Infinity | -Infinity
>
> This is more for my education. It looks like for oscillating units we
report
> NULL here but for monotonically increasing units we report infinity. I
came
> across those terms in the code. But I didn't find definitions of those
terms.
> Can you please point me to the document/resources defining those terms.

I was also unable to find a definition of oscillating or monotonically
increasing in this context. I used the existing timestamps and dates
code to form my own definition:

If there exists an two intervals with the same sign, such that adding
them together results in an interval with a unit that is less than the
unit of at least one of the original intervals, then that unit is
oscillating. Otherwise it is monotonically increasing.

So for example `INTERVAL '30 seconds' + INTERVAL '30 seconds'` results
in an interval with 0 seconds, so seconds are oscillating. You couldn't
find a similar example for days or hours, so they're monotonically
increasing.

> diff --git a/src/test/regress/sql/horology.sql
> b/src/test/regress/sql/horology.sql
> index f7f8c8d2dd..1d0ab322c0 100644
> --- a/src/test/regress/sql/horology.sql
> +++ b/src/test/regress/sql/horology.sql
> @@ -207,14 +207,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)
>
> I removed this and it did not have any effect on results. I think the
> isfinite(i.f1) is already covered by the two existing conditions.

Thanks for pointing this out, I've removed this in the attached patch.

> 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
> @@ -287,11 +290,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";
>
> IIUC, the isfinite() conditions are added to avoid any changes to the
> output due to new
> values added to INTERVAL_TBL. Instead, it might be a good idea to not
add these
> conditions and avoid extra queries testing infinity arithmetic in
interval.sql,
> timestamptz.sql and timestamp.sql like below
>
> +
> +-- infinite intervals
>
> ... some lines folded
>
> +
> +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';
>
> ... block truncated

I originally tried that, but the issue here is that errors propagate
through the whole query. So if one row produces an error then no rows
are produced and instead a single error is returned. So the rows that
would execute, for example,
SELECT date 'infinity' + interval '-infinity' would cause the entire
query to error out. If you have any suggestions to get around this
please let me know.

> With that I have reviewed the entire patch-set. Once you address these
> comments, we can mark it as ready for committer. I already see Tom
> looking at the patch. So that might be just a formality.

Thanks so much for taking the time to review this!

Thanks,
Joe Koshakow