The following bug has been logged on the website:
Bug reference: 7869
Logged by: Bernard Jech
Email address: bernardjech@yahoo.com
PostgreSQL version: 9.2.3
Operating system: Mac OS X 10.6.8
Description: =
-- Create the tables:
CREATE TABLE netblock (
id INTEGER,
ip_address inet NOT NULL
);
CREATE TABLE ipv4 (
netblock_id integer
);
-- Insert data:
INSERT INTO netblock VALUES(1, '2605:ee00::/32');
INSERT INTO netblock VALUES(2, '68.67.181.7/26');
INSERT INTO ipv4 VALUES(2);
-- Create the view:
CREATE OR REPLACE VIEW subnet AS
SELECT ipv4.netblock_id AS id,
set_masklen(n.ip_address, 32) - inet '0.0.0.0' AS cidr_address
FROM ipv4 JOIN netblock n ON ipv4.netblock_id =3D n.id;
If you select all rows from the view subnet, you get the expected result:
pokus=3D> select * from subnet;
id | cidr_address =
----+--------------
2 | 1145287943
(1 row)
The surprising part comes when you add a WHERE clause to the SELECT
statement to select only this one row:
pokus=3D> select * from subnet where cidr_address =3D 1145287943;
ERROR: cannot subtract inet values of different sizes
This is apparently caused by the IPv6 entry in the netblock table but this
row should be excluded by the inner JOIN in the VIEW definition.
