MySQL5.1以降の小数の扱いがキモい

穏やかな昼下がりにMySQLで小数の足し算をしていたところ、不思議な現象を見つけました。

mysql> select 0.8=0.7+0.1;
+-------------+
| 0.8=0.7+0.1 |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select 0.8=0.7+0.1e0;
+---------------+
| 0.8=0.7+0.1e0 |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> select 0.8=0.7+'0.1';
+---------------+
| 0.8=0.7+'0.1' |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

0.7と0.1を足す際に、0.1を指数表記にしたり、文字列にしてMySQLの自動キャストに委ねたりしてみました。比較演算子の結果は真偽値ですので、最初の例は等しいけれども2番目3番目は等しくないというわけです。

0.1e0や'0.1'を単体で比較すれば下記の通り0.1とイコールですから、不思議な結果に思えます。

mysql> select 0.1=0.1e0;
+-----------+
| 0.1=0.1e0 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select 0.1='0.1';
+-----------+
| 0.1='0.1' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

実は、この挙動はバグなどではなく、MySQLマニュアルにも書いてあるものです。

MySQL5.1以降で小数を扱う方法は2種類あります。1つは浮動小数点数で、DBのカラム型で言うとFLOAT型やDOUBLE型に対応するものです。

これに加え、MySQL5.1からは固定小数点数*1の演算が導入されました。これはDECIMAL型に対応するもので、多倍長整数演算を用いて固定小数点数を実装しているようです。（参照：「MySQL :: MySQL 5.1 リファレンスマニュアル :: 22 精密計算」）

上記の例は、これら2種類の小数の扱いの違いからくるものです。以下で詳しく見ていきましょう。

小数リテラルの扱いの違い

MySQL5.1以降、「0.1」のようなリテラルは正確な数とみなされ、固定小数点数として扱われるようになりました。固定小数点数同士の演算は誤差が入らないため、0.1を10回加えると1.0と等しくなります。

mysql> select 0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1=1;
+-------------------------------------------+
| 0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1=1 |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

一方で、0.1e0のような指数表記のリテラルは不正確な数とみなされ、倍精度浮動小数点数として扱われます。これは10回足しても1になりません。

mysql> select 0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0=1;
+---------------------------------------------------------------+
| 0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0+0.1e0=1 |
+---------------------------------------------------------------+
|                                                             0 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

こうした演算では、固定小数点数同士の場合のみ固定小数点数のまま演算が行われ、固定小数点数と浮動小数点数の演算では浮動小数点数へのキャストが起こります。最初の例の1番目と2番目の違いはこのためです。

また、文字列から小数へキャストする場合には浮動小数点数になります。最初の例の1番目と3番目は一見同じ結果になりそうですが、自動キャストが起こって型が変わってしまい、異なる結果になっています。

まとめ

0.1と0.1e0の扱いが異なるプログラミング言語は無いと思うので、本稿の内容は多くの人にとって意外な内容ではないでしょうか。

また、プログラミング言語から変数値をSQLに挿入してSQLを組み立てる場合に、型を考慮せず一律でSQLの文字列としてしまうことは珍しくないと思います*2。しかし、MySQL5.1以降で小数を扱う場合にはクォーティングの有無で扱いが変わるため、そうした実装を利用する場合は注意が必要です。

個人的には、プログラミング言語やデータベースで小数を扱うこと自体が要注意だと思いますけどね。

*1:MySQLマニュアル英語版ではFixed-PointとかExact Valueなどと書かれています

*2:O/Rマッパーにありがちですが、実装上の手抜きであり、悪い習慣だと感じます。カラムの型に合わせてクォーティングの有無を決めるのがベストの実装ではないでしょうか。一方で、自作のSQLビルダーなどで安易にクォートを外すとSQLインジェクションの原因になりかねないので、実力や時間的余裕によっては手抜きの方がマシな状況があるかもしれません。