VTRyo Blog

一歩ずつ前に進むブログ

うちのオカンがね、好きなSQLアンチパターンの名前を忘れてしまったらしいねん

A「うちのオカンがね、好きなSQLアンチパターンがあるらしいんやけど、その名前を忘れてしまったらしくてね。色々聞くんやけどな、全然わからへんねん」

Bill「おー。ほな俺がね、オカンの好きなSQLアンチパターン一緒に考えるから、どんな特徴言ってたか教えてー」

A「first_nameテーブルとlast_nameテーブルがあって、新規で作成したmiddle_nameテーブルの値を文字列連結したら、middle_nameに値のないユーザのフルネームを返さなくなるらしいねん」

Bill「おー……『恐怖のunknown』やないかい。その特徴は完全に『恐怖のunknown』やがなすぐわかったよ!」

A「俺もそう思ってんけどな。オカンが言うには、middle_nameテーブルには空文字列は入ってるらしいねんな」

Bill「おー……ほな『恐怖のunknown』とちがうかー。
middle_nameテーブルの値に空文字列が入ってたら何かしら結果を返すもんね。 『恐怖のunknown』はね、値にNULLが入ってるから結果を返してこないのよ。データベースってそういうもんやから!
ほな『恐怖のunknown』とちゃうがな!もうちょっと教えてくれるー?」

A「なんでゼロやFALSEや空文字と挙動が違うかわからんらしいねん」

Bill「『恐怖のunknown』やないかい!
NULLの振る舞いがいまいちわかってないソフトウェア開発者が『恐怖のunknown』にぶつかったときにありがちな感想や。でもあれはね、プログラマが普段から3値論理に慣れてないからやと睨んでんのよ。
SQLには第3の値「不明」があることを理解すれば絶対に理解できる!俺の目は騙されへんよ!俺は何でもお見通しやねんから!『恐怖のunknown』やそんなもん」

A「でもわからへんねん。
俺も『恐怖のunknown』と思てんけどな、オカンが言うには「NULLは何があっても絶対に使うな!」っていうねんな」

Bill「ほな『恐怖のunknown』とちゃうやないか!
NULL使いたくないからって一般値で「unknown」て定義されてたら、ちゃぶ台ひっくり返すもんね。
NULLはね、一般値とは違う扱いで存在しているから使ってられんのよ。そういうカラクリやから。
ほなもうちょっと何か言ってなかったー?」

A「この値を検索するときは「IS NULL」を使ったり、そもそもNOT NULLを使って制約をつけたりすることでアンチパターンを回避したりするらしいねん」

Bill「ほな『恐怖のunknown』やないかい!
さっきオカンが『NULLは何があっても使うな』って言ってたのはどこいったんや。
でもNULLの検索に関してはそうや。ある値とNULLを比較してもTRUEを返さへんからね。あとは列にNOT NULLを使うことで意味をなさないNULLについてはそもそも使わせない方法が一番や。
『恐怖のunknown』やこんなもん、なんでわからへんのよこれで!『恐怖のunknown』で決まり!
オカンの好きなSQLアンチパターンは『恐怖のunknown』で決まり!」

A「でもな、オカンが言うには、『恐怖のunknown』ではないっていうねん」

Bill「ほな『恐怖のunknown』とちゃうやないかい!!オカンが『恐怖のunknown』ではないと言うんやから、『恐怖のunknown』ちゃうがな!先言えよ!!
俺がNULL検索について語ってるときどう思っててんお前」

A「いや申し訳ない。んでオトンが言うにはな」

Bill「オトン?」

A「ファントムファイルちゃうか?って」

Bill「いや絶対ちゃうやろー。もうええわ」

*1

フィア・オブ・ジ・アンノウン

SQLアンチパターン第13章。

このようなSQLでユーザのフルネームを一列に整形していたとする。

SELECT first_name || ' ' || last_name AS full_name FROM Accounts;

ここに、ユーザのミドルネームのイニシャルをテーブルに追加して欲しいという要望が入ったとする。 テーブルを作成し、すでにいるユーザに対して手作業でイニシャルを追加した。

ALTER TABLE Accounts ADD COLUMN middle_initial CHAR(2);

UPDATE Accounts SET middle_initial = 'J.' WHERE account_id = 123;
UPDATE Accounts SET middle_initial = 'C.' WHERE account_id = 321;

SELECT first_name || ' ' || middle_initial || ' ' || last_name AS full_name FROM Accounts;

しかし、アプリケーションが返してきた結果はどこか違和感があった。 どうやら、一部のデータを表示していない。 ミドルネームのイニシャルを入れていないユーザだけがおかしい。

NULLの扱い

データベースに値がない(NULL)状況はいくらでもある。 ミドルネームがない人がいるように、住所においてマンション名が必要ないなど様々だ。

