勉強日記

チラ裏

SQL Antipatterns ch14 Fear of the Unknown

SQL 勉強メモ

pragprog.com

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

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

Declare Columns NOT NULL

  • NULLが入ると正しいデータが得られなくなるような場合はNON-NULL制約を
    • 例: Bugs.reported_by
      • 不具合は必ず報告者がいる
  • DEFAULTは必ずしも設定できない

Dynamic Defaults

  • あるクエリでのみ個別のデフォルト値が欲しい
    • 名前表示時のみmiddle_initialを空白にするなど
    • COALESCE(' ' || Accounts.middle_initial || ' ', ' ')

英語