元ねた: 「Ruby2.6のInfiniteRangeをActiveRecordで利用した時の挙動を各RDBMSに対して調査した」

上記の記事では、SELECT * FROM users WHERE id BETWEEN 1 and nullというようなSQLがどのような挙動を示すのかを4つのRDBMSで調査されており、結果としては4つのRDBMSすべてでEmpty Setとなったようです。__記事を興味深く読む中で、ふと気になったのがDb2ではどのような挙動になるのかということ。__そこで「Db2において、betweenに指定する2つの値のうち、片方をNULLにした場合、どのようにふるまうのか」を簡単に調査して、まとめたものが本記事になります。

SQLの実行環境としてはIBM Db2 on Cloud (Entry Plan)を利用しました。2019/01/20現在、IBM Db2 on CloudのDb2バージョンは以下の通りになります。

SQL> SELECT SERVICE_LEVEL, FIXPACK_NUM FROM SYSIBMADM.ENV_INST_INFO;

SERVICE_LEVEL                                      FIXPACK_NUM
-------------------------------------------------- -----------
DB2 v11.1.3.3                                                3

__さてIBM Db2 on Cloudにおいて、以下のようなSQLを実行したところ、SELECT * FROM users WHERE id BETWEEN 1 AND nullの結果セットはEmptyでした。__つまり「元ネタ」の記事で検証していた4つのRDBMSと同様の挙動をDb2は示すらしいということが分かりました。

SQL> CREATE TABLE users (id INTEGER, name VARCHAR(100));

DB250000I: The command completed successfully.

SQL> INSERT INTO users VALUES (NULL, 'Alice'), (0, 'Bob'), (1, 'Charlie'), (2, 'Dave');

DB250000I: The command completed successfully.

SQL> SELECT * FROM users;

         ID NAME
----------- --------------------------------------------------
            Alice
          0 Bob
          1 Charlie
          2 Dave

SQL> SELECT * FROM users WHERE id BETWEEN 1 AND NULL;

No rows were retrieved.

注意: ここからは筆者の推測と憶測が中心になります。 IBM Knowledge Center「BETWEEN 述部」には次のような記述があります。

次の BETWEEN 述部は、
value1 BETWEEN value2 AND value3
次の検索条件と同等です。
value1 >= value2 AND value1 <= value3

つまりid BETWEEN 1 AND NULLは1 <= id AND id <= NULLと同等になるわけです。ここでIBM Knowledge Center「基本述部」を確認したところ、NULLと数値を比較すると「不明」と判断されるようです。SQLは2値論理(TRUE/FALSE)ではなく、3値論理(TRUE/FALSE/UNKNOWN)を扱うというのはよく知られていますが、この「不明」というのはUNKNOWNに相当するものと思われます。

述部のオペランドが expression として指定されている場合、両方の式のデータ・タイプは比較可能なタイプでなければなりません。一方のオペランドの値が NULL 値の場合、述部の結果は不明です。それ以外の場合の結果は、真または偽のいずれかになります。

IBM Knowledge Center「検索条件」には真理値表が掲載されています。その真理値表を参考として、1 <= id AND id <= NULLがどのような真理値を返すかをまとめたものが以下の表です。

id	name	1 <= id	id <= NULL	1 <= id AND id <= NULL
NULL	Alice	不明	不明	不明
0	Bob	偽	不明	偽
1	Charlie	真	不明	不明
2	Dave	真	不明	不明

この表からわかるのはid BETWEEN 1 AND NULL(=1 <= id AND id <= NULL)は「偽ないし不明」として評価されるということです。「id BETWEEN 1 AND NULLが真を返すことはない」と言い換えてもいいかもしれません。

ここでWHERE節について書かれたIBM Knowledge Center「where-clause」を見ると、次のような記述が見つかるはずです。

WHERE 節は、 search-condition (検索条件) が真である R の行で構成される中間結果表を指定します。 R は、その副選択の FROM 節の結果です。

繰り返すようにid BETWEEN 1 AND NULLの結果値は不明か偽のどちらかです。対してWHERE節は検索条件が真のものだけをフィルタリングしようとするため、SELECT * FROM users WHERE id BETWEEN 1 AND NULLというクエリの結果が0行になるのだと思われます。

【参考1】1 <= id, id <= NULL, 1 <= id AND id <= NULLおよびid BETWEEN 1 AND NULLおよびがどのように評価されるのかについては、次のようなSQLで確認することができます。確かにid BETWEEN 1 AND NULLは偽もしくは不明になっていますね。

SQL> SELECT id, cast(name as varchar(10)), (1 <= id), (id <= NULL), (1 <= id AND id <= NULL), (id BETWEEN 1 AND NULL) FROM users;

         ID 2          3     4     5     6
----------- ---------- ----- ----- ----- -----
            Alice
          0 Bob        false       false false
          1 Charlie    true
          2 Dave       true

【参考2】WHERE節の性質、すなわち「結果を返すのは検索条件がTRUEのカラムだけで、FALSEやUNKNOWNの場合は返さない」という性質は、たとえば次のようなSQLで確認することができます。

SQL> SELECT * FROM users WHERE TRUE;

         ID NAME
----------- --------------------------------------------------
            Alice
          0 Bob
          1 Charlie
          2 Dave

SQL> SELECT * FROM users WHERE FALSE;

No rows were retrieved.

SQL> SELECT * FROM users WHERE NULL;

No rows were retrieved.

以上の挙動はDb2特有のものというよりは、DBMSに共通的なもののような気がします。ただ、自分が標準SQLやデータベース理論あるいは集合論などにまったくうといため、明言は避けます(´・ω・｀) ふだんからSQLやDBを雰囲気で使っていることがばれますね(反省) SQLやDBに詳しい人が教えてくれる--はず(´・ω・｀)

BETWEEN 1 AND NULLというのは極端な例ですが、概してNULL周りの挙動はトラブルのもとです。DBMSごとに挙動が変わったり、JOINする際に考えることが増えたり、あるいはインデックスの効かないSQLが出来上がったりします。そもそも現実のアプリケーション構築において、DBのカラムにNULLを持たせるような設計になっていた場合、正規化が不足していたり、ドメインの理解がおろそかになっていたりする可能性があります。仮にNULLを使うのであれば、そのカラムにおいてNULLという値が業務上どのような意味合いを持つのかをよく考え、それをプログラマやオペレータに周知徹底する必要があると思います。