From: | sszabo(at)bigpanda(dot)com |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Limit and Order by stuff |
Date: | 2000-02-14 15:55:14 |
Message-ID: | 200002141555.KAA10767@homeworld.bigpanda.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Actually, even currently, limit and order a non unique
order by can skip results if the table is being modified.
Even if no new rows are entered, as long as a row
on the border of the limit has been modified, you can
get indeterminate results.
acroyear=> create table test1 (a int, b varchar(10), c int);
CREATE
acroyear=> insert into test1 values (1, 'a', 1);
INSERT 748222 1
acroyear=> insert into test1 values (2, 'a', 1);
INSERT 748223 1
acroyear=> insert into test1 values (3, 'a', 1);
INSERT 748224 1
acroyear=> insert into test1 values (4, 'a', 1);
INSERT 748225 1
acroyear=> insert into test1 values (4, 'b', 2);
INSERT 748226 1
acroyear=> insert into test1 values (5, 'a', 1);
INSERT 748227 1
acroyear=> insert into test1 values (6, 'a', 1);
INSERT 748228 1
acroyear=> insert into test1 values (7, 'a', 1);
INSERT 748229 1
acroyear=> select a,b from test1 order by a limit 4;
a|b
-+-
1|a
2|a
3|a
4|a
(4 rows)
acroyear=> update test1 set c=3 where a=4 and b='a';
UPDATE 1
acroyear=> select a,b from test1 order by a offset 4 limit 4;
a|b
-+-
4|a
5|a
6|a
7|a
(4 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-02-14 16:00:16 | Re: [HACKERS] Another nasty cache problem |
Previous Message | Don Baccus | 2000-02-14 15:14:01 | Re: [HACKERS] Solution for LIMIT cost estimation |