Re: Infinite Interval

Hi Joseph,


On Mon, Apr 3, 2023 at 6:02 AM Joseph Koshakow <koshy44@gmail.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