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

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