Tested On: 9.4.1, 9.3.6
Severity: minor
Summary: PREPARE/EXECUTE appears to silently discard ORDER BY parameters.
josh=# \d test
Table "public.test"
Column | Type | Modifiers
--------+------+-----------
test | text |
josh=# insert into test values ('test1'),('test9'),('test3'),('test2');
INSERT 0 4
josh=# prepare foo as select * from test order by $1;
PREPARE
josh=# execute foo('test');
test
-------
test1
test9
test3
test2
(4 rows)
josh=# explain analyze
josh-# execute foo('test');
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..23.10 rows=1310 width=32) (actual
time=0.007..0.007 rows=4 loops=1)
Execution time: 0.026 ms
(2 rows)
What appears to be happening is that the prohibited parameter for ORDER
BY is being silently discarded during EXECUTE. At first I thought it
might just be doing ORDER BY 'test' in the background, but that's not it:
josh=# select * from test order by 'test';
ERROR: non-integer constant in ORDER BY
LINE 1: select * from test order by 'test';
josh=# execute foo(1);
test
-------
test1
test9
test3
test2
(4 rows)
josh=# select * from pg_prepared_statements ;
name | statement |
prepare_time | parameter_types | from_sql
------+------------------------------------------------+-------------------------------+-----------------+----------
foo | prepare foo as select * from test order by $1; | 2015-05-11
16:52:55.369479-07 | {text} | t
(1 row)
So something else is happening here. What should probably be happening
is that PREPARE should throw an error if it gets a parameter in the
ORDER BY clause.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
