Comparisons on NULLs (was Re: A small problem...)
darcy@druid.net (D'Arcy J.M. Cain) writes:
but I can see the reasonableness of defining "3 != NULL" as TRUE.
Actually I see it as FALSE. That's what I was suggesting earlier. All
comparisons to null should be false no matter what the sense of the
test.
Hmm. That yields extremely unintuitive results for = and !=. That is,
SELECT * FROM t WHERE b = NULL;
will never return any rows, even if there are some where b is null;
and
SELECT * FROM t WHERE b != NULL;
will never return any rows, even if there are some where b isn't null.
If this is the definition then you cannot select rows with null entries
using the same syntax as for selecting rows with particular non-null
values, which is what I thought the point of the CREATE FUNCTION example
was.
That way you can always decide in the select statement whether
you want a particular comparison to null to be included or not.
[D'Arcy proposes that these ops need not give the same result:
SELECT * FROM t WHERE i1 < i2;
SELECT * FROM t WHERE NOT (i1 >= i2);
Ugh. I think it'd be a lot more intuitive to write something like
SELECT * FROM t WHERE i1 < 33 OR i1 IS NULL;
But getting this to work without introducing unintended consequences
might be pretty tricky too. If "NULL < 33" returns NULL, as I'd prefer,
then OR has to be non-strict, and in fact NULL OR 't' has to give 't'.
That looks pretty reasonable at first glance, but there are probably
other examples where it does something undesirable.
Maybe, the boolean-combining operators (AND, OR, NOT) can safely be
made non-strict (treating NULL as FALSE), but I'm wary of that.
We probably ought to go re-read the IEEE float math specs. What I think
you are getting at is almost the same as their distinction between
"NaN-aware" and "non-NaN-aware" comparison operators, but I've forgotten
the details of how those work. (And I have to leave in a minute, so I
can't look them up right now...)
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofTue3Nov1998115736-0500m0zajlx-0000eRC@druid.net
On 04-Nov-98 Tom Lane wrote:
darcy@druid.net (D'Arcy J.M. Cain) writes:
but I can see the reasonableness of defining "3 != NULL" as TRUE.
Actually I see it as FALSE. That's what I was suggesting earlier. All
comparisons to null should be false no matter what the sense of the
test.Hmm. That yields extremely unintuitive results for = and !=. That is,
SELECT * FROM t WHERE b = NULL;
will never return any rows, even if there are some where b is null;
andSELECT * FROM t WHERE b != NULL;
will never return any rows, even if there are some where b isn't null.
If this is the definition then you cannot select rows with null entries
using the same syntax as for selecting rows with particular non-null
values, which is what I thought the point of the CREATE FUNCTION example
was.That way you can always decide in the select statement whether
you want a particular comparison to null to be included or not.
[D'Arcy proposes that these ops need not give the same result:
SELECT * FROM t WHERE i1 < i2;
SELECT * FROM t WHERE NOT (i1 >= i2);Ugh. I think it'd be a lot more intuitive to write something like
SELECT * FROM t WHERE i1 < 33 OR i1 IS NULL;
But getting this to work without introducing unintended consequences
might be pretty tricky too. If "NULL < 33" returns NULL, as I'd prefer,
then OR has to be non-strict, and in fact NULL OR 't' has to give 't'.
That looks pretty reasonable at first glance, but there are probably
other examples where it does something undesirable.Maybe, the boolean-combining operators (AND, OR, NOT) can safely be
made non-strict (treating NULL as FALSE), but I'm wary of that.We probably ought to go re-read the IEEE float math specs. What I think
you are getting at is almost the same as their distinction between
"NaN-aware" and "non-NaN-aware" comparison operators, but I've forgotten
the details of how those work. (And I have to leave in a minute, so I
can't look them up right now...)
I looked at this earlier, but it was me that had to leave then I forgot
all about it till now. Now it's confusing.
Looking at this (and *please* let's not get into IS vs = yet):
SELECT * FROM t WHERE b = NULL;
I first looked at this from within a C program. Consider the input coming
from a form and constructing the select statement from it's submission
values:
sprintf(buf,"SELECT * FROM t WHERE a = %d AND b = '%s'",abc,xyz);
If I understand what you're saying above, if xyz is NULL and b is NULL
then it doesn't matter what a is 'cuze it'll never return any results.
I'll shut up now in case I'm misintrepreting this..
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Searchable Campground Listings http://www.camping-usa.com
"There is no outfit less entitled to lecture me about bloat
than the federal government" -- Tony Snow
==========================================================================
Thus spake Tom Lane
but I can see the reasonableness of defining "3 != NULL" as TRUE.
Actually I see it as FALSE. That's what I was suggesting earlier. All
comparisons to null should be false no matter what the sense of the
test.Hmm. That yields extremely unintuitive results for = and !=. That is,
SELECT * FROM t WHERE b = NULL;
will never return any rows, even if there are some where b is null;
Hmmm. That would be a problem. Of course, we could treat the null
value at the higher level too. I guess that's why we have the "IS
NULL" syntax in the first place. It is different than comparing the
actual values.
Marc, how long can we hold 6.4 while we work this all out?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
On Tue, 3 Nov 1998, D'Arcy J.M. Cain wrote:
Thus spake Tom Lane
but I can see the reasonableness of defining "3 != NULL" as TRUE.
Actually I see it as FALSE. That's what I was suggesting earlier. All
comparisons to null should be false no matter what the sense of the
test.Hmm. That yields extremely unintuitive results for = and !=. That is,
SELECT * FROM t WHERE b = NULL;
will never return any rows, even if there are some where b is null;
Hmmm. That would be a problem. Of course, we could treat the null
value at the higher level too. I guess that's why we have the "IS
NULL" syntax in the first place. It is different than comparing the
actual values.Marc, how long can we hold 6.4 while we work this all out?
How long can we hold *what*? Is this a new bug that didn't exist
in previous version of PostgreSQL?
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Thus spake The Hermit Hacker
Marc, how long can we hold 6.4 while we work this all out?
How long can we hold *what*? Is this a new bug that didn't exist
in previous version of PostgreSQL?
Jeez Marc, you must be working too hard. I'll have to remember to
sprinkle those smileys a little better next time. :-)
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.On Wed, 4 Nov 1998, D'Arcy J.M. Cain wrote:
Thus spake The Hermit Hacker
Marc, how long can we hold 6.4 while we work this all out?
How long can we hold *what*? Is this a new bug that didn't exist
in previous version of PostgreSQL?Jeez Marc, you must be working too hard. I'll have to remember to
sprinkle those smileys a little better next time. :-)
*roll eyes* *groan* *wipe brow*
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
D'Arcy J.M. Cain <darcy@{druid|vex}.net> writes:
Thus spake Tom Lane
but I can see the reasonableness of defining "3 != NULL" as TRUE.
Actually I see it as FALSE. That's what I was suggesting earlier. All
comparisons to null should be false no matter what the sense of the
test.Hmm. That yields extremely unintuitive results for = and !=. That is,
SELECT * FROM t WHERE b = NULL;
will never return any rows, even if there are some where b is null;
Hmmm. That would be a problem. Of course, we could treat the null
value at the higher level too. I guess that's why we have the "IS
NULL" syntax in the first place. It is different than comparing the
actual values.
Not sure how serious this discussion is, so if I have wandered into the
middle of a joke, just kick me ...
That said,
SELECT * FROM t WHERE b = NULL;
_should not_ return any rows. NULL is not "=" to anything, not even another
NULL. NULL is also not ">", or "<", or "!=" to anything either.
So, "NULL = NULL" is false as is "NULL != NULL".
This indeed is why we have "IS NULL" and "IS NOT NULL".
-dg
David Gould dg@informix.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"Samba is a huge win ... ; it enables open-source techies to stealth
their Linux boxes so they look like Microsoft servers that somehow
miraculously fail to suck." -- Eric Raymond
Thus spake David Gould
D'Arcy J.M. Cain <darcy@{druid|vex}.net> writes:
Hmmm. That would be a problem. Of course, we could treat the null
value at the higher level too. I guess that's why we have the "IS
NULL" syntax in the first place. It is different than comparing the
actual values.SELECT * FROM t WHERE b = NULL;
_should not_ return any rows. NULL is not "=" to anything, not even another
NULL. NULL is also not ">", or "<", or "!=" to anything either.So, "NULL = NULL" is false as is "NULL != NULL".
This indeed is why we have "IS NULL" and "IS NOT NULL".
But no one really has a use for a statement that can never return a row.
If we need that we always have "-- select 1;" (1/2 :-)) so why not let
"= NULL" be a synonym for "IS NULL" and "NOT = NULL" be a synonym for "IS
NOT NULL?"
Well, other than the fact that M$ does it? (Other 1/2 of that :-))
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.