Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> Ah, well, I have a (late draft) of the SQL92 standard. And here is what it
> has to say on the matter (note 2-b-i):
> Case:
> a) If the <general set function> COUNT is specified, then the
> result is the cardinality of TXA.
> [ie, 0 for an empty set - tgl]
> b) If AVG, MAX, MIN, or SUM is specified, then
> Case:
> i) If TXA is empty, then the result is the null value.
Well, this is certainly adequate precedent for the behavior of these
particular aggregates --- although I'd have to say that the standard-
writers blew it for SUM; SUM of an empty set ought to return 0 not
null. (It looks like Postgres follows the spec, however.)
Anyway, we have here precedent for deciding on a function-by-function
basis whether an aggregate over an empty selection should return NULL
or return an appropriate ordinary value. There's no case in which the
result of the aggregate has no rows, however. So I think Postgres
is fairly standards-compliant on this point.
Now that I think about it, the arguments on the hackers list were not
about the plain SELECT case but about the GROUP BY case. For example,
if you do
SELECT productname, AVG(saleprice) FROM sales GROUP BY productname;
then you get a row in the output for each different productname, and
a separate instance of AVG is run over the prices for each group.
(Unless there are NULLs in the saleprice column, none of the AVG
instances could ever return a null result.)
BUT: what happens if the sales table is empty? There are no
productnames, therefore no groups, therefore no rows ought to appear
in the output (IMHO). However, what Postgres actually does right now
is to emit one all-nulls row (but only if an aggregate function was
used; if you say "SELECT productname FROM sales GROUP BY productname"
then you get no rows). That is the behavior that we've gone 'round and
'round on without any resolution; it seems obviously inconsistent to me,
but others think it's OK because it parallels what happens in the non-
GROUP BY case.
Is there anything in the SQL92 spec addressing this point?
regards, tom lane
