Re: inconsistency in aliasing

Hi,

This works:

critik=# select current_timestamp::abstime::int4 as score order by score;

This doesn't:

critik=# select current_timestamp::abstime::int4 as score order by score + 1;
ERROR: column "score" does not exist
LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...

Any idea ?

Thanks,

--
http://www.critikart.net

On Wednesday 14 January 2009 11:46:11 Louis-David Mitterrand wrote:
> Hi,
>
> This works:
>
> critik=# select current_timestamp::abstime::int4 as score order by score;
>
> This doesn't:
>
> critik=# select current_timestamp::abstime::int4 as score order by score +
> 1; ERROR: column "score" does not exist
> LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...
>
> Any idea ?
>
> Thanks,

Looks and smells like a bug.

--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

In response to Louis-David Mitterrand :
> Hi,
>
> This works:
>
> critik=# select current_timestamp::abstime::int4 as score order by score;
>
> This doesn't:
>
> critik=# select current_timestamp::abstime::int4 as score order by score + 1;
> ERROR: column "score" does not exist
> LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...
>
> Any idea ?

Yes, you can't use the alias in the ORDER BY. Use the real column-name.

select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4;

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote:
> In response to Louis-David Mitterrand :
> > Hi,
> >
> > This works:
> >
> > critik=# select current_timestamp::abstime::int4 as score order by score;
> >
> > This doesn't:
> >
> > critik=# select current_timestamp::abstime::int4 as score order by score + 1;
> > ERROR: column "score" does not exist
> > LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...
> >
> > Any idea ?
>
> Yes, you can't use the alias in the ORDER BY. Use the real column-name.
>
> select current_timestamp::abstime::int4 as score order by
> current_timestamp::abstime::int4;

Did you try

"select current_timestamp::abstime::int4 as score order by score;" ?

This seems to be an "order by <alias>"

--
http://www.critikart.net

what for of the syntax command +1 on order by ? maybe just wrong to given result about the error query on order by, it's BUG ?

dbalinglung

DataproSoft Developer

----- Original Message -----
From: "Louis-David Mitterrand" <vindex+lists-pgsql-general(at)apartia(dot)org>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, January 14, 2009 5:46 PM
Subject: [GENERAL] inconsistency in aliasing

> Hi,
>
> This works:
>
> critik=# select current_timestamp::abstime::int4 as score order by score;
>
> This doesn't:
>
> critik=# select current_timestamp::abstime::int4 as score order by score + 1;
> ERROR: column "score" does not exist
> LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...
>
> Any idea ?
>
> Thanks,
>
> --
> http://www.critikart.net
>

On Wed, Jan 14, 2009 at 06:31:55PM +0700, dbalinglung wrote:
> what for of the syntax command +1 on order by ? maybe just wrong to
> given result about the error query on order by, it's BUG ?

*PARSE ERROR*

In response to Louis-David Mitterrand :
> > Yes, you can't use the alias in the ORDER BY. Use the real column-name.
> >
> > select current_timestamp::abstime::int4 as score order by
> > current_timestamp::abstime::int4;
>
> Did you try
>
> "select current_timestamp::abstime::int4 as score order by score;" ?
>
> This seems to be an "order by <alias>"

Ouch, my fault, muddled with WHERE

Thx, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On Wed, Jan 14, 2009 at 3:46 AM, Louis-David Mitterrand
<vindex+lists-pgsql-general(at)apartia(dot)org> wrote:
> Hi,
>
> This works:
>
> critik=# select current_timestamp::abstime::int4 as score order by score;
>
> This doesn't:
>
> critik=# select current_timestamp::abstime::int4 as score order by score + 1;
> ERROR: column "score" does not exist
> LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...

A number in an order by refers to a column.

select a, b, c/d as f from table order by 3;

will order by the third field in the select list. I'm guessing the +
1 is trying to add col 1 to a field it can't find. doubt it's a bug,
more like a quirk.

Reg Me Please wrote:

> > critik=# select current_timestamp::abstime::int4 as score order
by score +
> > 1; ERROR: column "score" does not exist
> > LINE 1: ...urrent_timestamp::abstime::int4 as score order by
score + 1 ...
> >
> > Any idea ?
> >
> > Thanks,
>
> Looks and smells like a bug.

Read
http://www.postgresql.org/docs/8.3/static/queries-order.html

<quote>
The sort expression(s) can be any expression that would be valid in
the query's select list
</quote>

score+1 is not valid in the query's select list (as well as "score"
anyway)

<quote>
For backwards compatibility with the SQL92 version of the standard, a
sort_expression can instead be the name or number of an output column
</quote>

so that's why "score" alone works in the order by, despite it not being
valid in the select list.

<quote>
Note that an output column name has to stand alone, it's not allowed
as part of an expression
</quote>

Which looks like the very issue discussed here, and it just behaves as
documented.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

--- On Wed, 1/14/09, Louis-David Mitterrand <vindex+lists-pgsql-general(at)apartia(dot)org> wrote:

