Fear of the Unknown

• first_name,last_nameカラムがあった
• Nullableなmiddle_initialカラムを追加した

• アプリケーション上でほとんどのユーザの名前が表示されなくなった

  • イニシャルのあるユーザだけ名前が表示される
• NULLをconcatenateしたせい

Objective: Distinguish Missing Values

• NULLが用意されている

  • 行登録次点で未定

    • 社員エンティティの「退職日」カラムとか
    • 【補】リレーショナルモデル的にはリレーションを分割するのが正しいはず

  • 適用不可能(N/A, Not Applicable)

    • 電気自動車の「燃費」とか

  • 関数への入力が不正だった場合

    • DAY('2009-12-32')とか
    • 【補】これもN/A
  • 外部結合でマッチするレコードがなかった場合

Antipattern: Use Null as an Ordinary Value, or Vice Versa

• SQLのNULLに対して警戒を怠るソフトウェア開発者多し

Using Null in Expressions

• NULLは値ではない

  • 0ではない
  • ""ではない
  • falseではない
• 値用の演算子や関数を作用させるとNULLが帰る

Searching Nullable Columns

• 値用の比較演算子は軒並みNULLを返す

  • 「unknown」なので比較結果も「わからない」(unknown)

Using Null in Query Parameters

• 値用のプリペアドステートメントにNULLを渡すとうまく動かない

SELECT * FROM Bugs WHERE assigned_to = ?;

• NULLを渡すと

WHERE assigned_to = NULL;

• こうなってしまい、意図通りに動かない

Avoiding the Issue

• NULLの代わりに「不明」「適用不能」を表す「値」を用意してしまう

  • どっちみちアプリケーション側ではspecial caseの分岐が必要になる

• 例

  • Bugs.assigned_toに-1 (未アサイン)を設定する

    • Accounts.idは1以上の整数しかとらないため-1を特別な値とすることができる

• issue

  • カラムごとに「特別な値」を変えなくてはならない

    • -1がドメインに含まれていて特別扱いできなかったりする
    • 開発者が覚えておくかドキュメント化するかしないといけない

  • FK制約を課せない

    • FK制約を課すにはAccounts.id = -1なるレコードが必要
    • 「未登録」であることを表す登録が必要というワケのわからない事態に

How to Recognize the Antipattern

• こんなのが聞こえてきたら注意

  • 「assigned_toに値が設定されていない行を探すにはどうすればよいですか？」

    • 専用のIS NULL使え

  • 「DB上にはデータがあるのに、アプリケーション上で名前が表示されなくなった」

    • おそらくNULLとのconcatenationのせい

  • 「集計結果が明らかに少ない。修正完了済の不具合の工数しか含まれていない」

    • おそらくWHERE句内の評価結果がNULLとなっている

  • 「『不明』であることを表す特別な値が使えなくなってしまった。別の値を検討しなければ」

    • NULLの代わりに値を使った結果

• NULLの取り回しに関する問題は表面化しにくい

  • エッジケースがテストをすり抜けたりする
• DB上でNULLが入りうるデータには、実際に入るものと考えたほうが良い

Legitimate Uses of the Antipattern

• NULL自体はアンチパターンではない
• NULLを値のように/値をNULLのように使用するのがアンチパターン

• NULLを値のように扱わなければならない例

  • インポート/エクスポートする場合

    • MySQLのmysqlimportでは\NでNULLを表現

  • ユーザ入力

    • 空文字列をNULLとして解釈するオプション

• 値をNULLのように扱わなければならない例

  • 「値がないこと」に種類がある場合

  • 例: 担当者

    • 未アサイン
    • アサイン後退職

Solution: Use Null as a Unique Value

• 値用の関数や演算子は軒並みNULLを返す
• 論理演算のみ3値論理

コラム: Are Nulls Relational?

• NULLはリレーショナルモデルと衝突する
• ほとんどの言語はコンピュータサイエンス理論を完璧には実装しない
• SQLは良くも悪くもNULLをサポートする

• NULLとの付き合い方を学ぶことが肝要

  • どういうときにNULLが必要か把握し、生産的にNULLを利用する

コラム: The Right Result for the Wrong Reason

SELECT * FROM Bugs WHERE assigned_to <> 'NULL';

• 'NULL'なる文字列との比較だと、偶然にも意図通りの結果が帰ってくる

  • assigned_toがNULLの場合

    • assigned_to <> 'NULL'がNULLなので結果リストには含まれない

  • assigned_toが1以上の整数値の場合

    • 'NULL'は多くのDB製品で0扱い
    • assigned_to <> 0がTRUEなので結果リストに含まれる

Searching for Null

• IS NULL, IS NOT NULL演算子を使う

• IS DISTINCT FROM演算子を使う

  • NULLを値であるかのように扱うinequality
  • MySQLでは<=>演算子

Declare Columns NOT NULL

• NULLが入ると正しいデータが得られなくなるような場合はNON-NULL制約を

  • 例: Bugs.reported_by

    • 不具合は必ず報告者がいる
• DEFAULTは必ずしも設定できない

Dynamic Defaults

• あるクエリでのみ個別のデフォルト値が欲しい

  • 名前表示時のみmiddle_initialを空白にするなど
  • COALESCE(' ' || Accounts.middle_initial || ' ', ' ')

英語

• serendipity

  • 幸運にも予想外のものを発見