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

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


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

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

というわけで、今年も罠の話を、昔の[ゲームブック風](https://ja.wikipedia.org/wiki/%E3%82%B2%E3%83%BC%E3%83%A0%E3%83%96%E3%83%83%E3%82%AF)に書いてみます。
今年のお題はSQL（主な対象DBはMySQL）です。
「では、幸運を祈っています（[1へ](#1)進む）」

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


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

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

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

扉を開けて、最初の問題に挑戦しよう。（[2へ](#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へ](#16)進め。
- roomsテーブルは検索条件、passwordsテーブルは結合条件として「有効なもの」を指定するなら[11へ](#11)進め。


```sql:
-- [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へ](#14)）。

----

### 4
扉はあっさりと開いた（[15へ](#15)）。

----

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

----

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

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

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

----

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

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

----

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

----

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


----

### 10
扉は音を立てて開いた。が、少しずつしか開かない。
果たして、通れるようになるまでどれだけかかるのか……（[14へ](#14)）。
※[解説](#indexと関数と)

----

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

----

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

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

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

```sql
-- [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|ハスの花|良いにおいがする|

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

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

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

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

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

+ 赤いボタンを押すなら[3へ](#3)進め。
+ 青いボタンを押すなら[9へ](#9)進め。
+ 黄色いボタンを押すなら[5へ](#5)進め。

----

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

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

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

----

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

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

さて、どうしようか？

+ 各自の端末で、レコードをロックするなら[7へ](#7)

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

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

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

```sql:
-- 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へ](#14)）。
※[解説](#外部結合が内部結合に)

----



# 罠の解説
## 外部結合が内部結合に？

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

```sql:全ての部屋の合鍵＆有効フラグ、それに紐付く合い言葉のヒント＆有効フラグを取得するクエリ
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以外の値を指定すると、条件に合致しない→取得対象外になります。
つまり、外部結合したはずなのに、内部結合したような結果が返ってきてしまうのです。

```sql:上記のクエリに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です。

```sql:
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等、一部のデータベースでは、設定次第で別の挙動をすることもありますのでご注意ください。

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

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

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


なお、同じ理由で

```sql
SELECT * FROM items WHERE memo = NULL;
```

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

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

 ```sql: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が使いたい場合は[式インデックス](https://ja.wikipedia.org/wiki/%E5%BC%8F%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9)を使いましょう。
ただ、式インデックスは、どの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」で挙動が全く違います。

```sql:MySQLの場合
SELECT ISNULL(NULL);      -- TRUE
SELECT ISNULL(9);         -- FALSE
SELECT ISNULL(NULL, 9);   -- 構文エラー
```

```sql:SQLServerの場合
SELECT ISNULL(NULL);      -- 構文エラー
SELECT ISNULL(9);         -- 構文エラー
SELECT ISNULL(NULL, 9);   -- 9
```

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

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


```sql: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)](https://www.amazon.co.jp/%E7%9B%97%E8%B3%8A%E9%83%BD%E5%B8%82-%E3%82%A2%E3%83%89%E3%83%99%E3%83%B3%E3%83%81%E3%83%A3%E3%83%BC%E3%82%B2%E3%83%BC%E3%83%A0%E3%83%96%E3%83%83%E3%82%AF-5-I%E3%83%BB%E3%83%AA%E3%83%93%E3%83%B3%E3%82%B0%E3%82%B9%E3%83%88%E3%83%B3/dp/4390111485)