はじめに
とあるアプリケーションのテストをしていた際に、Oracle DatabaseのNUMBER型が不思議な挙動をしたので書き残します。
実行環境
| 項目 | 仕様 |
|---|---|
| ホストOS | macOS Sequoia 15.2 |
| Colima | 0.7.5 |
| Docker | 27.3.1 |
| Oracle Database | 23ai Free 23.0.0.0.0 |
公式で謳われていること
NUMBER型の最大値は $999...(9が38個)×10^{125}$
最小値は $-999...(9が38個)×10^{125}$
サマリー
本記事を読むと、以下のことがわかります。
Oracle Database では 23ai Free 以前のバージョンにおいて桁数、精度を指定しないNUMBER型のカラムについて
- 設定可能な最大値は$999...(9が40個)×10^{86}$であること
- $999...(9が40個)×10^{86}$以上、$999...(9が40個)×10^{86}+4999...(9が85個)$以下の値をINSERTすると
- オーバーフローしない（SQLエラーとならない） こと
- カラムに格納される値は86桁目以降が切り捨てられた値であること
- $999...(9が40個)×10^{86}+4999...(9が85個)$を超える値をINSERTするとオーバーフローする（SQLエラーとなる）こと
以下、ガチャガチャ弄った記録になります。
興味のある方は引き続き読み進めてください。
桁数を定義した場合
テーブルのNUMBER型列で上限桁数を指定したケースです。
この場合は期待通りのINSERTや、桁数を超過する値を投入すると、きちんとオーバーフローのエラーが返ります。
CREATE TABLE number_test_1 (num_col NUMBER(3, 0));
OK例
INSERT INTO number_test_1 VALUES (999);
1行挿入しました。
NG例
INSERT INTO number_test_1 VALUES (1000);
次のコマンド行の開始中にエラーが発生しました : 1 -
INSERT INTO number_test_1 VALUES (1000)
コマンド行 : 1 列 : 35 でのエラー
エラー・レポート -
SQLエラー: ORA-01438: 値1000が、指定された精度(3, 0) (列)より大きくなっています。
01438. 00000 - "value larger than specified precision allowed for this column"
*Cause: When inserting or updating records, a numeric value was entered
that exceeded the precision defined for the column.
*Action: Enter a value that complies with the numeric column's precision,
or use the MODIFY option with the ALTER TABLE command to expand
the precision.
3桁と定義しているのであれば、4桁以上を入力できないのは当たり前ですね。
この場合、怪奇現象は発生しません。
桁数を定義しない場合
怪奇現象が発生しうるケースです。
まずは整数部、小数部の桁数が未指定のカラムを持つ、テーブルを作成します。
CREATE TABLE number_test_2 (num_col NUMBER);
規定されている最大値をINSERTする
INSERT INTO number_test_2 VALUES (9999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
次のコマンド行の開始中にエラーが発生しました : 1 -
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999990000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000)
コマンド行 : 1 列 : 35 でのエラー
エラー・レポート -
SQLエラー: ORA-01426: 数値オーバーフローが発生しました
01426. 00000 - "numeric overflow"
*Cause: Evaluation of an value expression causes an overflow/underflow.
*Action: Reduce the operands.
怒られました。クエリを以下に変えても同じ結果に。
INSERT INTO number_test_2 VALUES (99999999999999999999999999999999999999e+125);
ところが、以下のクエリはきちんとINSERTできます。
INSERT INTO number_test_2 VALUES (9.99e+125);
どうやらOracle Database 23ai freeでは、NUMBER型の最大値は1世代前の21cと同一になっている模様。
よって、以下のクエリではエラーが返ります。
INSERT INTO number_test_2 VALUES (1.0e+126);
次のコマンド行の開始中にエラーが発生しました : 1 -
INSERT INTO number_test_2 VALUES (1.0e+126)
コマンド行 : 1 列 : 35 でのエラー
エラー・レポート -
SQLエラー: ORA-01426: 数値オーバーフローが発生しました
01426. 00000 - "numeric overflow"
*Cause: Evaluation of an value expression causes an overflow/underflow.
*Action: Reduce the operands.
いたずらをする
NUMBERの限界値を挿入して、結果を出力してみます。
ここでようやく本題の「怪」が発生します。
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
次のコマンド行の開始中にエラーが発生しました : 1 -
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999)
コマンド行 : 1 列 : 35 でのエラー
エラー・レポート -
SQLエラー: ORA-01426: 数値オーバーフローが発生しました
01426. 00000 - "numeric overflow"
*Cause: Evaluation of an value expression causes an overflow/underflow.
*Action: Reduce the operands.
1.0e+126未満なのにオーバーフローした！
ではどこまでの値ならINSERTできるのか、人力二分探索木で調べてみることに。
調べた結果
-- INSERTが正常終了する上限値
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999949999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
-- INSERTがオーバーフローする閾値
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
数値が「999999999999999999999999999999999999999949999999999999999999999999999999999999999999999999999999999999999999999999999999999999」以下であれば、INSERTが正常終了します。
それを超過するとオーバーフローが発生します。
ただ、INSERTできたからといってDBに格納された値が正しくないと意味ないですよね。
なので、実際に登録されたデータも確認してみましょう。
set numformat 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999949999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
SELECT num_col FROM number_test_2;
1行挿入しました。
NUM_COL
--------------------------------------------------------------------------------------------------------------------------------
999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000
指示した値がきちんと格納されていない！
返された値に1を加算した以下のクエリでも、同じ結果が返ります。
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000001);
まとめ
Oracle Database 23ai Free以前のバージョンでは、NUMBERの最大値は$999...(9が40個)×10^{86}$でした。
最小値も符号を反転させた値です。
やたらめったら大きな値を登録しうる場合、この点を意識してアプリを構築しましょう。（実際必要になるケースがどれほどあるかはさておき）
おまけ
その1 プログラミング言語ごとの10進数型最大値
| 言語 | 型 | 最大値 |
|---|---|---|
| C# | decimal | 79228162514264337593543950335 |
| Java | BigDecimal | 2793926648桁くらい |
| Python | Decimal | 制限なし |
| Ruby | bigdecimal | 制限なし |
GoやPHP、Rust、TypeScriptは標準で厳密な数値計算するクラスが用意されていないので割愛。
ほとんど全ての言語で、1.0e+126以上の数値を扱えます。
DB登録時にオーバーフローさせないように、fail-fastな設計が必要になります。
参考にさせて頂いた記事・サイト
その2 本事例以外でおかしな挙動をする小数値
下記小数値を持つ値でも、INSERT文はエラーを返さずに、指定した値もカラムに設定されません。
-- エラーにならないが端数が切り捨てられる
INSERT INTO number_test_2 VALUES (1.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001);
-- エラーにならないが、NULLが挿入される
INSERT INTO number_test_2 VALUES (0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001);