bug #7499 additional comments

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(dot)Grittner(at)wicourts(dot)gov> wrote:
> Denis Kolesnik <lirex(dot)software(at)gmail(dot)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
>