Re: [PATCH] Add support for IS NULL to btree indexes

On Mon, Sep 19, 2005 at 04:33:27PM -0400, Tom Lane wrote:
> This is a bad idea, because it translates "x IS NULL" into "x = NULL"
> which is under no circumstances the same thing. It might coincidentally
> fail to malfunction for btree indexes, depending on the specifics of the
> "=" operator in use; but that doesn't make it right. (AFAICS, the
> proposed patch simply breaks for non-btree indexes.) Also, it cannot
> handle IS NOT NULL.

That's point of the extra flag, to distinguish between the two cases.
It works for all types, the actual operator in the operator class is
never invoked (they're strict, the code can't call them with NULL even
if it wanted to). ExecInitIndexScan has always set SK_ISNULL for null
args, even though it never happened in practice. All it does is move to
the point in the index where the NULLs begin and start returning
tuples.

It takes a previously impossible state and makes it return something
useful.

It doesn't handle IS NOT NULL, since I don't think that's worth
indexing anyway, but perhaps for completeness. It the much harder case.

> A proper solution requires explicitly representing IS NULL/IS NOT NULL
> as distinct kinds of scankey.

Well, this patch has a kind for IS NULL. It does have issues with other
indexes, but there's no point working on that until there is a
possibility of acceptance.

The purpose was to demonstrate that it is trivially possible using what
is available.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.