Thread: Should Aggregate Functions always return one row?

Hello.

Is this a bug?

>linux=# SELECT * FROM test;
> x
>---
> 1
> 2
> 3
>(3 Rows)
>
>linux=# SELECT * FROM test WHERE FALSE;
> x
>---
>(0 Rows)
>
>phlegma=# SELECT max(x) FROM test WHERE FALSE;
> max
>-----
>
>(1 Rows)
>
>phlegma=#

So, if I use the max() aggregate, I receive one row with the value NULL. Is
that correct?

My background is the language plpgsql:

I expected to test for the existance of a maximum with
(...)
SELECT INTO e
  max( y ) AS x FROM (table) WHERE (condition);

IF NOT FOUND THEN RETURN; END IF;
(...)

But I have to test with:

(...)
SELECT INTO e
  max( y ) AS x FROM (table) WHERE (condition);

IF e.x IS NULL THEN RETURN; END IF;
(...)

Thanks in advance,
Wilhelm

wilhelm.pakulla@gmx.de writes:
> So, if I use the max() aggregate, I receive one row with the value NULL. Is
> that correct?

Yup, that's what the SQL spec says to do, and it seems reasonable to me.

IIRC, the spec also says that SUM() over no rows returns NULL, which is
less reasonable --- I'd have defined it as returning zero, myself.
But when in doubt we follow the spec.

            regards, tom lane