Skip to content
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Commit 3ba70d4

Browse files
j-naylorj-naylor
committedJul 28, 2021
Disallow negative strides in date_bin()
It's not clear what the semantics of negative strides would be, so throw an error instead. Per report from Bauyrzhan Sakhariyev Reviewed-by: Tom Lane, Michael Paquier Discussion: https://www.postgresql.org/message-id/CAKpL73vZmLuFVuwF26FJ%2BNk11PVHhAnQRoREFcA03x7znRoFvA%40mail.gmail.com Backpatch to v14
1 parent ed1884a commit 3ba70d4

File tree

6 files changed

+20
-12
lines changed

6 files changed

+20
-12
lines changed
 

‎doc/src/sgml/func.sgml

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -10008,12 +10008,8 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
1000810008
</para>
1000910009

1001010010
<para>
10011-
Negative intervals are allowed and are treated the same as positive intervals.
10012-
</para>
10013-
10014-
<para>
10015-
The <parameter>stride</parameter> interval cannot contain units of month
10016-
or larger.
10011+
The <parameter>stride</parameter> interval must be greater than zero and
10012+
cannot contain units of month or larger.
1001710013
</para>
1001810014
</sect2>
1001910015

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

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3843,10 +3843,10 @@ timestamp_bin(PG_FUNCTION_ARGS)
38433843

38443844
stride_usecs = stride->day * USECS_PER_DAY + stride->time;
38453845

3846-
if (stride_usecs == 0)
3846+
if (stride_usecs <= 0)
38473847
ereport(ERROR,
38483848
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3849-
errmsg("stride cannot equal zero")));
3849+
errmsg("stride must be greater than zero")));
38503850

38513851
tm_diff = timestamp - origin;
38523852
tm_delta = tm_diff - tm_diff % stride_usecs;
@@ -4026,10 +4026,10 @@ timestamptz_bin(PG_FUNCTION_ARGS)
40264026

40274027
stride_usecs = stride->day * USECS_PER_DAY + stride->time;
40284028

4029-
if (stride_usecs == 0)
4029+
if (stride_usecs <= 0)
40304030
ereport(ERROR,
40314031
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4032-
errmsg("stride cannot equal zero")));
4032+
errmsg("stride must be greater than zero")));
40334033

40344034
tm_diff = timestamp - origin;
40354035
tm_delta = tm_diff - tm_diff % stride_usecs;

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

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -706,7 +706,10 @@ SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp
706706
ERROR: timestamps cannot be binned into intervals containing months or years
707707
-- disallow zero intervals
708708
SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
709-
ERROR: stride cannot equal zero
709+
ERROR: stride must be greater than zero
710+
-- disallow negative intervals
711+
SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
712+
ERROR: stride must be greater than zero
710713
-- Test casting within a BETWEEN qualifier
711714
SELECT d1 - timestamp without time zone '1997-01-02' AS diff
712715
FROM TIMESTAMP_TBL

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

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -750,7 +750,10 @@ SELECT date_bin('5 years'::interval, timestamp with time zone '2020-02-01 01:01
750750
ERROR: timestamps cannot be binned into intervals containing months or years
751751
-- disallow zero intervals
752752
SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
753-
ERROR: stride cannot equal zero
753+
ERROR: stride must be greater than zero
754+
-- disallow negative intervals
755+
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');
756+
ERROR: stride must be greater than zero
754757
-- Test casting within a BETWEEN qualifier
755758
SELECT d1 - timestamp with time zone '1997-01-02' AS diff
756759
FROM TIMESTAMPTZ_TBL

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -266,6 +266,9 @@ SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp
266266
-- disallow zero intervals
267267
SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
268268

269+
-- disallow negative intervals
270+
SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
271+
269272
-- Test casting within a BETWEEN qualifier
270273
SELECT d1 - timestamp without time zone '1997-01-02' AS diff
271274
FROM TIMESTAMP_TBL

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -241,6 +241,9 @@ SELECT date_bin('5 years'::interval, timestamp with time zone '2020-02-01 01:01
241241
-- disallow zero intervals
242242
SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00');
243243

244+
-- disallow negative intervals
245+
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');
246+
244247
-- Test casting within a BETWEEN qualifier
245248
SELECT d1 - timestamp with time zone '1997-01-02' AS diff
246249
FROM TIMESTAMPTZ_TBL

0 commit comments

Comments
 (0)
Failed to load comments.