From: MT
To: Editor
Date: 12 Oct 2004
I've been reading your paper with great interest... Haven't
finished it completely yet (it's getting late here), but I surely will
tomorrow. Nevertheless, there is a mistake regarding NULLs and the SQL standard
though.
According to your paper, page 9, COUNT does not ignore NULLs.
That is not entirely correct. According to the SQL standard, NULLs are ignored
if that's the only thing your counting. This does get you pretty weird results
though - have a look at this example:
CREATE TABLE null_count
(id INTEGER);
INSERT INTO null_count(id) VALUES (1);
INSERT INTO null_count(id) VALUES (2);
INSERT INTO null_count(id) VALUES (NULL);
INSERT INTO null_count(id) VALUES (NULL);
This returns 4:
SELECT COUNT(*)
FROM null_count;
This, however, returns 2:
SELECT COUNT(id)
FROM null_count;
And this returns 0:
SELECT COUNT(id)
FROM null_count
WHERE id IS NULL;
Unlike this, which returns 2 again:
SELECT COUNT(*)
FROM null_count
WHERE id IS NULL;
And you mention that x IS NOT NULL is not the same as NOT(x
IS NULL). I'm unsure if this is the case as the following two statements return
the same for me:
SELECT *
FROM null_count
WHERE NOT (id IS NULL);
and
SELECT *
FROM null_count
WHERE id IS NOT NULL;
Just letting you know.
From: Fabian Pascal
To: TM
To be honest with
you, I really stopped dealing with the SQL crappola a while ago. And your
COUNT() results do not motivate me to change my mind, just the opposite. Don't
need ulcers.
C. J. Date
Responds: COUNT(*) does not ignore
NULLs.
x IS NOT NULL and
NOT(x IS NOT NULL) are the same if the row X has degree one. Not otherwise. Note that X here is always a row (in the standard), not, as most people believe, a scalar!
Point is, there are two operators in SQL both (lovely) called
COUNT
COUNT(*)
COUNT(…)
And they behave differently (different semantics).
Ed. Note: For a
logically correct solution to missing data, see PRACTICAL
DATABASE FOUNDATIONS paper #8, The Final NULL in the
Coffin.
Posted 2/4/05
