Re: max(*)

On 5/26/06, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Fri, 2006-05-26 at 10:22 +0200, Dennis Bjorklund wrote:
> > Shouldn't
> >
> > SELECT max(*) FROM foo;
> >
> > give an error?

IMO, yes.

>
> SQL:2003 would not allow this; SQL:2003 permits only COUNT(*) and no
> other aggregate function. All other aggregates require a value
> expression.
>

This precisely being the reason.

> > Instead it's executed like
> >
> > SELECT max(1) FROM foo;
> >
> > Just like count(*) is executed as count(1).
> >

That's right; see the intearction pasted below.

> > Something for the TODO or is it a feature?

We definitely cannot tout it as a feature, because it is not even a 'useful
extension of the standard'

> Doesn't seem an important or even useful extension of the standard, but
> would probably require special case processing for every aggregate
> function in order to implement that. Its not dangerous... so I'm not
> sure we should take any action at all.

A TODO wouldn't do any harm. If somebosy comes up with some smart solution,
you can always incorporate it.

Something not supported should be stated as such through an ERROR. Except
for count(), none of the following make any sense:

The transcipt:

test=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
Indexes:
"t1_pkey" PRIMARY KEY, btree (a)

test=# select * from t1;
a
---
1
2
3
4
5
(5 rows)

test=# select count(*) from t1;
count
-------
5
(1 row)

test=# select count(1) from t1;
count
-------
5
(1 row)

test=# select max(*) from t1;
max
-----
1
(1 row)

test=# select max(1) from t1;
max
-----
1
(1 row)

test=# select min(*) from t1;
min
-----
1
(1 row)

test=# select avg(*) from t1;
avg
------------------------
1.00000000000000000000
(1 row)

test=# select sum(*) from t1;
sum
-----
5
(1 row)

test=# select sum(1) from t1;
sum
-----
5 <--- this is correct
(1 row)

test=#