Re: Prepare/Execute silently discards prohibited ORDER BY values

On 05/11/2015 08:35 PM, Tom Lane wrote:
>> 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.

Well, the fact that:

ORDER BY 'test'

... errrors, whereas

ORDER BY 'test'::TEXT

... does not is a very small POLS violation.  I was not the only one
confused by it; pitching this on IRC, several postgres hackers and
advanced users thought it was a bug.

On the other hand, it's also not exactly breaking anyone's stuff once
you understand what it's doing.

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