Thread: [BUGS] ORDER BY $1 behaves inconsistently

Version: 10.0

As I understand it, the only valid constant datatype in an ORDER BY is integer. That's validated by the following test:

jordan=# SELECT * FROM t ORDER BY 'foo';

ERROR:  non-integer constant in ORDER BY

LINE 1: SELECT * FROM t ORDER BY 'foo';

However, using a prepared statement, this behavior can be avoided:

jordan=# PREPARE x as SELECT * FROM t ORDER BY $1;

PREPARE

jordan=# EXECUTE x('foo');

 c

---

 1

It seems to me that there is some missing type checking from ORDER BY.

Jordan Lewis <jordanthelewis@gmail.com> writes:
> Actually, it's even worse than I thought. It seems that placeholders in
> ORDER BY clauses get entirely ignored,
> as running `EXECUTE x(1)` on the previous example with unsorted table data
> does not sort the table by the 1st
> column as expected.

"ORDER BY $1" is asking to order by some constant value (constant within
any one execution of the command, anyway), which is useless, because all
rows in the query will have the same sort key.  The planner will throw
that away as being a no-op.

"ORDER BY 1" is asking to order by the first output column.  This is
not the same thing.  You cannot get that effect with a parameter;
if you could, it would probably represent a SQL-injection hazard.

If the value of $1 were "foo" and you complained that it didn't order
by column foo, it would be exactly the same issue...

                        regards, tom lane