I discovered a problem with the inet = operator a couple of months
ago and brought it up on pgsql-general ("Join works in 7.3.6, fails
in 7.4.2"). Here's my original message (apparently truncated) and
one of Tom Lane's responses:
http://archives.postgresql.org/pgsql-general/2004-04/msg00453.php
http://archives.postgresql.org/pgsql-general/2004-04/msg00458.php
This problem still exists in the 7.4.3 snapshot (I never did follow
up with a message to pgsql-bugs, so apparently the problem wasn't
addressed). I just installed 7.4.3 and ran initdb to get a fresh
cluster; I then issued the following commands:
CREATE FUNCTION inet2net (INET) RETURNS INET AS '
SELECT NETWORK(SET_MASKLEN($1, 24));
' LANGUAGE SQL IMMUTABLE;
CREATE TABLE ipinterface (
ifid INTEGER NOT NULL PRIMARY KEY,
ifaddr INET NOT NULL
);
CREATE INDEX ipinterface_ifaddr_idx ON ipinterface (ifaddr);
CREATE INDEX ipinterface_ifaddrnet_idx ON ipinterface (inet2net(ifaddr));
CREATE TABLE ipnet (
netid INTEGER NOT NULL PRIMARY KEY,
netaddr INET NOT NULL,
CONSTRAINT uniq_netaddr UNIQUE (netaddr)
);
CREATE INDEX ipnet_netaddr_idx ON ipnet (netaddr);
INSERT INTO ipinterface VALUES (1, '10.0.1.1');
INSERT INTO ipinterface VALUES (2, '10.0.2.1');
INSERT INTO ipnet VALUES (10, '10.0.1.0/24');
INSERT INTO ipnet VALUES (20, '10.0.2.0/24');
SELECT ifid, ifaddr, netid, netaddr
FROM ipinterface AS i JOIN ipnet AS n ON inet2net(i.ifaddr) = n.netaddr
WHERE netid IN (10, 20);
ifid | ifaddr | netid | netaddr
------+--------+-------+---------
(0 rows)
This query returns no rows although I expect it to return two rows.
If I turn off enable_hashjoin then the query works as I expect:
SET enable_hashjoin TO off;
SELECT ifid, ifaddr, netid, netaddr
FROM ipinterface AS i JOIN ipnet AS n ON inet2net(i.ifaddr) = n.netaddr
WHERE netid IN (10, 20);
ifid | ifaddr | netid | netaddr
------+----------+-------+-------------
1 | 10.0.1.1 | 10 | 10.0.1.0/24
2 | 10.0.2.1 | 20 | 10.0.2.0/24
(2 rows)
As Tom mentioned in a followup to my original message, the workaround
for 7.4.* is to set oprcanhash to false for that operator. I did
so with this command:
UPDATE pg_operator SET oprcanhash = FALSE WHERE oid = 1201;
Should src/include/catalog/pg_operator.h be patched accordingly?
That would take care of installs that involved initdb; a comment
in the Release Notes could suggest running the above UPDATE command
for those upgrading without initdb.
Thanks.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
