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
Date: Fri, 26 Jan 2024 12:32:02 -0500
Message-ID: <927312.1706290322@sss.pgh.pa.us> (raw)
In-Reply-To: <18313-64d2c8952d81e84b@postgresql.org>
References: <18313-64d2c8952d81e84b@postgresql.org>
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
Attachments:
[text/x-diff] catch-julian-date-overflow-in-interval-addition.patch (2.0K, ../927312.1706290322@sss.pgh.pa.us/2-catch-julian-date-overflow-in-interval-addition.patch)
download | inline diff:diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.cindex 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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, c.maurer@gmx.at, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18313: No error triggered when subtracting an interval from a timestamp
In-Reply-To: <927312.1706290322@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox