Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: lirex(dot)software(at)gmail(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #7499: wrong data sorting if I use "...limit 1..." SQL clause along with "...order by ..."
Date: 2012-08-20 03:41:44
Message-ID: 5048.1345434104@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
lirex(dot)software(at)gmail(dot)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


In response to

pgsql-bugs by date

Next:From: maxim.bogukDate: 2012-08-21 08:08:11
Subject: BUG #7500: hot-standby replica crash after an initial rsync
Previous:From: lirex.softwareDate: 2012-08-19 18:46:55
Subject: BUG #7499: wrong data sorting if I use "...limit 1..." SQL clausealong with "...order by ..."

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group