From: | Joseph Koshakow <koshy44(at)gmail(dot)com> |
---|---|
To: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Gregory Stark (as CFM)" <stark(dot)cfm(at)gmail(dot)com>, jian he <jian(dot)universality(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Infinite Interval |
Date: | 2023-04-08 15:23:59 |
Message-ID: | CAAvxfHe5jAstdPzTNpJcUN=vEyWGRjzXa8MeLdikjbaEeUPzfA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
v3-0001-Move-integer-helper-function-to-int.h.patch | text/x-patch | 3.3 KB |
v19-0003-Add-infinite-interval-values.patch | text/x-patch | 91.3 KB |
v3-0002-Check-for-overflow-in-make_interval.patch | text/x-patch | 5.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2023-04-08 15:37:56 | Re: Commitfest 2023-03 starting tomorrow! |
Previous Message | Tom Lane | 2023-04-08 15:04:05 | Re: broken master branch |