Re: Case in Order By Ignored without warning or error

On Tue, Jun 7, 2016 at 9:19 AM, Francisco Olarte <folarte@peoplecall.com>
wrote:

> On Tue, Jun 7, 2016 at 2:58 PM, Emiel Hermsen <s32191234@gmail.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.
>

=E2=80=8BPostgreSQL, please...=E2=80=8B


> >
> > 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.
> =E2=80=8B[...]=E2=80=8B
>
> > According to documentation,
> > https://www.postgresql.org/docs/9.3/static/queries-order.html, my selec=
t
> > query above is incorrect, however psql does not tell me this.
> =E2=80=8B[...]=E2=80=8B
>
> 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 ).
>

=E2=80=8BThis sentence, a couple below the one you quote, is either redunda=
nt or
imprecise.

"=E2=80=8BIt 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."

=E2=80=8BIn short, expressions are resolved and sorted on their result whil=
e
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.