I 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?
Digging further, I find that indeed this seems to be a mistake. CIDR
and INET values that have the same address and masklen compare as equal
according to network_eq(), but they will not hash the same because
there's a flag identifying whether a given value is considered CIDR or
INET. And what the network() function returns is marked as a CIDR.
It's a bit surprising that your hash join produces any matches at all...
I believe I got misled on this because there is a hash index operator
class for inet; at one point during the 7.4 cycle I went around and
cleaned up cases where the equality operator's canhash flag was
inconsistent with the set of hash index opclasses. Arguably the hash
opclass is broken, although in practice people probably don't notice the
failure since a given column is likely to contain either all inet or all
cidr values. (And of course it's entirely likely that there *aren't*
any people using the inet hash opclass, period...)
I can think of a number of possible fixes:
1. Mark inet = as not hashjoinable. We'd probably want to remove the
inet hash opclass too.
2. Redefine inet = so that CIDR and INET values are never considered
equal, thus eliminating the unused field. This could be back-patched
into 7.4 but otherwise seems to have little to recommend it. It
would certainly not help solve Michael's problem.
3. Provide a specialized hash method for type inet that ignores the
iptype field.
#3 seems the most desirable going forward, but is probably impractical
to back-patch into 7.4.*, so I'm not sure what to do about the problem
in that branch. Given the relatively low incidence of the problem,
maybe it's okay to just clear the oprcanhash flag in future 7.4.*
releases. This would not fix the problem for existing installations
(unless they initdb) but any complainers could be told how to adjust
their catalogs manually.
Can anyone think of any other approaches?
regards, tom lane