Re: Is this a bug?

I was going to present my argument until I saw who signed off this email.
Thank you Mr. Lane. Now I will send a bug report to Microsoft and hope that
Bill Gates responds.

Regards,
Prateek.

On Fri, Jan 22, 2016 at 9:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Prateek Sanyal <sanyal(dot)prateek(at)gmail(dot)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
>