BUG #14079: Issues with query

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.bejarano@gmail.com> wrote:

On 4/8/2016 2:47 PM, hector.bejarano@gmail.com wrote:

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.

nope. doesn't work that way. Aliases to selected fields can not
be used in the WHERE clause, because the WHERE clause is executed BEFORE
the fields are selected and calculated.

this is per the SQL standard.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

On Fri, Apr 8, 2016 at 4:53 PM, Phillip Couto <phillip.couto01@gmail.com>
wrote:

On Sat, Apr 9, 2016 at 6:37 AM, Hector Bejarano <hector.bejarano@gmail.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
/messages/by-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.