Re: When is a record NULL?

On Fri, Jul 24, 2009 at 1:35 PM, Merlin Moncure<mmoncure(at)gmail(dot)com> wrote:
> On Fri, Jul 24, 2009 at 5:15 AM, David E. Wheeler<david(at)kineticode(dot)com> wrote:
>> On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
>>
>>> Well, a ROW is an ordered set of values, each one of which may be
>>> either NULL or NOT NULL.
>>
>> Right.
>>
>>> It doesn't really make sense to talk about the ROW itself being NULL
>>> or NOT NULL, only its member values (but for extra confusion, contrast
>>> with the treatment of arrays, which can themselves be NULL).
>>
>> Well then maybe a record (row) should *never* be null.
>
> I disagree, and I think our current way of treating things is
> incorrect (although harmless).  I rowtype can be null:
>
> select null::somerowtype;
>
> I think the following should _not_ return true:
> select (null, null)::somerowtype is null;
>
> The reasoning being that while the rowtype members are null, the
> record variable itself is not; these are two distinct cases and should
> be checked for and treated differently.
>
> Another line of reasoning for this is that if something gives 'true'
> for the is null operator, it should behave as null does, giving null
> for any operations on it and giving null for STRICT functions, to give
> a couple of examples.
>
> create table foo (a int, b int);
> select (null, null)::foo is null;
>  ?column?
> ----------
>  t
>
> create or replace function doit(foo) returns void as $$ begin raise
> notice '!'; end; $$ language plpgsql strict;
>
> select doit(row(null, null)::foo);
> NOTICE:  !  <-- what???!?
>
> I think this is wrong, and if the sql standard sez it is so, then the
> standard is wrong :-).

Just to clarify...I think this is the right behavior to run the
function that way, but (null,null) is null == true is not correct.
There are not 'degrees' of null...something is either null or it
isn't.

merlin