この記事は、All About Group（株式会社オールアバウト） Advent Calendar 2019 7日目の記事です。

初めての方ははじめまして、そうでない方はお久しぶりです。
NACKと申します。

今年も「毎月７日は罠（わな＝０７）の日」……と言い張ってみます。
※記念日認定されているわけではありませんので、ご注意ください。

というわけで、今年も罠の話を、昔のゲームブック風に書いてみます。
今年のお題はSQL（主な対象DBはMySQL）です。
「では、幸運を祈っています（1へ進む）」

「そういうお遊びは興味無いから！」という方は、直接罠の解説まで飛んでください。
罠の話が列挙されています。

SQLトラップ（ゲームブック風）

1

「本日は、アトラクション『SQLトラップ』のご利用、誠にありがとうございます。
　当アトラクションでは、SQLに関する謎解きをお楽しみいただけます。

　なお、当アトラクションには時間制限がございます。
　営業時間内にクリアできない場合、各部屋に設けられております非常口よりお帰りください。
　それでは、良いゲームライフを！」

扉を開けて、最初の問題に挑戦しよう。（2へ）

2

係員がいる。
「いらっしゃいませ。
　さっそくですが、あちらの窓口で、この先の『有効な部屋』に入るために必要な合鍵を受け取ってください。
　ただし、一部『有効な合言葉』が必要になる部屋がございますので、そちらのヒントも一緒に受け取っていただく必要があります」
どうやら、必要なものが過不足なく揃わないと、次の部屋への扉を開けてもらえないようだ。

roomsテーブル

id	spare_key	valid_flag
1	鉄の鍵	1
2	銅の鍵	1
3	銀の鍵	1
4	金の鍵	0

passwordsテーブル

id	room_id	hint	valid_flag
1	2	折りたたまれたメモ	1
2	3	カセットテープ	0

君は窓口にたどり着いた。
さて、どうやって受け取ろうか？

roomsテーブル、passwordsテーブルともに、検索条件として「有効なもの」を指定するなら16へ進め。
roomsテーブルは検索条件、passwordsテーブルは結合条件として「有効なもの」を指定するなら11へ進め。

-- [16へ]のクエリ
SELECT rooms.spare_key, passwords.hint FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id
WHERE rooms.valid_flag = 1 AND passwords.valid_flag = 1;

-- [11へ]のクエリ
SELECT rooms.spare_key, passwords.hint FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id AND passwords.valid_flag = 1
WHERE rooms.valid_flag = 1;

3

君は全ての道具を取得した。
係員が走ってきて「黒真珠は『持出禁止』と書いてあるでしょう！」と叱られる。
この説教は、いったいいつになったら終わるのだろうか……（14へ）。

4

扉はあっさりと開いた（15へ）。

5

ハスの花と魔女のカツラを取得できた。
君は「なんでこんなものを……？」と思うかもしれないが、ともかく、取得したものを机の上に置いて、次の部屋へ進もう（12へ）。

6

ファンファーレが鳴り響く。

「制限時間内のゴール、おめでとうございます！
　こちらがクリア認定証となります」

挑戦してくれてありがとう。
非常口ではなく、正規の出口から颯爽と帰宅しよう。

7

さわべさんのレコードはロックできた。

が、おおはなさんのレコードロックが、いつまで経っても終わらない。
おおはなさんは　しんぱいそうに　こちらをみている……（14へ）。
※解説

8

二人のレコードは無事ロックできた。
二人は君にとても感謝し、お礼に扉を開けてくれた（6へ）。

9

ハスの花だけが取得できた。
良い匂いがしてきて、君はだんだん眠くなってくる……（14へ）。
※解説

10

扉は音を立てて開いた。が、少しずつしか開かない。
果たして、通れるようになるまでどれだけかかるのか……（14へ）。
※解説

11

「はい、たしかに。では、行ってらっしゃいませ」
係員が、次の部屋への扉を開けてくれた。13へ進め。

12

看板が立っている。
「この部屋の扉を開けるためには合言葉が必要です」

最初の窓口でもらったメモを開いてみると
「合言葉は、『１時間天気』のテーブルから、2019/12/07の天気を取得するクエリです。
　テーブルは hourly_weather 、カラムは target_datetime(datetime型) 、weather（string型）の２つです。
　なお、target_datetime にはINDEXがはられているものとします。」
と書かれている。

期間指定（BETWEEN）で検索するなら4へ進め。
日時カラムの値を日付に変換し、完全一致検索するなら10へ進め。

-- [4へ]のクエリ
SELECT * FROM hourly_weather WHERE target_datetime BETWEEN '2019-12-07 00:00:00' AND '2019-12-07 23:59:59';

