Re: [BUGS] We are not following the spec for HAVING without GROUP - Mailing list pgsql-hackers

From Tom Lane
Subject Re: [BUGS] We are not following the spec for HAVING without GROUP
Date
Msg-id 5753.1110782944@sss.pgh.pa.us
Whole thread Raw
In response to Re: [BUGS] We are not following the spec for HAVING without GROUP  (Greg Stark <gsstark@mit.edu>)
List pgsql-hackers
Greg Stark <gsstark@mit.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


pgsql-hackers by date:

Previous
From: Neil Conway
Date:
Subject: invalidating cached plans
Next
From: Tom Lane
Date:
Subject: Re: [BUGS] We are not following the spec for HAVING without GROUP