Jean-Christian Imbeault wrote:
> Why is it that "select null + 1" gives null but "select sum(a) from
> table" where there are null entries returns an integer?
>
> Shouldn't the sum() and "+" operators behave the same?
---
SQL92 (6.5 <set function specification>):
1) Case:
a) If COUNT(*) is specified, then the result is the cardinality of T.
b) Otherwise, let TX be the single-column table that is the result of
applying the <value expression> to each row of T and eliminating null
values. If one or more null values are eliminated, then a completion
condition is raised: warning-null value eliminated in set function.
---
So PostgreSQL is compliant with SQL92. Reading the above should
concern you regarding COUNT() as well:
CREATE TABLE foo (value integer);
INSERT INTO foo VALUES (NULL);
INSERT INTO foo VALUES (3);
Compare:
SELECT COUNT(*) FROM foo;
vs.
SELECT COUNT(value) FROM foo;
SQL has its problems. Of course, you could avoid this entirely by not
using NULLs :-)
Mike Mascari
mascarm@mascari.com