-- [10へ]のクエリ
SELECT * FROM hourly_weather WHERE DATE_FORMAT(target_datetime, '%Y-%m-%d') = '2019-12-07';

13

次の部屋への扉には、以下の文章が書かれている。
「メモに『持出禁止』と書かれていないと思われる道具（＝黒真珠以外）をすべて取得し、机の上に置いてください。
　なお、この機械に搭載されているデータベースは、標準SQLに準拠しています」

扉の横に自動販売機が置いてあり、中には黒真珠と魔女のカツラとハスの花が入っている。
また、それぞれの道具にメモもついている。

itemsテーブル

id	name	memo
1	黒真珠	持出禁止
2	魔女のカツラ	NULL
3	ハスの花	良いにおいがする

自動販売機には赤・青・黄色のボタンが付いており、それぞれのボタンの下にクエリが書いてある。
どうやら、そのクエリで取得可能な道具が出てくるようだ。

-- 「赤いボタン」のクエリ
SELECT * FROM items;

-- 「青いボタン」のクエリ
SELECT * FROM items WHERE memo <> '持出禁止';

-- 「黄色いボタン」のクエリ
SELECT * FROM items WHERE memo <> '持出禁止' OR memo IS NULL;

君は、どのボタンを押しても良い。

赤いボタンを押すなら3へ進め。
青いボタンを押すなら9へ進め。
黄色いボタンを押すなら5へ進め。

14

……音楽とともに、館内放送が聞こえてくる。

「当施設はただいまをもちまして、本日の営業を終了させていただきます。
　本日のご来訪、誠にありがとうございました。
　またのご利用を心よりお待ちいたしております」

今日のところは、非常口から帰宅したまえ。
そして、いつの日か再び挑戦してほしい。

15

部屋に入ると、二人の係員が悩んでいる。

話を聞いてみると、二人とも、急いでusersテーブルの、自分のレコードを更新したいらしい。
ただし、更新作業中、自分自身のレコードを、名前（nameカラム）でロックしておきたいとのこと（二人の名前は「さわべ」と「おおはな」というそうだ）。
なお、usersテーブルのプライマリキーはidで、INDEXは何もはられていないものとする。

さて、どうしようか？

各自の端末で、レコードをロックするなら7へ

-- さわべさんの端末で実行するクエリ
BEGIN;
SELECT * FROM users WHERE name = 'さわべ' FOR UPDATE;

-- おおはなさんの端末で実行するクエリ
BEGIN;
SELECT * FROM users WHERE name = 'おおはな' FOR UPDATE;

nameにINDEXをはってから、各自の端末でレコードをロックするなら8へ

-- nameにINDEXをはる
ALTER TABLE users ADD INDEX name(name);

-- さわべさんの端末で実行するクエリ
BEGIN;
SELECT * FROM users WHERE name = 'さわべ' FOR UPDATE;

-- おおはなさんの端末で実行するクエリ
BEGIN;
SELECT * FROM users WHERE name = 'おおはな' FOR UPDATE;

16

「鍵が足りませんね……。もう一度受け取りに行ってください」

再度受け取りに行こうとしたが、窓口には長い行列ができている。
君は並んで待つことにする……（14へ）。
※解説

罠の解説

外部結合が内部結合に？

テーブルを外部結合する場合、結合対象のテーブルにレコードが無い場合、そのテーブルのレコードはすべてNULLになります。
※確認用に、取得項目として各テーブルの「有効フラグ」を追加しています。

全ての部屋の合鍵＆有効フラグ、それに紐付く合い言葉のヒント＆有効フラグを取得するクエリ

SELECT rooms.spare_key, rooms.valid_flag AS room_valid_flag, passwords.hint, passwords.valid_flag AS password_valid_flag FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id;

spare_key	room_valid_flag	hint	password_valid_flag
鉄の鍵	1	NULL	NULL
銅の鍵	1	折りたたまれたメモ	1
銀の鍵	1	カセットテープ	0
金の鍵	0	NULL	NULL

そのため、「外部結合したテーブルのカラム」をWHERE句で使用し、かつ、NULL以外の値を指定すると、条件に合致しない→取得対象外になります。
つまり、外部結合したはずなのに、内部結合したような結果が返ってきてしまうのです。

上記のクエリにWHERE句で「有効かどうか」を追加したクエリ

SELECT rooms.spare_key, rooms.valid_flag AS room_valid_flag, passwords.hint, passwords.valid_flag AS password_valid_flag FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id
WHERE rooms.valid_flag = 1 AND passwords.valid_flag = 1;

spare_key	room_valid_flag	hint	password_valid_flag
銅の鍵	1	折りたたまれたメモ	1

