4

For the following query

SELECT sum(c) 
FROM (SELECT 1 c WHERE false) t;

I thought it takes the sum of zero numbers, and therefore should return 0. (Similarly, I expect, e.g. an array aggregate function to return an empty array on zero elements).

Yet, it is returning a NULL (using PostgreSQL 11).

=> SELECT sum(c) FROM (SELECT 1 c WHERE false) t; 
 sum 
-----

(1 row)

This doesn't make sense to me logically.

Why is the sum of zero numbers (for any numeric type) not zero? Is this NULL returning behavior standard?

Or maybe I am missing something. Any reason one has to make the sum NULL in some corner cases?

tinlyx
  • 3,540
  • 13
  • 46
  • 72

1 Answers1

5

This is a requirement from the SQL standard. I'm most familiar with the 1992 standard. Quoting from it, with emphasis mine:

General Rules

1) Case:

a) If COUNT(*) is specified, then the result is the cardinality of T.

6.5

b) Otherwise, let TX be the single-column table that is the result of applying the 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.

2) If DISTINCT is specified, then let TXA be the result of elimi-
nating redundant duplicate values from TX. Otherwise, let TXA be
TX.

Case:

a) If the COUNT is specified, then the result is the cardinality of TXA.

b) If AVG, MAX, MIN, or SUM is specified, then

Case:

i) If TXA is empty, then the result is the null value.

In short, NULLs are always excluded from the result of SUM, MAX, MIN, and AVG. I can't tell you exactly why this is, other than I think it's useful to be able to distinguish between a sum of a single value of 0 and a sum of an empty set.

Joe Obbish
  • 32,165
  • 4
  • 71
  • 151