X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B4D559FB724 for ; Fri, 1 Sep 2006 18:55:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 40343-01 for ; Fri, 1 Sep 2006 18:54:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 8AC1B9FB7E8 for ; Fri, 1 Sep 2006 17:21:56 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k81KLmWo020521; Fri, 1 Sep 2006 16:21:48 -0400 (EDT) To: Andrew Sullivan cc: Walter Cruz , pgsql-sql@postgresql.org Subject: Re: help with pagila In-reply-to: <20060901174040.GF17178@phlogiston.dyndns.org> References: <32cabba0609010631t233d13f9o38edba0483a2446c@mail.gmail.com> <20060901152116.GE17178@phlogiston.dyndns.org> <32cabba0609011026r6616a73bhe80b336f18aa84ac@mail.gmail.com> <20060901174040.GF17178@phlogiston.dyndns.org> Comments: In-reply-to Andrew Sullivan message dated "Fri, 01 Sep 2006 13:40:40 -0400" Date: Fri, 01 Sep 2006 16:21:48 -0400 Message-ID: <20520.1157142108@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.125 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/7 X-Sequence-Number: 26218 Andrew Sullivan 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