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.
select
ycis_id,
some_other_id,
min(tindex),
avg(tindex)
from
y
where
ycis_id = 15
group 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:
select
ycis_id,
some_other_id,
min(tindex),
avg(tindex)
from
y
where
ycis_id = some_single_valued_constant(foo, bar)
group by
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
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
