From: Walter Cruz <walter.php@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: help with pagila
Date: Fri, 1 Sep 2006 17:46:02 -0300
Message-ID: <32cabba0609011346v5e2cde53w6d85ec78f0db3a24@mail.gmail.com> (raw)
In-Reply-To: <20520.1157142108@sss.pgh.pa.us>
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>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: walter.php@gmail.com
Subject: Re: help with pagila
In-Reply-To: <32cabba0609011346v5e2cde53w6d85ec78f0db3a24@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox