Skip to content

Commit 924e0e2

Browse files
tvondratvondra
committed
Fix minmax-multi distance for extreme interval values
When calculating distance for interval values, the code mostly mimicked interval_mi, i.e. it built a new interval value for the difference. That however does not work for sufficiently distant interval values, when the difference overflows the interval range. Instead, we can calculate the distance directly, without constructing the intermediate (and unnecessary) interval value. Backpatch to 14, where minmax-multi indexes were introduced. Reported-by: Dean Rasheed Reviewed-by: Ashutosh Bapat, Dean Rasheed Backpatch-through: 14 Discussion: https://postgr.es/m/eef0ea8c-4aaa-8d0d-027f-58b1f35dd170@enterprisedb.com
1 parent 497fc92 commit 924e0e2

File tree

3 files changed

+54
-29
lines changed

3 files changed

+54
-29
lines changed

‎src/backend/access/brin/brin_minmax_multi.c

Lines changed: 4 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -2154,45 +2154,20 @@ brin_minmax_multi_distance_interval(PG_FUNCTION_ARGS)
21542154

21552155
Interval *ia = PG_GETARG_INTERVAL_P(0);
21562156
Interval *ib = PG_GETARG_INTERVAL_P(1);
2157-
Interval *result;
21582157

21592158
int64 dayfraction;
21602159
int64 days;
21612160

2162-
result = (Interval *) palloc(sizeof(Interval));
2163-
2164-
result->month = ib->month - ia->month;
2165-
/* overflow check copied from int4mi */
2166-
if (!SAMESIGN(ib->month, ia->month) &&
2167-
!SAMESIGN(result->month, ib->month))
2168-
ereport(ERROR,
2169-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2170-
errmsg("interval out of range")));
2171-
2172-
result->day = ib->day - ia->day;
2173-
if (!SAMESIGN(ib->day, ia->day) &&
2174-
!SAMESIGN(result->day, ib->day))
2175-
ereport(ERROR,
2176-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2177-
errmsg("interval out of range")));
2178-
2179-
result->time = ib->time - ia->time;
2180-
if (!SAMESIGN(ib->time, ia->time) &&
2181-
!SAMESIGN(result->time, ib->time))
2182-
ereport(ERROR,
2183-
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2184-
errmsg("interval out of range")));
2185-
21862161
/*
21872162
* Delta is (fractional) number of days between the intervals. Assume
21882163
* months have 30 days for consistency with interval_cmp_internal. We
21892164
* don't need to be exact, in the worst case we'll build a bit less
21902165
* efficient ranges. But we should not contradict interval_cmp.
21912166
*/
2192-
dayfraction = result->time % USECS_PER_DAY;
2193-
days = result->time / USECS_PER_DAY;
2194-
days += result->month * INT64CONST(30);
2195-
days += result->day;
2167+
dayfraction = (ib->time % USECS_PER_DAY) - (ia->time % USECS_PER_DAY);
2168+
days = (ib->time / USECS_PER_DAY) - (ia->time / USECS_PER_DAY);
2169+
days += (int64) ib->day - (int64) ia->day;
2170+
days += ((int64) ib->month - (int64) ia->month) * INT64CONST(30);
21962171

21972172
/* convert to double precision */
21982173
delta = (double) days + dayfraction / (double) USECS_PER_DAY;

‎src/test/regress/expected/brin_multi.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -558,3 +558,32 @@ SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
558558
DROP TABLE brin_date_test;
559559
RESET enable_seqscan;
560560
RESET datestyle;
561+
-- test handling of overflow for interval values
562+
CREATE TABLE brin_interval_test(a INTERVAL);
563+
INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series(-178000000, -177999980) s(i);
564+
INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series( 177999980, 178000000) s(i);
565+
CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
566+
SET enable_seqscan = off;
567+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
568+
SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
569+
QUERY PLAN
570+
-----------------------------------------------------------------------------
571+
Bitmap Heap Scan on brin_interval_test (actual rows=0 loops=1)
572+
Recheck Cond: (a = '@ 30 years ago'::interval)
573+
-> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1)
574+
Index Cond: (a = '@ 30 years ago'::interval)
575+
(4 rows)
576+
577+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
578+
SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
579+
QUERY PLAN
580+
-----------------------------------------------------------------------------
581+
Bitmap Heap Scan on brin_interval_test (actual rows=0 loops=1)
582+
Recheck Cond: (a = '@ 30 years'::interval)
583+
-> Bitmap Index Scan on brin_interval_test_a_idx (actual rows=0 loops=1)
584+
Index Cond: (a = '@ 30 years'::interval)
585+
(4 rows)
586+
587+
DROP TABLE brin_interval_test;
588+
RESET enable_seqscan;
589+
RESET datestyle;

‎src/test/regress/sql/brin_multi.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -499,3 +499,24 @@ SELECT * FROM brin_date_test WHERE a = '1900-01-01'::date;
499499
DROP TABLE brin_date_test;
500500
RESET enable_seqscan;
501501
RESET datestyle;
502+
503+
-- test handling of overflow for interval values
504+
CREATE TABLE brin_interval_test(a INTERVAL);
505+
506+
INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series(-178000000, -177999980) s(i);
507+
508+
INSERT INTO brin_interval_test SELECT (i || ' years')::interval FROM generate_series( 177999980, 178000000) s(i);
509+
510+
CREATE INDEX ON brin_interval_test USING brin (a interval_minmax_multi_ops) WITH (pages_per_range=1);
511+
512+
SET enable_seqscan = off;
513+
514+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
515+
SELECT * FROM brin_interval_test WHERE a = '-30 years'::interval;
516+
517+
EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
518+
SELECT * FROM brin_interval_test WHERE a = '30 years'::interval;
519+
520+
DROP TABLE brin_interval_test;
521+
RESET enable_seqscan;
522+
RESET datestyle;

0 commit comments

Comments
 (0)