When is a record NULL?
How can a record be neither NULL or NOT NULL?
try=# select ROW(1, NULL) IS NULL;
?column?
----------
f
(1 row)
try=# select ROW(1, NULL) IS NOT NULL;
?column?
----------
f
(1 row)
This makes it rather hard to tell, in PL/pgSQL, when I've fetched the
last record from a cursor…
Best,
David
On Jul 23, 2009, at 3:19 PM, David E. Wheeler wrote:
How can a record be neither NULL or NOT NULL?
try=# select ROW(1, NULL) IS NULL;
?column?
----------
f
(1 row)try=# select ROW(1, NULL) IS NOT NULL;
?column?
----------
f
(1 row)This makes it rather hard to tell, in PL/pgSQL, when I've fetched
the last record from a cursor…
Also:
select ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL);
?column?
----------
t
As expected, but the IS NULL above is not expected (by this bunny,
anyway).
Best,
David
On Thu, 2009-07-23 at 15:19 -0700, David E. Wheeler wrote:
How can a record be neither NULL or NOT NULL?
You could do:
not ROW(1, NULL) is null and not ROW(1, NULL) is not null
I don't know what the SQL standard says about this.
Regards,
Jeff Davis
"David E. Wheeler" <david@kineticode.com> writes:
How can a record be neither NULL or NOT NULL?
This is per SQL standard. IS NULL is true if *all* the record's
fields are null; IS NOT NULL is true if *none* of them are.
Yeah, it's a bit dubious, but that's what they said.
regards, tom lane
On Jul 23, 2009, at 4:08 PM, Tom Lane wrote:
This is per SQL standard. IS NULL is true if *all* the record's
fields are null; IS NOT NULL is true if *none* of them are.Yeah, it's a bit dubious, but that's what they said.
And yet they're DISTINCT FROM each other if either or both contain
NULLs and some other values? It seems to me that, to be consistent, it
should be:
select ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL);
?column?
----------
f
No?
Best,
David
"David E. Wheeler" <david@kineticode.com> writes:
And yet they're DISTINCT FROM each other if either or both contain
NULLs and some other values?
Well, that would depend on what the values were and in what columns...
It seems to me that, to be consistent, it
should be:
select ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL);
?column?
----------
f
Dunno how you can possibly come to that conclusion. Those row
values are certainly distinct (according to both PG and the spec).
regards, tom lane
On Jul 23, 2009, at 4:22 PM, Tom Lane wrote:
Dunno how you can possibly come to that conclusion. Those row
values are certainly distinct (according to both PG and the spec).
Are they not both null?
David
On Jul 23, 2009, at 3:33 PM, Jeff Davis wrote:
not ROW(1, NULL) is null and not ROW(1, NULL) is not null
I don't know what the SQL standard says about this
Thanks Jeff, that's just what I needed.
Best,
David
On Thu, 2009-07-23 at 17:32 -0700, David E. Wheeler wrote:
On Jul 23, 2009, at 4:22 PM, Tom Lane wrote:
Dunno how you can possibly come to that conclusion. Those row
values are certainly distinct (according to both PG and the spec).Are they not both null?
[ Is that a play on words? ;) ]
Here's the logical breakdown of your argument:
x = ROW(1, NULL)
0. x IS NOT NULL = false
1. => NOT x IS NOT NULL = true
2. => NOT NOT x IS NULL = true
3. => x IS NULL = true
However, in step 2, you transformed:
x IS NOT NULL => NOT x IS NULL
But in SQL that is not a tautology!
I don't think it's wise to assume SQL is consistent. I think it is
possible to create a consistent 3VL system, but you have to give up some
other very important property. I can't remember the details at the
moment, but there's an interesting proof in "Logic and Databases" by
C.J. Date.
Does the SQL spec lay out the tautologies anywhere, so that we can get a
clear picture of what's going on with NULLs? I won't make the claim that
SQL is inconsistent without actually seeing the system as a whole, but,
at a minimum, many of the tautologies that people are accustomed to are
not true in SQL.
Regards,
Jeff Davis
On Fri, Jul 24, 2009 at 1:32 AM, David E. Wheeler<david@kineticode.com> wrote:
On Jul 23, 2009, at 4:22 PM, Tom Lane wrote:
Dunno how you can possibly come to that conclusion. Those row
values are certainly distinct (according to both PG and the spec).Are they not both null?
Isn't that just what you were complaining about not being the case:
On Thu, Jul 23, 2009 at 11:29 PM, David E. Wheeler<david@kineticode.com> wrote:
try=# select ROW(1, NULL) IS NULL;
?column?
----------
f
(1 row)
On Jul 23, 2009, at 6:21 PM, Greg Stark wrote:
Are they not both null?
Isn't that just what you were complaining about not being the case:
Yes, but given that the standard says that `ROW(1, NULL)` is NULL,
then I would expect it to be NOT DISTINCT from `ROW(2, NULL)`.
Best,
David
On Jul 23, 2009, at 6:06 PM, Jeff Davis wrote:
However, in step 2, you transformed:
x IS NOT NULL => NOT x IS NULLBut in SQL that is not a tautology!
No, that's not the problem I see -- that solved the problem in my
particular code. The problem I see is that, given that the standard
says (according to Tom) that if any value is NULL then the record is
NULL, then I would expect this to return false:
SELECT ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL);
But it returns true.
I don't think it's wise to assume SQL is consistent. I think it is
possible to create a consistent 3VL system, but you have to give up
some
other very important property. I can't remember the details at the
moment, but there's an interesting proof in "Logic and Databases" by
C.J. Date.
If the standard says that, in the case of records, two NULLs are
distinct, then fine. Completely bizarre, but fine. But I suspect that
such is not the case.
Best,
David
On Jul 23, 2009, at 6:52 PM, David E. Wheeler wrote:
No, that's not the problem I see -- that solved the problem in my
particular code. The problem I see is that, given that the standard
says (according to Tom) that if any value is NULL then the record is
NULL, then I would expect this to return false:SELECT ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL);
But it returns true.
Sorry, I'm confused. Understandable I think. So:
ROW(1, NULL) is neither NULL nor NOT NULL. I've no idea what state it
is, but I guess that's the standard. In this case, IS DISTINCT FROM
gives me a sensible return value -- it assumes that the records are
NOT NULL, I guess, for its purposes. This is still inconsistent, since
the records are neither NULL nor NOT NULL, but perhaps sensible.
It's pretty insane, frankly. Gotta love SQL.
Best,
David
2009/7/24 David E. Wheeler <david@kineticode.com>:
ROW(1, NULL) is neither NULL nor NOT NULL. I've no idea what state it is,
but I guess that's the standard.
Well, a ROW is an ordered set of values, each one of which may be
either NULL or NOT NULL.
It doesn't really make sense to talk about the ROW itself being NULL
or NOT NULL, only its member values (but for extra confusion, contrast
with the treatment of arrays, which can themselves be NULL).
It does make sense, however, to talk about the ROW's member values
being entirely NULL or entirely NOT NULL, and that's what the IS NULL
and IS NOT NULL tests tell you about.
I guess the spec authors figured they might as well make IS [NOT] NULL
do something useful when applied to a row rather than throwing an
error. I tend to agree.
I hope that provides some clarity.
Cheers,
BJ
On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
Well, a ROW is an ordered set of values, each one of which may be
either NULL or NOT NULL.
Right.
It doesn't really make sense to talk about the ROW itself being NULL
or NOT NULL, only its member values (but for extra confusion, contrast
with the treatment of arrays, which can themselves be NULL).
Well then maybe a record (row) should *never* be null.
It does make sense, however, to talk about the ROW's member values
being entirely NULL or entirely NOT NULL, and that's what the IS NULL
and IS NOT NULL tests tell you about.
Ah! So that's where the three-valued logic comes in to play with
records:
try=# SELECT ROW(1, NULL) IS NULL, ROW (1, 1) IS NULL, ROW(NULL,
NULL) IS NULL;
?column? | ?column? | ?column?
----------+----------+----------
f | f | t
I guess the spec authors figured they might as well make IS [NOT] NULL
do something useful when applied to a row rather than throwing an
error. I tend to agree.
Frankly, I find the state where a record with a NULL and a not-null
value being neither NULL nor not NULL bizarre.
I hope that provides some clarity.
It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the
whole thing totally bizarre. Is it me?
Best,
David
David E. Wheeler wrote:
On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
I guess the spec authors figured they might as well make IS [NOT] NULL
do something useful when applied to a row rather than throwing an
error. I tend to agree.Frankly, I find the state where a record with a NULL and a not-null
value being neither NULL nor not NULL bizarre.
I'm guessing the justification (and presumably this was worked out based
on the behaviour of one or more of the big DB providers and then
justified afterwards) is that the composite is "partially unknown". Of
course you should either introduce a new code or throw an error, but
four-valued logic isn't going to win you any friends.
If the argument *is* that because you know part of the overall value the
composite isn't null then I'd argue that ('abc' || null) isn't null
either. After all, the first three characters are perfectly well
established.
I hope that provides some clarity.
It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the
whole thing totally bizarre. Is it me?
Yes, just you. None of the rest of us have any problems with this at all :-)
--
Richard Huxton
Archonet Ltd
2009/7/24 David E. Wheeler <david@kineticode.com>:
It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the whole
thing totally bizarre. Is it me?
*shrug* The IS [NOT] NULL tests mean something different when applied
to a ROW than they do when applied to a scalar value or an array.
"SELECT 1 IS NULL"
=> means "is this scalar set to the special value NULL?".
"SELECT ROW(1, 2) IS NULL"
=> means "are all the member values of this row set to the special
value NULL?"
So it is wrong to talk about ROW(NULL, NULL) being NULL. It doesn't
have the property of being NULL or not NULL, because it is a composite
value. "ROW(NULL, NULL) IS NULL" returns true, but that is not the
same as saying that it actually is NULL, because of the different
semantics above.
It's slightly different semantics from what you get with ordinary
scalar values, but that is cognisant with the fact that composites are
fundamentally different things from ordinary scalar values.
Cheers,
BJ
Does this also apply at the column level?
In Postgres is If column IS NOT NULL or If column IS NULL? Do all
columns in the record have to be NULL to have IF column IS NULL = true?
Best Regards
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
Brendan Jurd <direvus@gmail.com> writes:
So it is wrong to talk about ROW(NULL, NULL) being NULL. It doesn't
have the property of being NULL or not NULL, because it is a composite
value. "ROW(NULL, NULL) IS NULL" returns true, but that is not the
same as saying that it actually is NULL, because of the different
semantics above.
It's worse than that, because there actually is also such a thing as
the row value being NULL --- ie, there's no row structure at all.
At least internally, that's a completely different thing from having
a row all of whose fields are null.
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 ...
regards, tom lane
On Thu, Jul 23, 2009 at 06:46:25PM -0700, David E. Wheeler wrote:
Yes, but given that the standard says that `ROW(1, NULL)` is NULL, then I
would expect it to be NOT DISTINCT from `ROW(2, NULL)`.
Wait, didn't we decide upthread that the standard said ROW(1, NULL) isn't
NULL?
(From Tom):
This is per SQL standard. IS NULL is true if *all* the record's
fields are null; IS NOT NULL is true if *none* of them are.
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
