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