Re: non-integer constant in ORDER BY: why exactly, and documentation?

On Oct 11, 2012, at 4:48 PM, Ken Tanzer wrote:

> 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
askinghere about a practical problem. 
>
> I am curious though about why this "limitation" exists.  I get that integer constants are reserved for sorting by
columnnumbers.  But if Postgres already knows that it's a non-integer constant,  why not let it go through with the
(admittedlypointless) 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
expressionformed 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
SELECToutput 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
ofvalue 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
thedocumentation? 
>
> "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
expressionmay not, however, consist solely of a non-integer constant. And an integer constant will be interpreted as
theordinal number of an output column " 

Apparently, the parser tries to pull an column index out of any constant appearing in that position. It can be
triviallyworked around: 

select * from generate_series(1,10) order by coalesce('foo');

but that doesn't help if your query is automatically generated.

Cheers,
M