From: Tom Lane <tgl@sss.pgh.pa.us>
To: c.maurer@gmx.at
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18313: No error triggered when subtracting an interval from
 a timestamp
In-reply-to: <18313-64d2c8952d81e84b@postgresql.org>
References: <18313-64d2c8952d81e84b@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 26 Jan 2024 13:06:03 +0000"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <927297.1706290310.0@sss.pgh.pa.us>
Date: Fri, 26 Jan 2024 12:32:02 -0500
Message-ID: <927312.1706290322@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/927312.1706290322%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <927297.1706290310.1@sss.pgh.pa.us>

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


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="catch-julian-date-overflow-in-interval-addition.patch";
	charset="us-ascii"
Content-ID: <927297.1706290310.2@sss.pgh.pa.us>
Content-Description: catch-julian-date-overflow-in-interval-addition.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/tim=
estamp.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 =3D 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 =3D 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, &timestamp) !=3D 0)
@@ -3256,8 +3264,19 @@ timestamptz_pl_interval_internal(TimestampTz timest=
amp,
 						(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 						 errmsg("timestamp out of range")));
 =

-			/* Add days by converting to and from Julian */
-			julian =3D 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 =3D 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 =3D DetermineTimeZoneOffset(tm, attimezone);

------- =_aaaaaaaaaa0--