PostgreSQL, MySQLで重複したデータがあるカラムでorder byするときの注意点

業務でソートのキーとlimitとoffsetを指定できるList APIで,offset間でデータが重複するバグがありそのときに知ったRDBのorder by句の注意点についてまとめる。

検証

PostgreSQLにテーブルとデータを準備する。

create table test
(
    id integer primary key,
    val integer
);

INSERT INTO test (id, val) VALUES
    (1, 1),
    (2, 1),
    (3, 1),
    (4, 1),
    (5, 1),
    (6, 1),
    (7, 1),
    (8, 1),
    (9, 1),
    (10, 1);

そして以下のクエリを実行する。

select * from test order by val limit 2 offset 0;
select * from test order by val limit 2 offset 2;
select * from test order by val limit 2 offset 4;
select * from test order by val limit 2 offset 6;
select * from test order by val limit 2 offset 8;

すると以下のような結果になる。 id = 1の行がoffset 0とoffset 2に重複し、id = 3の行が取れていない。

select * from test order by val limit 2 offset 0;
 id | val 
----+-----
  2 |   1
  1 |   1
(2 rows)

select * from test order by val limit 2 offset 2;
 id | val 
----+-----
  4 |   1
  1 |   1
(2 rows)

select * from test order by val limit 2 offset 4;
 id | val 
----+-----
  5 |   1
  6 |   1
(2 rows)

select * from test order by val limit 2 offset 6;
 id | val 
----+-----
  7 |   1
  8 |   1
(2 rows)

select * from test order by val limit 2 offset 8;
 id | val 
----+-----
  9 |   1
 10 |   1
(2 rows)