Re: When is a record NULL? - Mailing list pgsql-hackers
From | Sam Mason |
---|---|
Subject | Re: When is a record NULL? |
Date | |
Msg-id | 20090724221520.GE5407@samason.me.uk Whole thread Raw |
In response to | Re: When is a record NULL? ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
Responses |
Re: When is a record NULL?
|
List | pgsql-hackers |
On Fri, Jul 24, 2009 at 12:30:39PM -0500, Kevin Grittner wrote: > Sam Mason <sam@samason.me.uk> wrote: > > The fact that it happens to be a NULL *value* in one case > > Well, according to Codd (and I tend to go with him on this) there is > no such thing. NULL is a way to flag a place where a value could be > stored, but is not -- because is unknown or is not applicable in that > context. (He seemed to feel it was a big weakness of SQL that it > didn't differentiate between these two conditions, but that's another > argument.) "NULL value" is an oxymoron. I think then maybe we're talking about different things; I was trying to draw attention to the distinction between types and values---types allow some invariants of the code to be automatically checked before it is run, a value only has meaning at run time with the set of possible values an expression is defined over being constrained by its type. In this dichotomy a NULL is most definitely a value and with my current experience I don't understand the distinction you're trying to draw. > >> SQL doesn't provide a test for this case that's separate from the > >> test involving null-ness of individual fields. Not much we can do > >> about it though. I'm not entirely sure that exposing the > >> distinction would be helpful anyway ... > > > > I think it would > > The distinction between not having a tuple and having a tuple for > which you don't know any applicable values seems thin. I'm not sure > what that would really mean. Other languages/type systems do define this precisely. For example, in object orientated languages there's a big difference between a reference to an object being NULL and some member of an object being NULL. Databases obviously have their own semantics, but the distinction is well defined. Any implementation that tries to be faithful to a standard has its hands somewhat tied and PG is no exception. PG currently seems to be some hybrid half way between, it internally knows there is a distinction between the two but it doesn't like to expose this. For example (and this appears particularly awkward because of annoying limitations in the syntax PG accepts): SELECT y FROM (SELECT 1) x(a) LEFT JOIN (SELECT 1,2) y(a,b) ON FALSE; I think it should be valid to express this as: SELECT (SELECT 1,2 WHERE FALSE); but PG doesn't like sub-queries returning two columns--but this is material for another discussion. This returns a single row whose only attribute is NULL (i.e. it's rendered as '') and not as a record whose attributes are all NULL (i.e. rendered as '(,)'). The fact PG does the former says that your mental model isn't congruent with PGs behavior. If your model is correct then when the IS DISTINCT FROM operator works on RECORDs the following should return FALSE for all of the following: SELECT NULL IS DISTINCT FROM ROW(NULL); SELECT NULL IS DISTINCT FROM ROW(NULL,NULL); SELECT NULL IS DISTINCTFROM ROW(NULL,ROW(NULL,NULL)); SELECT ROW(NULL) IS DISTINCT FROM ROW(NULL,ROW(NULL,NULL)); i.e. there is *no* difference between a NULL record and a record consisting entirely of NULLs. -- Sam http://samason.me.uk/
pgsql-hackers by date: