Thread: 7.3 "group by" issue

7.3 "group by" issue

From
"Dan Langille"
Date:
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/



Re: 7.3 "group by" issue

From
"Gaetano Mendola"
Date:
----- 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



Re: 7.3 "group by" issue

From
"Dan Langille"
Date:
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/



Re: 7.3 "group by" issue

From
"Chad Thompson"
Date:


> 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




Re: 7.3 "group by" issue

From
"Dan Langille"
Date:
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/



Re: 7.3 "group by" issue

From
"Chad Thompson"
Date:


> 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



Re: 7.3 "group by" issue

From
"Dan Langille"
Date:
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/



Re: 7.3 "group by" issue

From
"Chad Thompson"
Date:


> 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



Re: 7.3 "group by" issue

From
Josh Berkus
Date:
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



Re: 7.3 "group by" issue

From
Dan Langille
Date:
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.


Re: 7.3 "group by" issue

From
Peter Eisentraut
Date:
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



Re: 7.3 "group by" issue

From
Josh Berkus
Date:
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


Re: 7.3 "group by" issue

From
Peter Eisentraut
Date:
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



Re: 7.3 "group by" issue

From
Josh Berkus
Date:
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