[SQL] help with pagila
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 byfilm.film_id;That cant be find here: http://www.stardata.it/articoli_en/mysql_sample_db_articoli_en.htmlI 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
Re: [SQL] help with pagila
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 | [EMAIL PROTECTED] 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
Re: [SQL] help with pagila
So I can assume that the MySQL implementation is strange? (It accepts that kind of query)[]'s- WalterOn 9/1/06, Andrew Sullivan < [EMAIL PROTECTED]> 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 offilm.description, and that's not what you want. (More precisely and yet still completely imprecise, that's not even something you canhave, because of the way sets work.)A--Andrew Sullivan | [EMAIL PROTECTED]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
Re: [SQL] help with pagila
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 | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(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
Re: [SQL] help with pagila
> 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 ---(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
Re: [SQL] help with pagila
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) ---(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
Re: [SQL] help with pagila
Andrew Sullivan <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] help with pagila
> 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] help with pagila
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 <[EMAIL PROTECTED]> wrote: Andrew Sullivan <[EMAIL PROTECTED]> 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 thenreference the other columns of film without explicit grouping, becauseclearly 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 uniquevalues 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 giventheir project philosophy --- fine points like whether a query has asingle 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 referenceoutside aggregate functions. We'll probably implement some parts of thelooser SQL99 rules in the future, but that's where it stands today. regards, tom lane
Re: [SQL] help with pagila
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
