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

But, and here's the rub, which is the "correct" way to handle it? I'm
looking through the SQL99 spec to see if I can find an answer.