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.
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.
On Fri, Oct 27, 2017 at 12:18 PM Jordan Lewis <jordanthelewis@gmail.com>
wrote:
Show quoted text
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
---
1It 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Thanks for the explanation - that makes sense.
On Fri, Oct 27, 2017 at 2:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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 tabledata
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