Re: Syntax bug? Group by?

On Oct 17, 2006, at 15:19, Peter Eisentraut wrote:

> Mark Woodward wrote:
>> Shouldn't this work?
>>
>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15;
>>
>> ERROR:  column "y.ycis_id" must appear in the GROUP BY clause or be
>> used in an aggregate function
>
> This would require a great deal of special-casing, in particular
> knowledge of the = operator, and then the restriction to a particular
> form of the WHERE clause.  For overall consistency, I don't think this
> should be allowed.

In this particular case, the client constructing the query *knows*  
the value of ycis_id (since the client is generating the "ycis_id =  
15" clause). It's technically just a waste of bandwidth and server  
resources to recalculate it. If you really want to replicate the  
output of the query you proposed, you could rewrite it on the client as:
    select 15 as ycis_id, min(tindex), avg(tindex) from y where  
ycis_id = 15;

You could argue that the server should do this for you, but it seems  
ugly to do in the general case. And, like Peter points out, would  
need a lot of special-casing. I guess the parser could do it for  
expressions in the SELECT clause that exactly match expressions in  
the WHERE clause.

Thanks!

- Chris