【OracleDB】NUMBER型の怪

# はじめに
とあるアプリケーションのテストをしていた際に、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}$

https://docs.oracle.com/cd/G11854_01/refrn/datatype-limits.html

# サマリー
本記事を読むと、以下のことがわかります。
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や、桁数を超過する値を投入すると、きちんとオーバーフローのエラーが返ります。
```sql:DDL-1
CREATE TABLE number_test_1 (num_col NUMBER(3, 0));
```

## OK例
```sql:Query-1
INSERT INTO number_test_1 VALUES (999);
```
```plaintext:Result-1
1行挿入しました。
```

## NG例
```sql:Query-2
INSERT INTO number_test_1 VALUES (1000);
```
```plaintext:Result-2
次のコマンド行の開始中にエラーが発生しました : 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桁以上を入力できないのは当たり前ですね。
この場合、怪奇現象は発生しません。

# 桁数を定義しない場合
**怪奇現象が発生しうるケースです。**
まずは整数部、小数部の桁数が未指定のカラムを持つ、テーブルを作成します。

```sql:DDL-2
CREATE TABLE number_test_2 (num_col NUMBER);
```

## 規定されている最大値をINSERTする
```sql:Query-3
INSERT INTO number_test_2 VALUES (9999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
```
```plaintext:Result-3
次のコマンド行の開始中にエラーが発生しました : 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.
```
怒られました。クエリを以下に変えても同じ結果に。
```sql:Query-4
INSERT INTO number_test_2 VALUES (99999999999999999999999999999999999999e+125);
```
ところが、以下のクエリはきちんとINSERTできます。
```sql:Query-5
INSERT INTO number_test_2 VALUES (9.99e+125);
```
どうやらOracle Database 23ai freeでは、NUMBER型の最大値は1世代前の21cと同一になっている模様。

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483

よって、以下のクエリではエラーが返ります。
```sql:Query-6
INSERT INTO number_test_2 VALUES (1.0e+126);
```
```plaintext:Result-6
次のコマンド行の開始中にエラーが発生しました : 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の限界値を挿入して、結果を出力してみます。
ここでようやく本題の「怪」が発生します。
```sql:Query-7
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
```
```plaintext:Result-7
次のコマンド行の開始中にエラーが発生しました : 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できるのか、人力二分探索木で調べてみることに。

## 調べた結果
```sql:Query-8
-- INSERTが正常終了する上限値
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999949999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
-- INSERTがオーバーフローする閾値
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
```

数値が「9999999999999999999999999999999999999999**4**9999999999999999999999999999999999999999999999999999999999999999999999999999999999999」以下であれば、INSERTが正常終了します。
それを超過するとオーバーフローが発生します。

ただ、INSERTできたからといってDBに格納された値が正しくないと意味ないですよね。
なので、実際に登録されたデータも確認してみましょう。

```sql:Query-9
set numformat 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
INSERT INTO number_test_2 VALUES (999999999999999999999999999999999999999949999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
SELECT num_col FROM number_test_2;
```
```plaintext:Result-9
1行挿入しました。
                                                                                                                         NUM_COL
--------------------------------------------------------------------------------------------------------------------------------
  999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000

```

指示した値がきちんと格納されていない！
返された値に1を加算した以下のクエリでも、同じ結果が返ります。

```sql:Query-10
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な設計](https://amzn.asia/d/dgS4HBt)が必要になります。

### 参考にさせて頂いた記事・サイト

https://qiita.com/qfoqfo/items/834bba763a610ed19821

https://kidotaka.hatenablog.com/entry/2012/11/06/104655



## その2 本事例以外でおかしな挙動をする小数値
下記小数値を持つ値でも、INSERT文はエラーを返さずに、指定した値もカラムに設定されません。
```sql:Query-11
-- エラーにならないが端数が切り捨てられる
INSERT INTO number_test_2 VALUES (1.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001);
-- エラーにならないが、NULLが挿入される
INSERT INTO number_test_2 VALUES (0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001);
```