Re: Is this a bug?

Prateek Sanyal <sanyal.prateek@gmail.com> writes:
> I discovered a difference between sqlite3 and psql and I am not sure if it
> is intentional but it is definitely inconvenient.

> If you use an alias for an aggregate function and then do a GROUP BY with
> HAVING, you can't use that alias in the HAVING and you have to type in the
> entire function again.

> This is not the case with sqlite3 where you can just use the alias. Why
> doesn't psql allow this?

Because it is contrary to both the letter and the spirit of the SQL
standard.  The SELECT's result list is logically computed after every
operation except ORDER BY, so it's really not sensible for clauses
like GROUP BY or HAVING to refer to outputs of the SELECT list.
What's more, it's ambiguous, because the same name might mean
different things depending on whether you consider it to be an
input column name or a result column name.

PG deviates from the spec to the extent of allowing GROUP BY items
to be output column names *as long as they are just that, and not
expressions*.  (To my mind, that's probably a long-ago mistake,
but it's handy enough that there's never been any serious move to
remove it.)  Even if we applied that policy to HAVING, which
we don't, your example wouldn't work because the reference is
within an expression.

> SELECT SUM(total_sale) AS sum_sales FROM invoices GROUP BY customer_id
> HAVING SUM(total_sale) > 20 ORDER BY sum_sales DESC;

This code is correct per spec and should work in any SQL DBMS.  At least
as far as PG is concerned, there's no performance penalty, since the
common SUM() expression is evaluated only once anyway.

            regards, tom lane