Case in Order By Ignored without warning or error

From: Emiel Hermsen <s32191234(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Case in Order By Ignored without warning or error
Date: 2016-06-07 12:58:54
Message-ID: CABBJNBuLrK2giyL9tmauipdRHZsMoOpDs3cJcnCUUdWJGj6ftw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

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

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.

I don't see it as a problem, now that I know what PostGreSQL does, but I
would assume others without this knowledge would prefer at least a warning
or even an error.

Kind Regards,

Emiel Hermsen

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Francisco Olarte 2016-06-07 13:19:52 Re: Case in Order By Ignored without warning or error
Previous Message David G. Johnston 2016-06-07 12:36:39 Re: BUG #14179: Not enough info in error message