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

Commitfe3b1b5

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 parent09f0988 commitfe3b1b5

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
@@ -3870,8 +3870,9 @@ timestamp_bin(PG_FUNCTION_ARGS)
38703870
Timestamptimestamp=PG_GETARG_TIMESTAMP(1);
38713871
Timestamporigin=PG_GETARG_TIMESTAMP(2);
38723872
Timestampresult,
3873-
tm_diff,
38743873
stride_usecs,
3874+
tm_diff,
3875+
tm_modulo,
38753876
tm_delta;
38763877

38773878
if (TIMESTAMP_NOT_FINITE(timestamp))
@@ -3887,24 +3888,40 @@ timestamp_bin(PG_FUNCTION_ARGS)
38873888
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
38883889
errmsg("timestamps cannot be binned into intervals containing months or years")));
38893890

3890-
stride_usecs=stride->day*USECS_PER_DAY+stride->time;
3891+
if (unlikely(pg_mul_s64_overflow(stride->day,USECS_PER_DAY,&stride_usecs))||
3892+
unlikely(pg_add_s64_overflow(stride_usecs,stride->time,&stride_usecs)))
3893+
ereport(ERROR,
3894+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3895+
errmsg("interval out of range")));
38913896

38923897
if (stride_usecs <=0)
38933898
ereport(ERROR,
38943899
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
38953900
errmsg("stride must be greater than zero")));
38963901

3897-
tm_diff=timestamp-origin;
3898-
tm_delta=tm_diff-tm_diff %stride_usecs;
3902+
if (unlikely(pg_sub_s64_overflow(timestamp,origin,&tm_diff)))
3903+
ereport(ERROR,
3904+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3905+
errmsg("interval out of range")));
3906+
3907+
/* These calculations cannot overflow */
3908+
tm_modulo=tm_diff %stride_usecs;
3909+
tm_delta=tm_diff-tm_modulo;
3910+
result=origin+tm_delta;
38993911

39003912
/*
3901-
* Make sure the returned timestamp is at the start of the bin, even if
3902-
* the origin is in the future.
3913+
* We want to round towards -infinity, not 0, when tm_diff is negative and
3914+
* not a multiple of stride_usecs. This adjustment *can* cause overflow,
3915+
* since the result might now be out of the range origin .. timestamp.
39033916
*/
3904-
if (origin>timestamp&&stride_usecs>1)
3905-
tm_delta-=stride_usecs;
3906-
3907-
result=origin+tm_delta;
3917+
if (tm_modulo<0)
3918+
{
3919+
if (unlikely(pg_sub_s64_overflow(result,stride_usecs,&result))||
3920+
!IS_VALID_TIMESTAMP(result))
3921+
ereport(ERROR,
3922+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3923+
errmsg("timestamp out of range")));
3924+
}
39083925

39093926
PG_RETURN_TIMESTAMP(result);
39103927
}
@@ -4055,6 +4072,7 @@ timestamptz_bin(PG_FUNCTION_ARGS)
40554072
TimestampTzresult,
40564073
stride_usecs,
40574074
tm_diff,
4075+
tm_modulo,
40584076
tm_delta;
40594077

40604078
if (TIMESTAMP_NOT_FINITE(timestamp))
@@ -4070,24 +4088,40 @@ timestamptz_bin(PG_FUNCTION_ARGS)
40704088
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
40714089
errmsg("timestamps cannot be binned into intervals containing months or years")));
40724090

4073-
stride_usecs=stride->day*USECS_PER_DAY+stride->time;
4091+
if (unlikely(pg_mul_s64_overflow(stride->day,USECS_PER_DAY,&stride_usecs))||
4092+
unlikely(pg_add_s64_overflow(stride_usecs,stride->time,&stride_usecs)))
4093+
ereport(ERROR,
4094+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4095+
errmsg("interval out of range")));
40744096

40754097
if (stride_usecs <=0)
40764098
ereport(ERROR,
40774099
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
40784100
errmsg("stride must be greater than zero")));
40794101

4080-
tm_diff=timestamp-origin;
4081-
tm_delta=tm_diff-tm_diff %stride_usecs;
4102+
if (unlikely(pg_sub_s64_overflow(timestamp,origin,&tm_diff)))
4103+
ereport(ERROR,
4104+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4105+
errmsg("interval out of range")));
4106+
4107+
/* These calculations cannot overflow */
4108+
tm_modulo=tm_diff %stride_usecs;
4109+
tm_delta=tm_diff-tm_modulo;
4110+
result=origin+tm_delta;
40824111

40834112
/*
4084-
* Make sure the returned timestamp is at the start of the bin, even if
4085-
* the origin is in the future.
4113+
* We want to round towards -infinity, not 0, when tm_diff is negative and
4114+
* not a multiple of stride_usecs. This adjustment *can* cause overflow,
4115+
* since the result might now be out of the range origin .. timestamp.
40864116
*/
4087-
if (origin>timestamp&&stride_usecs>1)
4088-
tm_delta-=stride_usecs;
4089-
4090-
result=origin+tm_delta;
4117+
if (tm_modulo<0)
4118+
{
4119+
if (unlikely(pg_sub_s64_overflow(result,stride_usecs,&result))||
4120+
!IS_VALID_TIMESTAMP(result))
4121+
ereport(ERROR,
4122+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4123+
errmsg("timestamp out of range")));
4124+
}
40914125

40924126
PG_RETURN_TIMESTAMPTZ(result);
40934127
}

‎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