From: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> |
---|---|
To: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: btree_gin: Incorrect leftmost interval value |
Date: | 2023-10-27 12:56:14 |
Message-ID: | CAExHW5tHqdiBcKD5hvV31RDQ8V7WR+u9qdWrW2MC9t=1k=ZMfA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, Oct 27, 2023 at 2:57 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> 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.
Should we change this to call INTERVAL_NOBEGIN() to be added by
infinite interval patches? It's the same effect but looks similar to
leftmostvalue_timestamp/float8 etc. It will need to wait for the
infinite interval patches to commit but I guess, the wait won't be too
long and the outcome will be better. I can include this change in
those patches.
--
Best Wishes,
Ashutosh Bapat
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Rasheed | 2023-10-27 13:05:21 | Re: btree_gin: Incorrect leftmost interval value |
Previous Message | Étienne BERSAC | 2023-10-27 12:54:48 | Re: RFC: Logging plan of the running query |