Re: Case in Order By Ignored without warning or error

Fracisco,

2016-06-08 12:37 GMT+02:00 Francisco Olarte <folarte(at)peoplecall(dot)com>:

> Emiel:
>
> 1.- Please, do not top post. It does not matter too much in this case,
> but makes the discussion extremely difficult to follow.
>
>
I did not think of this yet, thank you for your suggestion and the required
patience.

> 2.- This is not a bug, maybe you should move it to general.
>
> Agreed and I will, so for as far as I am concerned this "topic" can be
"closed" (I do not know a better way to describe it).

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

Understood. I did test the order by (a+b)+c with the statement: SELECT *
FROM films ORDER BY 1+1; which does not sort on the second column.
Therefore I assume that any construction like (a+b)+c will not work either.

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

I do agree on your last statement about the difficulty.
My opinion in this matter is mostly based of my findings regarding the
"ORDER BY 1+1" not doing anything.
However, this discussion should be followed up in the general section.

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

The application has been doing well for multiple years now. However the
edge case that was to be covered by the ORDER BY in this query is likely to
have been overlooked due to the general error margin of the output data.

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

Agreed, which is how I got to find the initial faulty query.

Thank you for your patients, and the responses and explanations.

Kind Regards,
Emiel Hermsen