Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitdb8855b

Browse files
committed
Fix mis-rounding and overflow hazards in date_bin().
In the case where the target timestamp is before the origin timestampand their difference is already an exact multiple of the stride, thecode incorrectly subtracted the stride anyway.Also detect several integer-overflow cases that previously producedbogus results. (The submitted patch tried to avoid overflow, butI'm not convinced it's right, and problematic cases are so far out ofthe plausibly-useful range that they don't seem worth sweating over.Let's just use overflow-detecting arithmetic and throw errors.)timestamp_bin() and timestamptz_bin() are basically identical andso had identical bugs. Fix both.Report and patch by Moaaz Assali, adjusted some by me. Back-patchto v14 where date_bin() was introduced.Discussion:https://postgr.es/m/CALkF+nvtuas-2kydG-WfofbRSJpyODAJWun==W-yO5j2R4meqA@mail.gmail.com
1 parent172d7f7 commitdb8855b

File tree

5 files changed

+97
-19
lines changed

5 files changed

+97
-19
lines changed

‎src/backend/utils/adt/timestamp.c

Lines changed: 53 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -3924,8 +3924,9 @@ timestamp_bin(PG_FUNCTION_ARGS)
39243924
Timestamptimestamp=PG_GETARG_TIMESTAMP(1);
39253925
Timestamporigin=PG_GETARG_TIMESTAMP(2);
39263926
Timestampresult,
3927-
tm_diff,
39283927
stride_usecs,
3928+
tm_diff,
3929+
tm_modulo,
39293930
tm_delta;
39303931

39313932
if (TIMESTAMP_NOT_FINITE(timestamp))
@@ -3941,24 +3942,40 @@ timestamp_bin(PG_FUNCTION_ARGS)
39413942
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
39423943
errmsg("timestamps cannot be binned into intervals containing months or years")));
39433944

3944-
stride_usecs=stride->day*USECS_PER_DAY+stride->time;
3945+
if (unlikely(pg_mul_s64_overflow(stride->day,USECS_PER_DAY,&stride_usecs))||
3946+
unlikely(pg_add_s64_overflow(stride_usecs,stride->time,&stride_usecs)))
3947+
ereport(ERROR,
3948+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3949+
errmsg("interval out of range")));
39453950

39463951
if (stride_usecs <=0)
39473952
ereport(ERROR,
39483953
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
39493954
errmsg("stride must be greater than zero")));
39503955

3951-
tm_diff=timestamp-origin;
3952-
tm_delta=tm_diff-tm_diff %stride_usecs;
3956+
if (unlikely(pg_sub_s64_overflow(timestamp,origin,&tm_diff)))
3957+
ereport(ERROR,
3958+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3959+
errmsg("interval out of range")));
3960+
3961+
/* These calculations cannot overflow */
3962+
tm_modulo=tm_diff %stride_usecs;
3963+
tm_delta=tm_diff-tm_modulo;
3964+
result=origin+tm_delta;
39533965

39543966
/*
3955-
* Make sure the returned timestamp is at the start of the bin, even if
3956-
* the origin is in the future.
3967+
* We want to round towards -infinity, not 0, when tm_diff is negative and
3968+
* not a multiple of stride_usecs. This adjustment *can* cause overflow,
3969+
* since the result might now be out of the range origin .. timestamp.
39573970
*/
3958-
if (origin>timestamp&&stride_usecs>1)
3959-
tm_delta-=stride_usecs;
3960-
3961-
result=origin+tm_delta;
3971+
if (tm_modulo<0)
3972+
{
3973+
if (unlikely(pg_sub_s64_overflow(result,stride_usecs,&result))||
3974+
!IS_VALID_TIMESTAMP(result))
3975+
ereport(ERROR,
3976+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3977+
errmsg("timestamp out of range")));
3978+
}
39623979

39633980
PG_RETURN_TIMESTAMP(result);
39643981
}
@@ -4109,6 +4126,7 @@ timestamptz_bin(PG_FUNCTION_ARGS)
41094126
TimestampTzresult,
41104127
stride_usecs,
41114128
tm_diff,
4129+
tm_modulo,
41124130
tm_delta;
41134131

41144132
if (TIMESTAMP_NOT_FINITE(timestamp))
@@ -4124,24 +4142,40 @@ timestamptz_bin(PG_FUNCTION_ARGS)
41244142
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
41254143
errmsg("timestamps cannot be binned into intervals containing months or years")));
41264144

4127-
stride_usecs=stride->day*USECS_PER_DAY+stride->time;
4145+
if (unlikely(pg_mul_s64_overflow(stride->day,USECS_PER_DAY,&stride_usecs))||
4146+
unlikely(pg_add_s64_overflow(stride_usecs,stride->time,&stride_usecs)))
4147+
ereport(ERROR,
4148+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4149+
errmsg("interval out of range")));
41284150

41294151
if (stride_usecs <=0)
41304152
ereport(ERROR,
41314153
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
41324154
errmsg("stride must be greater than zero")));
41334155

4134-
tm_diff=timestamp-origin;
4135-
tm_delta=tm_diff-tm_diff %stride_usecs;
4156+
if (unlikely(pg_sub_s64_overflow(timestamp,origin,&tm_diff)))
4157+
ereport(ERROR,
4158+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4159+
errmsg("interval out of range")));
4160+
4161+
/* These calculations cannot overflow */
4162+
tm_modulo=tm_diff %stride_usecs;
4163+
tm_delta=tm_diff-tm_modulo;
4164+
result=origin+tm_delta;
41364165

41374166
/*
4138-
* Make sure the returned timestamp is at the start of the bin, even if
4139-
* the origin is in the future.
4167+
* We want to round towards -infinity, not 0, when tm_diff is negative and
4168+
* not a multiple of stride_usecs. This adjustment *can* cause overflow,
4169+
* since the result might now be out of the range origin .. timestamp.
41404170
*/
4141-
if (origin>timestamp&&stride_usecs>1)
4142-
tm_delta-=stride_usecs;
4143-
4144-
result=origin+tm_delta;
4171+
if (tm_modulo<0)
4172+
{
4173+
if (unlikely(pg_sub_s64_overflow(result,stride_usecs,&result))||
4174+
!IS_VALID_TIMESTAMP(result))
4175+
ereport(ERROR,
4176+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4177+
errmsg("timestamp out of range")));
4178+
}
41454179

41464180
PG_RETURN_TIMESTAMPTZ(result);
41474181
}

‎src/test/regress/expected/timestamp.out

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -699,6 +699,13 @@ SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2
699699
Sat Feb 01 00:57:30 2020
700700
(1 row)
701701

702+
-- test roundoff edge case when source < origin
703+
SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00');
704+
date_bin
705+
--------------------------
706+
Thu Feb 01 15:00:00 2024
707+
(1 row)
708+
702709
-- disallow intervals with months or years
703710
SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
704711
ERROR: timestamps cannot be binned into intervals containing months or years
@@ -710,6 +717,13 @@ ERROR: stride must be greater than zero
710717
-- disallow negative intervals
711718
SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
712719
ERROR: stride must be greater than zero
720+
-- test overflow cases
721+
select date_bin('15 minutes'::interval, timestamp '294276-12-30', timestamp '4000-12-20 BC');
722+
ERROR: interval out of range
723+
select date_bin('200000000 days'::interval, '2024-02-01'::timestamp, '2024-01-01'::timestamp);
724+
ERROR: interval out of range
725+
select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamp, '4000-01-01 BC'::timestamp);
726+
ERROR: timestamp out of range
713727
-- Test casting within a BETWEEN qualifier
714728
SELECT d1 - timestamp without time zone '1997-01-02' AS diff
715729
FROM TIMESTAMP_TBL

‎src/test/regress/expected/timestamptz.out

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -743,6 +743,13 @@ SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timesta
743743
Fri Jan 31 16:57:30 2020 PST
744744
(1 row)
745745

746+
-- test roundoff edge case when source < origin
747+
SELECT date_bin('30 minutes'::interval, timestamptz '2024-02-01 15:00:00', timestamptz '2024-02-01 17:00:00');
748+
date_bin
749+
------------------------------
750+
Thu Feb 01 15:00:00 2024 PST
751+
(1 row)
752+
746753
-- disallow intervals with months or years
747754
SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00');
748755
ERROR: timestamps cannot be binned into intervals containing months or years
@@ -754,6 +761,13 @@ ERROR: stride must be greater than zero
754761
-- disallow negative intervals
755762
SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
756763
ERROR: stride must be greater than zero
764+
-- test overflow cases
765+
select date_bin('15 minutes'::interval, timestamptz '294276-12-30', timestamptz '4000-12-20 BC');
766+
ERROR: interval out of range
767+
select date_bin('200000000 days'::interval, '2024-02-01'::timestamptz, '2024-01-01'::timestamptz);
768+
ERROR: interval out of range
769+
select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamptz, '4000-01-01 BC'::timestamptz);
770+
ERROR: timestamp out of range
757771
-- Test casting within a BETWEEN qualifier
758772
SELECT d1 - timestamp with time zone '1997-01-02' AS diff
759773
FROM TIMESTAMPTZ_TBL

‎src/test/regress/sql/timestamp.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -259,6 +259,9 @@ FROM (
259259
-- shift bins using the origin parameter:
260260
SELECT date_bin('5 min'::interval,timestamp'2020-02-01 01:01:01',timestamp'2020-02-01 00:02:30');
261261

262+
-- test roundoff edge case when source < origin
263+
SELECT date_bin('30 minutes'::interval,timestamp'2024-02-01 15:00:00',timestamp'2024-02-01 17:00:00');
264+
262265
-- disallow intervals with months or years
263266
SELECT date_bin('5 months'::interval,timestamp'2020-02-01 01:01:01',timestamp'2001-01-01');
264267
SELECT date_bin('5 years'::interval,timestamp'2020-02-01 01:01:01',timestamp'2001-01-01');
@@ -269,6 +272,11 @@ SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp
269272
-- disallow negative intervals
270273
SELECT date_bin('-2 days'::interval,timestamp'1970-01-01 01:00:00' ,timestamp'1970-01-01 00:00:00');
271274

275+
-- test overflow cases
276+
select date_bin('15 minutes'::interval,timestamp'294276-12-30',timestamp'4000-12-20 BC');
277+
select date_bin('200000000 days'::interval,'2024-02-01'::timestamp,'2024-01-01'::timestamp);
278+
select date_bin('365000 days'::interval,'4400-01-01 BC'::timestamp,'4000-01-01 BC'::timestamp);
279+
272280
-- Test casting within a BETWEEN qualifier
273281
SELECT d1-timestamp without time zone'1997-01-02'AS diff
274282
FROM TIMESTAMP_TBL

‎src/test/regress/sql/timestamptz.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -234,6 +234,9 @@ FROM (
234234
-- shift bins using the origin parameter:
235235
SELECT date_bin('5 min'::interval,timestamptz'2020-02-01 01:01:01+00',timestamptz'2020-02-01 00:02:30+00');
236236

237+
-- test roundoff edge case when source < origin
238+
SELECT date_bin('30 minutes'::interval,timestamptz'2024-02-01 15:00:00',timestamptz'2024-02-01 17:00:00');
239+
237240
-- disallow intervals with months or years
238241
SELECT date_bin('5 months'::interval,timestamp with time zone'2020-02-01 01:01:01+00',timestamp with time zone'2001-01-01+00');
239242
SELECT date_bin('5 years'::interval,timestamp with time zone'2020-02-01 01:01:01+00',timestamp with time zone'2001-01-01+00');
@@ -244,6 +247,11 @@ SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:0
244247
-- disallow negative intervals
245248
SELECT date_bin('-2 days'::interval,timestamp with time zone'1970-01-01 01:00:00+00' ,timestamp with time zone'1970-01-01 00:00:00+00');
246249

250+
-- test overflow cases
251+
select date_bin('15 minutes'::interval,timestamptz'294276-12-30',timestamptz'4000-12-20 BC');
252+
select date_bin('200000000 days'::interval,'2024-02-01'::timestamptz,'2024-01-01'::timestamptz);
253+
select date_bin('365000 days'::interval,'4400-01-01 BC'::timestamptz,'4000-01-01 BC'::timestamptz);
254+
247255
-- Test casting within a BETWEEN qualifier
248256
SELECT d1-timestamp with time zone'1997-01-02'AS diff
249257
FROM TIMESTAMPTZ_TBL

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp