agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: gsaviane@gmail.com
Subject: BUG #19582: Query fails on mixed IPv4 IPv6 data when index added
Date: Mon, 27 Jul 2026 16:18:59 +0000
Message-ID: <19582-b9e0113754eb1d62@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19582
Logged by:          Giorgio Saviane
Email address:      gsaviane@gmail.com
PostgreSQL version: 17.10
Operating system:   Ubuntu 22.04
Description:        

The attached SQL code snippet shows how to reproduce the problem. A table
with generated mixed IPv4 and IPv6 data, when queried filtering by IP family
fails when a conditional index is being added to the table. I suspect the
planner pre-executes the query on some samples, applying the index condition
only partially.
This is the output I get when executing the SQL:

BEGIN
CREATE TABLE
INSERT 0 20000000
  count
---------
 8355840
(1 row)

CREATE INDEX
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=1913.76..1913.77 rows=1 width=8)
   ->  Bitmap Heap Scan on test_inet  (cost=8.31..1912.51 rows=500 width=0)
         Recheck Cond: (((addr & '255.0.0.0'::inet) = '1.0.0.0'::inet) AND
(family(addr) = 4))
         ->  Bitmap Index Scan on test_inet_expr_idx  (cost=0.00..8.18
rows=500 width=0)
               Index Cond: ((addr & '255.0.0.0'::inet) = '1.0.0.0'::inet)
(5 rows)

ERROR:  cannot AND inet values of different sizes
ROLLBACK

=============== CODE HERE ==============
begin;

-- Create a table to host inet addresses
create table test_inet (
  addr inet not null
);

-- Generate mixed IPv4 and IPv6 data.
-- The amout of data makes the difference.
-- Experimentally, lower than 13M it does not reproduce.
-- It might depend on server configuration
insert into test_inet(addr)
  select (case
    mod(s,2) when 0
      then 'fe80:1::'::inet + s
    else '1.1.0.0'::inet + s
  end) from generate_series(1, 20000000) s;


-- Count filtered by IPv4 family and subnet.
-- Suceeds without an index
select count(*)
  from test_inet
  where family(addr) = 4
    and addr & '255.0.0.0'::inet = '1.0.0.0';

-- Now create an index filtered only on IPv4 family
create index
  on test_inet((addr & '255.0.0.0'::inet))
  where family(addr) = 4;

-- The plan should show how subnet and family
-- conditions are split due to rechecking
explain select count(*)
  from test_inet
  where family(addr) = 4
    and addr & '255.0.0.0'::inet = '1.0.0.0';

-- Same count filtered by IPv4 family and subnet fails with:
-- ERROR:  cannot AND inet values of different sizes
select count(*)
  from test_inet
  where family(addr) = 4
    and addr & '255.0.0.0'::inet = '1.0.0.0';

rollback;
===============  END CODE ==============

view thread (2+ messages)  latest in thread

Message-ID: <19582-b9e0113754eb1d62@postgresql.org>
Permalink:  ../19582-b9e0113754eb1d62@postgresql.org/
Also on:    postgresql.org/message-id/19582-b9e0113754eb1d62@postgresql.org

reply

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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, gsaviane@gmail.com
  Subject: Re: BUG #19582: Query fails on mixed IPv4 IPv6 data when index added
  In-Reply-To: <19582-b9e0113754eb1d62@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox