Re: select null + 0 question

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(at)mascari(dot)com