LIMITの限界に挑戦 - HHeLiBeXの日記正道編

以下のエントリで、各構文のサポート状況について書いた。

指定した範囲の行を取得するクエリ - HHeLiBeXの日記正道編

この中のLIMIT/OFFSETで不思議な挙動を示したものがあったり、どこまでいけるんだろうと思ったりしたので、調子に乗って(謎)、LIMITに指定できる数値の限界などを調べてみた。
なお、PostgreSQLの不思議な挙動に関しては、同エントリへのコメントとしていただいた情報(バグ報告)を参考にしている。

BUG #6139: LIMIT doesn't return correct result when the value is huge

併せていただいた「式も書ける」に関しては別の機会に試してみる予定(予定は未定、未定は(以下略(謎)))。

検証に使用しているDBMSは最新ではない可能性があり、バグとして既に修正済みのものがあるかもしれないが、そこはまぁそういうことで(謎)。

さて、以下のクエリを実行してみた。なお、指定しているでっかい数値は、「符号付き32ビット整数の最大値」「符号付き64ビット整数の最大値」「符号なし64ビット整数の最大値」「1000穣(1000*10^28)」「符号付き128ビット整数の最大値」に足したり引いたり、という数値になっている。

-- (1)
SELECT str FROM tbl1 ORDER BY str LIMIT 2147483646 OFFSET 1
-- (2)
SELECT str FROM tbl1 ORDER BY str LIMIT 2147483647 OFFSET 0
-- (3)
SELECT str FROM tbl1 ORDER BY str LIMIT 2147483646 OFFSET 2
-- (4)
SELECT str FROM tbl1 ORDER BY str LIMIT 2147483647 OFFSET 1
-- (5)
SELECT str FROM tbl1 ORDER BY str LIMIT 2147483647 OFFSET 2
-- (6)
SELECT str FROM tbl1 ORDER BY str LIMIT 2147483648 OFFSET 0
-- (7)
SELECT str FROM tbl1 ORDER BY str LIMIT 2147483648 OFFSET 2
-- (8)
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775806 OFFSET 1
-- (9)
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775807 OFFSET 0
-- (10)
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775806 OFFSET 2
-- (11)
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775807 OFFSET 1
-- (12)
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775807 OFFSET 2
-- (13)
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775808 OFFSET 0
-- (14)
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775808 OFFSET 2
-- (15)
SELECT str FROM tbl1 ORDER BY str LIMIT 18446744073709551615 OFFSET 0
-- (16)
SELECT str FROM tbl1 ORDER BY str LIMIT 18446744073709551616 OFFSET 0
-- (17)
SELECT str FROM tbl1 ORDER BY str LIMIT 9999999999999999999999999999999 OFFSET 0
-- (18)
SELECT str FROM tbl1 ORDER BY str LIMIT 10000000000000000000000000000000 OFFSET 0
-- (19)
SELECT str FROM tbl1 ORDER BY str LIMIT 170141183460469231731687303715884105727 OFFSET 0
-- (20)
SELECT str FROM tbl1 ORDER BY str LIMIT 170141183460469231731687303715884105728 OFFSET 0
-- (21)
SELECT str FROM tbl1 ORDER BY str LIMIT ALL OFFSET 6

結果は以下の通り(横に入りきらないので、2つに分割)。

DBMS名	(1)	(2)	(3)	(4)	(5)	(6)	(7)	(8)	(9)	(10)	(11)	(12)
DB2 9.7	○	○	×	×	×	○	○	○	○	×	×	×
PostgreSQL 8.0	○	○	(*1)	(*1)	(*1)	×	×	×	×	×	×	×
PostgreSQL 8.1	○	○	(*1)	(*1)	(*1)	×	×	×	×	×	×	×
PostgreSQL 8.2	○	○	○	○	○	○	○	○	○	(*1)	(*1)	(*1)
PostgreSQL 8.3	○	○	○	○	○	○	○	○	○	(*1)	(*1)	(*1)
PostgreSQL 8.4	○	○	○	○	○	○	○	○	○	(*1)	(*1)	(*1)
PostgreSQL 9.0	○	○	○	○	○	○	○	○	○	(*1)	(*1)	(*1)
MySQL 4.1	○	○	○	○	○	○	○	○	○	○	○	○
MySQL 5.0	○	○	○	○	○	○	○	○	○	○	○	○
MySQL 5.1	○	○	○	○	○	○	○	○	○	○	○	○
MySQL 5.5	○	○	○	○	○	○	○	○	○	○	○	○
SQLite 3.6.14.2	○	○	○	○	○	○	○	○	○	○	○	○
H2 1.2	○	○	○	○	○	×	×	×	×	×	×	×
H2 1.3	○	○	○	○	○	×	×	×	×	×	×	×
HSQLDB 1.8.1	○	○	○	○	○	×	×	×	×	×	×	×

DBMS名	(13)	(14)	(15)	(16)	(17)	(18)	(19)	(20)	(21)
DB2 9.7	○	○	○	○	○	×	×	×	×
PostgreSQL 8.0	×	×	×	×	×	×	×	×	○
PostgreSQL 8.1	×	×	×	×	×	×	×	×	○
PostgreSQL 8.2	×	×	×	×	×	×	×	×	○
PostgreSQL 8.3	×	×	×	×	×	×	×	×	○
PostgreSQL 8.4	×	×	×	×	×	×	×	×	○
PostgreSQL 9.0	×	×	×	×	×	×	×	×	○
MySQL 4.1	(*2)	(*2)	○	○	○	○	○	○	×
MySQL 5.0	○	○	○	×	×	×	×	×	×
MySQL 5.1	○	○	○	×	×	×	×	×	×
MySQL 5.5	○	○	○	×	×	×	×	×	×
SQLite 3.6.14.2	×	×	×	×	×	×	×	×	×
H2 1.2	×	×	×	×	×	×	×	×	×
H2 1.3	×	×	×	×	×	×	×	×	×
HSQLDB 1.8.1	×	×	×	×	×	×	×	×	×

(*1)：クエリエラーにはならないが、結果がおかしい。
(*2)：クエリエラーにはならないが、結果が0件。

DB2

(3)〜(5)と(10)〜(12)で×になっているが、この時のSQLエラーが「SQLCODE=-802, SQLSTATE=22003」、つまり「数値が範囲を超えています。」というのだが、それにしてはそれより大きい数値でも通っているものもある。
で、ここからは推測なのだが、LIMIT(おそらくOFFSETも)に指定された数値によって、32ビット符号付き整数なのか64ビット符号付き整数なのかそれ以上なのかが判別され、それにしたがって行数(行番号と言ったほうが正しいか)の上限が決定される。その上でLIMITとOFFSETの合計がその上限を超えるとエラーになっているのではないかと。
バグの可能性もあるが、v9.7.4でどうなっているか確認してみたいところ。
ちなみに、(17)以降は「SQLCODE=-802, SQLSTATE=42820」、つまり「数値定数が長すぎるか、またはそのデータ・タイプの範囲内にない値を持っています。」

PostgreSQL

冒頭で書いたバグ報告にあるとおり、LIMITとOFFSETの合計がある上限を超えた場合に変な結果が返ってきているらしい。
ある上限というのは、v8.0とv8.1では32ビット符号付き整数の最大値、v8.2以降では64ビット符号付き整数の最大値。これは、エラーメッセージからも推測できる。

v8.0, v8.1

ERROR: integer out of range

v8.2以降

ERROR: bigint out of range

また、唯一「LIMIT ALL」をサポートする。

MySQL

v4.1の挙動以外は特段問題はなく、指定可能な最大値が「符号なし64ビット整数」の最大値なのだろう。
v4.1の挙動をもう少し詳しく調べてみたが、どうもバグっぽい。

-- 0件
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775808 OFFSET 0
-- 1件
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775809 OFFSET 0
-- 2件
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775810 OFFSET 0
-- 3件
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775811 OFFSET 0
-- 4件
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775812 OFFSET 0
-- 5件
SELECT str FROM tbl1 ORDER BY str LIMIT 9223372036854775813 OFFSET 0