Re: Prepare/Execute silently discards prohibited ORDER BY values

On 05/11/2015 05:18 PM, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> Tested On: 9.4.1, 9.3.6
>> Severity: minor
>> Summary: PREPARE/EXECUTE appears to silently discard ORDER BY parameters.
>
>> josh=# prepare foo as select * from test order by $1;
>> PREPARE
>
> I don't see anything wrong with this.  Ordering by a provably constant
> expression is a no-op, not an error.
>
>> 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
>
> This error is purely a syntactic restriction, not a semantic one.
> There's nothing that will stop you from ordering by, say, "cos(0)";
> and the planner will throw that away too.

Ah, ok.  The problem is that in the SELECT case, 'test' isn't typed, so
the parser is trying to evaluate it and fails?  That makes sense.

josh=# select * from test order by 'test'::TEXT;
 test
-------
 test1
 test9
 test3
 test2
(4 rows)

Issue closed.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com