これを避け、意図した外部結合をさせるためには、外部結合したテーブルのカラムを条件に追加する場合、WHERE句ではなくON句に追加すればOKです。

SELECT rooms.spare_key, rooms.valid_flag AS room_valid_flag, passwords.hint, passwords.valid_flag AS password_valid_flag FROM rooms
LEFT JOIN passwords
ON rooms.id = passwords.room_id AND passwords.valid_flag = 1
WHERE rooms.valid_flag = 1;

spare_key	room_valid_flag	hint	password_valid_flag
鉄の鍵	1	NULL	NULL
銅の鍵	1	折りたたまれたメモ	1
銀の鍵	1	NULL	NULL

NULLの扱い

標準SQLにおいて、NULLは決まった値ではありません。NULLは「値が不定、不明」ものとして扱われます。
※一般的なプログラミング言語の「NULL」とは位置づけが異なります（一般的なプログラミング言語は2値論理、SQLは3値論理を使用しています）。
※これはあくまで「標準SQLにおいて」の位置付けです。PostgreSQL等、一部のデータベースでは、設定次第で別の挙動をすることもありますのでご注意ください。

値が不定、不明ということは、「〇〇と一致しないこと」も確約できないため

SELECT * FROM items WHERE memo <> '持出禁止';

と書いても、memoがNULLのデータは取得対象外になります。

なお、同じ理由で

SELECT * FROM items WHERE memo = NULL;

と書いても、itemsがNULLのデータは取得できません。

NULLのデータも扱うためには、専用の構文 hoge IS NULL を使う必要があります。

memoが'持出禁止'ではない（と思われる）全ての道具を取得するクエリ

SELECT * FROM items WHERE memo <> '持出禁止' OR memo IS NULL;

詳しいことが知りたい方は

http://mickindex.sakura.ne.jp/database/db_3vl.html
https://codezine.jp/article/detail/532 (注：2ページ目以降は要会員登録）

をどうぞ。

INDEXと関数と

普通のINDEXは、あくまで「今、そのカラムに格納されている値」に対して目次を作ります。
そして、その目次を使って検索等を行うため、実行速度が早くなるのです。
INDEXが張られたカラムに関数や式を適用してしまうと、「関数が適用された値」の目次は作られていないためINDEXが使えません。
そのため、実行速度が遅くなります。

なお、関数や式に対してもINDEXが使いたい場合は式インデックスを使いましょう。
ただ、式インデックスは、どのDBでもサポートしているわけではありません。
MySQLの場合は、8.0.13からサポートされたそうです。

https://yoku0825.blogspot.com/2018/10/mysql-8013_25.html

行ロック？表ロック？

MySQLの場合、行ロックをする場合、ロック条件のカラムにINDEXが張られている必要があります。

https://qiita.com/kinpira/items/f4606c4a19cfbe82c607

なお、ロックについては、他にも罠がたくさんあります……。
実は今年のゲームブック、「ロックトラップ」と「SQLトラップ」のどちらにしようかと悩みました。

罠の解説（Extra Stage）

ゲームブックには入れませんでしたが、Extra StageとしてDB違いによるトラップを掲載しておきます。

ISNULLという関数

こちら、同じ関数名なのに、「MySQL」と「SQLServer」で挙動が全く違います。

MySQLの場合

SELECT ISNULL(NULL);      -- TRUE
SELECT ISNULL(9);         -- FALSE
SELECT ISNULL(NULL, 9);   -- 構文エラー

SQLServerの場合

SELECT ISNULL(NULL);      -- 構文エラー
SELECT ISNULL(9);         -- 構文エラー
SELECT ISNULL(NULL, 9);   -- 9

MySQLのISNULLは「引数がNULLかどうか判定し、判定結果（boolean）を返すもの」です。
SQLServerのISNULLは「１つめの引数がNULLの場合に、２つめの引数の値を返すもの」です。

なお、「SQLServerのISNULLに該当する関数」は、DBによって異なります。

Nullを9に置換するクエリ（DB別）

-- SQL Server
SELECT ISNULL(NULL, 9);
-- MySQL・SQLite
SELECT IFNULL(NULL, 9);
-- MySQL・PostgreSQL
SELECT COALESCE(NULL, 9);
-- Oracle
SELECT NVL(NULL, 9);

なお、上の例でIFNULLという関数をご紹介していますが、これとは別にNULLIFという関数もあります（名前が似ているだけで、全く別の関数です）。
ISNULLとIFNULLとNULLIFがあって、しかもISNULLはDBによって挙動が違っていて……なかなかのトラップだと思います。

（ゲームブック部分の）参考文献

盗賊都市-アドベンチャーゲームブック (5)