Re: BUG #6701: IS NOT NULL doesn't work on complex composites - Mailing list pgsql-bugs

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Rikard Pavelic <rikard.pavelic@zg.htnet.hr> wrote:
>> The only inconsistent thing is check constraint, which behaves as
>> NOT column IS NULL instead of column IS NOT NULL as docs says.

> So currently a NOT NULL constraint on a column with a composite type
> is equivalent to:
> CHECK (NOT c IS NULL)

I don't believe this statement is accurate.  What's really happening
is that a column-not-null constraint is a datatype-independent check
for whether the datum per se is null or not.  In the case of a composite
column, it's possible that the datum is a heaptuple all of whose fields
are null.  IS NULL will say "true" for such a value, per SQL spec, but
the attnotnull code will not reject it.  So actually the attnotnull
check doesn't exactly correspond to either IS NOT NULL or NOT IS NULL,
when you're talking about composite types.

There are two ways we could make that more consistent:

1. Force all-null heaptuple datums to become real nulls.  This is not
terribly attractive IMV; for one thing it loses any opportunity to carry
the rowtype's OID, which is something I think we need at least in some
contexts.  We could narrow the scope for such problems by delaying the
application of the conversion until storage time, but then it would have
to be checked in places that now are datatype-independent, which is
unpleasant from both modularity and performance standpoints.  Another
issue is that, while the spec seems not to distinguish between NULL and
ROW(NULL,NULL,...), it is far from clear that we should seek to suppress
the difference.  They are different in I/O representation for instance.

2. Change the attnotnull checking code to be datatype-dependent
so that it could peer into a composite value to check the field values.
This is unpleasant for the same modularity and performance reasons
mentioned above.

We've been over this ground before, and not come to any consensus about
changing the behavior.  Somebody who really cares about having the
SQL-spec definition can write a CHECK constraint as suggested above,
and then he'll get the composite-type-aware behavior, so it's not like
there's no way to get that.

BTW, the same inconsistency exists for function-argument strictness
checks: those will consider a heaptuple-of-all-nulls to be something
you can call a strict function with.  I think changing this would be
a pretty bad idea, not only on modularity and performance grounds but
because it'd likely break existing applications that expect the current
behavior.

Here's another interesting example, using int8_tbl which is just
a two-column composite type:

regression=# select null::int8_tbl;
 int8_tbl
----------

(1 row)

regression=# select row(null,null)::int8_tbl;
 row
-----
 (,)
(1 row)

regression=# select null::int8_tbl is distinct from row(null,null)::int8_tbl;
 ?column?
----------
 t
(1 row)

It's not clear to me whether the SQL standard rules on what should
happen in this case, or whether we should listen to it if it does say
that these values are not distinct.  They certainly *look* distinct.

(Oh, and dare I mention arrays of nulls?)

            regards, tom lane