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

Added several test cases for timestamptz.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/timestamptz.out | 35 +++++++++++++++++++++++
 src/test/regress/sql/timestamptz.sql      | 15 ++++++++++
 2 files changed, 50 insertions(+)

diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index a084357480..5315b45e9c 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -780,6 +780,41 @@ SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timesta
  Fri Jan 31 16:57:30 2020 PST
 (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, timestamptz '2024-02-01 15:00:00', timestamptz '2024-02-01 17:00:00');
+           date_bin           
+------------------------------
+ Thu Feb 01 15:00:00 2024 PST
+(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, timestamptz '294276-12-30 10:24:00', timestamptz '4000-12-20 23:00:00 BC');
+            date_bin            
+--------------------------------
+ Sat Dec 30 10:15:00 294276 PST
+(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, timestamptz '4000-12-20 23:42:32 BC', timestamptz '294276-12-30 10:30:00');
+            date_bin             
+---------------------------------
+ Thu Dec 20 23:30:00 4000 PST 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, timestamptz '294276-12-30 10:24:00', timestamptz '294276-12-30 10:30:00');
+            date_bin            
+--------------------------------
+ Sat Dec 30 10:15:00 294276 PST
+(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, timestamptz '4000-12-20 23:42:32 BC', timestamptz '4000-10-30 23:30:00 BC');
+            date_bin             
+---------------------------------
+ Thu Dec 20 23:30:00 4000 PST BC
+(1 row)
+
 -- disallow intervals with months or years
 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');
 ERROR:  timestamps cannot be binned into intervals containing months or years
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index a2dcd5f5d8..ec2839eafc 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -243,6 +243,21 @@ FROM (
 -- shift bins using the origin parameter:
 SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00');
 
+-- test date_bin output is correct when source < origin and source is equivalent to a valid binned date
+SELECT date_bin('30 minutes'::interval, timestamptz '2024-02-01 15:00:00', timestamptz '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, timestamptz '294276-12-30 10:24:00', timestamptz '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, timestamptz '4000-12-20 23:42:32 BC', timestamptz '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, timestamptz '294276-12-30 10:24:00', timestamptz '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, timestamptz '4000-12-20 23:42:32 BC', timestamptz '4000-10-30 23:30:00 BC');
+
 -- disallow intervals with months or years
 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');
 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');
-- 
2.34.1