Re: Prepare/Execute silently discards prohibited ORDER BY values

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> On 05/11/2015 05:18 PM, Tom Lane wrote:
>> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> 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.

Well, not quite. The core problem is that SQL92 said that "ORDER BY n"
(where "n" could only be an integer constant) means "order by the N'th
output column" ... and then SQL99 forgot about that altogether, and
defined the entirely more sensible rule that ORDER BY items are just
expressions that have their face value. We try to support both of those
cases, both for backwards compatibility and because ORDER BY n (also
GROUP BY n) is such a damn handy abbreviation so much of the time.

Somewhere along the line we decided that "ORDER BY non-integer-constant"
was too close to the boundary line between those two interpretations, so
it would be better to reject it and make you use a less ambiguous syntax.
I'm too lazy to go digging in the archives for that discussion (it was
quite a few years back, though). But that's why you're seeing a syntax
failure for "ORDER BY 'test'". We could certainly make that case do
something else if we wanted ... but I'm not sure it'd be an improvement.

regards, tom lane