Re: Join works in 7.3.6, fails in 7.4.2

This has not been fixed yet, right?

---------------------------------------------------------------------------

Tom Lane wrote:
> I wrote:
> > Michael Fuhr <mike(at)fuhr(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073