Re: max(*)

Shouldn't

SELECT max(*) FROM foo;

give an error? Instead it's executed like

SELECT max(1) FROM foo;

Just like count(*) is executed as count(1).

Something for the TODO or is it a feature?

ps. I know it's not an important case since no one sane would try to
calculate max(*), but still.

/Dennis

On Fri, 2006-05-26 at 10:22 +0200, Dennis Bjorklund wrote:
> Shouldn't
>
> SELECT max(*) FROM foo;
>
> give an error?

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

> Instead it's executed like
>
> SELECT max(1) FROM foo;
>
> Just like count(*) is executed as count(1).
>
> Something for the TODO or is it a feature?

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.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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=#

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

> 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.

We shouldn't. The spec's prohibition is based on the assumption that
the only aggregate functions in existence are those listed in the spec.
Since we allow user-defined aggregates, who are we to say that there are
no others for which "*" is sensible?

You could imagine adding a catalog attribute to aggregate functions to
say whether they allow "*", but quite honestly that strikes me as a
waste of implementation effort. The amount of work would be nontrivial
and the benefit negligible.

(Another possibility, if we get around to implementing N-argument
aggregates, is to equate "agg(*)" to an invocation of a zero-argument
aggregate as I suggested awhile ago. Then count() would be the only
zero-argument aggregate mentioned in the standard catalogs. That would
at least fall out of some work that's actually worth doing ...)

regards, tom lane

On Fri, May 26, 2006 at 11:03:17AM -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > On Fri, 2006-05-26 at 10:22 +0200, Dennis Bjorklund wrote:
> >> Shouldn't
> >> SELECT max(*) FROM foo;
> >> give an error?
>
> > 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.
>
> We shouldn't. The spec's prohibition is based on the assumption that
> the only aggregate functions in existence are those listed in the spec.
> Since we allow user-defined aggregates, who are we to say that there are
> no others for which "*" is sensible?

But if aggregate(*) just gets turned into aggregate(1) by the backend,
why not just tell people to use aggregate(1) for their custom
aggregates? Or am I misunderstanding how aggregate(*) is actually
handled?

My concern is that it's not inconceiveable to typo max(field) into
max(*), which could make for a rather frustrating error. Not to mention
this being something that could trip newbies up. If nothing else I'd say
it warrants a %TODO just so it doesn't end up on the PostgreSQL gotcha's
page. :)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Fri, May 26, 2006 at 14:06:29 -0500,
"Jim C. Nasby" <jnasby(at)pervasive(dot)com> wrote:
>
> But if aggregate(*) just gets turned into aggregate(1) by the backend,
> why not just tell people to use aggregate(1) for their custom
> aggregates? Or am I misunderstanding how aggregate(*) is actually
> handled?
>
> My concern is that it's not inconceiveable to typo max(field) into
> max(*), which could make for a rather frustrating error. Not to mention
> this being something that could trip newbies up. If nothing else I'd say
> it warrants a %TODO just so it doesn't end up on the PostgreSQL gotcha's
> page. :)

Tom's suggestion that (*) map to () which would refer to a zero argument
aggregate would cover this case, since there wouldn't be a zero argument
version of max.

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> My concern is that it's not inconceiveable to typo max(field) into
> max(*), which could make for a rather frustrating error. Not to mention
> this being something that could trip newbies up. If nothing else I'd say
> it warrants a %TODO just so it doesn't end up on the PostgreSQL gotcha's
> page. :)

count(*) has been implemented that way since about 1999, and no one's
complained yet, so I think you are overstating the importance of the
problem.

regards, tom lane

On Friday 26 May 2006 09:45, Gurjeet Singh wrote:
> 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)
>

given:

pagila=# select 1 from t1;
?column?
----------
1
1
1
1
1
(5 rows)

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

this makes sense

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

and so does this

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

not so much

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

but this does

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

not here though

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

nor here

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

or here

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

yep... but really most aggregates are ok with a 1

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL