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.
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.
selectycis_id,some_other_id,min(tindex),avg(tindex)from ywhere ycis_id = 15group by some_other_id;
Here, postgres would have to use the group by you specified, and also
recognize the single-valued constant assigned to ycis_id. Maybe not too
bad, but:
selectycis_id,some_other_id,min(tindex),avg(tindex)from ywhere ycis_id = some_single_valued_constant(foo,
bar)groupby some_other_id;
In this case, postgres doesn't know whether
some_single_valued_constant() will really return the same single value
for every tuple. Ultimately, as more complex queries are introduced, it
would become a lot simpler for the query writer to just specify the
group by columns instead of trying to guess it from the where clause.
Final note: I could also see situations where an implied group by would
silently allow a poorly written query to execute, instead of throwing an
error that suggests to the query writer that they did something wrong.
--
Nolan Cafferky
Software Developer
IT Department
RBS Interactive
nolan.cafferky@rbsinteractive.com
