pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18313: No error triggered when subtracting an interval from a timestamp
2+ messages / 2 participants
[nested] [flat]
* BUG #18313: No error triggered when subtracting an interval from a timestamp
@ 2024-01-26 13:06 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2024-01-26 13:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: c.maurer@gmx.at
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18313: No error triggered when subtracting an interval from a timestamp
@ 2024-01-26 17:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-01-26 17:32 UTC (permalink / raw)
To: c.maurer@gmx.at; +Cc: pgsql-bugs@lists.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.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);
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-01-26 17:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-26 13:06 BUG #18313: No error triggered when subtracting an interval from a timestamp PG Bug reporting form <noreply@postgresql.org>
2024-01-26 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
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