From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | btree_gin: Incorrect leftmost interval value |
Date: | 2023-10-27 09:26:53 |
Message-ID: | CAEZATCV80+gOfF8ehNUUfaKBZgZMDfCfL-g1HhWGb6kC3rpDfw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
In contrib/btree_gin, leftmostvalue_interval() does this:
leftmostvalue_interval(void)
{
Interval *v = palloc(sizeof(Interval));
v->time = DT_NOBEGIN;
v->day = 0;
v->month = 0;
return IntervalPGetDatum(v);
}
which is a long way short of the minimum possible interval value.
As a result, a < or <= query using a GIN index on an interval column
may miss values. For example:
CREATE EXTENSION btree_gin;
CREATE TABLE foo (a interval);
INSERT INTO foo VALUES ('-1000000 years');
CREATE INDEX foo_idx ON foo USING gin (a);
SET enable_seqscan = off;
SELECT * FROM foo WHERE a < '1 year';
a
---
(0 rows)
Attached is a patch fixing this by setting all the fields to their
minimum values, which is guaranteed to be less than any other
interval.
Note that this doesn't affect the contents of the index itself, so
reindexing is not necessary.
Regards,
Dean
Attachment | Content-Type | Size |
---|---|---|
fix-leftmostvalue_interval.patch | text/x-patch | 2.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Kapila | 2023-10-27 09:27:10 | Re: A recent message added to pg_upgade |
Previous Message | tender wang | 2023-10-27 09:05:49 | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |