Thread: BUG #18313: No error triggered when subtracting an interval from a timestamp

The following bug has been logged on the website:

Bug reference:      18313
Logged by:          Christian Maurer
Email address:      c.maurer@gmx.at
PostgreSQL version: 16.1
Operating system:   RHEL 9
Description:

Hi

The SQL script below uses an anchor date (2000-01-01) in a table and tries
to subtract an interval (in days) from it.
Considering the timestamp range, the maximum number of days that can be
successfully subtracted should be 2451545. All higher values are then
expected to trigger an exception.

However, when subtracting 2483590, no error is triggered. This is true up to
2539738.
Subtracting 2539739 again shows an error message.

Is this the intended behavior?

Platform: We use PostgreSQL 16.1 (PGDG) under Red Hat Enterprise Linux 9
with standard settings.

Regards,
Christian

create table tbl_timestamp_limit (anchor_date TIMESTAMP(6), offset_value
BIGINT);
insert into tbl_timestamp_limit (anchor_date) values
(to_timestamp('20000101','YYYYMMDD'));

-- the maximum value in days we can subtract from '2000-01-01' without an
error is 2451545
update tbl_timestamp_limit set offset_value=2483589;
-- 2483589 fails as expected
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;

-- one day more
update tbl_timestamp_limit set offset_value=2483590;
-- 2483590 surprisingly succeeds, no error is triggered
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;

update tbl_timestamp_limit set offset_value=2539738;
-- 2539738 still does not trigger an error
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;

update tbl_timestamp_limit set offset_value=2539739;
-- 2539739 fails as expected
SELECT (anchor_date - INTERVAL '1 day' * (offset_value)) FROM
tbl_timestamp_limit;

PG Bug reporting form <noreply@postgresql.org> writes:
> The SQL script below uses an anchor date (2000-01-01) in a table and tries
> to subtract an interval (in days) from it.
> Considering the timestamp range, the maximum number of days that can be
> successfully subtracted should be 2451545. All higher values are then
> expected to trigger an exception.

> However, when subtracting 2483590, no error is triggered. This is true up to
> 2539738.
> Subtracting 2539739 again shows an error message.

> Is this the intended behavior?

Nope.  Looks like we need to check for an out-of-range Julian date
when subtracting intervals from timestamps, more or less as attached.

            regards, tom lane

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 866fdd5af8..c38f88dba7 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3120,8 +3120,16 @@ timestamp_pl_interval(PG_FUNCTION_ARGS)
                         (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                          errmsg("timestamp out of range")));

-            /* Add days by converting to and from Julian */
-            julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
+            /*
+             * Add days by converting to and from Julian.  We need an overflow
+             * check here since j2date expects a non-negative integer input.
+             */
+            julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
+            if (pg_add_s32_overflow(julian, span->day, &julian) ||
+                julian < 0)
+                ereport(ERROR,
+                        (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                         errmsg("timestamp out of range")));
             j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);

             if (tm2timestamp(tm, fsec, NULL, ×tamp) != 0)
@@ -3256,8 +3264,19 @@ timestamptz_pl_interval_internal(TimestampTz timestamp,
                         (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                          errmsg("timestamp out of range")));

-            /* Add days by converting to and from Julian */
-            julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
+            /*
+             * Add days by converting to and from Julian.  We need an overflow
+             * check here since j2date expects a non-negative integer input.
+             * In practice though, it will give correct answers for small
+             * negative Julian dates; we should allow -1 to avoid
+             * timezone-dependent failures, as discussed in timestamp.h.
+             */
+            julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
+            if (pg_add_s32_overflow(julian, span->day, &julian) ||
+                julian < -1)
+                ereport(ERROR,
+                        (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                         errmsg("timestamp out of range")));
             j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);

             tz = DetermineTimeZoneOffset(tm, attimezone);