Re: Infinite Interval - Mailing list pgsql-hackers

On Mon, Apr 3, 2023 at 10:11 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.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

>    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