SQLでfloat型を使うということ

SQLAP

9章 ラウンディングエラー(丸め誤差)より

floatを使う場合に精度に注意すべきという点については同意です。

SELECT * FROM Accounts WHERE hourly_rate = 59.95;

このため、account_idが123の行hourly_rate列にサイド59.95を割り当てても、リテラル値59.95とは等価になりません。

Accounts.hourly_rateがfloat型のとき、MySQLとは異なりPostgreSQLでは、このクエリは59.95が格納された行を返却する。 これはMySQLでのfloat型が単精度であるのに対して、PostgreSQLのfloat型は倍精度だからだ。単精度浮動小数点型であるreal又はfloat4を使うとMySQLと同様マッチしなくなる。

この時点でもうfloatのことなんて考えたくなくなるところだ。

さてAccounts.hourly_rateに格納した単精度浮動小数点型の59.95とWHERE句に書いたリテラルの59.95が何故一致しないのか。というのはつまり、リテラルの59.95がどのように解釈されているかというとだ。

PostgreSQLでは、小数または指数を含む数値リテラルはnumeric型として解釈される。明示的に単精度浮動小数点型のリテラルを書きたければ、以下の方法がある。

SELECT floa4 '59.95';
SELECT CAST('59.95' AS float4);
SELECT '59.95'::float4;
SELECT 59.95 = float4 '59.95'; -- false
SELECT * FROM Accounts WHERE hourly_rate = float4 '59.95'; -- マッチする

冒頭で書いた、PostgreSQLでfloat型の59.95がリテラル59.95と一致するのは、倍精度浮動小数点型の59.95がnumeric型の59.95とたまたま一致しただけで安心して使って良いということではない。

このあたりMySQLでのリテラルの扱いについてはよくわからなかった。また、CAST関数はあるが、浮動小数点型を指定することはできない。

さて浮動小数点型の扱いに注意すべきなのはSQLにかぎったことではないのだけれども、特にSQLを使う場合に問題になるのは、あるプログラミング言語からSQLの浮動小数点型にアクセスしたとき、その戻り値は外部形式として丸められてしまい、無駄に精度を失ってしまうケースだと思う。

一方内部形式というのはつまりIEEE 754形式のことなんだけど、必死に調べた結果59.95は0x426fcccdになる。これは(1 + 0x6fcccd / 0x800000) * 2 ** (0x42 * 2 - 127)みたいなことですよウフフ。

クライアントとサーバーの間でやりとりされているのが0x426fcccdで、それがその通り何らかのプログラミング言語の浮動小数点型にマップされているのであれば、問題はそのプログラムの中だけの話になる。

クライアントとサーバーの間でやりとりされているのが'59.95'なのか0x426fcccdなのかは、データベースやドライバの実装による。PostgreSQLにはテキストプロトコルとバイナリプロトコルがあるが、どのドライバ側で使われているか、どのように言語の型にマップされているかについては把握していない。