PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Invalid order by expression placeholder ignored by prepared statement

Given this table and data:

test# create table t (c int);
CREATE TABLE
test# insert into t (c) values (1), (3), (2);
INSERT 0 3

If I try to query the table with an invalid order by expression in a statement I get an error:

test# select c from t order by 'c' desc;
ERROR:  non-integer constant in ORDER BY
LINE 1: select c from t order by 'c' desc;

which is logged:

2024-02-14 11:26:31.734 GMT test user [5047]LOG:  statement: select c from t order by 'c' desc;
2024-02-14 11:26:31.734 GMT test user [5047]ERROR:  non-integer constant in ORDER BY at character 26
2024-02-14 11:26:31.734 GMT test user [5047]STATEMENT:  select c from t order by 'c' desc;

However if I bind the invalid value to a prepared statement, there is no error and the rows are returned unordered:

test# prepare q (varchar) as select c from t order by $1;
PREPARE
test# execute q ('c');
 c 
═══
 1
 3
 2
(3 rows)

No error is logged:

2024-02-14 15:18:17.626 GMT test user [5047]LOG:  statement: prepare q (varchar) as select c from t order by $1;
2024-02-14 15:18:29.834 GMT test user [5047]LOG:  statement: execute q ('c');
2024-02-14 15:18:29.834 GMT test user [5047]DETAIL:  prepare: prepare q (varchar) as select c from t order by $1;

Using the bind meta-command likewise does not error and produces the same result:

test# select c from t order by $1 desc bind 'c' g
 c 
═══
 1
 3
 2
(3 rows)

Logs:

2024-02-14 15:22:31.123 GMT test user [5047]LOG:  execute <unnamed>: select c from t order by $1 desc 
2024-02-14 15:22:31.123 GMT test user [5047]DETAIL:  parameters: $1 = 'c'

Why doesn’t the prepared statement fail with the same error as in the first statement?

Background context: I noticed in this Python question, originally tagged as *, the statement cursor.execute("SELECT * FROM cars ORDER BY %s DESC;", (k,)) raises the above error when executed by psycopg2, which binds parameters client-side, but succeeds, returning an unordered response, using psycopg3, which lets the server perform the binding.

* psycopg2 and psycopg3 are Python connection packages (drivers) for PostgreSQL. Both are wrappers around libpq as I understand it.

Tags:

2

Answers


  1. 0

    The parameter should not be used as column name or table name ever. If you want to do it, you should to use dynamic SQL. The clause ORDER BY is difficult, because (naturally) supports column names, but it can supports any expressions too – but the order is based on the result on expression, and result of expression cannot be used as name of sorted column. ORDER BY supports integer values there, and then it means sort by column on position.

    You can check EXPLAIN of prepared statement:

    (2024-02-14 17:14:39) postgres=# explain analyze execute q ('c');
┌────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                           QUERY PLAN                                           │
╞════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on t  (cost=0.00..35.50 rows=2550 width=36) (actual time=0.016..0.018 rows=3 loops=1) │
│ Planning Time: 0.097 ms                                                                        │
│ Execution Time: 0.038 ms                                                                       │
└────────────────────────────────────────────────────────────────────────────────────────────────┘
(3 rows)

    There is not sort node.

    The reason, why the error is not raised in this case is probably effect of some heuristic for that separates processing for constants and expressions. In this concept the constant is some different from immutable expression. I think it depends on implementation of SQL parser (based on bison). When you use $1 – query parameter, it is expression (it is allowed), but because it is immutable expression, then it is ignored.

    (2024-02-14 17:23:32) postgres=# select c from t order by 'c' desc;
ERROR:  non-integer constant in ORDER BY
LINE 1: select c from t order by 'c' desc;
                                 ^
(2024-02-14 17:29:28) postgres=# select c from t order by 'c'::varchar desc;
┌───┐
│ c │
╞═══╡
│ 1 │
│ 3 │
│ 2 │
└───┘
(3 rows)
    Login or Signup to reply.
  2. 0

    Order by clause need sort_expression.
    A sort_expression can also be the column label or number of an output column.

    Some examples:
    In this query, c is output column name, output sorted by column c

    select * from test
order by c

    In this query, 1 is number of output column, output sorted by output column 1

    select * from test
order by 1

    In this query, 'c' not is output column name, therefore must be number of output column

    select * from test
order by 'c'

ERROR:  non-integer constant in ORDER BY
LINE 3: order by 'c'

    Next example. 'c'||'a' is sort_expression. There no error and, no ordering 🙂 because expression is constant.

    select * from test
order by 'c'||'a'

    There 2-1 is sort_expression

    select * from test
order by 2-1

    For prepared statement $1 is only sort_expression, not column name, not output column number. By design, this is constant expression – no sorting

    select * from test
order by $1

    For conditional sorting you can

    select * 
from test
order by case when $1='c' then c
              when $1='d' then d
         else e
         end
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search