BETWEEN 1 AND NULLとしたときの挙動について

元ねた: [「Ruby2.6のInfiniteRangeをActiveRecordで利用した時の挙動を各RDBMSに対して調査した」](https://qiita.com/color_box/items/7fa900909745787ac821)

上記の記事では、`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 述部」](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000748.html)には次のような記述があります。

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

つまり`id BETWEEN 1 AND NULL`は`1 <= id AND id <= NULL`と同等になるわけです。ここで[IBM Knowledge Center「基本述部」](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000746.html)を確認したところ、NULLと数値を比較すると「不明」と判断されるようです。SQLは2値論理(TRUE/FALSE)ではなく、3値論理(TRUE/FALSE/UNKNOWN)を扱うというのはよく知られていますが、この「不明」というのはUNKNOWNに相当するものと思われます。

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

[IBM Knowledge Center「検索条件」](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000754.html)には真理値表が掲載されています。その真理値表を参考として、`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」](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059208.html)を見ると、次のような記述が見つかるはずです。

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