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...4/msg00453.php
http://archives.postgresql.org/pgsql...4/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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Yeah. I have this on my "must fix for 7.5" list but I'm not sure I see
any point in changing it in the 7.4 branch.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend