Обсуждение: group by error message?

Hi,

Running this query:

    critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type pt natural join person_to_event join event e
using(id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON
e.id_event_type= et.id_event_type where et.type_fr='théâtre' GROUP BY pt.type_fr IS NULL; 

gives this error:

    ERROR:  column "pt.type_fr" must appear in the GROUP BY clause or be used in an aggregate function

But pt.type_fr is _already_ in the group by.

When adding e2.id_event however ("GROUP BY pt.type_fr,e2.id_event") it works.

Did I miss something there?

Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> writes:
>     critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type pt natural join person_to_event join event
eusing (id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON
e.id_event_type= et.id_event_type where et.type_fr='th��tre' GROUP BY pt.type_fr IS NULL; 

> gives this error:

>     ERROR:  column "pt.type_fr" must appear in the GROUP BY clause or be used in an aggregate function

> But pt.type_fr is _already_ in the group by.

No, "pt.type_fr IS NULL" is in the group by.  That doesn't ensure that
each group has a unique value of pt.type_fr itself.

            regards, tom lane

On Thu, Sep 25, 2008 at 11:01:08AM -0400, Tom Lane wrote:
> Louis-David Mitterrand <vindex+lists-pgsql-general@apartia.org> writes:
> >     critik=# select distinct pt.type_fr,sum(e2.id_event) from person_type pt natural join person_to_event join
evente using (id_event) LEFT JOIN event e2 ON e.id_event = e2.id_event AND e2.id_event=219 join event_type et ON
e.id_event_type= et.id_event_type where et.type_fr='théâtre' GROUP BY pt.type_fr IS NULL; 
>
> > gives this error:
>
> >     ERROR:  column "pt.type_fr" must appear in the GROUP BY clause or be used in an aggregate function
>
> > But pt.type_fr is _already_ in the group by.
>
> No, "pt.type_fr IS NULL" is in the group by.  That doesn't ensure that
> each group has a unique value of pt.type_fr itself.

Oops, once more I jumped the gun a bit too fast. Thanks for catching it
Tom!

Best regards,