Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The reason I mentioned arrays is that it seems clear to me that
> nobody sane would consider ARRAY[NULL,NULL]::int[] to be
> equivalent to NULL::int[].
I will defer on that to anyone who has been in a position where the
former has any meaningful semantics in a SQL environment; that is a
set of people which does not include me.
> So I think the standard is not being very bright by conflating a
> null container with a container full of nulls in the ROW case.
I'm pretty sure that Codd argued that any attempt to create such a
row within a relation should be treated as an error; I tend to
agree. I'm far less clear on the semantics of a row value which is
not a top-level tuple of a relation. That seems like it's in a gray
area between set theory and practical details of procedural
programming. There are bound to be a few things in that realm which
don't make sense when viewed from one perspective or the other.
While I might not have chosen to draw the lines where the standard
does, they did make choices that allow reasonable things to be done
in a wide variety of cases, which is, IMO, the most important thing.
> I'm willing to hold my nose and do what they say for the specific
> case of "foo IS NULL" and "foo IS NOT NULL" expressions, but I'm
> not eager to let that confusion propagate anyplace else.
Not having used arrays in SQL, I don't feel able to offer much
opinion on implementation details; just the general opinion that we
should try to allow reasonable things to be done in a wide variety
of cases. Where it's possible to do that and have behavior which is
easy to explain, all the better. The vast majority of problematic
SQL code that I've seen is a direct consequence of programmers using
procedural code where they could have used declarative code, so I
would tend to stress clean and complete set operations over
concessions to procedural coding.
I'll stop waving my hands around now.
-Kevin