My arguments are: is that even select id, str_last_name from tbl_owners_individual where id in (83,175,111,1) order by id;
id | str_last_name -----+---------------------- 1 | Kolesnik 83 | GXXXXXXXXX 111 | Kolesnik 175 | GXXXXXXXXX (4 строки) select id, str_last_name from tbl_owners_individual where id in (83,175,111,1) order by str_last_name; id | str_last_name -----+---------------------- 83 | GXXXXXXXXX 175 | GXXXXXXXXX 1 | Kolesnik 111 | Kolesnik (4 строки) Compare this 2 results and you see, that even if the records with the same last names do not come directly one after other then "id 1" always closer to the top, then "id 111" and "id 83" always clother to the top then "id 175". It proves, that the sorting by id remains always even if only among records for the same lastname. Suppose a person who has basic SQL knowledges would learn on praxis how would result a query if a person adds the clause "limit 1" to it and if a person sees results for this query: select id, str_last_name from tbl_owners_individual order by str_last_name offset 26 limit 1; id | str_last_name ----+---------------------- 83 | GXXXXXXXXX (1 строка) and compares result to the query select id, str_last_name from tbl_owners_individual order by str_last_name offset 26; id | str_last_name -----+---------------------- 83 | GXXXXXXXXX 175 | GXXXXXXXXX ... then one makes conclusion, that a sorting by id always remain in both cases, but if one replaces this queries so: select id, str_last_name from tbl_owners_individual order by str_last_name limit 1 offset 53; id | str_last_name -----+---------------------- 111 | Kolesnik (1 строка) select id, str_last_name from tbl_owners_individual order by str_last_name offset 53; id | str_last_name -----+---------------------- 1 | Kolesnik 111 | Kolesnik ... Then a person comes to misunderstanding. You would sugguest, that one should read documentation. in the (where with ... replaced a directory in which the PostgreSQL installed) ...PostgreSQL\9.1\doc\postgresql\html\queries-limit.html "...When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. .." here asked to use "ORDER BY" which is done in every query above. "...The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; ..." the values of "ORDER BY" for LIMIT/OFFSET are not different as you see. All requirements are filled. this part "...The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET." would explain, that adding "LIMIT" will result in some unxplained data sorting, but this "...Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY." then the query with the results as you see: select id, str_last_name from tbl_owners_individual where str_last_name='Kolesnik' order by str_last_name limit 2 offset 2; id | str_last_name -----+---------------------- 111 | Kolesnik 144 | Kolesnik (2 строки) inconsistent results unless you enforce a predictable result ordering with ORDER BY. order by is here predictable, exists, but: select id, str_last_name from tbl_owners_individual order by str_last_name; ... 49 | Kolesnik 224 | Kolesnik 144 | Kolesnik 1 | Kolesnik 111 | Kolesnik ... as you see: offset 2 should return "144 | Kolesnik " and "...inconsistent results..." nowhere in this page of documentation (as I read it, if I do wrong) stated that "...inconsistent results..." not applies to the following 2 queries: select id, str_last_name from tbl_owners_individual order by str_last_name; select id, str_last_name from tbl_owners_individual order by str_last_name limit 2 offset 2; I and not only I by reading this page of documentation will conclude not without a reason that the queries different on presense or absense "...limit 2 offset 2..." should return results which are consistent. Basing on this I conclude, that it is a bug. With respect, Denis Kolesnik. On 8/22/12, Kevin Grittner <kevin.gritt...@wicourts.gov> wrote: > Denis Kolesnik <lirex.softw...@gmail.com> wrote: > >> I have now VERY strong argument to consider it is as a bug: > > No, you appear to have very strong feelings about it, but you are > not making an argument that holds water. > >> if there a understandable for SQL language sequence which sorts >> in other fashion when adding "LIMIT". > > Each query is evaluated in terms of satisfying what is requested in > that query, and the fastest plan which returns those results is > chosen. If you want results to be generated in a specific order, it > is incumbent on you to specify that in the query -- there is no > "natural order" to rows which is used as a tie-breaker. There are > even optimizations to have one query which is going to scan a table > start at the point that another table scan, already in progress is > at, to prevent extra reads -- so exactly the same query run at about > the same time, with no concurrent database changes can easily return > rows in different orders. That's a feature, not a bug. If you want > them in a particular order, say so, and appropriate index usage or > sorts will be added to the query execution to provide them the way > you ask, even though that is slower than it would be if you didn't > care about the order. > >> and even sorting by id: >> select id, str_last_name from tbl_owners_individual where id in >> (83,175,111,1) order by str_last_name; >> >> id | str_last_name >> -----+---------------------- >> 83 | GXXXXXXXXX >> 175 | GXXXXXXXXX >> 1 | Kolesnik >> 111 | Kolesnik >> (4 ******) > > No, it didn't go out of its way to sort that way, it just happened > to fall out that way that time; don't count on it always being that > way, even if it happens many times in a row. > > test=# create table tbl_owners_individual > test-# (id int not null primary key, str_last_name text not null); > CREATE TABLE > test=# insert into tbl_owners_individual values > test-# (1, 'Kolesnik'), > test-# (83, 'GXXXXXXXXX'), > test-# (111, 'Kolesnik'), > test-# (175, 'GXXXXXXXXX'); > INSERT 0 4 > test=# select id, str_last_name from tbl_owners_individual where id > in > test-# (83,175,111,1) order by str_last_name; > id | str_last_name > -----+--------------- > 83 | GXXXXXXXXX > 175 | GXXXXXXXXX > 1 | Kolesnik > 111 | Kolesnik > (4 rows) > > -Kevin > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
