Should Aggregate Functions always return one row?

Started by Wilhelm Pakullaabout 22 years ago2 messagesbugs
Jump to latest
#1Wilhelm Pakulla
wilhelm.pakulla@gmx.de

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Wilhelm Pakulla (#1)
Re: Should Aggregate Functions always return one row?

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