Thread: Re: very big problem with NULL
On Thu, 07 Jun 2001 19:11:21 -0400, Grant <grant@xactcommerce.com> wrote: > ok. i've found a weird bug. I have five records in a table. in one > column i'm doing a select based on, two values for the column are NULL. > i do a 'SELECT testcolumn FROM testtable WHERE testcolumn!=1'. This > query for some reason also excludes NULL, which does not make any sense > considering NULL is not equal to 1, so the records with NULL in this > column should be showing up as well. The thing about NULL is ... you just don't know! Maybe the column is number of children and sometimes when people are entering data they forget to ask that question and so don't enter that data (and you allow that, by not marking the column NOT NULL) so some of the records have a "value" of NULL. That does NOT mean that those people don't have 1 child. Therefore, when you want those people which DEFINITELY DON'T have 1 child, those records are not returned. Now, if you want the records which don't have 1 child or which you don't know how many children there are, you can do that.
Grant <grant@xactcommerce.com> schreef: >However, the last time i checked, NULL does not equal to 1, 2, 3, or >anything else besides NULL. Thats why I'm confused as to why SELECT column >FROM table WHERE column != 1 also excludes NULL, because 1 does NOT have the >same value as NULL. NULL is unknown. Therefore, it is unknown if NULL equals 1, and it is also unknown if NULL does not equal 1. NULL = 1 --> NULL NULL != 1 --> NULL That's why SQL has the special operator IS NULL and IS NOT NULL (or NOT IS NULL, I allways forget which one). -- Vriendelijke groet, René Pijlman <rpijlman@spamcop.net> Wat wil jij leren? http://www.leren.nl/
Lee Harr wrote: > On Thu, 07 Jun 2001 19:11:21 -0400, Grant <grant@xactcommerce.com> wrote: > > ok. i've found a weird bug. I have five records in a table. in one > > column i'm doing a select based on, two values for the column are NULL. > > i do a 'SELECT testcolumn FROM testtable WHERE testcolumn!=1'. This > > query for some reason also excludes NULL, which does not make any sense > > considering NULL is not equal to 1, so the records with NULL in this > > column should be showing up as well. > > The thing about NULL is ... you just don't know! > > Maybe the column is number of children and sometimes when people > are entering data they forget to ask that question and so don't > enter that data (and you allow that, by not marking the column > NOT NULL) so some of the records have a "value" of NULL. > > That does NOT mean that those people don't have 1 child. > > Therefore, when you want those people which DEFINITELY DON'T > have 1 child, those records are not returned. > > Now, if you want the records which don't have 1 child or which > you don't know how many children there are, you can do that. so what you're saying is NULL = * in Postgres?? With every other database server I've used, NULL is its own value, not any value, or I'm completely misunderstanding what you're trying to say here. However, the last time i checked, NULL does not equal to 1, 2, 3, or anything else besides NULL. Thats why I'm confused as to why SELECT column FROM table WHERE column != 1 also excludes NULL, because 1 does NOT have the same value as NULL.
Grant <grant@xactcommerce.com> writes:
> However, the last time i checked, NULL does not equal to 1, 2, 3, or
> anything else besides NULL.
NULL does not equal NULL, either. Read the SQL spec, or any of the
many prior coverings of this territory in our list archives ...
regards, tom lane
> > The thing about NULL is ... you just don't know! <snip what I thought was a very good example> > so what you're saying is NULL = * in Postgres?? With every other database > server I've used, NULL is its own value, not any value, or I'm completely > misunderstanding what you're trying to say here. Either you are mistaken, or those other databases were incorrectly implemented (assuming they were following the SQL standard). Here is an independant Knowledge Base item (with a great subject: "SQL: NULL is not Nothing") that discusses NULLs in Oracle: http://www.metrokc.gov/gis/services/KnowledgeBase/SQLtipNull.htm They even quote the Oracle documentation: "To test for nulls, use only the comparison operators IS NULL and IS NOT NULL. If you use any other operator with nulls and the result depends on the value of the null, the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null." > However, the last time i checked, NULL does not equal to 1, 2, 3, or > anything else besides NULL. Thats why I'm confused as to why SELECT column > FROM table WHERE column != 1 also excludes NULL, because 1 does NOT have the > same value as NULL. NULL does NOT *have* a value. NULL is an *undefined* value. Therefore you can't say if it is equal to a value or not. They probably said it better than I am: "A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows."
Greetings, Tom!
At 13.06.2001, 00:20, you wrote:
TL> Grant <grant@xactcommerce.com> writes:
>> However, the last time i checked, NULL does not equal to 1, 2, 3, or
>> anything else besides NULL.
TL> NULL does not equal NULL, either. Read the SQL spec, or any of the
TL> many prior coverings of this territory in our list archives ...
Er, how about this stuff?
newweb=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
newweb=# select null=null;
?column?
----------
t
(1 row)
--
Yours, Alexey V. Borzov, Webmaster of RDW.ru
On Wed, Jun 13, 2001 at 04:33:40PM +0400, > > newweb=# select version(); > version > --------------------------------------------------------------------- > PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 > (1 row) > > newweb=# select null=null; > ?column? > ---------- > t > (1 row) This is even stranger: area=> select null<>null; ?column? ---------- (1 row)
Alexey Borzov <borz_off@rdw.ru> writes:
> TL> NULL does not equal NULL, either. Read the SQL spec, or any of the
> TL> many prior coverings of this territory in our list archives ...
> Er, how about this stuff?
> newweb=# select null=null;
> ?column?
> ----------
> t
> (1 row)
That's in the archives, too: there's a special hack to convert the
literal sequence "= NULL" into the SQL-legal "IS NULL" operator.
Wihout that hack, Microsoft Access' forms code does not work (seems
no one at M$ can read the spec :-().
In fact I was just arguing a few days ago on pghackers that it's time
to remove that hack, because it confuses too many people... I seem to
have lost the argument (again), but it's still a pet peeve.
Here's an example:
regression=# create table foo (f1 int);
CREATE
regression=# insert into foo values (1);
INSERT 412352 1
regression=# insert into foo values (null);
INSERT 412353 1
regression=# select f1 = f1 from foo;
?column?
----------
t
(2 rows)
regression=# create view vfoo as select f1 = null from foo;
CREATE
-- Peeking at the view definition shows how the parser interpreted this:
regression=# \d vfoo
View "vfoo"
Attribute | Type | Modifier
-----------+---------+----------
?column? | boolean |
View definition: SELECT (foo.f1 ISNULL) FROM foo;
regression=# select * from vfoo;
?column?
----------
f
t
(2 rows)
regards, tom lane