Re: sum gives different answer

Chairudin Sentosa <chairudin(at)prima(dot)net(dot)id> writes:
> I have two SQL statements that I expect to give (0 rows) as output.
> However the first one with 'sum' does not do that.

> select pin, sum(duration) from tablename where date(start_time)=
> (select date 'today') group by pin;
> pin|sum
> - ---+---
> |
> (1 row)

> select pin, duration from tablename where date(start_time)=
> (select date 'today') group by pin;
> pin|duration
> - ---+--------
> (0 rows)

As George Moga pointed out, SUM() applied to an empty collection of tuples
still gives a result (one tuple containing NULL). Aggregates in general
will give a result of some kind for an empty input collection --- for
example, you'd surely want COUNT() to return 0 not nothing.

However what you're talking about here is different: there are no groups
in the result, therefore SUM() should never have been applied at all,
not applied once to an empty set of tuples. I'm firmly of the opinion
that the first example above is a bug. The hackers list has been around
on this question a couple of times, and there are some folks who claim
that the current behavior is OK, but I'm at a loss to follow their
reasoning.

regards, tom lane