Thread: 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 <<a href="mailto:simon@2ndquadrant.com" target="_blank">simon@2ndquadrant.com</a>> wrote:<br/>> On Fri, 2006-05-26 at 10:22 +0200, Dennis Bjorklund wrote: <br />> > Shouldn't<br />> ><br />>> SELECT max(*) FROM foo; <br />> ><br />> > give an error?<br /><br />IMO, yes.<br /><br />><br />> SQL:2003 would not allow this; SQL:2003 permits only COUNT(*) and no<br />> other aggregate function.All other aggregates require a value<br /> > expression.<br />> <br /><br />This precisely being the reason.<br/><br />> > Instead it's executed like<br />> ><br />> > SELECT max(1) FROM foo;<br />>><br />> > Just like count(*) is executed as count(1). <br />> ><br /><br />That's right; see the intearctionpasted below.<br /><br />> > Something for the TODO or is it a feature?<br /><br />We definitely cannottout it as a feature, because it is not even a 'useful extension of the standard' <br /><br />> Doesn't seem animportant or even useful extension of the standard, but <br />> would probably require special case processing for everyaggregate<br />> function in order to implement that. Its not dangerous... so I'm not<br />> sure we should takeany action at all.<br /><br />A TODO wouldn't do any harm. If somebosy comes up with some smart solution, you can alwaysincorporate it. <br /><br />Something not supported should be stated as such through an ERROR. Except for count(),none of the following make any sense:<br /><br />The transcipt:<br /><br /><span style="font-family: courier new,monospace;">test=#\d t1 </span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> Table"public.t1"</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> Column | Type | Modifiers</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> --------+---------+--</span><span style="font-family:courier new,monospace;">---------</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> a | integer | not null </span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">Indexes:</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> "t1_pkey" PRIMARY KEY, btree (a) </span><br style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">test=# select * from t1;</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> a</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">---</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> 1</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> 2</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> 3</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> 4</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> 5</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">(5 rows)</span><br style="font-family: courier new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">test=#select count(*) from t1; </span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> count</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">------- </span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> 5</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;">(1 row) </span><br style="font-family: courier new,monospace;" /><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">test=# select count(1) fromt1;</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> count</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">-------</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> 5</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">(1row)</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"></span><span style="font-family: courier new,monospace;">test=#select max(*) from t1;<br /> max<br />-----<br /> 1 <br />(1 row) <br /><br /></span><span style="font-family:courier new,monospace;">test=# select max(1) from t1;</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> max</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">-----</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> 1 </span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">(1 row)<br /><br />test=# select min(*) from t1;<br /> min<br/>-----<br /> 1<br />(1 row)<br /><br />test=# select avg(*) from t1; <br /> avg<br />------------------------<br/> 1.00000000000000000000<br />(1 row)<br /><br />test=# select sum(*) from t1;<br /> sum<br/>-----<br /> 5<br />(1 row)<br /><br />test=# select sum(1) from t1;<br /> sum<br />-----<br /> 5 <--- this is correct <br />(1 row)<br /><br />test=#<br style="font-family: courier new,monospace;" /></span><br/>
Simon Riggs <simon@2ndquadrant.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@2ndquadrant.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@pervasive.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@pervasive.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@pervasive.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