Re: Case in Order By Ignored without warning or error
От | Emiel Hermsen |
---|---|
Тема | Re: Case in Order By Ignored without warning or error |
Дата | |
Msg-id | CABBJNBvw11d+6LS0yoeivLYGhf40DYZ77JicEjhmCNSusPOLyg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Case in Order By Ignored without warning or error ("David G. Johnston" <david.g.johnston@gmail.com>) |
Список | pgsql-bugs |
I'd like to thank you both for your responses. These have helped me understand the behavior of PostgreSQL. However, I am a little lost on what happens now or what I am to do now, so I'm going out on a limb: Forgive me for being bolt in stating my humble opinion below: 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 =E2=80=94 for example, this is not correct: SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong---------------------------------------------------------------- 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. 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. 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. I would like to conclude with my apologies in advance in case any of the above is out of line for this mailing list. this is my first bug report. Thank you for your time, Emiel Hermsen 2016-06-07 15:38 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>: > 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 t= o >> 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 a= n >> > 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 >> select >> > 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 redun= dant or > imprecise. > > "=E2=80=8BIt is also possible to use arbitrary expressions in the ORDER B= Y 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 wh= ile > unadorned column names and literal integers are used as lookup values int= o > a column map and the values in the referenced columns are then sorted. > > David J. > >
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Bo Ørsted AndresenДата:
Сообщение: Re: BUG #14180: Segmentation fault on replication slave
Следующее
От: APДата:
Сообщение: Re: BUG #14178: output of jsonb_object and json_object doesn't match textually