こんにちは！
ourly株式会社 執行役員CTO(@tigers_loveng)の相澤です。
最近APIの開発をしていて、データ取得のコードを書いていた際に思いがけずNULLの扱いにハマってしまったことがあり、軽くその内容を書こうと思います。
「SQLの3値論理(三値ロジック)」をきちんと意識していればコードを書いてる時点で気づけたかもしれませんが、すぐに気づくことができなかったのもあり、ちゃんと理解しようと思った次第です。
問題です！
以下のようなusersテーブルがあるとします。
| id | name | age |
|---|---|---|
| 1 | 田中 | 30 |
| 2 | 田中 | NULL |
| 3 | 鈴木 | NULL |
| 4 | NULL | 20 |
| 5 | NULL | NULL |
ActiveRecordを継承したUserモデルで、「nameが田中の時、ageがNULLの行だけ除外したい」という条件にあてはまるデータ取得のコードを書いてみてください。
素直に書くとこんな条件になりそうですよね。
SELECT
*
FROM
users
WHERE
NOT(name = '田中' AND age IS NULL)
User.where.not(name: '田中', age: nil)
nameが田中 かつ ageがNULL の行以外なので、WHERE NOTの条件にそれぞれを渡せば、先ほどのテーブルのid = 2以外が取れそうです。
同じように思った方は、ぜひ最後まで読んでください。
そうでなかった方は(いいねだけして)そっと閉じていただいて大丈夫です。
こちら実行すると以下の3行しか取得されません。両方NULLの行どこいった...！？
[#<User:0x0000ffff9573fb00 id: 1, name: "田中", age: 30>,
#<User:0x0000ffff9573fa38 id: 3, name: "鈴木", age: nil>,
#<User:0x0000ffff9573f970 id: 4, name: nil, age: 20>]
SQLの3値論理(三値ロジック)
SQLでは論理評価がTRUE / FALSE / UNKNOWNの3段階で行われます。
NULLが絡むと、比較結果がTRUEでもFALSEでもなくUNKNOWNになる可能性があるんですよね。
そして、WHERE句ではTRUEだけが残り、FALSEやUNKNOWNは除外されるという点に注意が必要です。
20年近く前の記事になりますがなぜDBに3値論理が適用されたかの歴史的背景なども踏まえて解説してくれているので参考になります。
また、3値の組み合わせとAND/ORの結果が最終的にTRUE/FALSEのどちらになるかはPostgreSQLの公式ドキュメントに記載があるので、そちらを引用させていただきます。
図1
| No | a | b | a AND b | a OR b |
|---|---|---|---|---|
| 1 | TRUE | TRUE | TRUE | TRUE |
| 2 | TRUE | FALSE | FALSE | TRUE |
| 3 | TRUE | NULL | NULL | TRUE |
| 4 | FALSE | FALSE | FALSE | FALSE |
| 5 | FALSE | NULL | FALSE | NULL |
| 6 | NULL | NULL | NULL | NULL |
図2
| No | a | NOT a |
|---|---|---|
| 1 | TRUE | FALSE |
| 2 | FALSE | TRUE |
| 3 | NULL | NULL |
今回のケースだと
SELECT
*
FROM
users
WHERE
NOT(name = '田中' AND age IS NULL)
User.where.not(name: '田中', age: nil)
は、「nameが田中 かつ ageがNULL」の行だけを除外したいという意図ですが、実際には
-
nameがNULLの行は、name = '田中'の評価結果はUNKNOWNになる -
ageがNULLの行は、age IS NULLの評価結果はTRUEになる
この2つがANDで結合すると「UNKNOWN AND TRUE」→ UNKNOWN となってしまい、さらにNOTをかけてもUNKNOWNのままです。（図1のNo.3のa AND bのケースと図2のNo.3のNOT aのケースの組み合わせ）
結果、WHERE句の評価ではUNKNOWNはFALSEと同じように扱われるので、両方NULLの行も除外されてしまうというわけです。
対処法
その1：条件を分解してOR条件で救済する
泥臭いやり方としては、両方NULLの場合は明示的に残すという方針でORを足してあげる、という方法があります。
SELECT
*
FROM
users
WHERE
NOT(name = '田中' AND age IS NULL) OR (name IS NULL AND age IS NULL)
User.where.not(name: '田中', age: nil).or(User.where(name: nil, age: nil))
[#<User:0x0000ffff9e253110 id: 1, name: "田中", age: 30>,
#<User:0x0000ffff9e253048 id: 3, name: "鈴木", age: nil>,
#<User:0x0000ffff9e252f80 id: 4, name: nil, age: 20>,
#<User:0x0000ffff9e252eb8 id: 5, name: nil, age: nil>]
のように書けば、「nameが田中 かつ ageがNULL」の行は除外しつつ、nameもageもNULLの場合はorでチェーンした方の条件に引っかかるので抽出可能というわけです。
ロジックが増えると可読性は落ちがちですが、シンプルに「特定ケースだけ後から抽出する」という発想です。
その2：IS DISTINCT FROMを使う
PostgreSQLなら、NULLを通常の値として扱うための演算子としてIS [NOT] DISTINCT FROMがあります(一応SQL標準（ISO/IEC 9075）では定義されているという記述はChatGPTやGoogle検索の結果では見つかりましたがソース見つけられずなのでPostgreSQLに限定して書いております)
例えば今回の条件の中で、name = '田中'がNULLになってほしくない（NULLは「'田中'と違う」として評価してほしい）場合には、以下のように書けばOKです。
SELECT
*
FROM
users
WHERE
NOT(name IS NOT DISTINCT FROM '田中' AND age IS NULL)
User.where.not("name IS NOT DISTINCT FROM '田中' AND age IS NULL")
すると、nameがNULLの場合でも「田中とは異なる値（＝FALSE）」と確定してくれるので、三値論理でUNKNOWNにならずに済みます。
これで「nameが'田中'かつage`がNULL」の行だけをきちんと除外し、両方NULLの行はちゃんと残ります。
そもそもNULL値を入れなければ良いのでは？
そもそも論ですが、NULL値を入れないように設計したら？という意見もあるかと思います。
NULL値が入りうるカラムは中間テーブルで切り出すなどして、テーブル内にNULL値を入れないようにするということです。
ですが、やり過ぎるとデータ取得時にJOINするテーブルの量が増えてパフォーマンス面での懸念が出てきたり、LEFT JOINする場合も考えるとどちらにせよNULL値の考慮が必要だったり、対処法にはなり得ないと思ったので含めませんでした。
理想論としてNULL値がない方が良い、は分かりますがビジネス要件や仕様を踏まえるとNULL許可せざるを得ない時もあり、無理やりNULL排除するのは違うと考えています。
まとめ
今回のポイントは、SQLの3値論理ではNULLが絡むと比較結果がUNKNOWNになる → WHERE句でUNKNOWNはFALSE扱い → 意図せず行が除外されてしまう、という流れです。
NULLセーフに書きたいときは
- OR条件などでNULLケースを抽出する
-
IS DISTINCT FROMを活用してNULLを普通の値として扱う
などの方法が実用的です。
PostgreSQL以外でも、たとえばMySQLやOracleでも似たような挙動（3値論理）がベースにあるため、NULLに絡む条件は常に意識しておくと良さそうです。
それでは今回はこのへんで。最後まで読んでいただき、ありがとうございました！
Discussion