Re: bug #7499 additional comments - Mailing list pgsql-bugs

Denis Kolesnik <lirex.software=40gmail.com> wrote:
=20
> My arguments are:
>=20
>  is that even
> select id, str_last_name from tbl_owners_individual where id in
> (83,175,111,1) order by id;
>=20
>  id  =7C    str_last_name
> -----+----------------------
>    1 =7C Kolesnik
>   83 =7C GXXXXXXXXX
>  111 =7C Kolesnik
>  175 =7C GXXXXXXXXX
> (4 ******)
>=20
> 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
> 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 =22id
> 1=22 always closer to the top, then =22id 111=22 and =22id 83=22 always
> clother to the top then =22id 175=22. It proves, that the sorting by
> id remains always even if only among records for the same
> lastname.
=20
=5Bsigh=5D
=20
It proves no such thing.  It happened to pick a path *that time*
which happened to generate them in that order.  There are no
guarantees that it always will.  Do you imagine that a column named
=22id=22 has any special properties compared to a column by any other
name?  It doesn=27t.  What do you imagine would happen if you had
columns with integers in different sequences?
=20
Please run this script on your system before your next post:
=20
drop if exists table tbl_test;
create table tbl_test
  (id int not null primary key,
   str_last_name text not null,
   misc text);
insert into tbl_test values
  (1, =27Kolesnik=27),
  (83, =27GXXXXXXXXX=27),
  (111, =27Kolesnik=27),
  (175, =27GXXXXXXXXX=27);
select id, str_last_name from tbl_test
  where id in (83,175,111,1) order by str_last_name;
update tbl_test set misc =3D =27x=27 where id =3D 1;
select id, str_last_name from tbl_test
  where id in (83,175,111,1) order by str_last_name;
analyze tbl_test;
select id, str_last_name from tbl_test
  where id in (83,175,111,1) order by str_last_name;
=20
> You would sugguest, that one should read documentation.
>=20
> in the (where with ... replaced a directory in which the
> PostgreSQL installed)
> ...PostgreSQL=5C9.1=5Cdoc=5Cpostgresql=5Chtml=5Cqueries-limit.html
>=20
> =22...When using LIMIT, it is important to use an ORDER BY clause
> that constrains the result rows into a unique order. ..=22
>=20
> here asked to use =22ORDER BY=22 which is done in every query above.
=20
No, it asked to specify ORDER BY such that it =22constrains the result
rows into a unique order=22 -- which you are not doing in your
examples.  That is exactly what you *should* do to get the results
you want.
=20
> =22...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; ...=22
>=20
> the values of =22ORDER BY=22 for LIMIT/OFFSET are not different as you
> see. All requirements are filled.
=20
Not even close.  Read it again.  Your ORDER BY clause is not
guaranteeing predictable results.  It would if you added the primary
key columns to the ORDER BY clause (assuming there is a primary
key).
=20
You would be well served to fix your query and move on.  There=27s no
way we=27re going to make it behave the way you are requesting.  The
current behavior conforms to the SQL standard, performs better than
it could if it worked the way you suggest, and allows you to get the
results you want by fully specifying the ORDER BY clause.
=20
-Kevin