Re: strange IS NULL behaviour

On Tue, Sep 3, 2013 at 8:32 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Fri, Jul  5, 2013 at 10:21:19AM -0400, Bruce Momjian wrote:
>> On Thu, Jul  4, 2013 at 04:29:20PM -0400, Tom Lane wrote:
>> > Bruce Momjian <bruce@momjian.us> writes:
>> > > I developed the attached patch which properly recurses into ROW()
>> > > records checking for NULLs;  you can see it returns the right answer in
>> > > all cases (and constant folds too):
>> >
>> > My recollection of the previous discussion is that we didn't have
>> > consensus on what the "right" behavior is, so I'm not sure you can just
>> > assert that this patch is right.  In any case this is only touching the
>> > tip of the iceberg.  If we intend that rows of nulls should be null,
>> > then we have got issues with, for example, NOT NULL column constraint
>> > checks, which don't have any such recursion built into them.  I think
>> > the same is true for plpgsql variable NOT NULL restrictions, and there
>> > are probably some other places.
>>
>> Well we have three cases:
>>
>>       1  SELECT ROW(NULL) IS NULL;
>>       2  SELECT ROW(ROW(NULL)) IS NULL;
>>       3  SELECT ROW(ROW(ROW(NULL))) IS NULL;
>>
>> I think we could have them all return false, or all true, or the first
>> one true, and the rest false.  What I don't think we can justify is 1
>> and 2 as true, and 3 false.
>
> I have done some more research in this, and was able to verify Tom's
> concern that PL/pgSQL's IS NULL doesn't recurse into ROW expressions:
>
>         DO LANGUAGE plpgsql $$
>                 DECLARE
>                         r RECORD;
>                 BEGIN
>
>                 SELECT NULL INTO r;
>                   IF (r IS NULL)
>                         THEN RAISE NOTICE 'true';
>                         ELSE RAISE NOTICE 'false';
>                         END IF;
>                 END;
>                 $$;
>         NOTICE:  true
>         DO
>
> In this test, SELECT NULL (which internally would produce SELECT
> ROW(NULL)), returns TRUE, while SELECT ROW(NULL) and further nesting
> returns false.
>
> This has made me adjust my goal and change it so SELECT ROW(NULL) IS
> NULL returns true, and any further nesting returns false.

It gets worse and worse.  The IS NULL operator is already pretty much
special cased -- in just about all other case concerning rowtypes (for
example coalesce) 'null containing rowtypes are *not* considered to be
null as the container itself has a null bit independent of it's
elements which is expressly contrary to the SQL standard.  This is
tragic; postgres's way of doing it (except with IS NULL) makes an
awful lot of sense to me but here we are.  I think before making any
behavior changes at all, we need to ask ourselves:

1. Are we willing to break compatibility in order to move to spec
compliant behavior?
2. and if so, what mountains do we have to cross to get there?

Your proposed change (implementation details aside) seems ok in the
sense that it doesn't seem to have a lot of obvious side effects but
the elephant in the room is #1; if the answer is 'no', then I'd say
the best course of action is to let things be.

merlin