Re: strange order by behavior

Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> writes:
> I've run across a bug with order by:
> select 1 as a order by a;
> -- works

> select 1 as a, 2 as b order by a + b;
> ERROR: column "a" does not exist

This isn't a bug, strangely enough. The "order by output-column-name"
syntax is a holdover from SQL92, in which ORDER BY items could only be
the names or numbers of SELECT output columns. The new SQL99 theory is
that ORDER BY items are expressions over the input columns, same as the
SELECT output expressions themselves. We have to walk a fine line in
deciding how much of each spec to support, and the line we've drawn is
that simple names will be sought as output column names, but any
expression more complex than a simple name is an expression over the
input columns.

regards, tom lane