Re: Nulls, arrays, records, IS NULL, IS DISTINCT FROM

On Fri, Sep 29, 2006 at 12:53:19PM -0400, Tom Lane wrote:
> Following up yesterday's discussion, I've been studying the SQL spec for
> <null predicate> and <distinct predicate>, and it seems a bit
> inconsistent.

Do you have the official spec in hand, or just the draft from wiscorp?

> SQL2003 has completely rewritten the text but the meaning seems the
> same.  I suppose we want to generalize the NX/NY business to say "if
> the array bounds are not identical then the arrays are distinct".

That sounds like a reasonable generalization.

> We are clearly getting this wrong since the introduction of nulls in
> arrays, but I'll go fix that.

> Similarly, given two row expressions, distinctness is determined
> field-wise: X and Y are distinct if any two corresponding fields
> are distinct.  We are currently getting this correct only for
> the case of parse-time ROW expressions, ie
>     ROW(x,y,z) IS [NOT] DISTINCT FROM ROW(xx,yy,zz)
> This is pretty much analogous to the case Teodor noted yesterday
> for IS NULL: it's not being done in gram.y but it's still being
> done much too early.  We need to be able to do it in the executor
> to handle situations where a row value is coming from a function
> or some other source that's not disassemblable at parse time.
> 
> What's bothering me is that for "foo IS [NOT] NULL", the spec clearly
> prescribes drilling down into a rowtype value to examine the individual
> fields, but I can't find any language that prescribes the same for
> arrays.  Is this intentional, or an oversight?  In particular, the
> spec says
>     ROW(1,2,NULL) IS NOT NULL
> is false, because the row fields must be *all* not null to make it true.

That's odd because as I understand the above,
   ROW(1,2,NULL) IS NULL

is also false.  Have I misunderstood?

> But it's very unclear whether
>     ARRAY[1,2,NULL] IS NOT NULL
> should be false on the same reasoning.  Right now, we respond "true" on
> the grounds that the array object as-a-whole isn't null, without
> examining its contents.
> 
> Comments?  Does anyone see any guidance in the spec?

Sadly, I don't have the official one in hand.  Is there one available?

> If there is none, which behavior do we think is most
> useful/consistent?

My reading of the (provisional) spec is that a complex type (ROW,
ARRAY, MULTISET) is NULL iff all of its elements are NULL or the whole
of it is NULL.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!