Обсуждение: BUG #14079: Issues with query
The following bug has been logged on the website: Bug reference: 14079 Logged by: Hector Bejarano Email address: hector.bejarano@gmail.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.
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: > The following bug has been logged on the website: > > Bug reference: 14079 > Logged by: Hector Bejarano > Email address: hector.bejarano@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
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
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: > 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: > >> The following bug has been logged on the website: >> >> Bug reference: 14079 >> Logged by: Hector Bejarano >> Email address: hector.bejarano@gmail.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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs >> >
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 colu= mn > 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 th= e > WHERE clause: > > select 1 as a group by a having a=3D1 > > 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. > > =E2=80=8BTom 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 =3D 'alpha'" in the HAVING clause would downright confusing. David J. > On Fri, Apr 8, 2016 at 4:53 PM, Phillip Couto <phillip.couto01@gmail.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 =3D 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: >> >>> The following bug has been logged on the website: >>> >>> Bug reference: 14079 >>> Logged by: Hector Bejarano >>> Email address: hector.bejarano@gmail.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 =3D 1 >>> >>> And I think they should both work. >>> >>> Regards, >>> Hector. >>> >>> >>> -- >>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-bugs >>> >> >