Thread: max(*)

max(*)

From
Dennis Bjorklund
Date:
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


Re: max(*)

From
Simon Riggs
Date:
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



Re: max(*)

From
"Gurjeet Singh"
Date:
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/> 

Re: max(*)

From
Tom Lane
Date:
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


Re: max(*)

From
"Jim C. Nasby"
Date:
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


Re: max(*)

From
Bruno Wolff III
Date:
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.


Re: max(*)

From
Tom Lane
Date:
"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


Re: max(*)

From
Robert Treat
Date:
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