Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19582: Query fails on mixed IPv4 IPv6 data when index added
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: gsaviane@gmail.com
Reply-To: gsaviane@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Mon, 27 Jul 2026 16:18:59 +0000
Message-ID: <19582-b9e0113754eb1d62@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19582-b9e0113754eb1d62%40postgresql.org>
Precedence: bulk

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:       =20

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=3D1913.76..1913.77 rows=3D1 width=3D8)
   ->  Bitmap Heap Scan on test_inet  (cost=3D8.31..1912.51 rows=3D500 widt=
h=3D0)
         Recheck Cond: (((addr & '255.0.0.0'::inet) =3D '1.0.0.0'::inet) AND
(family(addr) =3D 4))
         ->  Bitmap Index Scan on test_inet_expr_idx  (cost=3D0.00..8.18
rows=3D500 width=3D0)
               Index Cond: ((addr & '255.0.0.0'::inet) =3D '1.0.0.0'::inet)
(5 rows)

ERROR:  cannot AND inet values of different sizes
ROLLBACK

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CODE HERE =3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
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) =3D 4
    and addr & '255.0.0.0'::inet =3D '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) =3D 4;

-- The plan should show how subnet and family
-- conditions are split due to rechecking
explain select count(*)
  from test_inet
  where family(addr) =3D 4
    and addr & '255.0.0.0'::inet =3D '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) =3D 4
    and addr & '255.0.0.0'::inet =3D '1.0.0.0';

rollback;
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D  END CODE =3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D