【MySQL】NULL問題：条件付きクエリに「カラムA = カラムA」というコーディングをしてはならない

【環境情報・製品情報】

• MySQL(Version 8.0.21)

【事象】

「画面項目に応じて検索条件（抽出条件）を変更する」というケースは頻繁にあり、その中でも「●●の場合のみ、条件適用をする。それ以外の場合、条件は適用しない」としたいシチュエーションは往々として存在します。

とあるプロジェクトで本番稼働が始まり1年以上の月日が流れたある日。「全職員の情報が表示されるべき画面で、一部の職員情報が表示されていない」という本番障害が発生しました。

【経緯】

画面イメージと仕様は以下の通りです。

• 「主任のみ抽出」のチェックボックスがONの場合、主任の職員のみ抽出
• 「主任のみ抽出」のチェックボックスがOFFの場合、全職員情報を抽出

＜図：画面イメージ＞

職員テーブルのデータイメージは以下の通りです。

カラム「主任フラグ」はint型で、1の場合は主任と見なすデータ定義となっています。

＜図：データイメージ＞

【原因】

実装されていたクエリは以下のようなイメージです。

※実際のクエリは、他条件や他テーブルとの結合もあり300～500行近いクエリで、原因個所のみ抜粋しています。

SELECT
    Id
    ,StaffCode
    ,Name
    ,IsChief
FROM
    yh.staffinfo
WHERE
    (
        CASE 
            -- 「主任のみ抽出」がONの場合、主任フラグがONのデータのみ抽出
            WHEN @主任のみ抽出するフラグ = '1' THEN IsChief = '1'
            -- 「主任のみ抽出」がOFFの場合、条件なし
            WHEN @主任のみ抽出するフラグ = '0' THEN IsChief = IsChief
        END
    )

＜コード：障害発生時のクエリ＞

画面上で「主任のみ抽出」のチェックボックスをON/OFFにして実行した場合の結果をそれぞれ確認していきます。

• 「主任のみ抽出」をONにて実行ボタンを押下した結果
• 期待する結果：ID'1'及び'3'のデータが取得できること

＜図：「主任のみ抽出」がONの場合の取得結果＞

• 「主任のみ抽出」をOFFにて実行ボタンを押下した結果
• 期待する結果：ID1～5の全てのデータが取得できること

＜図：「主任フラグ」がOFFの場合の取得結果＞

期待する結果に反し、主任フラグ「NULL」のデータが取得出来ていませんでした。

「『カラムA = カラムA』で疑似的にTRUEにし条件を殺す」書き方は、NULLのデータは対象外（unknown）となり、いかなる場合でも「TRUE」とはならず実装者の意図に反した結果とりました。

【解決方法】

本来実装されるべきクエリ（イメージ）は以下のような通りです。

SELECT
    Id
    ,StaffCode
    ,Name
    ,IsChief
FROM
    yh.staffinfo
WHERE
    (
        CASE 
            -- 「主任のみ抽出」がONの場合、主任フラグがONのデータのみ抽出
            WHEN @主任のみ抽出するフラグ = '1' THEN IsChief = '1'
            -- 「主任のみ抽出」がOFFの場合、条件なし
            WHEN @主任のみ抽出するフラグ = '0' THEN TRUE
        END
    )

＜コード：あるべきクエリ＞

明示的に「TRUE」と記載することで、期待する結果の通りになることが出来ます。

＜図：「主任フラグ」がOFFの場合の取得結果（修正後）＞

【まとめ】

「カラムA=カラムA」という書き方は、以下の観点で望ましくありません。

• NULLに対応出来ない
• 条件を殺す（疑似的にTRUEにさせる）ことを目的とした場合、今回のようにNULLのデータが意図と反した結果となります。
• 可読性が悪い
• 仮にNULLのデータが存在しなかったとしても、可読性が悪く複雑なクエリの中に「カラムA = カラムA」を見かけると、初見時に何がしたいのか理解に時間を要すケースがあります。

時々見かける実装ですが、CASE構文内で「TRUE」が記載が可能なので完結に「TRUE」と明示しましょう。

可読性の観点から私はTRUE一択で実装していたためたまたまこのようなバグに遭遇することなく生きてきたのですが、正直このバグと出会うまでNULLが拾うことが出来ないとは正直驚きでした。

この問題を掘り下げると0/1のデータ構成の中に「NULL」が存在すること自体が更に上のレイヤーの問題として出てくるわけですが、これはまた別の機会に。