Re: [BUGS] We are not following the spec for HAVING without GROUP

Greg Stark <gsstark(at)mit(dot)edu> 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