Re: Case in Order By Ignored without warning or error

I'd like to thank you both for your responses.
These have helped me understand the behavior of PostgreSQL.

However, I am a little lost on what happens now or what I am to do now, so
I'm going out on a limb:

Forgive me for being bolt in stating my humble opinion below:
Based on the explanation give by David, one option would be to alter the
documentation section linked and copied below. This because PostgreSQL
accepts and executes the query, but will almost guaranteed not do what the
writer of the statement intends.

----------------------------------------------------------------
Note that an output column name has to stand alone, that is, it cannot be
used in an expression — for example, this is not correct:

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; --
wrong----------------------------------------------------------------

The second option, again just my opinion, would be to change the behavior
where the ORDER BY clause refuses any contained content other than numbers
and column names combined with the ASC and DESC keywords.

My most important argument for this is that the code that led me to asking
this question has been implemented in 2003 and run in a production
environment ever since.
Of course the edge case the ORDER BY was to cover, should have been
properly tested and the programmer at the time should have known the
restrictions on the order by statement. But I would argue that PostgreSQL
will "never" do what the programmer has intended, in which case, preferably
an error but at least, a warning would be warranted.

I would like to conclude with my apologies in advance in case any of the
above is out of line for this mailing list. this is my first bug report.

Thank you for your time,

Emiel Hermsen

2016-06-07 15:38 GMT+02:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> On Tue, Jun 7, 2016 at 9:19 AM, Francisco Olarte <folarte(at)peoplecall(dot)com>
> wrote:
>
>> On Tue, Jun 7, 2016 at 2:58 PM, Emiel Hermsen <s32191234(at)gmail(dot)com>
>> wrote:
>> > Hello,
>> >
>> > I'm currently working with PostGreSQL 9.3 on a RedHat 6.6 device.
>> > One of my predecessors decided he wanted dynamic sorting which seems to
>> be
>> > ignored.
>>
>
> ​PostgreSQL, please...​
>
>
>> >
>> > My made-up testing table definition is as follows:
>> > CREATE TABLE films (
>> > id SERIAL PRIMARY KEY,
>> > title varchar(40) NOT NULL,
>> > imdbnumber integer
>> > );
>> >
>> > INSERT INTO films (title, imdbnumber) VALUES ('Film a', 2000), ('Film
>> b',
>> > 1999);
>> >
>> > When using psql on the command line, I enter the following query:
>> >
>> > select * from films order by (case 1 when 1 then 3 else 1 end);
>> >
>> > I would expect this query to either sort on column 3, or refuse with an
>> > error.
>> > Instead it executes the query with incorrect sorting and no warning or
>> > error.
>> ​[...]​
>>
>> > According to documentation,
>> > https://www.postgresql.org/docs/9.3/static/queries-order.html, my
>> select
>> > query above is incorrect, however psql does not tell me this.
>> ​[...]​
>>
>> The section for the order by clause in the page for the select command
>> states "Each expression can be the name or ordinal number of an output
>> column (SELECT list item), or it can be an arbitrary expression formed
>> from input-column values.", and I supose it goes the ordinal number
>> way only when it is a simple constant integer, I even doubt order by
>> 1+0 would work ( because otherwise every integer-valued expresion
>> could be interpreted as an ordinal, so it seems to be interpreting it
>> ( as I would expect ) as an arbitrary expression fomed from ( 0 )
>> input column values ).
>>
>
> ​This sentence, a couple below the one you quote, is either redundant or
> imprecise.
>
> "​It is also possible to use arbitrary expressions in the ORDER BY clause,
> including columns that do not appear in the SELECT output list. Thus the
> following statement is valid:"
>
> If kept if should be written:
>
> "It is also possible to use arbitrary expressions in the ORDER BY clause,
> but those expressions cannot refer to column in the SELECT output list.
> Thus the following statement is valid."
>
> ​In short, expressions are resolved and sorted on their result while
> unadorned column names and literal integers are used as lookup values into
> a column map and the values in the referenced columns are then sorted.
>
> David J.
>
>