From postgresql 8.0.1 on FreeBSD:
syslog-ng=# select NULL is null;
?column?
----------
t
(1 row)
syslog-ng=# select (0+NULL) is null;
?column?
----------
t
(1 row)
syslog-ng=# select (0) is null;
?column?
----------
f
(1 row)
syslog-ng=# create table tmp1 ( a int4 );
CREATE TABLE
syslog-ng=# insert into tmp1 values (2);
INSERT 16949751 1
syslog-ng=# insert into tmp1 values (0);
INSERT 16949752 1
syslog-ng=# insert into tmp1 values (NULL);
INSERT 16949756 1
syslog-ng=# select * from tmp1;
a
---
2
0
(3 rows)
syslog-ng=# select a, a is null from tmp1;
a | ?column?
---+----------
2 | f
0 | f
| t
(3 rows)
syslog-ng=# select sum(a) from tmp1;
sum
-----
2
(1 row)
syslog-ng=# drop table tmp1;
DROP TABLE
syslog-ng=# \q
So, in some cases (explicite addition), NULL + 0 = NULL. (we all know
that NULL != NULL). However, the "sum" function skips NULLs. This
seems inconsistant.
I understand the rational for "NULL + anything" returning NULL (makes
sense mathematically). I understand the utility of having "sum" skip
NULLs. However, we can now construct a math in the sql engine where
a+b+c != a+b+c. Yucky.
