Re: Syntax bug? Group by?

Dnia 17-10-2006 o godz. 23:21 Tom Lane napisał(a):
> Andrew Dunstan <andrew@dunslane.net> writes:
> > Mark Woodward wrote:
> >> My question, is it a syntactic technicality that PostgreSQL asks for a
> >> "group by," or a bug in the parser?
> 
> > AFAIK what you want is not per sql spec.
> 
> It would in fact be a violation of spec.  Consider the case where there
> are no rows matching 15.  In this case
> 
> select min(tindex), avg(tindex) from y where ycis_id = 15;
> 
> will yield one row containing NULLs, whereas
> 
> select min(tindex), avg(tindex) from y where ycis_id = 15 group by 
> ycis_id;
> 
> will yield no rows (because there are no groups).  Therefore, if
> Postgres were to implicitly insert a GROUP BY to make it legal to
> reference ycis_id directly, we'd be changing the query behavior
> and breaking spec.
> 
>             regards, tom lane
> 


Hello

IMHO:

Also take into consider that - what should be expected behavior of this:

select ycis_id, min(tindex), avg(tindex) from y where ycis_id = truncate(random()*100);

Since result of comparission is not known on parsing phase it couldn't be done here if there is no other requirements
onargum