NULL許容カラムに対するWHERE句の作りが甘かった話
概要
タスク管理システムのビュー改修作業において、NULL許容カラムに対するWHERE句の作りが甘く、条件式の書き方次第で「常に除外される」「常に出力される」のどちらにもなり得る状態だった。
たまたま要件上問題にならなかったが、三値論理とOR/ANDの組み合わせで想定外の挙動になる典型的なパターンだった。
環境
- Oracle 11
前提
タスク管理システムでは、以下のようにデータが格納されていた。
- タスクには「作業中(ステータス=1)」と「完了済み(ステータス=0)」がある
- 完了済みのタスクには「完了日」が記録されている
そこで、
- パラメータで「作業中を含むか」「完了日の期間」を指定して抽出したい
という要望があったため、以下のようなテーブルとビューを作成した。
パラメータテーブル
CREATE TABLE ビューパラメータ (
ビューID NUMBER,
作業中含む NUMBER(1), -- 0 or 1 1なら作業中のものを含む
完了日開始 DATE,
完了日終了 DATE
);
実装したビュー
CREATE OR REPLACE VIEW タスクビュー AS
SELECT
t.*
FROM タスクテーブル t
LEFT JOIN ビューパラメータ p ON p.ビューID = 1
WHERE
(t.ステータス = 0 OR p.作業中含む = 1)
AND (t.ステータス = 1 OR t.完了日 BETWEEN p.完了日開始 AND p.完了日終了);
WHERE句の意図:
- 1つ目: 完了済みデータ、または作業中を含む設定なら通過
- 2つ目: 作業中データ、または完了日が期間内なら通過
何が問題だったか
NULL値での挙動
完了日はNULL許容カラムだった。
実際のデータには、キャンセルされたタスクが「ステータス=0(完了済み)、完了日=NULL」で登録されていた。
業務上は「キャンセルは作業不要なので完了扱いだが、実際には完了していないのでNULL」という運用。
WHERE句を書く際、この完了日=NULLのケースを全く考慮していなかった。
(補足)三値論理について
NULL値を含む比較演算はUNKNOWNを返す。WHERE句はTRUEのみ通過、UNKNOWNは除外される。
NULL = 1 → UNKNOWN
NULL BETWEEN DATE '2024-01-01' AND DATE '2024-12-31' → UNKNOWN
実際の挙動
ステータス=0、完了日=NULL のデータに対して評価してみる。
ここでは、すべてのデータにマッチするようにパラメータテーブルでは以下のように設定する。
- 「作業中含む」=1 (作業中、完了すべてのデータ)
- 「完了日開始」= 1900/1/1,「完了日終了」= 2999/12/31
1つ目の条件: (t.ステータス = 0 OR p.作業中含む = 1)
→ (TRUE OR TRUE)
→ TRUE
2つ目の条件: (t.ステータス = 1 OR t.完了日 BETWEEN 1900/1/1 AND 2999/12/31)
→ (FALSE OR UNKNOWN)
→ UNKNOWN
WHERE句全体: TRUE AND UNKNOWN
→ UNKNOWN
結果: 除外される
今回はたまたま「キャンセルタスクは不要」という要件だったので問題にならなかったが、ビューを通すことでどうやっても抽出できないデータが発生するということに気付く必要があった。
正しい実装
NULL許容カラムに対しては、IS (NOT) NULLの明示的なハンドリングが必要となる。
WHERE
(t.ステータス = 0 OR p.作業中含む = 1)
AND (
t.ステータス = 1
OR (t.完了日 IS NOT NULL AND t.完了日 BETWEEN p.完了日開始 AND p.完了日終了)
);
IS (NOT) NULLを追加することで、UNKNOWNではなくFALSEが返ることになる。今回のケースでは同じ結果となるが、少なくともSQL上で明示的にどう扱うべきかを表明しておくことでメンテナンスコストを下げることができる。
反省点
1. NULL許容カラムの扱いが甘かった
完了日がNULL許容だと分かっていたのに、WHERE句でNULLのケースを考慮していなかった。
「NULLはないだろう」という思い込みが問題。
2. テストデータにNULLパターンがなかった
以下のパターンをテストすべきだった:
- 完了日に値が入っているケース
- 完了日がNULLのケース
- 期間の境界値
まとめ
NULL許容カラムに対するWHERE句では:
- 必ずIS NULL/IS NOT NULLでの明示的なハンドリングを行う
- 複雑な条件式では、三値論理の評価をパターンごとに確認する
- テストデータにNULLパターンを含める
「たまたま問題にならなかった」は単なる幸運。WHERE句の作りが甘いと、条件次第で真逆の結果になる。
あぶな。
Discussion