Re: BUG #7499: wrong data sorting if I use "...limit 1..." SQL clause along with "...order by ..." - Mailing list pgsql-bugs

lirex.software@gmail.com writes:
> 3. select id, str_last_name from tbl_owners_individual order by
> str_last_name offset 53;

> ...
>    1 | Kolesnik
>  111 | Kolesnik
>  251 | XXXXXXXXXX
>  112 | XXXXX
> ...

> 4. select id, str_last_name from tbl_owners_individual order by
> str_last_name limit 1 offset 53;

>  111 | Kolesnik

This is not a bug.  Your ORDER BY does not fully determine the order of
the rows (since all rows with str_last_name = 'Kolesnik' sort the same);
so Postgres is entitled to return equivalent rows in any order, or to
select any subset of the equivalent rows for a LIMIT query.

It's not completely nondeterministic, of course, but I suspect
if you examine EXPLAIN results you'll find that different query
plans got chosen for the queries with and without LIMIT.

            regards, tom lane