Re: [Firebird-docs] langref ORDER BY incorrect caution

[Mark R. <ma...@la...>]

On 31-05-2020 18:37, Paul Vinkenoog wrote:
> Mark Rotteveel wrote:
>> Any objections if I just remove this caution in the new version?
> 
> No, please correct this. I sometimes use expressions for ordering
> levels, e.g. with IIF or DECODE constructs that return, say, the
> numbers 1, 2 and 3, and they do the expected thing in Fb 2.5 and 3,
> i.e. they sort by the numerical value returned, not by the contents
> of column 1, 2 or 3.

Did you mean "No, I have no objections", or "No, don't remove it, but 
correct it"? The caution is entirely wrong. I could write another note 
to explain the actual behaviour, but that sounds more like an 
improvement for later than removing factually incorrect information.

> It would be silly anyway, because the value of X+Y may vary wildly
> across rows, so which one would determine the column number
> to sort by?
> 
> Maybe what they're (trying to) say is true for constant expressions
> and/or expressions involving only constants and local variables in
> PSQL.

Constant expressions other than `number` or `(number)` sort by the value 
as well (so `1+1` does not sort by column 2). Sorting by a PSQL variable 
(is that possible?) will likely also not sort by column position, as the 
sort needs to be known at prepare time.

Interestingly enough, Firebird does perform some sort of sort when using 
constant expressions. Without thorough testing it looks like it performs 
an equivalent of `ORDER BY <all columns of the select clause>`, but that 
might just be the limited set of data I used when testing.

Mark
-- 
Mark Rotteveel