Re: Syntax bug? Group by?

> Mark Woodward wrote:
>>>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15;
>>>>
>>
>> I still assert that there will always only be one row to this query.
>> This
>> is an aggregate query, so all the rows with ycis_id = 15, will be
>> aggregated. Since ycis_id is the identifying part of the query, it
>> should
>> not need to be grouped.
>>
>> My question, is it a syntactic technicality that PostgreSQL asks for a
>> "group by," or a bug in the parser?
>>
> I think your point is that every non-aggregate column in the results of
> the query also appears in the where clause and is given a single value
> there, so conceivably, an all-knowing, all-powerful postgres could
> recognize this and do the implied GROUP by on these columns.

Not exactly.
>
> I'm not in a position to give a definitive answer on this, but I suspect
> that adjusting the query parser/planner to allow an implied GROUP BY
> either gets prohibitively complicated, or fits too much of a special
> case to be worth implementing.
>
> select
>     ycis_id,
>     some_other_id,
>     min(tindex),
>     avg(tindex)
>     from
>         y
>     where
>         ycis_id = 15
>     group by
>         some_other_id;

This is not, in fact, like the example I gave and confuses the point I am
trying to make.


The original query:
select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15;

ycis_id is unambiguous and MUST be only one value, there should be no
requirement of grouping. In fact, a "group by" implies multiple result
rows in an aggregate query.

As I said in other branches of this thread, this isn't a SQL question, it
is a question of whether or not the PostgreSQL parser is correct or not,
and I do not believe that it is working correctly.