Re: 7.3 "group by" issue - Mailing list pgsql-sql

From Peter Eisentraut
Subject Re: 7.3 "group by" issue
Date
Msg-id Pine.LNX.4.44.0302220245460.2067-100000@peter.localdomain
Whole thread Raw
In response to Re: 7.3 "group by" issue  ("Dan Langille" <dan@langille.org>)
Responses Re: 7.3 "group by" issue
List 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



pgsql-sql by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: function defination help ..
Next
From: Tom Lane
Date:
Subject: Re: [BUGS] 7.3 GROUP BY differs from 7.2