Re: Question, how intelligent is optimizer with subplans? - Mailing list pgsql-general

Greg Stark <gsstark@MIT.EDU> writes:
> On second thought what I wanted to do should work, I think. I'm not clear why
> the first of these works fine but the second doesn't. What I want to do is
> effectively the second of these:
> slo=> select (select count(*) from t2) as x from t order by x;
>  x
> ---
>  0
> (1 row)

> slo=> select (select count(*) from t2) as x from t order by sign(x);
> ERROR:  Attribute "x" not found

The first of those works because the SQL spec says it should: unadorned
names appearing in ORDER BY are output column names per the spec.

The second of those is not legal per SQL spec (you can't put anything
except an unadorned output column name or number in ORDER BY, according
to the spec).  Postgres accepts expressions in ORDER BY, but we consider
them to be expressions in the input column names.

You could do something like

SELECT x
FROM
  (select count(*) as x from t2) as ss
ORDER BY sign(x);

            regards, tom lane