Peter Geoghegan <pg@bowt.ie> writes:
> It would be fairly easy to teach nbtree about a new kind of
> ScalarArrayOp that worked just like a conventional SAOP, but also
> returned tuples matching "IS NULL" (IS NULL uses the equals strategy
> internally already, so it'd be almost the same as treating NULL as
> just another array element). This could have significant advantages
> over what's even possible right now, particularly in cases involving
> ORDER BY ... LIMIT.
> I suppose that we'd have to invent some kind of new syntax for this.
> But wouldn't it also make sense if it worked with "WHERE a IN (1, 2)
> OR a IS NULL"? Or even with "WHERE a = 1 OR a IS NULL"?
I'd be a strong -1 for inventing new syntax for that. However, if
that's actually a common query pattern (I'm not convinced of it)
we could certainly build something to recognize that usage and
transform it into a suitable executable structure.
However, I don't see the connection between that and the current
thread. That pattern is not self-contradictory. My doubts
about its usefulness have more to do with it being evidence of
the SQL anti-pattern of treating NULL as a normal data value.
But once you've made that choice in your data representation,
you're going to end up with queries like this, and there's
nothing you could do to write them "better".
regards, tom lane
