Обсуждение: Unexpected behaviour of ORDER BY and LIMIT/OFFSET

Hi all,

I'm having a problem with LIMITed queries, and I don't know is it me who
is doing something wrong or Postgres. (Probably me.)

When I browse through the list of employees:

id   id_dept  name
-------------------
1    1        Tom
2    1        Mike
3    2        Meggie
4    2        Marge
5    3        Bart
6    3        Lisa
7    4        Homer

using LIMITed selects like:

   SELECT * FROM employee ORDER BY id_dept LIMIT 3
   SELECT * FROM employee ORDER BY id_dept LIMIT 3 OFFSET 3
   SELECT * FROM employee ORDER BY id_dept LIMIT 3 OFFSET 6

it seems that Meggie is not in the result list on any of the SELECTs.
 From the first query I get Tom, Mike and Marge, and then from the
second Marge, Bart and Lisa.

Is this expected behavior?

My configuration is: CentOS 5.3 and Postgres 8.3.7.

Regards,
Ognjen

Ognjen Blagojevic <ognjen@etf.bg.ac.yu> writes:
> When I browse through the list of employees:

> id   id_dept  name
> -------------------
> 1    1        Tom
> 2    1        Mike
> 3    2        Meggie
> 4    2        Marge
> 5    3        Bart
> 6    3        Lisa
> 7    4        Homer

> using LIMITed selects like:

>    SELECT * FROM employee ORDER BY id_dept LIMIT 3
>    SELECT * FROM employee ORDER BY id_dept LIMIT 3 OFFSET 3
>    SELECT * FROM employee ORDER BY id_dept LIMIT 3 OFFSET 6

> it seems that Meggie is not in the result list on any of the SELECTs.

"ORDER BY id_dept" isn't a unique sort key.  In this example the
implementation is free to return Meggie and Marge in either order,
and the ordering can indeed vary depending on the LIMIT/OFFSET values.

Moral: don't use LIMIT/OFFSET without a fully specified sort order.

            regards, tom lane

Tom Lane wrote:
> "ORDER BY id_dept" isn't a unique sort key.  In this example the
> implementation is free to return Meggie and Marge in either order,
> and the ordering can indeed vary depending on the LIMIT/OFFSET values.
>
> Moral: don't use LIMIT/OFFSET without a fully specified sort order.

Thank you, Tom, for your prompt response.

Regards,
Ognjen