From 9f27881e99186376b5343efc0719c21cde9d8a32 Mon Sep 17 00:00:00 2001
From: Moaaz Assali <ma5679@nyu.edu>
Date: Wed, 28 Feb 2024 15:29:19 +0000
Subject: [PATCH v2] Regression test in timestamp.sql for date_bin() function

Added several test cases for timestamp.sql to test for integer overflow issues when
valid timestamps are provided to ensure correct results from date_bin().

Added test case to ensure no unnecessary stride interval is subtracted when
timestamp is already a valid binned date when timestamp < origin.

Requires attached v2-0001 patch in the same email thread to pass the new test cases.
---
 src/test/regress/expected/timestamp.out | 35 +++++++++++++++++++++++++
 src/test/regress/sql/timestamp.sql      | 15 +++++++++++
 2 files changed, 50 insertions(+)

diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 835f0e5762..e473913d75 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -736,6 +736,41 @@ SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2
  Sat Feb 01 00:57:30 2020
 (1 row)
 
+-- test date_bin output is correct when source < origin and source is equivalent to a valid binned date
+SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00');
+         date_bin         
+--------------------------
+ Thu Feb 01 15:00:00 2024
+(1 row)
+
+-- test for invalid results due to integer overflow when when source ~ INT64_MAX and origin ~ INT64_MIN
+select date_bin('15 minutes'::interval, timestamp '294276-12-30 10:24:00', timestamp '4000-12-20 23:00:00 BC');
+          date_bin          
+----------------------------
+ Sat Dec 30 10:15:00 294276
+(1 row)
+
+-- test for invalid results due to integer overflow when when source ~ INT64_MIN and origin ~ INT64_MAX
+select date_bin('15 minutes'::interval, timestamp '4000-12-20 23:42:32 BC', timestamp '294276-12-30 10:30:00');
+          date_bin           
+-----------------------------
+ Thu Dec 20 23:30:00 4000 BC
+(1 row)
+
+-- test for invalid results due to integer overflow when when source ~ INT64_MAX and origin ~ INT64_MAX
+select date_bin('15 minutes'::interval, timestamp '294276-12-30 10:24:00', timestamp '294276-12-30 10:30:00');
+          date_bin          
+----------------------------
+ Sat Dec 30 10:15:00 294276
+(1 row)
+
+-- test for invalid results due to integer overflow when when source ~ INT64_MIN and origin ~ INT64_MIN
+select date_bin('15 minutes'::interval, timestamp '4000-12-20 23:42:32 BC', timestamp '4000-10-30 23:30:00 BC');
+          date_bin           
+-----------------------------
+ Thu Dec 20 23:30:00 4000 BC
+(1 row)
+
 -- disallow intervals with months or years
 SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
 ERROR:  timestamps cannot be binned into intervals containing months or years
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index ea12ffd18d..8a608c132a 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -268,6 +268,21 @@ FROM (
 -- shift bins using the origin parameter:
 SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30');
 
+-- test date_bin output is correct when source < origin and source is equivalent to a valid binned date
+SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00');
+
+-- test for invalid results due to integer overflow when when source ~ INT64_MAX and origin ~ INT64_MIN
+select date_bin('15 minutes'::interval, timestamp '294276-12-30 10:24:00', timestamp '4000-12-20 23:00:00 BC');
+
+-- test for invalid results due to integer overflow when when source ~ INT64_MIN and origin ~ INT64_MAX
+select date_bin('15 minutes'::interval, timestamp '4000-12-20 23:42:32 BC', timestamp '294276-12-30 10:30:00');
+
+-- test for invalid results due to integer overflow when when source ~ INT64_MAX and origin ~ INT64_MAX
+select date_bin('15 minutes'::interval, timestamp '294276-12-30 10:24:00', timestamp '294276-12-30 10:30:00');
+
+-- test for invalid results due to integer overflow when when source ~ INT64_MIN and origin ~ INT64_MIN
+select date_bin('15 minutes'::interval, timestamp '4000-12-20 23:42:32 BC', timestamp '4000-10-30 23:30:00 BC');
+
 -- disallow intervals with months or years
 SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
 SELECT date_bin('5 years'::interval,  timestamp '2020-02-01 01:01:01', timestamp '2001-01-01');
-- 
2.34.1