Re: BUG #14079: Issues with query

On Sat, Apr 9, 2016 at 6:37 AM, Hector Bejarano <hector(dot)bejarano(at)gmail(dot)com>
wrote:

> Right, this explains the how but not the why, which is really my question.
> For instance, there are other statements I can use with this derived column
> like a group by:
>
> select 1 as a group by a
>
> But then if I try to use "having" then it fails just like it does with the
> WHERE clause:
>
> select 1 as a group by a having a=1
>
> See, the thing here is I don't understand WHY it was designed this way,
> because to me it would be very useful to have the ability to use the
> derived column without having to wrap the whole thing in a derived query.
>
>
​Tom answered the why a long time ago:

Google: postgresql having not using alias
http://www.postgresql.org/message-id/7608.1259177709@sss.pgh.pa.us

Basically, it shouldn't work for GROUP BY but it does so we live with it.

The difference is that in GROUP BY all you are referring to is an alias
(grouping_element) while in HAVING you are using it in an expression
(condition)

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-HAVING

Its not any different than the fact you can say: "GROUP BY 1, 2" but
saying "1 = 'alpha'" in the HAVING clause would downright confusing.

David J.

> On Fri, Apr 8, 2016 at 4:53 PM, Phillip Couto <phillip(dot)couto01(at)gmail(dot)com>
> wrote:
>
>> The WHERE is executed before the SELECT. The value of a is not available
>> to the WHERE clause as it is assigned at the end of the query.
>>
>> To actually make your query work it would have to read:
>> select * from (select 1 as a) as b where a = 1
>>
>> The from provides the data that the where will operate on, the select
>> then determines what to be returned.
>>
>> On Fri, 8 Apr 2016 at 18:41 <hector(dot)bejarano(at)gmail(dot)com> wrote:
>>
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: 14079
>>> Logged by: Hector Bejarano
>>> Email address: hector(dot)bejarano(at)gmail(dot)com
>>> PostgreSQL version: 9.5.0
>>> Operating system: Ubuntu 14
>>> Description:
>>>
>>> This query works:
>>> select 1 as a
>>>
>>> But this one fails:
>>> select 1 as a where a = 1
>>>
>>> And I think they should both work.
>>>
>>> Regards,
>>> Hector.
>>>
>>>
>>> --
>>> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>>
>>
>