non-integer constant in ORDER BY: why exactly, and documentation? - Mailing list pgsql-general

Hi.  I recently ran a query that generate the same error as this:

SELECT * FROM generate_series(1,10) ORDER BY 'foo';
ERROR:  non-integer constant in ORDER BY
LINE 1: SELECT * FROM generate_series(1,10) ORDER BY 'foo';

The query was generated by an app (and the result somewhat inadvertent), so it was easy enough to change and I'm not asking here about a practical problem.

I am curious though about why this "limitation" exists.  I get that integer constants are reserved for sorting by column numbers.  But if Postgres already knows that it's a non-integer constant,  why not let it go through with the (admittedly pointless) ordering?

Also, I couldn't see that this was explictly mentioned in the documentation.  The relevant pieces seemed to be:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

followed closely by:
It is also possible to use arbitrary expressions in the ORDER BY clause, including columns that do not appear in the SELECT output list.
(http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-ORDERBY)

And looking at the expressions page (http://www.postgresql.org/docs/8.4/static/sql-expressions.html), the first type of value expression is a "constant or literal expression."  So nothing seems to explicitly rule out a literal ORDER BY.

I'm not sure if it would do violence to something I'm missing, but would the following combined statement work for the documentation?

"Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression.   The expression can include column values--whether they appear in the SELECT output list or not.  An expression may not, however, consist solely of a non-integer constant. And an integer constant will be interpreted as the ordinal number of an output column "

Thanks in advance.

Ken




--