Re: max(*)

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