Re: additional message to the bug #7499

Denis Kolesnik <lirex.software=40gmail.com> wrote:
=20
> I have now VERY strong argument to consider it is as a bug:
=20
No, you appear to have very strong feelings about it, but you are
not making an argument that holds water.
=20
> if there a understandable for SQL language sequence which sorts
> in other fashion when adding =22LIMIT=22.
=20
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
=22natural order=22 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=27s 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=27t
care about the order.
=20
> 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;
>=20
>  id  =7C    str_last_name
> -----+----------------------
>   83 =7C GXXXXXXXXX
>  175 =7C GXXXXXXXXX
>    1 =7C Kolesnik
>  111 =7C Kolesnik
> (4 ******)
=20
No, it didn=27t go out of its way to sort that way, it just happened
to fall out that way that time; don=27t count on it always being that
way, even if it happens many times in a row.
=20
test=3D=23 create table tbl_owners_individual
test-=23   (id int not null primary key, str_last_name text not null);
CREATE TABLE
test=3D=23 insert into tbl_owners_individual values
test-=23   (1, =27Kolesnik=27),
test-=23   (83, =27GXXXXXXXXX=27),
test-=23   (111, =27Kolesnik=27),
test-=23   (175, =27GXXXXXXXXX=27);
INSERT 0 4
test=3D=23 select id, str_last_name from tbl_owners_individual where id
in
test-=23 (83,175,111,1) order by str_last_name;
 id  =7C str_last_name=20
-----+---------------
  83 =7C GXXXXXXXXX
 175 =7C GXXXXXXXXX
   1 =7C Kolesnik
 111 =7C Kolesnik
(4 rows)
=20
-Kevin