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

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 741dfbae4a50a8dc6ee3313c10bb979fb86fa53b..c12d03e58326a88dc1d420d2026d315c2185ff2e 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10008,12 +10008,8 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
    </para>
 
    <para>
-    Negative intervals are allowed and are treated the same as positive intervals.
-   </para>
-
-   <para>
-    The <parameter>stride</parameter> interval cannot contain units of month
-    or larger.
+    The <parameter>stride</parameter> interval must be greater than zero and
+    cannot contain units of month or larger.
    </para>
   </sect2>
 

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index ea847576cd2aa532dfcdbc961f32a490bcf75964..1c0bf0aa5c803a6a667a0bc48e07d4f59c95f4fb 100644 (file)
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -3843,10 +3843,10 @@ timestamp_bin(PG_FUNCTION_ARGS)
 
    stride_usecs = stride->day * USECS_PER_DAY + stride->time;
 
-   if (stride_usecs == 0)
+   if (stride_usecs <= 0)
        ereport(ERROR,
                (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
-                errmsg("stride cannot equal zero")));
+                errmsg("stride must be greater than zero")));
 
    tm_diff = timestamp - origin;
    tm_delta = tm_diff - tm_diff % stride_usecs;
@@ -4026,10 +4026,10 @@ timestamptz_bin(PG_FUNCTION_ARGS)
 
    stride_usecs = stride->day * USECS_PER_DAY + stride->time;
 
-   if (stride_usecs == 0)
+   if (stride_usecs <= 0)
        ereport(ERROR,
                (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
-                errmsg("stride cannot equal zero")));
+                errmsg("stride must be greater than zero")));
 
    tm_diff = timestamp - origin;
    tm_delta = tm_diff - tm_diff % stride_usecs;

diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 7a2cbd9b3f0d0dc6643c9fe51d6945b9d8c3bc86..1a2d48cae96762ff6520df049db6af8f52d797a7 100644 (file)
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -706,7 +706,10 @@ SELECT date_bin('5 years'::interval,  timestamp '2020-02-01 01:01:01', timestamp
 ERROR:  timestamps cannot be binned into intervals containing months or years
 -- disallow zero intervals
 SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
-ERROR:  stride cannot equal zero
+ERROR:  stride must be greater than zero
+-- disallow negative intervals
+SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
+ERROR:  stride must be greater than zero
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL

diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index be6ead0fb509f4c30361ff2a8617652979f2a738..990c4eddf13654a2b58e78188abbdd3c8e072c1b 100644 (file)
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -750,7 +750,10 @@ SELECT date_bin('5 years'::interval,  timestamp with time zone '2020-02-01 01:01
 ERROR:  timestamps cannot be binned into intervals containing months or years
 -- disallow zero intervals
 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');
-ERROR:  stride cannot equal zero
+ERROR:  stride must be greater than zero
+-- disallow negative intervals
+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');
+ERROR:  stride must be greater than zero
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL

diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 7307a2409271b7420248e7029bbb41a828b1d09b..e011e779ea2da20393f624505ad6dea7f9582438 100644 (file)
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -266,6 +266,9 @@ SELECT date_bin('5 years'::interval,  timestamp '2020-02-01 01:01:01', timestamp
 -- disallow zero intervals
 SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
 
+-- disallow negative intervals
+SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00');
+
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp without time zone '1997-01-02' AS diff
   FROM TIMESTAMP_TBL

diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 3642d8c143cad3faaa4f37b437a05265bf3dc6a1..b18821de53b25749cc876b6f263d28925e7990e6 100644 (file)
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -241,6 +241,9 @@ SELECT date_bin('5 years'::interval,  timestamp with time zone '2020-02-01 01:01
 -- disallow zero intervals
 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');
 
+-- disallow negative intervals
+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');
+
 -- Test casting within a BETWEEN qualifier
 SELECT d1 - timestamp with time zone '1997-01-02' AS diff
   FROM TIMESTAMPTZ_TBL