Edited at

PostgreSQLの不思議なNaNの扱い ~ PostgreSQL is Chuck Norris

More than 3 years have passed since last update.

Привет. 今回はPostgreSQLでのNaNの挙動がユニークだという話です。チャックノリスは0除算ができる事で有名ですが、ポスグレも負けていません。


TL;DR

PostgreSQLでは…

- NaN同士を比較するとTrueになる

- $-\infty < 0 < +\infty < \pm NaN$

- 浮動小数点数の0除算はできない。ついでに負数の平方根も生成不可


Intro: PostgreSQLの不思議なNaNの扱い

Postgresのドキュメントを読んでいると、こんな言葉が目に飛び込んできました。バージョンは9.4.2


Note: IEEE754 specifies that NaN should not compare equal to any other floating-point value (including NaN). In order to allow floating-point values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.


要約すると、NaN == NaN -> Trueを許容してしまえばtree-based indexが張りやすいので、うちではNaNをNaNと比較するとになるよ、IEEE 754のNaN実装とは違ってね。あと、NaNはそれ以外の数値より常に大きいと判定されるよ。だそうです。

tree-based indexとはRDBMSのインデックス実装でよく使われる、B-treeインデックスなどの事を指します。またIEEE 754は浮動小数点数に関する標準です。ほとんど(たぶん)のプログラミング言語処理系やOSやプロセッサなどがこれに準拠していますが、SQL標準はこれに準拠していません。1


検証

まず単純にWHEREで直接NaNを同値比較して引っこ抜けるのか検証。PostgreSQLの数値型の中で、NaNを格納できるのは


  • decimal

  • numeric

  • real

  • double precision

の4つ2です。今回はdouble precisionを利用します。

CREATE TABLE example (

id INTEGER,
val DOUBLE PRECISION,
PRIMARY KEY (id)
);

INSERT INTO example (id, val) VALUES (1, 'NaN');

SELECT * FROM example;

id
val

1
NaN

んで、

SELECT * FROM example WHERE val = 'NaN';

id
val

1
NaN

'NaN'と書いているので文字列に見えますが、カラムの型をdouble precision にしているので、NaNになります。試しに小文字で'nan'としても結果が同じになります。

SELECT * FROM example WHERE val = 'nan';

id
val

1
NaN

直接の同値比較では抜けるみたいですね。では、SQL中で生成したNaNで比較してみます。

-- (0/0)でNaNを生成する

SELECT * FROM example WHERE val = (0 / 0);
ERROR: division by zero

-- あぁ、Integerになってるからか。
-- ではrealにキャストする。
SELECT * FROM example WHERE val = (
CAST(0 AS REAL) / CAST(0 AS REAL)
);
ERROR: division by zero

あるぇ。0除算はできない3みたいですね。チャックノリスはやはり無二の存在だったようです。しょうがないので $0 \times \infty$ を利用してNaNを生成してみます。4

SELECT * FROM example WHERE val = (

CAST(0 AS REAL) * '+Infinity'
);

id
val

1
NaN

