Thread: 'AS' column-alias beign ignored on outer select

'AS' column-alias beign ignored on outer select

From
"Markus Wollny"
Date:
Hi!

Is this the expected behaviour?

 select ID
  , USK_RATING AS USK
  from (
   select ID
    , USK_RATING
   from MAIN_SONY_PS2_GAME
   where ID = 101400
   limit 1
   )
   as PRODUCT_LIST
 limit 1;

   id   | usk_rating
--------+------------
 101400 |
(1 row)

Note the column-header being labeled 'usk_rating', not 'usk'. Obviously
the 'AS' column alias of the outer select is being ignored in the
resultset.

 select ID
  , USK
  from (
   select ID
    , USK_RATING AS USK
   from MAIN_SONY_PS2_GAME
   where ID = 101400
   limit 1
   )
   as PRODUCT_LIST
 limit 1;

   id   | usk
--------+-----
 101400 |
(1 row)

If the column alias is being declared in the subselect, the column alias
is working.

 select version();
                               version
----------------------------------------------------------------------
 PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2

Is this working as expected or is this a bug?

Kind regards

   Markus

Re: 'AS' column-alias beign ignored on outer select

From
Tom Lane
Date:
"Markus Wollny" <Markus.Wollny@computec.de> writes:
> Is this the expected behaviour?

No, it's a bug introduced in 8.1.  It's fixed in CVS tip.

            regards, tom lane

Re: 'AS' column-alias beign ignored on outer select

From
"Thomas F. O'Connell"
Date:
On Dec 2, 2005, at 6:00 AM, Markus Wollny wrote:

> Hi!
>
> Is this the expected behaviour?
>
>  select ID
>   , USK_RATING AS USK
>   from (
>    select ID
>     , USK_RATING
>    from MAIN_SONY_PS2_GAME
>    where ID = 101400
>    limit 1
>    )
>    as PRODUCT_LIST
>  limit 1;
>
>    id   | usk_rating
> --------+------------
>  101400 |
> (1 row)
>
> Note the column-header being labeled 'usk_rating', not 'usk'.
> Obviously
> the 'AS' column alias of the outer select is being ignored in the
> resultset.
>
>  select ID
>   , USK
>   from (
>    select ID
>     , USK_RATING AS USK
>    from MAIN_SONY_PS2_GAME
>    where ID = 101400
>    limit 1
>    )
>    as PRODUCT_LIST
>  limit 1;
>
>    id   | usk
> --------+-----
>  101400 |
> (1 row)
>
> If the column alias is being declared in the subselect, the column
> alias
> is working.
>
>  select version();
>                                version
> ----------------------------------------------------------------------
>  PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
>
> Is this working as expected or is this a bug?


I have to say, this looks like a regression. I can't reproduce it in
an 8.0.3 database. The interesting thing is that the bug seems to be
related to the limit clause of the inner query. Hackers?

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)

Re: 'AS' column-alias beign ignored on outer select

From
"Thomas F. O'Connell"
Date:
On Dec 28, 2005, at 10:03 PM, Thomas F. O'Connell wrote:

[snip]

>> If the column alias is being declared in the subselect, the column
>> alias
>> is working.
>>
>>  select version();
>>                                version
>> ---------------------------------------------------------------------
>> -
>>  PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
>>
>> Is this working as expected or is this a bug?
>
> I have to say, this looks like a regression. I can't reproduce it
> in an 8.0.3 database. The interesting thing is that the bug seems
> to be related to the limit clause of the inner query. Hackers?

Whoops. Just saw Tom's reply. Ignore noise. Threaded mail must've
missed the reply.

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)