Обсуждение: sql query bug???

Is this a bug (in 9.0.4), or have I just gone cross-eyed from too much work?

The query:

select t0."ICD9", t0."Description", count(*)
from (select distinct "Person_Id", "ICD9", "Description" from "PatientDiagnoses") as t0
group by (t0."ICD9", t0."Description")
order by count(*) desc limit 10;

The error:

column "t0.ICD9" must appear in the GROUP BY clause or be used in an aggregate function

Huh?

FWIW, I'm not providing the "PatientDiagnoses" def because it's a complex query involving 3-way union of 5-way
joins--intendedfor end-user querying. 

Of note, this query works (and performance is good enough as well):

select "ICD9", count(*) from (select distinct "Person_Id", "ICD9" from "PatientDiagnoses") as t0 group by "ICD9" order
bycount(*) desc limit 10; 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Scott Ribe <scott_ribe@elevated-dev.com> writes:
> Is this a bug (in 9.0.4), or have I just gone cross-eyed from too much work?
> The query:

> select t0."ICD9", t0."Description", count(*)
> from (select distinct "Person_Id", "ICD9", "Description" from "PatientDiagnoses") as t0
> group by (t0."ICD9", t0."Description")
> order by count(*) desc limit 10;

> The error:

> column "t0.ICD9" must appear in the GROUP BY clause or be used in an aggregate function

Drop the parentheses in the GROUP BY.  As is, this is requesting to
group by the composite value ROW(t0."ICD9", t0."Description").
You could argue that that's sufficient to determine both of those column
values, but PG doesn't make that deduction (and, I think, is not
required to by spec).

            regards, tom lane

On Feb 5, 2012, at 10:46 PM, Tom Lane wrote:

> Drop the parentheses in the GROUP BY.

I had the suspicion that it was some kind of a late-night brain fart ;-)

I don't know where the hell the parens came from, since I've *NEVER* put spurious parens in a group by clause before.
Butit took someone pointing it out to me to get me to notice that irregularity. Fatigue... One more day of super-crunch
andthen I get to take a break... 

Thanks.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice