From: Markus Schaber <schabi@logix-tt.com>
To: pgsql-sql@postgresql.org
Subject: Re: help with pagila
Date: Mon, 04 Sep 2006 09:55:56 +0200
Message-ID: <44FBDC0C.5030902@logix-tt.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>
Hi, Tom,
Tom Lane wrote:
> 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.
In situations like this, I often missed (and sometimes implemented) a
simple "first()" aggregate to put around those other columns.
Some of those cases could be fixed by creative use of "DISTINCT ON", but
sometimes lead to suboptimal query plans (due to the useless sorting).
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
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: schabi@logix-tt.com
Subject: Re: help with pagila
In-Reply-To: <44FBDC0C.5030902@logix-tt.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