Thread: BUG #14079: Issues with query

BUG #14079: Issues with query

From
hector.bejarano@gmail.com
Date:
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.

Re: BUG #14079: Issues with query

From
Phillip Couto
Date:
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
>

Re: BUG #14079: Issues with query

From
John R Pierce
Date:
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

Re: BUG #14079: Issues with query

From
Hector Bejarano
Date:
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
>>
>

Re: BUG #14079: Issues with query

From
"David G. Johnston"
Date:
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
>>>
>>
>