v4-0007-Add-rounding-cases-tests-for-date.patch

From 13cbcbcbd958c742cd4a60ae690aa9b63338c399 Mon Sep 17 00:00:00 2001
From: Arseniy Mukhin <arseniy.mukhin.dev@gmail.com>
Date: Wed, 2 Jul 2025 10:29:07 +0300
Subject: [PATCH v4 7/8] Add rounding cases tests for date

---
 contrib/btree_gin/expected/date.out | 85 +++++++++++++++++++++++++++++
 contrib/btree_gin/sql/date.sql      | 16 ++++++
 2 files changed, 101 insertions(+)

diff --git a/contrib/btree_gin/expected/date.out b/contrib/btree_gin/expected/date.out
index d638299b218..2372f333ffb 100644
--- a/contrib/btree_gin/expected/date.out
+++ b/contrib/btree_gin/expected/date.out
@@ -319,3 +319,88 @@ SELECT * FROM test_date WHERE i>'infinity'::timestamptz ORDER BY i;
 ---
 (0 rows)
 
+-- Check rounding cases
+-- '2004-10-25 00:00:01' rounds to '2004-10-25' for date.
+-- '2004-10-25 23:59:59' also rounds to '2004-10-25', so it's the same case as '2004-10-25 00:00:01'
+SELECT * FROM test_date WHERE i < '2004-10-25 00:00:01'::timestamp ORDER BY i;
+     i      
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+(4 rows)
+
+SELECT * FROM test_date WHERE i <= '2004-10-25 00:00:01'::timestamp ORDER BY i;
+     i      
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+(4 rows)
+
+SELECT * FROM test_date WHERE i = '2004-10-25 00:00:01'::timestamp ORDER BY i;
+ i 
+---
+(0 rows)
+
+SELECT * FROM test_date WHERE i > '2004-10-25 00:00:01'::timestamp ORDER BY i;
+     i      
+------------
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(4 rows)
+
+SELECT * FROM test_date WHERE i >= '2004-10-25 00:00:01'::timestamp ORDER BY i;
+     i      
+------------
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(4 rows)
+
+SELECT * FROM test_date WHERE i < '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+     i      
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+(4 rows)
+
+SELECT * FROM test_date WHERE i <= '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+     i      
+------------
+ -infinity
+ 10-23-2004
+ 10-24-2004
+ 10-25-2004
+(4 rows)
+
+SELECT * FROM test_date WHERE i = '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+ i 
+---
+(0 rows)
+
+SELECT * FROM test_date WHERE i > '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+     i      
+------------
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(4 rows)
+
+SELECT * FROM test_date WHERE i >= '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+     i      
+------------
+ 10-26-2004
+ 10-27-2004
+ 10-28-2004
+ infinity
+(4 rows)
+
diff --git a/contrib/btree_gin/sql/date.sql b/contrib/btree_gin/sql/date.sql
index 6bd6a08da3b..0357c20d5d7 100644
--- a/contrib/btree_gin/sql/date.sql
+++ b/contrib/btree_gin/sql/date.sql
@@ -66,3 +66,19 @@ SELECT * FROM test_date WHERE i<='infinity'::timestamptz ORDER BY i;
 SELECT * FROM test_date WHERE i='infinity'::timestamptz ORDER BY i;
 SELECT * FROM test_date WHERE i>='infinity'::timestamptz ORDER BY i;
 SELECT * FROM test_date WHERE i>'infinity'::timestamptz ORDER BY i;
+
+-- Check rounding cases
+-- '2004-10-25 00:00:01' rounds to '2004-10-25' for date.
+-- '2004-10-25 23:59:59' also rounds to '2004-10-25', so it's the same case as '2004-10-25 00:00:01'
+
+SELECT * FROM test_date WHERE i < '2004-10-25 00:00:01'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i <= '2004-10-25 00:00:01'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i = '2004-10-25 00:00:01'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i > '2004-10-25 00:00:01'::timestamp ORDER BY i;
+SELECT * FROM test_date WHERE i >= '2004-10-25 00:00:01'::timestamp ORDER BY i;
+
+SELECT * FROM test_date WHERE i < '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i <= '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i = '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i > '2004-10-25 00:00:01'::timestamptz ORDER BY i;
+SELECT * FROM test_date WHERE i >= '2004-10-25 00:00:01'::timestamptz ORDER BY i;
\ No newline at end of file
-- 
2.43.0