Horton

Re: BUG #19000: gist index returns inconsistent result with gist_inet_ops

Peter Eisentraut <peter@eisentraut.org>

View thread
From: Peter Eisentraut <peter@eisentraut.org>
To: Richard Guo <guofenglinux@gmail.com>, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
Date: 2025-07-31T15:43:18Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. ecpg: Fix memory leaks in ecpg_auto_prepare()

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.