Skip site navigation (1) Skip section navigation (2)

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

From: David Fetter <david(at)fetter(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Nulls, arrays, records, IS NULL, IS DISTINCT FROM
Date: 2006-09-29 17:06:31
Message-ID: 20060929170631.GG2823@fetter.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

In response to

Responses

pgsql-hackers by date

Next:From: Paul B. AndersonDate: 2006-09-29 17:08:15
Subject: Re: Array assignment behavior (was Re: Stored procedure array
Previous:From: Strong, DavidDate: 2006-09-29 17:06:08
Subject: Testing strlcpy ()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group