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

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ken Tanzer
Sent: Thursday, October 11, 2012 4:49 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] non-integer constant in ORDER BY: why exactly, and
documentation?

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 "

I would categorize this under "help people avoid shooting themselves in the
foot". A possible situation is that the user meant to use double-quotes to
specify an identifier but instead used single quotes. Since a literal
constant would not impact the sort order the planner should either discard
it silently or throw an exception. The exception is preferred since the
presence of a constant literal likely means whatever generated the query is
broken and should be fixed.

The documentation tweak probably is overkill given the rarity of the issue
and the fact the system generates an appropriate error message when it does
occur.

David J.