あい。生成したNaNをWHEREに使用して引っこ抜けました。ドキュメントは正しかったようです(あたりまえ


IEEE 754

ドキュメントが引き合いに出しているIEEE 754でのNaNですが、Wikipediaの記事が日本語版も詳しいです。面白いのでぜひ読んでください。5

で軽く要約しますが、NaNは∞などと同様に、浮動小数点数の演算用途に開発されました。なので整数演算では基本6出現しません。7

NaNにはQuiet NaN (qNaN)Signaling NaN (sNaN)があって、qNaNはNaNが発生したことを伝播するだけのものです。sNaNは例外8を投げた後、qNaNに変化します。

NaNの発生条件は幾つか存在しますが、要は実数と±∞で表現できない数値を生成した時発生します。詳しくはWikipediaを参照してください。

また0除算の記事も非常に面白いので、オススメです。これを読めば、1.0 / 0.0+∞になり、-1.0 / 0.0-∞になり、0.0 / 0.0NaNになる理由がわかると思います。


totalOrder

IEEE 754では、本来比較しても同値と判定される値同士の大小についても定義されています。例えば+0-0の比較などです。通常の比較演算では同値と判定されますが、totalOrderを利用すると、$-0 < +0$と判定されます。定義対象には±∞±NaNも含まれます。

細かい部分は端折りますが、以下のような感じです。

$$

-NaN < -\infty < -real < -0 < +0 < real < +\infty < +NaN

$$

NaN自体には正負の概念は無いっぽい(自信なし)ですが、totalOrderの際は符号ビットも考慮されるようです。


∞ v.s. NaN

totalOrderを解説したところで、次の問題である果たしてPostgreSQLでは$-∞ < +∞ < \pm NaN$になるのか?を検証します。

∞を挿入できるのは浮動小数点数の型だけです。なので今回も double precisionを利用します。

CREATE TABLE example2 (

id INTEGER,
val DOUBLE PRECISION,
PRIMARY KEY (id)
);

-- 小さい順に挿入
INSERT INTO example2 (id, val) VALUES (1, '-NaN');
INSERT INTO example2 (id, val) VALUES (2, '-Infinity');
INSERT INTO example2 (id, val) VALUES (3, '+Infinity');
INSERT INTO example2 (id, val) VALUES (4, '+NaN');

SELECT * FROM example2;

id
val

1
NaN

2
-Infinity

3
Infinity

4
NaN

-NaNの符号が表示されませんね。この時点でNaNの符号が消えて格納されたのかな?ソートしてみます。

SELECT * FROM example2 ORDER BY val;

id
val

2
-Infinity

3
Infinity

1
NaN

4
NaN

ドキュメント通りですね。違和感はあまりないかな。符号の違うNaN同士の比較はどうでしょう。一行追加してみます。

INSERT INTO example2 (id, val) VALUES (5, '-NaN');

SELECT * FROM example2 ORDER BY val;

id
val

2
-Infinity

3
Infinity

1
NaN

4
NaN

5
NaN

やはり-NaNと+NaNは区別されていないようです。


Internal

実際の内部の動きは、ざっとpostgres/src/backend/utils/adt/numeric.cを読んだ感じだと、NaNの区別は一切されていないようです。そもそもNaNを格納するのに、2Byteで1100 0000 0000 0000 と短かく符号化しているので、±などの情報は落ちているのでしょう。

コードはGithubにもミラーされています。


Outro

最初にNaNの同値比較がTrueな事を知った時は気持ち悪いなと思いましたが、そもそもNaNをDBに格納するシチュエーションというのもあまり無い9だろうし、問題になるとしたら集計操作の途中で発生する時かと思います。が、いい感じにソートしてくれるのなら別に許せるかなぁと。良し悪しは僕にはちょっと語れません。

あと、SQL標準の仕様を読もうとしたんですが、「仕様で定義されていない」という事を仕様を元に証明するのは難易度高すぎでやめました。あとISOなので参照し辛い。

ともあれ大変勉強になりました。Пока.


References


  1. PostgreSQL: Documentation: 9.4: Numeric Types

  2. Joe Celko著, ミック訳 (2013) プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに, 翔泳社

  3. IEEE Std 754-2008

  4. IEEE 754 - Wikipedia

  5. NaN - Wikipedia

  6. ECMAScript Language Specification - ECMA-262 Edition 5.1

  7. Intel 64 and IA-32 Architectures Software Developer's Manual Volume 1: Basic Architecture

  8. ゼロ除算 - Wikipedia





  1. プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに, 10章1節 p206 



  2. この他float(p)宣言もあるが、ドキュメントの数値型一覧に記載されていなかったので省略。 



  3. 0除算したら例外投げられるのは当たり前と思うかもしれないが、IEEE754では0除算例外が発生した際、qNaNや±∞を返すのがデフォルトの挙動で、オプションとして処理系での例外を定義するなどしても良いとなっている。実際調べた範囲では、RubyやJavaScriptは浮動小数点数演算での0除算は例外を投げなかった。整数は別の話。JSに整数無いけど。 



  4. ちなみに、虚数つまり $i = \sqrt { -1 }$ を使ってNaNを生成しようとしたが、sqrt(-1)とするとエラーとなった。 



  5. ちなIEEEの規格本文は有料。まぁ、ネットに落ちてはいますが。 



  6. 絶対とは言わないですけれど。 



  7. JavaScriptでparseInt(0) / parseInt(0)するとNaNになるが、parseIntが返すのは小数点以下数を切り捨てた状態の64Bit倍精度浮動小数点数(つまりDouble Precision)なので整数演算とは言えない。 



  8. ここで言う例外とはプロセッサレベルの話で、言語処理系のレベルの話ではない。なので、OSが投げたsNaN発生の例外を言語処理系が例外として扱うかどうかは実装依存。多分。自信なし。 



  9. 実際MariaDBでNaNをinsertする方法がわからなかった。0.0/0.01.0/0.0を挿入してみたが例外投げずにNULLが挿入されたたので、そもそもNaNをinsertできないのかも。MySQLは宗教上の理由で検証していない。