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 は以下で作成しています。

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> 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 ファイルをそれぞれロードします。

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

結果は以下になります。

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 ファイルをそれぞれロードします。

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

結果は以下になります。

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

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

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

結果は以下になります。

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 としてロードできるとか、マジ勘弁してほしい）（マジ勘弁してほしいのでもう一度書く）