Prepare/Execute silently discards prohibited ORDER BY values - Mailing list pgsql-bugs

From Josh Berkus
Subject Prepare/Execute silently discards prohibited ORDER BY values
Date
Msg-id 555143EC.1000808@agliodbs.com
Whole thread Raw
Responses Re: Prepare/Execute silently discards prohibited ORDER BY values
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table
Next
From: Tom Lane
Date:
Subject: Re: Prepare/Execute silently discards prohibited ORDER BY values