On Wed, Aug 12, 2009 at 10:14 AM, Daniel Verite<daniel@manitou-mail.org> wrote:
> Sam Mason wrote:
>
>> Nope, I still don't get it. Why treat rows specially? If this was
>> true, then what should:
>>
>> SELECT a IS NULL, a IS NOT NULL
>> FROM (SELECT ARRAY [1,NULL]) x(a);
>>
>> evaluate to? As "part of it" is NULL and part isn't then, by your
>> reasoning, it should return TRUE for both. PG doesn't and I think this
>> is much more useful behavior.
>
> 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);
>
> 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.
>
> 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.
>
> Maybe they could have made this easier for us by naming the operators
> differently, such as "is entirely null" and "is entirely not null"
IMO, the standard really blew it.
PostgreSQL's approach is ok, minimal standards compliance balanced out
with practical considerations. This leads to some strange behaviors
as noted upthread, but it's workable if you know the tricks. I guess
it's not very well documented....
merlin