agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value
3+ messages / 3 participants
[nested] [flat]
* BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value
@ 2026-09-07 14:06 PG Bug reporting form <noreply@postgresql.org>
2026-09-26 15:25 ` Re: BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value Rahul Yadav <rahul@rhyadav.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-09-07 14:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 1950233439@qq.com
The following bug has been logged on the website:
Bug reference: 19670
Logged by: Tianyu Shi
Email address: 1950233439@qq.com
PostgreSQL version: 19beta3
Operating system: Ubuntu22.04
Description:
### Summary
`time_pl_interval()` in `src/backend/utils/adt/date.c` (lines 2174–2195)
silently produces incorrect results when adding a near-maximal interval to a
time value. The infinity guard (`INTERVAL_NOT_FINITE`) requires all three
interval fields to simultaneously hold their extreme values, so an interval
such as `'9223372036854 seconds'` (where `span->time = 9223372036854000000`,
slightly below `INT64_MAX`) bypasses the check entirely. The subsequent
unchecked addition `result = time + span->time` overflows signed 64-bit
integer arithmetic (C undefined behavior), returning a garbage `TimeADT`
with no error raised. Applications relying on correct time arithmetic for
security decisions — session expiry, scheduling windows, access-time
enforcement — may silently receive a corrupted value and act on it.
### PoC
Any authenticated database user can trigger the overflow with a single SQL
statement; no special privileges are required.
```sql
SELECT '23:59:59.999999'::time + interval '9223372036854 seconds';
```
To run against the local build:
```sql
-- Connect: ./build/bin/psql -h ./build/run -p 5432 postgres
SELECT
'23:59:59.999999'::time + interval '9223372036854 seconds' AS
actual_result,
make_time(0, 0, 0) + 24053999999::bigint * interval '1 microsecond' AS
expected_result,
CASE
WHEN ('23:59:59.999999'::time + interval '9223372036854 seconds') !=
(make_time(0, 0, 0) + 24053999999::bigint * interval '1
microsecond')
THEN 'MISMATCH: Integer overflow confirmed - result is WRONG'
ELSE 'MATCH: No overflow detected'
END AS verdict;
```
### Result
Expected output (correct modular arithmetic): `06:40:53.999999`.
Actual output observed: `19:59:04.448383` — an overflow-corrupted value
returned without any error or warning.
```
actual_result | expected_result | verdict
-----------------+-----------------+--------------------------------------------------------
19:59:04.448383 | 06:40:53.999999 | MISMATCH: Integer overflow confirmed -
result is WRONG
```
The semantic invariant `(time + interval) mod USECS_PER_DAY` is violated. No
exception is raised, so callers cannot distinguish a correct result from a
corrupted one.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value
2026-09-07 14:06 BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-26 15:25 ` Rahul Yadav <rahul@rhyadav.com>
2026-09-27 17:57 ` Re: BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value Andrew Krylosov <krylosov.andrew@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rahul Yadav @ 2026-09-26 15:25 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 1950233439@qq.com
Hi,
I can reproduce this on master. time_pl_interval() adds the
interval's time field to the time value before reducing the result
modulo one day, so a large enough interval overflows int64.
time_mi_interval() and the two timetz variants have the same problem.
Since the result wraps around at midnight anyway, only the interval's
time field modulo one day matters. The attached patch reduces it
first, so the intermediate result always fits in an int64; results
for intervals that didn't overflow are unchanged. It also adds
regression tests for the largest and smallest interval time values,
which fail without the fix.
The same arithmetic exists in all supported branches, so I think this
should be back-patched.
Regards,
Rahul Yadav
Attachments:
[application/x-patch] v1-0001-Fix-integer-overflow-in-time-and-timetz-interval-.patch (5.4K, ../../CAJJjRRe0fUFsh4ocp4bBui=Jd+_t4cwYAs53kYzteRGmMm_SBw@mail.gmail.com/2-v1-0001-Fix-integer-overflow-in-time-and-timetz-interval-.patch)
download | inline diff:
From f7f09855722bf0ae2b211dc4f99fe4a66870ef11 Mon Sep 17 00:00:00 2001
From: Rahul Yadav <rahul@rhyadav.com>
Date: Sat, 26 Sep 2026 15:44:06 +0100
Subject: [PATCH v1] Fix integer overflow in time and timetz interval
arithmetic
time_pl_interval() and time_mi_interval(), and their timetz
counterparts, added the interval's time field to the time value before
reducing the result modulo one day. A large enough interval made that
addition overflow int64, which is undefined behavior; in practice it
produced a wrong time of day without any error. For example,
SELECT time '23:59:59.999999' + interval '9223372036854 seconds';
returned 19:59:04.448383 instead of 04:00:53.999999.
Since the result wraps around at midnight, only the interval's time
field modulo one day matters. Reduce it modulo USECS_PER_DAY before
adding or subtracting, so the intermediate result always fits in an
int64. Results for intervals that did not overflow are unchanged.
Add regression tests using the largest and smallest possible interval
time fields.
Author: Rahul Yadav <rahul@rhyadav.com>
Reported-by: Tianyu Shi <1950233439@qq.com>
Bug: #19670
Discussion: https://postgr.es/m/19670-c4e56832fa6686f8@postgresql.org
Backpatch-through: 14
---
src/backend/utils/adt/date.c | 16 ++++++++++++----
src/test/regress/expected/interval.out | 25 +++++++++++++++++++++++++
src/test/regress/sql/interval.sql | 6 ++++++
3 files changed, 43 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 7f746dd84c..b30b570ab1 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -2176,7 +2176,12 @@ time_pl_interval(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("cannot add infinite interval to time")));
- result = time + span->time;
+ /*
+ * The result wraps around at midnight, so only the interval's time field
+ * modulo one day matters. Reducing it first also prevents integer
+ * overflow when the interval is very large.
+ */
+ result = time + (span->time % USECS_PER_DAY);
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
result += USECS_PER_DAY;
@@ -2200,7 +2205,8 @@ time_mi_interval(PG_FUNCTION_ARGS)
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("cannot subtract infinite interval from time")));
- result = time - span->time;
+ /* As in time_pl_interval, reduce modulo one day to prevent overflow */
+ result = time - (span->time % USECS_PER_DAY);
result -= result / USECS_PER_DAY * USECS_PER_DAY;
if (result < INT64CONST(0))
result += USECS_PER_DAY;
@@ -2728,7 +2734,8 @@ timetz_pl_interval(PG_FUNCTION_ARGS)
result = palloc_object(TimeTzADT);
- result->time = time->time + span->time;
+ /* As in time_pl_interval, reduce modulo one day to prevent overflow */
+ result->time = time->time + (span->time % USECS_PER_DAY);
result->time -= result->time / USECS_PER_DAY * USECS_PER_DAY;
if (result->time < INT64CONST(0))
result->time += USECS_PER_DAY;
@@ -2756,7 +2763,8 @@ timetz_mi_interval(PG_FUNCTION_ARGS)
result = palloc_object(TimeTzADT);
- result->time = time->time - span->time;
+ /* As in time_pl_interval, reduce modulo one day to prevent overflow */
+ result->time = time->time - (span->time % USECS_PER_DAY);
result->time -= result->time / USECS_PER_DAY * USECS_PER_DAY;
if (result->time < INT64CONST(0))
result->time += USECS_PER_DAY;
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index a16e3ccdb2..5ae1a4897b 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -2128,6 +2128,31 @@ SELECT timetz '11:27:42' - interval 'infinity';
ERROR: cannot subtract infinite interval from time
SELECT timetz '11:27:42' - interval '-infinity';
ERROR: cannot subtract infinite interval from time
+-- time +/- interval must not overflow, however large the interval
+SELECT time '23:59:59.999999' + interval '9223372036854775807 microseconds';
+ ?column?
+-----------------
+ 04:00:54.775806
+(1 row)
+
+SELECT time '23:59:59.999999' - interval '-9223372036854775808 microseconds';
+ ?column?
+-----------------
+ 04:00:54.775807
+(1 row)
+
+SELECT timetz '23:59:59.999999+01' + interval '9223372036854775807 microseconds';
+ ?column?
+--------------------
+ 04:00:54.775806+01
+(1 row)
+
+SELECT timetz '23:59:59.999999+01' - interval '-9223372036854775808 microseconds';
+ ?column?
+--------------------
+ 04:00:54.775807+01
+(1 row)
+
SELECT lhst.i lhs,
rhst.i rhs,
lhst.i < rhst.i AS lt,
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 43bc793925..5ab8a6dcea 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -728,6 +728,12 @@ SELECT timetz '11:27:42' + interval '-infinity';
SELECT timetz '11:27:42' - interval 'infinity';
SELECT timetz '11:27:42' - interval '-infinity';
+-- time +/- interval must not overflow, however large the interval
+SELECT time '23:59:59.999999' + interval '9223372036854775807 microseconds';
+SELECT time '23:59:59.999999' - interval '-9223372036854775808 microseconds';
+SELECT timetz '23:59:59.999999+01' + interval '9223372036854775807 microseconds';
+SELECT timetz '23:59:59.999999+01' - interval '-9223372036854775808 microseconds';
+
SELECT lhst.i lhs,
rhst.i rhs,
lhst.i < rhst.i AS lt,
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value
2026-09-07 14:06 BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value PG Bug reporting form <noreply@postgresql.org>
2026-09-26 15:25 ` Re: BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value Rahul Yadav <rahul@rhyadav.com>
@ 2026-09-27 17:57 ` Andrew Krylosov <krylosov.andrew@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andrew Krylosov @ 2026-09-27 17:57 UTC (permalink / raw)
To: Rahul Yadav <rahul@rhyadav.com>; +Cc: pgsql-bugs@lists.postgresql.org; 1950233439@qq.com
Rahul Yadav <rahul@rhyadav.com> wrote:
> Since the result wraps around at midnight anyway, only the interval's
> time field modulo one day matters. The attached patch reduces it
> first, so the intermediate result always fits in an int64; results
> for intervals that didn't overflow are unchanged. It also adds
> regression tests for the largest and smallest interval time values,
> which fail without the fix.
I applied v1 on top of 3c5d9d914f, built it on macos clang 17 with
assertions enabled and ran the regression tests; they pass, and the new
ones fail without the date.c change. The example from the report now
gives 04:00:53.999999.
The fix looks right to me. Since 0 <= time <= USECS_PER_DAY and the
reduced offset is in (-USECS_PER_DAY, USECS_PER_DAY), the sum can't
overflow, and the existing normalization maps it to the same result as
before. I also compared time +/- interval against an exact numeric
computation for a couple of thousand random interval values plus the
boundaries: the results match HEAD wherever HEAD doesn't overflow, and
are correct where it does. The timetz zone is never touched.
Best regards,
Andrew Krylosov
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-27 17:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-07 14:06 BUG #19670: Silent Integer Overflow in time_pl_interval() Returns Wrong Time Value PG Bug reporting form <noreply@postgresql.org>
2026-09-26 15:25 ` Rahul Yadav <rahul@rhyadav.com>
2026-09-27 17:57 ` Andrew Krylosov <krylosov.andrew@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox