Thread: 7.3 "group by" issue
Hi folks, This query: SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id AND watch_list.user_id = 1 GROUP BY watch_list_element.element_id gives this error: ERROR: Attribute unnamed_join.element_id must be GROUPed or used in an aggregate function Note that in the select the table name is not mentioned but it is in the GROUP BY. To solve the problem, you either have to name the table in both locations or not name it in either location. Why? -- Dan Langille : http://www.langille.org/
----- Original Message ----- From: "Dan Langille" <dan@langille.org> To: <pgsql-sql@postgresql.org> Sent: Friday, February 21, 2003 7:01 PM Subject: [SQL] 7.3 "group by" issue > Hi folks, > > This query: > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > AND watch_list.user_id = 1 > GROUP BY watch_list_element.element_id Try: SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY watch_list_element.element_id Ciao Gaetano
On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > Hi folks, > > > > This query: > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > FROM watch_list JOIN watch_list_element > > ON watch_list.id = watch_list_element.watch_list_id > > AND watch_list.user_id = 1 > > GROUP BY watch_list_element.element_id > > Try: > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 > GROUP BY watch_list_element.element_id ERROR: Attribute unnamed_join.element_id must be GROUPed or used in an aggregate function -- Dan Langille : http://www.langille.org/
> On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > > > Hi folks, > > > > > > This query: > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > FROM watch_list JOIN watch_list_element > > > ON watch_list.id = watch_list_element.watch_list_id > > > AND watch_list.user_id = 1 > > > GROUP BY watch_list_element.element_id > > > > Try: > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > FROM watch_list JOIN watch_list_element > > ON watch_list.id = watch_list_element.watch_list_id > > WHERE > > watch_list.user_id = 1 > > GROUP BY watch_list_element.element_id > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in > an aggregate function > I think that the wrong problem was solved here. Items in the order by clause must be in the target list. heres what it says in the docs *The ORDER BY clause specifies the sort order: *SELECT select_list * FROM table_expression * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...] *column1, etc., refer to select list columns. These can be either the output name of a column (see Section 4.3.2) or the number of a column. Some examples: Note that "column1, etc., refer to select list" HTH Chad
On 21 Feb 2003 at 13:00, Chad Thompson wrote: > > On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > > > > > Hi folks, > > > > > > > > This query: > > > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > > FROM watch_list JOIN watch_list_element > > > > ON watch_list.id = watch_list_element.watch_list_id > > > > AND watch_list.user_id = 1 > > > > GROUP BY watch_list_element.element_id > > > > > > Try: > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > FROM watch_list JOIN watch_list_element > > > ON watch_list.id = watch_list_element.watch_list_id > > > WHERE > > > watch_list.user_id = 1 > > > GROUP BY watch_list_element.element_id > > > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in > > an aggregate function > > > > I think that the wrong problem was solved here. Items in the order by > clause must be in the target list. > > heres what it says in the docs > *The ORDER BY clause specifies the sort order: > > *SELECT select_list > * FROM table_expression > * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...] > *column1, etc., refer to select list columns. These can be either the output > name of a column (see Section 4.3.2) or the number of a column. Some > examples: > > Note that "column1, etc., refer to select list" I don't see how ORDER BY enters into this situation. It's not used. What are you saying? -- Dan Langille : http://www.langille.org/
> On 21 Feb 2003 at 13:00, Chad Thompson wrote: > > > > > On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > > > > > > > Hi folks, > > > > > > > > > > This query: > > > > > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > > > FROM watch_list JOIN watch_list_element > > > > > ON watch_list.id = watch_list_element.watch_list_id > > > > > AND watch_list.user_id = 1 > > > > > GROUP BY watch_list_element.element_id > > > > > > > > Try: > > > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > > FROM watch_list JOIN watch_list_element > > > > ON watch_list.id = watch_list_element.watch_list_id > > > > WHERE > > > > watch_list.user_id = 1 > > > > GROUP BY watch_list_element.element_id > > > > > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in > > > an aggregate function > > > > > > > I think that the wrong problem was solved here. Items in the order by > > clause must be in the target list. > > > > heres what it says in the docs > > *The ORDER BY clause specifies the sort order: > > > > *SELECT select_list > > * FROM table_expression > > * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...] > > *column1, etc., refer to select list columns. These can be either the output > > name of a column (see Section 4.3.2) or the number of a column. Some > > examples: > > > > Note that "column1, etc., refer to select list" > > I don't see how ORDER BY enters into this situation. It's not used. > What are you saying? > -- The same applies to group by... Sorry for the confusion. If the column is not in the select section of the statement, it cant group by it. Try this. SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY wle_element_id
On 21 Feb 2003 at 13:30, Chad Thompson wrote: > The same applies to group by... Sorry for the confusion. > > If the column is not in the select section of the statement, it cant group > by it. > Try this. > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 > GROUP BY wle_element_id Yes, that works. But so do these. SELECT watch_list_element.element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY watch_list_element.element_id SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY element_id The original situation which did not work is: SELECT watch_list_element.element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY element_id My question: why should it not work? It's referring to the same column as the previous two examples which do work. -- Dan Langille : http://www.langille.org/
> On 21 Feb 2003 at 13:30, Chad Thompson wrote: > > > The same applies to group by... Sorry for the confusion. > > > > If the column is not in the select section of the statement, it cant group > > by it. > > Try this. > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > FROM watch_list JOIN watch_list_element > > ON watch_list.id = watch_list_element.watch_list_id > > WHERE > > watch_list.user_id = 1 > > GROUP BY wle_element_id > > Yes, that works. But so do these. > > SELECT watch_list_element.element_id as wle_element_id, > COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 > GROUP BY watch_list_element.element_id > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 > GROUP BY element_id > > The original situation which did not work is: > > SELECT watch_list_element.element_id as wle_element_id, > COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 > GROUP BY element_id > > My question: why should it not work? It's referring to the same > column as the previous two examples which do work. > -- I see the distinction you are making. Maybe Tom or Josh could throw out a better answer, but I think that youve called it one thing in your select and tried to group by it using a syntaticly different name. Any one have any other ideas? Chad
Dan, Chad, > I see the distinction you are making. > > Maybe Tom or Josh could throw out a better answer, but I think that youve > called it one thing in your select and tried to group by it using a > syntaticly different name. This looks like a bug to me. Please write it up and send it to BUGS. 7.3.2, I assume? -- -Josh BerkusAglio Database SolutionsSan Francisco
On Fri, 21 Feb 2003, Josh Berkus wrote: > Dan, Chad, > > > I see the distinction you are making. > > > > Maybe Tom or Josh could throw out a better answer, but I think that youve > > called it one thing in your select and tried to group by it using a > > syntaticly different name. > > This looks like a bug to me. Please write it up and send it to BUGS. Will do. > 7.3.2, I assume? Yes. FWIW, I'm upgrading FreshPorts.org from 7.2.3.
Dan Langille writes: > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > FROM watch_list JOIN watch_list_element > > ON watch_list.id = watch_list_element.watch_list_id > > WHERE > > watch_list.user_id = 1 > > GROUP BY wle_element_id This works because the first select list item is mentioned in the GROUP BY clause (using its output label, this is a PostgreSQL extension). > Yes, that works. But so do these. > > SELECT watch_list_element.element_id as wle_element_id, > COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 > GROUP BY watch_list_element.element_id This works because the first select list item is mentioned in the GROUP BY clause. > SELECT element_id as wle_element_id, COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 > GROUP BY element_id This works because the first select list item is mentioned in the GROUP BY clause. > The original situation which did not work is: > > SELECT watch_list_element.element_id as wle_element_id, > COUNT(watch_list_id) > FROM watch_list JOIN watch_list_element > ON watch_list.id = watch_list_element.watch_list_id > WHERE > watch_list.user_id = 1 > GROUP BY element_id This does not work because the first select list item references a column inside a join, which is not (necessarily) mathematically identical to the column that arrives outside of the join and is in the GROUP BY clause. (Think of an outer join: the column outside the join might contain added null values. Of course you are using an inner join, but the constructs work the same either way.) -- Peter Eisentraut peter_e@gmx.net
Peter, > This does not work because the first select list item references a column > inside a join, which is not (necessarily) mathematically identical to the > column that arrives outside of the join and is in the GROUP BY clause. > (Think of an outer join: the column outside the join might contain added > null values. Of course you are using an inner join, but the constructs > work the same either way.) Hmmm ... I don't see that. I can see that it might be practically difficult-to-impossible to make the planner distinguish between cases where the columns in the select list are different from the columns in the JOIN series and when they aren't, but as a theory issue I just don't buy it. What Dan's doing is: SELECT a.c1, count(a.c2) FROM a JOIN b WHERE b.c5 = x GROUP BY c1 In a case like this, a.c1 == c1 without possibility of ambiguity. The only difference is whether or not a table qualifier is used on the name, which in theory should *only* make a difference when there are more than one table in the query with that column name. So if you're saying that that kind of equivalency is a challenging parser implementation issue, then I'm with you. If you're saying its a SQL theory issue, though, I don't agree at all. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus writes: > SELECT a.c1, count(a.c2) > FROM a JOIN b > WHERE b.c5 = x > GROUP BY c1 > > In a case like this, a.c1 == c1 without possibility of ambiguity. Consider JOIN to be a black-box function, then you'd be writing something like SELECT a.c1, count(a.c2) FROM func(a, b) AS r (c1, c2, c3, c4, c5) WHERE b.c5 = x GROUP BY c1; -- <== refers to r.c1 Clearly in this case you cannot claim that in general a.c1 == r.c1. If the func() is an inner join, then the claim is true, but if it's an outer join it's not. The scoping rules for inner and outer joins are the same, and that might be considered a good thing. -- Peter Eisentraut peter_e@gmx.net
Peter, > Consider JOIN to be a black-box function, then you'd be writing something > like > > SELECT a.c1, count(a.c2) > FROM func(a, b) AS r (c1, c2, c3, c4, c5) > WHERE b.c5 = x > GROUP BY c1; -- <== refers to r.c1 > > Clearly in this case you cannot claim that in general a.c1 == r.c1. > > If the func() is an inner join, then the claim is true, but if it's an > outer join it's not. The scoping rules for inner and outer joins are the > same, and that might be considered a good thing. I see what you mean ... though I still hold that it's an implementation issue, rather than a theory one. That is, a human can easily tell the differences when differently qualified table names are exactly equivalent even if a parser cannot. But I can definitely see where trying to accomodate this would be doing a *lot* of programming to support sloppy syntax in a rather limited number of useful cases. More on BUGS -- Josh Berkus Aglio Database Solutions San Francisco