To: "Rick Schumeyer" <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: question re. count, group by, and having 
In-reply-to: <000b01c5ce62$514db090$0300a8c0@dell8200> 
References: <000b01c5ce62$514db090$0300a8c0@dell8200>
Comments: In-reply-to "Rick Schumeyer" <rschumeyer@ieee.org>
	message dated "Tue, 11 Oct 2005 08:50:10 -0400"
Date: Tue, 11 Oct 2005 10:03:58 -0400
Message-ID: <13793.1129039438@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Rick Schumeyer" <rschumeyer@ieee.org> writes:
> Is this a bug or a feature?  I'm not sure why I can use 'c' in the order by
> clause but not the having clause.  pg is much happier with the full "having
> count(state) > 5".

Actually, referring to any of the output columns in any of the modifier
clauses is logically suspect.  Original SQL (back around 89 or so)
required ORDER BY items to be output column names, thus wiring in an
assumption that sorting happens after calculation of the output values,
but that is surely not true for any of the other clauses.  And it's
pretty bogus even for sorting, since you might wish to sort on a value
you're not displaying.

If we were working in a green field we'd doubtless get rid of the
output-column-reference feature entirely.  But for backward
compatibility's sake we're stuck with allowing ORDER BY items to
be simple output column names, per ancient versions of the SQL spec.
At one point or another somebody thought it a good idea to propagate
that special rule into GROUP BY; which in hindsight was an awful idea.
(It's not in the spec.  I'm not sure if this is just a Postgres-ism
or if we borrowed someone else's bad idea.)  But we're stuck with
supporting that odd case too, now.  We certainly aren't going to add
more.

> Will this cause count to be evaluated twice?

Recent versions of PG are smart enough to merge duplicate aggregates.
This isn't necessarily true for other forms of common subexpressions,
but it works for aggregate functions.

			regards, tom lane