From: Peter Eisentraut <peter@eisentraut.org>
To: Richard Guo <guofenglinux@gmail.com>
To: exclusion@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops
Date: Thu, 31 Jul 2025 17:43:18 +0200
Message-ID: <d570c46d-1e20-4766-93de-c6fcbc18f282@eisentraut.org> (raw)
In-Reply-To: <CAMbWs49vGc45oQ98bhx4dFpXOsimUX9TTnXOXG=i6+X6sAqBRw@mail.gmail.com>
References: <19000-2525470d200672ab@postgresql.org>
<CAMbWs49vGc45oQ98bhx4dFpXOsimUX9TTnXOXG=i6+X6sAqBRw@mail.gmail.com>
On 28.07.25 04:23, Richard Guo wrote:
> On Mon, Jul 28, 2025 at 5:16 AM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> CREATE EXTENSION btree_gist;
>>
>> CREATE TABLE t AS SELECT '192.168.1.0/25'::inet AS i;
>>
>> SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
>> i
>> ----------------
>> 192.168.1.0/25
>>
>> CREATE INDEX ON t USING gist(i);
>>
>> SELECT * FROM t WHERE i << '192.168.1.0/24'::cidr;
>> i
>> ---
>> (0 rows)
>
> It seems that with gist_inet_ops the index's opfamily does not support
> the '<<' operator correctly.
>
> With inet_ops, the query works correctly.
The generated index condition is the same for the gist and the btree
index, namely:
((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
If I run the query with the lower bound directly, like
SELECT * FROM t WHERE i > '192.168.1.0/24'::inet;
then I also get no result rows for the gist index, but I do get one for
the btree index. (The upper bound works correctly in either case.)
This can be reproduced even in PG17.
My mind is a bit boggled about what the actual meaning of the > operator
should be in this case, but in any case it seems there might be a
pre-existing discrepancy in the btree_gist module.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: peter@eisentraut.org, guofenglinux@gmail.com, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops
In-Reply-To: <d570c46d-1e20-4766-93de-c6fcbc18f282@eisentraut.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox