Re: help with pagila

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> On Fri, Sep 01, 2006 at 02:26:39PM -0300, Walter Cruz wrote:
>> So I can assume that the MySQL implementation is strange? (It accepts that
>> kind of query)

> In my experience, it is almost never safe to assume that the MySQL
> approach to SQL bears anything but a passing resemblance to SQL
> proper. This is considerably better under recent releases, however,
> and I think you'd find, if you used the strict mode in the most
> recent release, that MySQL would choke on a query like you posted as
> well.

If film_id is a primary key for film, then it's actually legal per SQL99
(though not in earlier SQL specs) to just GROUP BY film_id and then
reference the other columns of film without explicit grouping, because
clearly there can be only one value of them per film_id value. However
the quoted query includes ungrouped references to other tables as well,
and it's not immediately obvious that those references must have unique
values for any one value of film_id.

It's possible that MySQL is taking the trouble to validate that this
query is legal per SQL99 rules, but I'd find it quite surprising given
their project philosophy --- fine points like whether a query has a
single right answer tend not to matter to them.

Postgres currently implements only the older SQL92 rules, under which
you gotta explicitly GROUP BY all the columns you want to reference
outside aggregate functions. We'll probably implement some parts of the
looser SQL99 rules in the future, but that's where it stands today.

regards, tom lane