Re: Join works in 7.3.6, fails in 7.4.2

On Tue, Apr 13, 2004 at 03:42:54PM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > I have a query that works in 7.3.6 but not in 7.4.2 unless I turn
> > off enable_hashjoin.  I'm joining a table of network interfaces and
> > a table of networks so I can find additional info about a particular
> > interface's network.
>
> Hmm.  The inet = operator is marked hashable in 7.4 but not in 7.3 ...
> I wonder if that is a mistake?  I recall looking at the datatype and
> deciding there were no insignificant bits in it, but that could be
> wrong.  Or it could be that the network() function is taking some
> shortcut it shouldn't.

So would a workaround be to set oprcanhash to false for that
operator?  I did the following and it appeared to solve the
problem:

UPDATE pg_operator SET oprcanhash = FALSE WHERE oid = 1201;

Or, without knowing that 1201 is the correct OID:

UPDATE pg_operator SET oprcanhash = FALSE
WHERE oprname = '='
  AND oprleft IN (SELECT oid FROM pg_type WHERE typname = 'inet');

> Is any of this data IPv6 addresses by any chance?

Nope -- all IPv4.

> > From my sample data set (available upon request),
>
> Could we see the specific values that join in 7.3 and fail to do so in
> 7.4?

I can duplicate the problem with the following data:

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');

Thanks for looking into this.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/