From: | Prateek Sanyal <sanyal(dot)prateek(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Is this a bug? |
Date: | 2016-01-23 02:28:12 |
Message-ID: | CALByby-_ob_ZGc1N4Log9cO0vg8m=rCdiuEWQQoVXho=fDmdDA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | djm7 | 2016-01-23 04:41:13 | BUG #13883: Very Important Facility |
Previous Message | Tom Lane | 2016-01-23 02:09:59 | Re: Is this a bug? |