Greg Stark <[EMAIL PROTECTED]> writes: > On Wed, 2005-03-09 at 21:21 -0500, Tom Lane wrote: >> Comments? Can anyone confirm whether DB2 or other databases allow >> ungrouped column references with HAVING?
> Mysql treats ungrouped columns as an assertion that those columns will all be > equal for the group and it can pick an arbitrary one. Essentially it does an > implicit "first(x) AS x". The expected use case is for things like: > select count(*),a.* > from a,b > where a.pk = b.a_fk > group by a.pk [ Your comment is completely unrelated to my question, but anyway: ] Well, that query is actually legitimate per SQL99 (though not per SQL92) if a.pk actually is a primary key. A correct implementation of SQL99 would deduce that the columns of A are all functionally dependent on a.pk and not make you list them in GROUP BY. I dunno whether mysql goes through that pushup or whether they just assume the user knows what he's doing (though from what I know of their design philosophy I bet the latter). I'm not sure if we have a TODO item about working on the SQL99 grouping rules, but I'd like to see us implement at least the simpler cases, such as this one. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match