Re: Case in Order By Ignored without warning or error

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.
>
> 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.

Or does it execute it sorting by a constant value 3?

> Also when using indexes that are out of bounds, like -2 or 8, there are
> neither warnings/errors nor sorting.

Or does it sort by the constant value -2 / 8 ?

I say this because 3/-2/8 are valid ( although meaningless ) sort
keys, more on this....

> 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.

Psql just sends the queries to the server. OTOH, the page you quote
says near the top "The sort expression(s) can be any expression that
would be valid in the query's select list.", and given

xxx=# select version();
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.10 on x86_64-pc-linux-gnu, compiled by
x86_64-pc-linux-gnu-gcc (Gentoo 4.9.3 p1.4, pie-0.6.4) 4.9.3, 64-bit
(1 row)

xxx=# select (case 1 when 1 then 3 else 1 end);
case
------
3
(1 row)

You are just sorting by a constant expression, like if you had a
column with the value 3 in every row.

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 ).

Francisco Olarte.