From: | "Mark Woodward" <pgsql(at)mohawksoft(dot)com> |
---|---|
To: | "Nolan Cafferky" <Nolan(dot)Cafferky(at)rbsinteractive(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Syntax bug? Group by? |
Date: | 2006-10-17 18:46:54 |
Message-ID: | 18219.24.91.171.78.1161110814.squirrel@mail.mohawksoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2006-10-17 18:48:09 | Re: Syntax bug? Group by? |
Previous Message | Mark Woodward | 2006-10-17 18:41:25 | Re: Syntax bug? Group by? |