Re: Proposal: revert behavior of IS NULL on row types

On Fri, Jul 22, 2016 at 7:01 PM, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> In light of the fact that it is an endless cause of bugs both in pg and
> potentially to applications, I propose that we cease attempting to
> conform to the spec's definition of IS NULL in favour of the following
> rules:
>
> 1. x IS NULL is true if and only if x has the null value (isnull set).
>

​I don't have a problem conforming to "ROW(NULL, NULL) IS NULL" being
true...​if you somehow get a hold of something in that form, which your
others points address.

> 2. x IS NOT NULL if and only if NOT (x IS NULL)
>

​I would rather prohibit "IS NOT NULL" altogether.​ If one needs to test
"NOT (x IS NULL)" they can write it that way.

3. ROW() and other row constructors never return the null value.
>

​I think I get this (though if they return row(null, null) I'd say there is
not difference as far as the user is conconcerned)...

> Whole-row vars when constructed never contain the null value.
>

...but what does this mean in end-user terms?​

> 4. Columns or variables of composite type can (if not declared NOT NULL)
>
contain the null value (isnull set) which is distinct from an
> all-columns-null value.
>

Is this just about the validation of the component types; which seems only
to be realized via DOMAINs? If not I don't follow how this applies or is
different from what we do today.

> 5. COALESCE(x,y) continues to return y if and only if x is the null
> value. (We currently violate the spec here.)
>

​I would concur - especially if in your referenced example
COALESCE((null,1),(2,null)) indeed would have to return (2,null​)

My comment to #1 implies that I think COALESCE((null,null),(2,null)) should
return (2,null)...I am OK with that. Operationally (null,null) should be
indistinguishable from the null value. It mostly is today and we should
identify and fix those areas where they are different - not work to make
them more distinguishable.

>
> (X. Optionally, consider adding new predicates:
>
> x IS ALL NULL
> x IS NOT ALL NULL
> x IS ALL NOT NULL
> x IS NOT ALL NOT NULL
>
> which would examine the fields of x non-recursively.)
>
>
​Not sure regarding recursion here but I'd much rather work a way to fit
this into the existing ANY syntax:

NULL IS ANY(x) -- definitely needs some bike-shedding though...

​This presupposes that ROW(null, null) and null are indistinguishable
operationally which makes the "ALL" form unnecessary; and ANY = NOT(ALL)

David J.