Re: Infinite Interval

On Sat, Apr 8, 2023 at 8:54 PM Joseph Koshakow <koshy44(at)gmail(dot)com> wrote:
>
> 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.

Thanks for the explanation with an example. Makes sense considering
that the hours and days are not convertible to their wider units
without temporal context.

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

Those two are operations with Time which does not allow infinity. So I
think this is fine.

> >
> > -- 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";
> 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.

I modified this to WHERE isfinite(t.f1) or isfinite(d.f1). The output
contains a lot of additions with infinity::interval but that might be
ok. No errors. We could further improve it to allow operations between
infinity which do not result in error e.g, both operands being same
signed for plus and opposite signed for minus. But I think we can
leave this to the committer's judgement. Which route to choose.

>
> > 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!

My pleasure. I am very much interested to see this being part of code.
Given that the last commit fest for v16 has ended, let's target this
for v17. I will mark this as ready for committer now.

--
Best Wishes,
Ashutosh Bapat