Problem with inet = operator in 7.4.3 - Mailing list pgsql-bugs

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/