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 9F1259FB3C0 for ; Fri, 1 Sep 2006 19:04:06 -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 42361-01 for ; Fri, 1 Sep 2006 19:03:15 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.194]) by postgresql.org (Postfix) with ESMTP id 120629FB3BD for ; Fri, 1 Sep 2006 17:46:03 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id 14so747674nzn for ; Fri, 01 Sep 2006 13:46:02 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=LcN81dox6VZAXCln8qtx6lKB8lswpVWkDlzk2nibJxkUpB8inLWldx21tpMihqN6iEj+aRJiu97Io2AJrDTh2n3HfVxQJ4uA/4e6zBe1af7u7kId33dKzm4/C0+E6vKJkr9edy86cP4YH/5UZ+gBbzg7OGL4IcSVz4GYhzYP8Wc= Received: by 10.65.219.1 with SMTP id w1mr975937qbq; Fri, 01 Sep 2006 13:46:02 -0700 (PDT) Received: by 10.65.61.16 with HTTP; Fri, 1 Sep 2006 13:46:02 -0700 (PDT) Message-ID: <32cabba0609011346v5e2cde53w6d85ec78f0db3a24@mail.gmail.com> Date: Fri, 1 Sep 2006 17:46:02 -0300 From: "Walter Cruz" To: pgsql-sql@postgresql.org Subject: Re: help with pagila In-Reply-To: <20520.1157142108@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_37653_31413001.1157143562105" References: <32cabba0609010631t233d13f9o38edba0483a2446c@mail.gmail.com> <20060901152116.GE17178@phlogiston.dyndns.org> <32cabba0609011026r6616a73bhe80b336f18aa84ac@mail.gmail.com> <20060901174040.GF17178@phlogiston.dyndns.org> <20520.1157142108@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.388 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/9 X-Sequence-Number: 26220 ------=_Part_37653_31413001.1157143562105 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Thank you all. I found myself with the same trouble in last week, when I tried to port mambo CMS to PostgreSQL. After some work, In fall i a query like the one that was quoted by Andrew and decided to stop. In that time, my guess wae that something was wrong with MySQL. (I don't know.. I have seen queries like that on a lot of mysql free projects. Maybe we can put that on a FAQ?) []'s - Walter On 9/1/06, Tom Lane wrote: > > 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 > ------=_Part_37653_31413001.1157143562105 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thank you all.

I found myself with the same trouble in last week, when I tried to port mambo CMS to PostgreSQL.

After some work, In fall i a query like the one that was quoted by Andrew and decided to stop.

In that time, my guess wae that something was wrong with MySQL.

(I don't know.. I have seen queries like that on a lot of mysql free projects. Maybe we can put that on a FAQ?)

[]'s
- Walter


On 9/1/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Sullivan <ajs@crankycanuck.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

------=_Part_37653_31413001.1157143562105--