元ねた: 「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という値が業務上どのような意味合いを持つのかをよく考え、それをプログラマやオペレータに周知徹底する必要があると思います。