先程のクエリでは、NULLのある列へのクエリの書き方がまちがっていることになる。

その前に、このように開発者を苦しめる可能性のあるNULLの必要性とは何か。

  • 行の作成時点では不明な値にNULLを使用できる(在籍中従業員の退職日、住所におけるマンション名など)
  • ある行において適用可能(applicable)な値が他にない場合にNULLを使用できる(異世界転生したときの俺のチートスキルは?などわかりようがないもの)
  • 無効な値が入力された場合にNULLを使用できる('2020-12-40'など)
  • 外部結合は一致しないテーブルの列のためにNULLをプレースホルダ(実際の内容を後から挿入するために、とりあえず仮に確保した場所)として使用する

このように『今はわからんけど後で入力される可能性がある』場合か『絶対にわからない or 存在しない』場合にNULLを使用できるということだ。

アンチパターン:NULLを一般値として使う、または一般値をNULLとして使う

NULLはゼロ、FALSE、空文字列とは違う。これらはどれも一般値である。NULLはNULLでしかない。 特殊な値である。

NULLがゼロとは違うことを示すには、このような例がある。

  • 年齢不詳(NULL)の男性と53歳の男性ではどちらが年上ですか→年齢がわからない人と53歳(NULLと53)を比較しても答えは出ない
  • 年齢不詳(NULL)の男性と年齢不詳(NULL)の男性はどちらが年齢が上ですか→年齢がわからない人と年齢がわからない人(NULLとNULL)を比較しても答えは出ない

人間のゼロ歳はありえるため、比較対象になる。

しかし、わからない値(NULL)に対して何を比較しても結果はわからないままである。 比較しようがないということだ。

NULLの使用を避ける

NULLの扱いが複雑だからといって、一切つかわないように設計すると面倒なことになる。

『NULLを使用しない=適用不能、不明を意味する値が不要』というわけではない。となると、一般値でNULL同様の値を定義することになる。

不明な値を表すために-1を使用したとすると、あとでこの-1を除外させるようなクエリを書く羽目になる。 複雑化を避けようとNULLを回避したのに、これでは意味がない。

アンチパターンの見つけ方

  • A『オカンがな、ある列で値が設定されていない行をどうやって見つけたらいいか忘れたらしいねん』
    • Bill『NULLの行を見つけるために等価演算子は使えへんのよ』
  • A『オカンがな、一部のユーザのフルネームがブランクで表示されるいうねん。俺もおかしいと思ってな。でもデータベースにはデータがあんねん』
    • Bill『NULLの文字列を連結したんやと俺は睨んでるよ』
  • A『オカンがな、unknownと表すために使ってた文字列が禁止になるっててんやわんやしてんねん。しかも新しい特殊値を何にするか決めて、コード修正せなあかんらしい』
    • Bill「いつか正当な値になりそうな値を特別なフラグ値に設定するからそうなんねん。しかもな、『特殊値はこれです』って言い伝えるのもコストになってくねん」

アンチパターンを用いてもいい場合

NULLを用いる事自体がアンチパターンではない。NULLを一般値として使用したり、一般値をNULLのように扱うことが『フィア・オブ・ジ・アンノウン(恐怖のunknown)』である。

NULLが一般値として使用されるケースは

  • 外部データの書き出し/読み込み
    • テキストファイルのカンマ区切りフィールドにおいて、値はすべてテキストになる
  • ユーザ入力
    • 自動的に空文字列をNULLに変換するConvertEmptyStringToNullというプロパティをサポートしているものもある(.NET)

解決策:NULLを一意な値として使う

  • NULLの検索にはIS NULLを使用する
  • ある列においてNULLが意味をなさない場合はNOT NULL制約を宣言する
  • COALESCE関数を使ってNULLを引数に置き換える

dev.mysql.com

おわりに

これは、社内のSQLアンチパターン勉強会に使用するための資料として作成したものである。 内容はSQLアンチパターン13章のテキストを読んで引用。

なお、冒頭に書かれている関西弁のやりとりは、ミルクボーイのコーンフレークネタが元。
この会話フレームワークは実にわかりやすくて助かる(僕はこれで覚えられる)。

知らない人はGoogle先生に「コーンフレーク ネタ」とかで検索してもらえれば。

https://www.google.com/search?q=%E3%82%B3%E3%83%BC%E3%83%B3%E3%83%95%E3%83%AC%E3%83%BC%E3%82%AF+%E3%83%8D%E3%82%BF&oq=%E3%82%B3%E3%83%BC%E3%83%B3%E3%83%95%E3%83%AC%E3%83%BC%E3%82%AF&aqs=chrome.1.69i57j0l7.2887j0j1&sourceid=chrome&ie=UTF-8

ではこれにて。

*1:拙い文字起こしお許しください