DBに指数表記の数値をロードしようとしたときの挙動

# 1. はじめに

通常の RDBMS では ```1.2e3``` (= 1200)といった指数表記の数値リテラルを扱うことができます。ただし、DB の種類によって許容する指数表記や CSV ファイルからロードしようとしたときの挙動は異なります。今回は Oracle / MySQL / PostgreSQL / SQL Server の4種類の DB で指数表記の数値データを以下のような CSV ファイルからテーブルにロードした時の挙動をまとめておきたいと思います。

| id | value |
|:-:|:-:|
| 1  | e  |
| 2  | 1e  |
| 3  | e3  |
| 4  | 1e3  |
| 5  | 1000  |

（id = 5 の行は当然正しくロードできるはずですが、動作確認のために加えています）

なぜこんな確認をしているかというと、数値列にデータをロードする場合は当然数値フォーマットチェックは行われると思いがちですが、実際には指数表現と解釈可能な文字列が来るとエラーが発生せずにロードできてしまうケースがあるので怖いねという話から来ています。

ちなみに、本記事には含めませんが某クラウドデータベースは全てのケースがロードされます。（```e``` が数値 0 としてロードできるとか、マジ勘弁してほしい）

# 2. 実際の挙動

今回は以下の DB を用いています。

- Oracle Database 21c 21.0.0.0.0
- MySQL 8.0.27
- PostgreSQL 14.1
- SQL Server 2019

（すべて Oracle Linux 8.5 x86_64 上で動作させています）

また、ロード先のテーブル ```test_tbl``` は以下で作成しています。

```sql
create table test_tbl (
    id int primary key,
    value int
)
```

（本題には関係ないですが、Oracle で int 型って利用可能なんですね）

## 2-1. Oracle

以下のようなカンマ区切りのファイル test.csv を用意します。

```test.csv
1,e
2,1e
3,e3
4,1e3
5,1000
```

これを以下のコマンドでロードします。（Oracle のデータロードは外部表を使うのが推奨ですが、手間を省きたかったので今回は SQL*Loader のエクスプレスモードを利用しています）

```
$ sqlldr userid=dbuser01/*****@localhost:1521/pdb01 data=/tmp/test.csv table=test_tbl
```

結果は以下になります。

```sql
SQL> select * from test_tbl;

        ID      VALUE
---------- ----------
         4       1000
         5       1000
```

【2/16 追記】

制御ファイルで以下のようにフォーマット指定の ```to_number``` 関数による変換を行えば ```1e3``` などの指数表記もエラーにすることができます（桁数は適用）。

```
load data
infile '/tmp/test.csv'
truncate into table test_tbl
fields terminated by ","
(
    id char "to_number(:id, '999999')",
    value char "to_number(:value, '999999')"
)
```

## 2-2. MySQL

MySQL のデータロードコマンド ```load data infile``` はエラーが発生すると処理をそこで打ち切るため、CSV ファイルも 1 行ずつ 5 ファイルに分割しておきます（今回はタブ区切り）。

```
$ cat /var/lib/mysql-files/test0[1-5].csv
1       e
2       1e
3       e3
4       1e3
5       1000
```

5 個の CSV ファイルをそれぞれロードします。

```sql
mysql> load data infile '/var/lib/mysql-files/test01.csv' into table test_tbl;
ERROR 1366 (HY000): Incorrect integer value: 'e' for column 'value' at row 1

mysql> load data infile '/var/lib/mysql-files/test02.csv' into table test_tbl;
Query OK, 1 row affected (0.06 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile '/var/lib/mysql-files/test03.csv' into table test_tbl;
ERROR 1366 (HY000): Incorrect integer value: 'e3' for column 'value' at row 1

mysql> load data infile '/var/lib/mysql-files/test04.csv' into table test_tbl;
Query OK, 1 row affected (0.11 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile '/var/lib/mysql-files/test05.csv' into table test_tbl;
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
```

結果は以下になります。

```sql
mysql> select * from test_tbl;
+----+-------+
| id | value |
+----+-------+
|  2 |     1 |
|  4 |  1000 |
|  5 |  1000 |
+----+-------+
```

## 2-3. PostgreSQL

PostgreSQL のロードコマンド ```copy``` も MySQL 同様、エラーが発生した時点で処理が打ち切られるので、ファイル分割しておきます。

```
$ cat /tmp/test0[1-5].csv
1       e
2       1e
3       e3
4       1e3
5       1000
```


5 個の CSV ファイルをそれぞれロードします。

```sql
mydb=> copy test_tbl from '/tmp/test01.csv';
ERROR:  invalid input syntax for type integer: "e"
CONTEXT:  COPY test_tbl, line 1, column value: "e"

mydb=> copy test_tbl from '/tmp/test02.csv';
ERROR:  invalid input syntax for type integer: "1e"
CONTEXT:  COPY test_tbl, line 1, column value: "1e"

mydb=> copy test_tbl from '/tmp/test03.csv';
ERROR:  invalid input syntax for type integer: "e3"
CONTEXT:  COPY test_tbl, line 1, column value: "e3"

mydb=> copy test_tbl from '/tmp/test04.csv';
ERROR:  invalid input syntax for type integer: "1e3"
CONTEXT:  COPY test_tbl, line 1, column value: "1e3"

mydb=> copy test_tbl from '/tmp/test05.csv';
COPY 1
```

結果は以下になります。

```sql
mydb=> select * from test_tbl;
 id | value 
----+-------
  5 |  1000
(1 行)
```

## 2-4. SQL Server

以下のようなタブ区切りのファイル test.csv を用意します。

```
$ cat /tmp/test.csv
1       e
2       1e
3       e3
4       1e3
5       1000
```

このファイルを以下のコマンドでロードします。

```sql
bulk insert test_tbl from '/tmp/test.csv'
with (
	format = 'CSV',
	fieldterminator = '\t',
	rowterminator = '0x0A'
)
```

結果は以下になります。

```sql
id	value
5	1000
```

# 3. まとめ

上の結果をまとめると以下の表の通りになります。

| DBMS | e | 1e | e3 | 1e3 | 1000 |
|:-:|:-:|:-:|:-:|:-:|:-:|
| Oracle  | ×  | ×  | ×  | ○  | ○  |
| MySQL  | ×  | ○ | ×  |  ○ |○  |
| PostgreSQL  | ×  |  × | ×  | ×  | ○  |
| SQL Server  | ×  |  × | ×  |  × |  ○ |

- 個人的には指数表記はデフォルトでロードできない PostgreSQL / SQL Server の挙動が好きです。
- Oracle ではちゃんと制御ファイルを書けば ```1e3``` をエラーにできるかもしれません。（データ型に EXTERNAL 指定を試してみましたが挙動は変わりませんでした）。
    - 【2/16 追記】```to_number``` による変換を組み合わせれば、 ```1e3```もエラーにすることができます。
- MySQL は ```1e``` をロードできていますが、数値リテラルとして ```1e``` は許容しません。（若干謎仕様）
- PostgreSQL は指数表記のデータをデフォルトではロードできませんが、数値リテラルとしては ``` 1e``` と ```1e3``` は許容し、```e3``` は許容しません。（MySQL よりロードは厳しいのに、数値リテラルとしては緩い。ちょっと不思議）

ちなみに、某クラウドデータベースは全てのケースがロードされます。（```e``` が数値 0 としてロードできるとか、マジ勘弁してほしい）（マジ勘弁してほしいのでもう一度書く）