Re: BUG #14235: inconsistencies with IS NULL / IS NOT NULL

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> And here's my analysis of what seems to be going on:

> The executor, when doing IS [NOT] NULL on a composite value, looks at
> each column to see if it is the null value. It does NOT recurse into
> nested composite values, and my reading of the spec suggests that this
> is correct.

Hmm.  Of course the $64 question is whether that really is correct, or
sensible.

I went to look at the spec, and discovered that SQL:2011 actually has
wording that is different from SQL99, which I think is what we relied
on last time we considered this issue.  Specifically, in 2011,
section 8.8 <null predicate> quoth:

<null predicate> ::= <row value predicand> <null predicate part 2>
<null predicate part 2> ::= IS [ NOT ] NULL

(Oddly, SQL does not seem to allow IS [NOT] NULL on non-composite values,
which is just silly.)

1) Let R be the <row value predicand> and let V be the value of R.

2) Case:
  a) If V is the null value, then “R IS NULL” is True and the value of
     “R IS NOT NULL” is False.
  b) Otherwise:
    i) The value of “R IS NULL” is
       Case:
       1) If the value of every field of V is the null value, then True.
       2) Otherwise, False.
    ii) The value of “R IS NOT NULL” is
       Case:
       1) If the value of no field of V is the null value, then True.
       2) Otherwise, False.

NOTE 267 — For all R, “R IS NOT NULL” has the same result as “NOT R IS
NULL” if and only if R is of degree 1.

Rule (2a) was not there in SQL99.  But look at what this is doing: it
is admitting straight out that a null composite value is not the same
as a composite value all of whose fields are null.  It is only asserting
that a <null predicate> will not distinguish them.  The implication is
that it's just fine if, say, COALESCE() doesn't act that way.  Previously,
we thought this part of the spec was supposed to define what "V is null"
means everywhere else in the spec if V is composite; but now it seems
clear that "V is null" is a primitive test that is not the same as the
<null predicate> construct.

> It seems possible that this could be fixed by simply setting
> argisrow=false for all the null tests generated in such cases.

I concur that this is an appropriate fix if we believe that
ExecEvalNullTest's behavior is correct.

            regards, tom lane