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