0004-Add-tests-for-inifite-date-timestamp-values.patch

text/x-patch
Download
Filename: 0004-Add-tests-for-inifite-date-timestamp-values.patch
Type: text/x-patch
Part: 3
Message: Re: BRIN minmax multi - incorrect distance for infinite timestamp/date
Patch

Format: format-patch
Series: patch 0004
Subject: Add tests for inifite date/timestamp values
File	+	−
src/test/regress/expected/brin_multi.out	72	0
src/test/regress/sql/brin_multi.sql	54	0
From 7f608536adeaecc0817edfb81b85c91e09e543a9 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
Date: Tue, 17 Oct 2023 19:15:40 +0200
Subject: [PATCH 4/8] Add tests for inifite date/timestamp values

Make sure that infinite values in date/timestamp columns are treated as
if in infinite distance. This means the values should not be merged with
any other values, leaving them as outliers. The test queries a value in
the "gap" and checks the range was eliminated by the BRIN index.
---
 src/test/regress/expected/brin_multi.out | 72 ++++++++++++++++++++++++
 src/test/regress/sql/brin_multi.sql      | 54 ++++++++++++++++++
 2 files changed, 126 insertions(+)

diff --git a/src/test/regress/expected/brin_multi.out b/src/test/regress/expected/brin_multi.out
index d5bd600f8fd..fad0c536b9a 100644
--- a/src/test/regress/expected/brin_multi.out
+++ b/src/test/regress/expected/brin_multi.out
@@ -881,6 +881,78 @@ SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
          Index Cond: (a = '2023-01-01'::date)
 (4 rows)
 
+DROP TABLE brin_date_test;
+RESET enable_seqscan;
+-- test handling of infinite timestamp values
+CREATE TABLE brin_timestamp_test(a TIMESTAMP);
+INSERT INTO brin_timestamp_test VALUES
+ ('-infinity'),   ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'),
+('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'),
+('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'),
+('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'),
+('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'),
+('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'),
+('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'),
+('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'),
+('2000-02-08'), ('2000-02-09');
+CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan = off;
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
+                                  QUERY PLAN                                  
+------------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1)
+   Recheck Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
+   ->  Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1)
+         Index Cond: (a = '2023-01-01 00:00:00'::timestamp without time zone)
+(4 rows)
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
+                                  QUERY PLAN                                  
+------------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_timestamp_test (actual rows=0 loops=1)
+   Recheck Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone)
+   ->  Bitmap Index Scan on brin_timestamp_test_a_idx (actual rows=0 loops=1)
+         Index Cond: (a = '1900-01-01 00:00:00'::timestamp without time zone)
+(4 rows)
+
+DROP TABLE brin_timestamp_test;
+RESET enable_seqscan;
+-- test handling of infinite date values
+CREATE TABLE brin_date_test(a DATE);
+INSERT INTO brin_date_test VALUES
+('-infinity'),   ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'),
+('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'),
+('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'),
+('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'),
+('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'),
+('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'),
+('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'),
+('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'),
+('2000-02-08'), ('2000-02-09');
+CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
+SET enable_seqscan = off;
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
+   Recheck Cond: (a = '2023-01-01'::date)
+   ->  Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
+         Index Cond: (a = '2023-01-01'::date)
+(4 rows)
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Bitmap Heap Scan on brin_date_test (actual rows=0 loops=1)
+   Recheck Cond: (a = '1900-01-01'::date)
+   ->  Bitmap Index Scan on brin_date_test_a_idx (actual rows=0 loops=1)
+         Index Cond: (a = '1900-01-01'::date)
+(4 rows)
+
 DROP TABLE brin_date_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 63d35eacd2d..25f939325f9 100644
--- a/src/test/regress/sql/brin_multi.sql
+++ b/src/test/regress/sql/brin_multi.sql
@@ -648,6 +648,60 @@ SET enable_seqscan = off;
 EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
 SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
 
+DROP TABLE brin_date_test;
+RESET enable_seqscan;
+
+-- test handling of infinite timestamp values
+CREATE TABLE brin_timestamp_test(a TIMESTAMP);
+
+INSERT INTO brin_timestamp_test VALUES
+ ('-infinity'),   ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'),
+('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'),
+('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'),
+('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'),
+('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'),
+('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'),
+('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'),
+('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'),
+('2000-02-08'), ('2000-02-09');
+
+CREATE INDEX ON brin_timestamp_test USING brin (a timestamp_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan = off;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_timestamp_test WHERE a = '2023-01-01'::timestamp;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_timestamp_test WHERE a = '1900-01-01'::timestamp;
+
+DROP TABLE brin_timestamp_test;
+RESET enable_seqscan;
+
+-- test handling of infinite date values
+CREATE TABLE brin_date_test(a DATE);
+
+INSERT INTO brin_date_test VALUES
+('-infinity'),   ('infinity'), ('2000-01-01'), ('2000-01-02'), ('2000-01-03'),
+('2000-01-04'), ('2000-01-05'), ('2000-01-06'), ('2000-01-07'), ('2000-01-08'),
+('2000-01-09'), ('2000-01-10'), ('2000-01-11'), ('2000-01-12'), ('2000-01-13'),
+('2000-01-14'), ('2000-01-15'), ('2000-01-16'), ('2000-01-17'), ('2000-01-18'),
+('2000-01-19'), ('2000-01-20'), ('2000-01-21'), ('2000-01-22'), ('2000-01-23'),
+('2000-01-24'), ('2000-01-25'), ('2000-01-26'), ('2000-01-27'), ('2000-01-28'),
+('2000-01-29'), ('2000-01-30'), ('2000-01-31'), ('2000-02-01'), ('2000-02-02'),
+('2000-02-03'), ('2000-02-04'), ('2000-02-05'), ('2000-02-06'), ('2000-02-07'),
+('2000-02-08'), ('2000-02-09');
+
+CREATE INDEX ON brin_date_test USING brin (a date_minmax_multi_ops) WITH (pages_per_range=1);
+
+SET enable_seqscan = off;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;
+
+EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
+SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
+
 DROP TABLE brin_date_test;
 RESET enable_seqscan;
 RESET datestyle;
-- 
2.41.0