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.

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.

"g" == grant <grant@xactcommerce.com> writes:

g> However, the last time i checked, NULL does not equal to 1, 2, 3,
g> or anything else besides NULL. Thats why I'm confused as to why
g> SELECT column FROM table WHERE column != 1 also excludes NULL,
g> because 1 does NOT have the same value as NULL.

Ok, NULL is not equal to 1, 2, 3, or anything else. It is also not
equal to itself, and it is not inequal to anything. NULL is special.

If you want to treat it the same as zero in your app, then you are
setting up your tables incorrectly. Make your column "NOT NULL
default 0".

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

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/

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