Re: Case in Order By Ignored without warning or error

Emiel:

1.- Please, do not top post. It does not matter too much in this case,
but makes the discussion extremely difficult to follow.

2.- This is not a bug, maybe you should move it to general.

And now, regarding your message....

On Wed, Jun 8, 2016 at 10:39 AM, Emiel Hermsen <s32191234(at)gmail(dot)com> wrote:
> 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 alias problem is, IIRC, mandated by std compatibility. Anyway, you
can easily order by (a+b)+c ( not too sure about it, now that I think
). Anyway, the problem of languages not doing what the programmer
expects is common, and in my experience commonly caused by failure to
properly read the docs by the programmer.

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

I *strongly* disagree with that. Even if it was just because it will
make a lot of perfectly good code written by people who properly read
the docs before forming some expectations against which they code.
This is SQL, is a powerful, complicated language, and it has to be
learnt.

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

This I accept, but has it been running well?

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

It normally does what the programmer intends. And, in your case, it
seems to me the programmer decided on a convoluted construct and put
it without doing an elementary test. I mean, something like this:

# values (2,20),(1,30),(3,10) order by 1;
column1 | column2
---------+---------
1 | 30
2 | 20
3 | 10
(3 rows)

# values (2,20),(1,30),(3,10) order by 2;
column1 | column2
---------+---------
3 | 10
2 | 20
1 | 30
(3 rows)

# values (2,20),(1,30),(3,10) order by case 1 when 1 then 1 else 2 end;
column1 | column2
---------+---------
2 | 20
1 | 30
3 | 10
(3 rows)

# values (2,20),(1,30),(3,10) order by case 2 when 1 then 1 else 2 end;
column1 | column2
---------+---------
2 | 20
1 | 30
3 | 10
(3 rows)

Easily shows you how it works ( it's known some places, liki
start/offset and group / rder by do not support the whole expression
syntax, so it's better to test ).

Regards.
Francisco Olarte.