pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with pagila
11+ messages / 7 participants
[nested] [flat]
* help with pagila
@ 2006-09-01 13:31 Walter Cruz <walter.php@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: Walter Cruz @ 2006-09-01 13:31 UTC (permalink / raw)
To: pgsql-sql
Hi all. I'm with a little doubt.
I'm testing the pagila (the postgres port of mysql sakila sample).
Well, I was trying to translate the query:
select
film.film_id AS FID,
film.title AS title,
film.description AS description,
category.name AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
group_concat(concat(actor.first_name,_utf8' ',actor.last_name)
separator ',') AS actors
from
category
inner join film on(category.category_id = film.category_id)
inner join film_actor on(film.film_id = film_actor.film_id)
inner join actor on(film_actor.actor_id = actor.actor_id)
group by
film.film_id;
That cant be find here:
http://www.stardata.it/articoli_en/mysql_sample_db_articoli_en.html
I read a comment by David Fetter on a blog about group concat. I'm following
his example:
select
film.title AS title,
array_to_string(array_accum(actor.first_name || ' ' ||
actor.last_name),',')
AS actors
from
film
inner join film_actor on film.film_id = film_actor.film_id
inner join actor on film_actor.actor_id = actor.actor_id
GROUP BY film.title
But, when I add another column on select, like, film_description, I get the
following error:
"ERROR: column "film.description" must appear in the GROUP BY clause or be
used in an aggregate function"
If I put that column on GROUP BY everything works ok. But I want understant
why do I need to do that. Can someone teach me, please?
[]'s
- Walter
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 15:21 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Walter Cruz <walter.php@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Andrew Sullivan @ 2006-09-01 15:21 UTC (permalink / raw)
To: pgsql-sql
On Fri, Sep 01, 2006 at 10:31:48AM -0300, Walter Cruz wrote:
> "ERROR: column "film.description" must appear in the GROUP BY clause or be
> used in an aggregate function"
>
> If I put that column on GROUP BY everything works ok. But I want understant
> why do I need to do that. Can someone teach me, please?
You need to because everything else is being grouped or aggregated.
Otherwise, you should get one row for every match of
film.description, and that's not what you want. (More precisely and
yet still completely imprecise, that's not even something you can
have, because of the way sets work.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 17:26 Walter Cruz <walter.php@gmail.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 3 replies; 11+ messages in thread
From: Walter Cruz @ 2006-09-01 17:26 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
So I can assume that the MySQL implementation is strange? (It accepts that
kind of query)
[]'s
- Walter
On 9/1/06, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
>
> On Fri, Sep 01, 2006 at 10:31:48AM -0300, Walter Cruz wrote:
> > "ERROR: column "film.description" must appear in the GROUP BY clause or
> be
> > used in an aggregate function"
> >
> > If I put that column on GROUP BY everything works ok. But I want
> understant
> > why do I need to do that. Can someone teach me, please?
>
> You need to because everything else is being grouped or aggregated.
> Otherwise, you should get one row for every match of
> film.description, and that's not what you want. (More precisely and
> yet still completely imprecise, that's not even something you can
> have, because of the way sets work.)
>
> A
>
> --
> Andrew Sullivan | ajs@crankycanuck.ca
> In the future this spectacle of the middle classes shocking the avant-
> garde will probably become the textbook definition of Postmodernism.
> --Brad Holland
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 17:40 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Walter Cruz <walter.php@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Andrew Sullivan @ 2006-09-01 17:40 UTC (permalink / raw)
To: Walter Cruz <walter.php@gmail.com>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 18:06 Tomas Vondra <tv@fuzzy.cz>
parent: Walter Cruz <walter.php@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Tomas Vondra @ 2006-09-01 18:06 UTC (permalink / raw)
To: pgsql-sql
> But, when I add another column on select, like, film_description, I get
> the following error:
>
> "ERROR: column "film.description" must appear in the GROUP BY clause or
> be used in an aggregate function"
>
> If I put that column on GROUP BY everything works ok. But I want
> understant why do I need to do that. Can someone teach me, please?
The reason is pretty simple - GROUPing actually means "sorting into
boxes by values in the columns after the GROUP BY keyword" (and then
applying some aggregate functions to these boxes, as for example COUNT,
AVG etc.) Besides these aggregates, you can select a column that
'constant' for each of the boxes, that is all the rows in that box have
the same value in this column.
That's the case of the first SQL query you've posted - you're grouping
by 'film_id', thus all the rows in a box have the same value in this
column. And thanks to this you can select the value in the SELECT.
But in the second query, you'd like to select another column (directly,
not through an aggregate function) - title. But there could be different
values for each row in the box (PostgreSQL doesn't know that the ID
uniquely identifies the title). For example imagine you would group by
'published_year' instead of the 'film_id' - in this case there would be
many different movies in the same box, in which case it's impossible to
select 'title' for all of them.
There are two ways to solve this:
1) add the 'title' to the GROUP BY clause, thus all the rows in a box
have the same value of 'title' (and postgresql knows about that)
2) use a subselect
film_id,
(SELECT title FROM film WHERE film_id = film_outer.film_id) AS title
FROM film AS film_outer
...
GROUP BY film_id;
Tomas
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 19:58 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Walter Cruz <walter.php@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2006-09-01 19:58 UTC (permalink / raw)
To: Walter Cruz <walter.php@gmail.com>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
On Fri, 2006-09-01 at 12:26, Walter Cruz wrote:
> So I can assume that the MySQL implementation is strange? (It accepts
> that kind of query)
Yes, according to the SQL spec, you should generally get an error when
you run a query like this:
select field1, field2 from table group by field1
since you could theoretically get a different value for field2 each time
you run the query. If the data looked like this:
field1 | field2
-------+--------
1 | 1
1 | 3
2 | 5
2 | 3
The possible answers to that query would be (1,1)(2,5), (1,1)(2,3),
(1,3)(2,5), (1,3)(2,3)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 20:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2006-09-01 20:21 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: Walter Cruz <walter.php@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 20:25 Tomas Vondra <tv@fuzzy.cz>
parent: Walter Cruz <walter.php@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Tomas Vondra @ 2006-09-01 20:25 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
> So I can assume that the MySQL implementation is strange? (It accepts
> that kind of query)
Yes, MySQL behaves strangely in this case (as well as in several other
cases). I wouldn't rely on this as it probably can choose different
values each time (although as far as I remember I haven't seen this).
t.v.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 20:46 Walter Cruz <walter.php@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Walter Cruz @ 2006-09-01 20:46 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-01 21:12 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Walter Cruz <walter.php@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2006-09-01 21:12 UTC (permalink / raw)
To: Walter Cruz <walter.php@gmail.com>; +Cc: pgsql-sql
On Fri, 1 Sep 2006, Walter Cruz wrote:
> Hi all. I'm with a little doubt.
>
> I'm testing the pagila (the postgres port of mysql sakila sample).
>
> Well, I was trying to translate the query:
>
> select
> film.film_id AS FID,
> film.title AS title,
> film.description AS description,
> category.name AS category,
> film.rental_rate AS price,
> film.length AS length,
> film.rating AS rating,
> group_concat(concat(actor.first_name,_utf8' ',actor.last_name)
> separator ',') AS actors
> from
> category
> inner join film on(category.category_id = film.category_id)
> inner join film_actor on(film.film_id = film_actor.film_id)
> inner join actor on(film_actor.actor_id = actor.actor_id)
> group by
> film.film_id;
Assuming that film_id is the primary key on film and category_id is the
primary key on category, I think you'd be allowed to have the other column
references in SQL03 (and 99?) but not in SQL92 (which is the version that
PostgreSQL currently implements).
IIRC, the later specs allow you to not mention columns in group by that
are functionally dependant on other columns that are mentioned.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: help with pagila
@ 2006-09-04 07:55 Markus Schaber <schabi@logix-tt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Markus Schaber @ 2006-09-04 07:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-09-04 07:55 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-01 13:31 help with pagila Walter Cruz <walter.php@gmail.com>
2006-09-01 15:21 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-09-01 17:26 ` Walter Cruz <walter.php@gmail.com>
2006-09-01 17:40 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-09-01 20:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-01 20:46 ` Walter Cruz <walter.php@gmail.com>
2006-09-04 07:55 ` Markus Schaber <schabi@logix-tt.com>
2006-09-01 19:58 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-09-01 20:25 ` Tomas Vondra <tv@fuzzy.cz>
2006-09-01 18:06 ` Tomas Vondra <tv@fuzzy.cz>
2006-09-01 21:12 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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