On Wed, Aug 12, 2009 at 04:14:31PM +0200, Daniel Verite wrote:
> But IS NULL applied to an array is useless to test if there are null values
> inside, whereas this is apparently the whole point of IS NULL applied to
> rows.
> I mean:
> select a is null from (select array[null]) x(a);
> returns false, as well as:
> select a is null from (select array[1]) x(a);
Yes, I know. But it seems to be a somewhat arbitrary choice to handle
IS NULL for rows differently from everything else.
> When applied to rows, if you consider that:
> - is null applied to a row means that all columns are null
> - is not null applied to a row means that all columns are not null
> which is what the standard seems to dictate, then these operators make sense
> and are probably useful in some situations.
Yes, I understand what it's specified to do and that it's consistent
with SQL spec. I just think (and Merlin seems to agree) that the spec
has specified the "wrong" behavior.
> Now there is the unfortunate consequence that (r is null) is not equivalent
> to (not (r is not null)), yet it's not the standard's fault if "not all
> values are null" is not the same as "all values are not null", that's just
> set logic.
Yes; but this means the user now has to be aware of exactly which type
their code is using as the behavior of various things will magically
change in rare circumstances.
> Maybe they could have made this easier for us by naming the operators
> differently, such as "is entirely null" and "is entirely not null"
Yes, this would be *much* more preferable. For people aware of it this
it's obviously an easy translation to make, but it's a nasty waiting for
those who aren't and especially for anybody doing anything formal. I.e.
when reasoning about operator semantics you suddenly have to know the
type of data you're dealing with before you can say useful things about
the result. There will of course be ways of avoiding the general case
of an exponential increase in complexity, but it's still nasty.
Anybody else think this thread is past it's bed time and should be put
to rest?
--
Sam http://samason.me.uk/
