Re: Infinite Interval

Hi Joseph,

On Mon, Apr 3, 2023 at 6:02 AM Joseph Koshakow <koshy44(at)gmail(dot)com> wrote:

>
> I've attached a patch with all of the errcontext calls removed. None of
> the existing out of range errors have an errdetail call so I think this
> is more consistent. If we do want to add errdetail, then we should
> probably do it in a later patch and add it to all out of range errors,
> not just the ones related to infinity.

Hmm, I realize my errcontext suggestion was in wrong direction. We can
use errdetail if required in future. But not for this patch.

Here are comments on the test and output.

+ 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.

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.

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

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.

--
Best Wishes,
Ashutosh Bapat