Re: GROUP BY does not follow SQL standard

On Thu, Oct 9, 2008 at 8:01 AM, Tony Marston <tony(at)nospam(dot)demon(dot)co(dot)uk> wrote:
> The Postgresql implementation of GROUP BY does not conform to either the
> 1999 or 2003 SQL standard. The documentation states that every field in the
> SELECT list which is not aggregated must be specified in the GROUP BY
> clause. While this was true in the 1992 standard, in 1999 this was changed
> to "any non-aggregated column appearing in the SELECT list is functionally
> dependent upon the GROUP BY clause". In the example both p.name and p.price
> are functionally dependent on product_id, therefore there is no need for
> them to be included in the GROUP BY clause.
>
> In this respect Postgresql is wrong

Correct. This has been discussed in the past a few times. No one has
stepped up to add the functionality to postgres yet though.

> and MySQL is right.

I wouldn't push it. MySQL also lets you include columns that are not
functionally dependent on other grouped by fields as well, and just
gives you whichever row data pops up at the moment when it should
throw a function. Postgres doesn't quite give you enough rope to get
the job done by the spec, MySQL gives you enough rope to hang yourself
and everyone for the tri-county area.