pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed7.3 "group by" issue
14+ messages / 5 participants
[nested] [flat]
* 7.3 "group by" issue
@ 2003-02-21 18:01 Dan Langille <dan@langille.org>
0 siblings, 1 reply; 14+ messages in thread
From: Dan Langille @ 2003-02-21 18:01 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-21 18:18 Gaetano Mendola <mendola@bigfoot.com>
parent: Dan Langille <dan@langille.org>
0 siblings, 1 reply; 14+ messages in thread
From: Gaetano Mendola @ 2003-02-21 18:18 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; pgsql-sql
----- 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-21 18:25 Dan Langille <dan@langille.org>
parent: Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 1 reply; 14+ messages in thread
From: Dan Langille @ 2003-02-21 18:25 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-21 20:00 Chad Thompson <chad@weblinkservices.com>
parent: Dan Langille <dan@langille.org>
0 siblings, 1 reply; 14+ messages in thread
From: Chad Thompson @ 2003-02-21 20:00 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-21 20:22 Dan Langille <dan@langille.org>
parent: Chad Thompson <chad@weblinkservices.com>
0 siblings, 1 reply; 14+ messages in thread
From: Dan Langille @ 2003-02-21 20:22 UTC (permalink / raw)
To: Chad Thompson <chad@weblinkservices.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-21 20:30 Chad Thompson <chad@weblinkservices.com>
parent: Dan Langille <dan@langille.org>
0 siblings, 1 reply; 14+ messages in thread
From: Chad Thompson @ 2003-02-21 20:30 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-21 20:45 Dan Langille <dan@langille.org>
parent: Chad Thompson <chad@weblinkservices.com>
0 siblings, 2 replies; 14+ messages in thread
From: Dan Langille @ 2003-02-21 20:45 UTC (permalink / raw)
To: Chad Thompson <chad@weblinkservices.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-21 20:51 Chad Thompson <chad@weblinkservices.com>
parent: Dan Langille <dan@langille.org>
1 sibling, 1 reply; 14+ messages in thread
From: Chad Thompson @ 2003-02-21 20:51 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-21 23:25 Josh Berkus <josh@agliodbs.com>
parent: Chad Thompson <chad@weblinkservices.com>
0 siblings, 1 reply; 14+ messages in thread
From: Josh Berkus @ 2003-02-21 23:25 UTC (permalink / raw)
To: Chad Thompson <chad@weblinkservices.com>; Dan Langille <dan@langille.org>; +Cc: pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-22 00:13 Dan Langille <dan@langille.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Dan Langille @ 2003-02-22 00:13 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Chad Thompson <chad@weblinkservices.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-22 02:09 Peter Eisentraut <peter_e@gmx.net>
parent: Dan Langille <dan@langille.org>
1 sibling, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2003-02-22 02:09 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: Chad Thompson <chad@weblinkservices.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-22 19:34 Josh Berkus <josh@agliodbs.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 14+ messages in thread
From: Josh Berkus @ 2003-02-22 19:34 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; Dan Langille <dan@langille.org>; +Cc: Chad Thompson <chad@weblinkservices.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-23 02:24 Peter Eisentraut <peter_e@gmx.net>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2003-02-23 02:24 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Dan Langille <dan@langille.org>; Chad Thompson <chad@weblinkservices.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 7.3 "group by" issue
@ 2003-02-23 19:59 Josh Berkus <josh@agliodbs.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2003-02-23 19:59 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Dan Langille <dan@langille.org>; Chad Thompson <chad@weblinkservices.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2003-02-23 19:59 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-21 18:01 7.3 "group by" issue Dan Langille <dan@langille.org>
2003-02-21 18:18 ` Gaetano Mendola <mendola@bigfoot.com>
2003-02-21 18:25 ` Dan Langille <dan@langille.org>
2003-02-21 20:00 ` Chad Thompson <chad@weblinkservices.com>
2003-02-21 20:22 ` Dan Langille <dan@langille.org>
2003-02-21 20:30 ` Chad Thompson <chad@weblinkservices.com>
2003-02-21 20:45 ` Dan Langille <dan@langille.org>
2003-02-21 20:51 ` Chad Thompson <chad@weblinkservices.com>
2003-02-21 23:25 ` Josh Berkus <josh@agliodbs.com>
2003-02-22 00:13 ` Dan Langille <dan@langille.org>
2003-02-22 02:09 ` Peter Eisentraut <peter_e@gmx.net>
2003-02-22 19:34 ` Josh Berkus <josh@agliodbs.com>
2003-02-23 02:24 ` Peter Eisentraut <peter_e@gmx.net>
2003-02-23 19:59 ` Josh Berkus <josh@agliodbs.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