Re: When is a record NULL?

On Fri, Jul 24, 2009 at 4:29 PM, Greg Stark<gsstark(at)mit(dot)edu> wrote:
> On Fri, Jul 24, 2009 at 7:49 PM, Merlin Moncure<mmoncure(at)gmail(dot)com> wrote:
>> Still, why:
>>
>> *) is select null::foo is distinct from (null, null)::foo; true?
>> (null should not be distinct from null)
>>
>> *) does (null, null) allow STRICT functions to execute?
>>
>> *) is (null, null)::text not null?
>
> These are all good questions. Are you interested in checking what the
> spec says should happen in these cases?

I don't have a copy :-). (Am I being obtuse, and not noticing
something obvious?)

I think though that one of three cases is true:

*) postgresql is not treating (null, null) as null except in the case
where checked with 'is null'.
*) postgresql is following spec, which basically contradicts itself.
*) the behavior is undefined

If the first case is true (i bet it is), then many things we do with
composite types are wrong on some level, seriously in some cases. for
example:

select count(rowtype) from foo;
select distinct rowtype from foo;
select * from foo join bar using (rowtype);

can all give the wrong answer.

regardless, I think the sql standard definition of null for rowtypes
is lame -- the way null works for arrays is much better, in that the
elements inside the array an be null, independently can the array
itself.

I like the current behavior of composites (all composite fields being
null is a pretty rare case in practice), and would argue against
bringing the database closer to spec if it turns out that we are doing
it incorrectly.

merin