From 401bed518f89454eac4bf0a8b871ccae57cd53f9 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Tue, 17 Oct 2023 20:49:01 +0200
Subject: [PATCH 8/8] Add more tests for BRIN on interval values

Make sure we don't build inefficient ranges on large interval values.
---
 src/test/regress/expected/brin_multi.out | 49 ++++++++++++++++++++++++
 src/test/regress/sql/brin_multi.sql      | 40 +++++++++++++++++++
 2 files changed, 89 insertions(+)

diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index e07042cd8a1..d361b344d42 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -977,3 +977,52 @@ INSERT INTO brin_interval_test VALUES
 ('-178000000 years'), ('178000000 years');
 CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
 DROP TABLE brin_interval_test;
+-- test handling of long intervals
+CREATE TABLE brin_interval_test(a INTERVAL);
+INSERT INTO brin_interval_test VALUES
+('-1 years'), ('1 years'),
+('-2 years'), ('2 years'),
+('-3 years'), ('3 years'),
+('-4 years'), ('4 years'),
+('-5 years'), ('5 years'),
+('-6 years'), ('6 years'),
+('-7 years'), ('7 years'),
+('-8 years'), ('8 years'),
+('-9 years'), ('9 years'),
+('-10 years'), ('10 years'),
+('-11 years'), ('11 years'),
+('-12 years'), ('12 years'),
+('-13 years'), ('13 years'),
+('-14 years'), ('14 years'),
+('-15 years'), ('15 years'),
+('-16 years'), ('16 years'),
+('-17 years'), ('17 years'),
+('-18 years'), ('18 years'),
+('-19 years'), ('19 years'),
+('-20 years'), ('20 years'),
+('-178000000 years'), ('178000000 years');
+CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan = off;
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_interval_test (actual rows=0 loops=1)
+   Recheck Cond: (a = '@ 30 years ago'::interval)
+   ->  Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1)
+         Index Cond: (a = '@ 30 years ago'::interval)
+(4 rows)
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_interval_test (actual rows=0 loops=1)
+   Recheck Cond: (a = '@ 30 years'::interval)
+   ->  Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1)
+         Index Cond: (a = '@ 30 years'::interval)
+(4 rows)
+
+DROP TABLE brin_interval_test;
+RESET enable_seqscan;
+RESET datestyle;
diff --git a/src/test/regress/sql/brin_multi.sql b/src/test/regress/sql/brin_multi.sql
index 302b41e7fd0..e22ce0c138b 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -729,3 +729,43 @@ INSERT INTO brin_interval_test VALUES
 
 CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
 DROP TABLE brin_interval_test;
+
+-- test handling of long intervals
+CREATE TABLE brin_interval_test(a INTERVAL);
+
+INSERT INTO brin_interval_test VALUES
+('-1 years'), ('1 years'),
+('-2 years'), ('2 years'),
+('-3 years'), ('3 years'),
+('-4 years'), ('4 years'),
+('-5 years'), ('5 years'),
+('-6 years'), ('6 years'),
+('-7 years'), ('7 years'),
+('-8 years'), ('8 years'),
+('-9 years'), ('9 years'),
+('-10 years'), ('10 years'),
+('-11 years'), ('11 years'),
+('-12 years'), ('12 years'),
+('-13 years'), ('13 years'),
+('-14 years'), ('14 years'),
+('-15 years'), ('15 years'),
+('-16 years'), ('16 years'),
+('-17 years'), ('17 years'),
+('-18 years'), ('18 years'),
+('-19 years'), ('19 years'),
+('-20 years'), ('20 years'),
+('-178000000 years'), ('178000000 years');
+
+CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan = off;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
+
+DROP TABLE brin_interval_test;
+RESET enable_seqscan;
+RESET datestyle;
-- 
2.41.0