> From: Louis-David Mitterrand <vindex+lists-pgsql-general(at)apartia(dot)org>
> Subject: Re: [GENERAL] inconsistency in aliasing
> To: pgsql-general(at)postgresql(dot)org
> Date: Wednesday, January 14, 2009, 11:27 AM
> On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer
> wrote:
> > In response to Louis-David Mitterrand :
> > > Hi,
> > >
> > > This works:
> > >
> > > critik=# select current_timestamp::abstime::int4
> as score order by score;
> > >
> > > This doesn't:
> > >
> > > critik=# select current_timestamp::abstime::int4
> as score order by score + 1;
> > > ERROR: column "score" does not exist
> > > LINE 1: ...urrent_timestamp::abstime::int4 as
> score order by score + 1 ...
> > >
> > > Any idea ?
> >
> > Yes, you can't use the alias in the ORDER BY. Use
> the real column-name.
> >
> > select current_timestamp::abstime::int4 as score order
> by
> > current_timestamp::abstime::int4;
>
> Did you try
>
> "select current_timestamp::abstime::int4 as score
> order by score;" ?
>
> This seems to be an "order by <alias>"
>
> --
> http://www.critikart.net
>

you can't use operator in the group by, try this

select score,score+1 as score2 from (
select current_timestamp::abstime::int4 as score)
order by score2

On Wed, Jan 14, 2009 at 05:53:57AM -0800, Lennin Caro wrote:
> --- On Wed, 1/14/09, Louis-David Mitterrand <vindex+lists-pgsql-general(at)apartia(dot)org> wrote:
>
> > From: Louis-David Mitterrand <vindex+lists-pgsql-general(at)apartia(dot)org>
> > Subject: Re: [GENERAL] inconsistency in aliasing
> > To: pgsql-general(at)postgresql(dot)org
> > Date: Wednesday, January 14, 2009, 11:27 AM
> > On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer
> > wrote:
> > > In response to Louis-David Mitterrand :
> > > > Hi,
> > > >
> > > > This works:
> > > >
> > > > critik=# select current_timestamp::abstime::int4
> > as score order by score;
> > > >
> > > > This doesn't:
> > > >
> > > > critik=# select current_timestamp::abstime::int4
> > as score order by score + 1;
> > > > ERROR: column "score" does not exist
> > > > LINE 1: ...urrent_timestamp::abstime::int4 as
> > score order by score + 1 ...
> > > >
> > > > Any idea ?
> > >
> > > Yes, you can't use the alias in the ORDER BY. Use
> > the real column-name.
> > >
> > > select current_timestamp::abstime::int4 as score order
> > by
> > > current_timestamp::abstime::int4;
> >
> > Did you try
> >
> > "select current_timestamp::abstime::int4 as score
> > order by score;" ?
> >
> > This seems to be an "order by <alias>"
> >
> > --
> > http://www.critikart.net
> >
>
> you can't use operator in the group by, try this

Really?

select current_timestamp::abstime::int4 as score order by 1 + 1 ;

score
------------
1231941662
(1 row)

--
http://www.critikart.net

Lennin Caro wrote:

> select score,score+1 as score2 from (
> select current_timestamp::abstime::int4 as score)
> order by score2

That additional score2 is not needed in the select output.

This works just fine:

=> select score from (select current_timestamp::abstime::int4 as score)
subsel order by score+1;

The point is that the subselect makes "score" available as a valid
expression to the upper select.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

2009/1/14 Daniel Verite <daniel(at)manitou-mail(dot)org>:
> Lennin Caro wrote:
>
>> select score,score+1 as score2 from (
>> select current_timestamp::abstime::int4 as score)
>> order by score2
>
> That additional score2 is not needed in the select output.
>
> This works just fine:
>
> => select score from (select current_timestamp::abstime::int4 as score)
> subsel order by score+1;
>

I'm been watching that the string name of order by with operator just work fine
if the column name is in the select clause. But if you use alias this
not work...

I'm don't believe is a bug, is a string name question. You can't add 1
to an alias,
but you can add 1 to a field... but the results is the same

> The point is that the subselect makes "score" available as a valid
> expression to the upper select.
>
> Best regards,
> --
> Daniel
> PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Emanuel Calvo Franco
ArPUG / AOSUG Member
Postgresql Support & Admin

On 2009-01-14, Louis-David Mitterrand <vindex+lists-pgsql-general(at)apartia(dot)org> wrote:
> Hi,
>
> This works:
>
> critik=# select current_timestamp::abstime::int4 as score order by score;
>
> This doesn't:
>
> critik=# select current_timestamp::abstime::int4 as score order by score + 1;
> ERROR: column "score" does not exist
> LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...
>
> Any idea ?
>
> Thanks,

if you really want to do that you can use a subselect,

select score from ( select current_timestamp::abstime::int4 as score )
as foo order by score+1;

but the demonstration is more interesting when the query returns several rows
and the expression changes the order.

select score from ( select generate_series(1,10) as score ) as foo
order by score % 2 desc; -- odds and evens.

:)

bye.

as far as I know, this bit (statement evaluation) wasn't implemented
then. It only got there in 8.4, so you can have even subselects
evaluated.
So it isn't a bug, it just wasn't implemented to work that way back than,