field must appear in the GROUP BY clause or be used in an aggregatefuncti on?

[Bill Moran]

Hey all.

I've hit an SQL problem that I'm a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?

The following query:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;

Produces the following error:

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

That field is a CHAR, so I'm not sure what kind of aggregate to use,
or (more important to my understanding) why one is necessary.

As I said, I'm not sure I understand why this occurs. I'm assuming that I
don't understand "group by" as well as I thought I did ;)

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?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bill Moran]

Tom Lane wrote:

Mike Mascari <ma*****@mascar i.com> writes:

Bill Moran wrote:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject 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
interpretati on?

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[terry]

Hey guys

I have dealt with this before.

And there is a simple solution: If the value really is unique, just wrap it
in a max(). Since it's unique, it has *ZERO* effect on your output, but it
then complies to PostgreSQL's GROUP BY implementation, and hence will run...

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
te***@greatgulf homes.com
Fax: [phone]

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Tom Lane
Sent: Friday, February 27, 2004 1:09 PM
To: Mike Mascari
Cc: Bill Moran; pg***********@p ostgresql.org
Subject: Re: [GENERAL] field must appear in the GROUP BY clause or be
used
Mike Mascari <ma*****@mascar i.com> writes:

Bill Moran wrote:

SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Pro ject 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?

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.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

[ drifting a bit off the thread topic, but just for completeness... ]

Bill Moran <wm****@potenti altech.com> writes:

I turned that over in my head a little and tried this:
select id, max(name) from gov_capital_pro ject group by id order by MAX(name);
...
A little more playing around shows that this also works:
select id, max(name) as name from gov_capital_pro ject group by id order by name; Which will probably be a little faster since MAX() is evaluated less.

Actually I believe you'll get the exact same plan either way. GROUP and
ORDER BY expressions are merged with any matching SELECT-list entries
during parsing.

In fact, as of (I think) 7.4, the executor detects and eliminates
duplicate aggregate-function calls even when the parser didn't.
So for instance this:
SELECT max(x), max(x) + 1 FROM ...
will only run the MAX() aggregate once.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org