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.

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