Re: field must appear in the GROUP BY clause or be used

Tom Lane wrote:
> Mike Mascari <mascarm(at)mascari(dot)com> writes:
>
>>Bill Moran wrote:
>>
>>>SELECT GCP.id,
>>>GCP.Name
>>>FROM Gov_Capital_Project GCP,
>>>WHERE TLM.TLI_ID = $2
>>>group by GCP.id
>>>ORDER BY gcp.name;
>>>
>>>ERROR: column "gcp.name" must appear in the GROUP BY clause or be used
>>>in an aggregate function
>>>
>>>This isn't my query, I'm translating a system prototyped in MSSQL to
>>>Postgres. This query _does_ work in MSSQL. Does that constitute a
>>>bug in MSSQL, or a shortcomming of Postgres, or just a difference of
>>>interpretation?
>
>>If MSSQL picks an arbitrary value for the non-group by attribute, it
>>is violating spec.
>
> They might be operating per spec. If "id" is a primary or unique key
> for the table, then SQL99 (but not SQL92) says that it's sufficient to
> group by the id column; the database is supposed to realize that the
> other columns can't have more than one value per group, and allow direct
> references to them. Or at least that's my interpretation of the pages
> and pages in SQL99 about functional dependency. It seems like a pretty
> useless frammish ... if you know that id is unique, why are you
> bothering with GROUP BY at all?

It's possible that you're right about MSSQL, the column in question _is_
unique. I also had another theory (see other post).

As for why I'm using a GROUP BY: it's not my decision, I'm converting
SQL that someone else wrote, and (honestly) I don't understand the
data well enough to say whether it's required in this query or not.

Also, the _actual_ query that I'm converting here is more complex than
this (it's a join of 5 tables) but in my experimenting/testing, I found
that the query that I had minimized down to had the exact same behaviour.
So I posted the simplified query instead of the actual query, to make it
easier on those who would reply.

If you think it would help with Postgres' development, I'll give you
access to my development machine and the actual query involved. I'm
sure the client won't mind, since their banking their future on the
reliability of Postgres anyway ;)

> Anyway, Postgres currently implements the SQL92 definition, which is
> that you can't refer to an ungrouped column except within an aggregate
> function call. So you need to call out all the columns to be referenced
> in GROUP BY.

To me, that seems the most likely explanation (i.e. id is a primary key,
and MSSQL is SQL99 compliant)

--
Bill Moran
Potential Technologies
http://www.potentialtech.com