Re: Case in Order By Ignored without warning or error

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.