2018.03.22

ORDER BYとLIMIT, OFFSETの組み合わせには注意しよう

shimo

こんにちは、下條です。今日はSQLの軽い (しかし重要な) 話題について書いてみようと思います。

まずは以下の通りユニークでない col カラムを含む test テーブルを作成し、データを投入するSQLをご覧ください。 (MySQLでの例です。)

create table test(id INT, col INT);

insert into test values(1,1);
insert into test values(2,1);
insert into test values(10,1);
insert into test values(3,1);

そして、以下の2つのSQLを実行した場合、結果はどうなるでしょうか？ここで何が言いたいかが分かる方はこの先は読まなくてかまいません。

select * from test order by col limit 2 offset 0;
select * from test order by col limit 2 offset 2;

上記はほぼほぼ　https://bugs.mysql.com/bug.php?id=69732　より持ってきたSQLなのですが、実機検証したところ重複したレコードが取得される結果となりました。

mysql> select * from test order by col limit 2 offset 0;
+------+------+
| id   | col  |
+------+------+
|    3 |    1 |
|    2 |    1 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from test order by col limit 2 offset 2;
+------+------+
| id   | col  |
+------+------+
|   10 |    1 |
|    3 |    1 |
+------+------+
2 rows in set (0.00 sec)

これは人にとっては意図しない結果と思われるかもしれません。実際、先ほどのバグレポートでもこれはバグではないかとコメントしている方もいらっしゃいます。

しかし、ユニークではないカラムのオーダー順序は不定となります。先ほどのバグレポートの以下のコメントである通り、

[12 Jul 2013 15:50] Hartmut Holzgraefe
Without a distinct ORDER BY the result order is undefined. Period.

Even if the same query, executed twice, results results in different order there is nothing wrong with that. You may be used to what looks like deterministic results but that is actually an illusion. It may be what you see in the usual case but there is nothing that guarantees this. Result order can change as statistics get updated, as index trees are reshuffled, as data is partitioned across different physical machines so that result order depends on network latencies ...

So if you want pagination then make sure you ORDER BY on something guaranteed to be UNIQUE, or live with the fact that the sort order of identical values in a non-unique sequence is not deterministic and can change at any time without prior notice.

Your example is equivalent to

SELECT * FROM table ORDER BY 'x';

( not the same as "ORDER BY 1" as that would be 'order by the first result column )

and almost equivalent to

SELECT * FROM table ORDER BY RAND();

and you wouldn't expect that to return the same row order every time either, would you?

ユニークでないカラムのORDER BYでは、結果の順序は不定というのがMySQLの仕様です。
引用例に書いてあるように、ユニークでないカラム x に対して