> On Oct 17, 2006, at 15:19, Peter Eisentraut wrote: > >> Mark Woodward wrote: >>> Shouldn't this work? >>> >>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; >>> >>> ERROR: column "y.ycis_id" must appear in the GROUP BY clause or be >>> used in an aggregate function >> >> This would require a great deal of special-casing, in particular >> knowledge of the = operator, and then the restriction to a particular >> form of the WHERE clause. For overall consistency, I don't think this >> should be allowed. > > In this particular case, the client constructing the query *knows* > the value of ycis_id (since the client is generating the "ycis_id = > 15" clause). It's technically just a waste of bandwidth and server > resources to recalculate it. If you really want to replicate the > output of the query you proposed, you could rewrite it on the client as: > > select 15 as ycis_id, min(tindex), avg(tindex) from y where > ycis_id = 15; > > You could argue that the server should do this for you, but it seems > ugly to do in the general case. And, like Peter points out, would > need a lot of special-casing. I guess the parser could do it for > expressions in the SELECT clause that exactly match expressions in > the WHERE clause. >
But, and here's the rub, which is the "correct" way to handle it? I'm looking through the SQL99 spec to see if I can find an answer. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match