この記事は、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がはられているものとします。」
と書かれている。
-- [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;
君は、どのボタンを押しても良い。
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以外の値を指定すると、条件に合致しない→取得対象外になります。
つまり、外部結合したはずなのに、内部結合したような結果が返ってきてしまうのです。
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 を使う必要があります。
SELECT * FROM items WHERE memo <> '持出禁止' OR memo IS NULL;
詳しいことが知りたい方は
-
https://codezine.jp/article/detail/532 (注：2ページ目以降は要会員登録）
をどうぞ。
INDEXと関数と
普通のINDEXは、あくまで「今、そのカラムに格納されている値」に対して目次を作ります。
そして、その目次を使って検索等を行うため、実行速度が早くなるのです。
INDEXが張られたカラムに関数や式を適用してしまうと、「関数が適用された値」の目次は作られていないためINDEXが使えません。
そのため、実行速度が遅くなります。
なお、関数や式に対してもINDEXが使いたい場合は式インデックスを使いましょう。
ただ、式インデックスは、どのDBでもサポートしているわけではありません。
MySQLの場合は、8.0.13からサポートされたそうです。
行ロック？表ロック？
MySQLの場合、行ロックをする場合、ロック条件のカラムにINDEXが張られている必要があります。
なお、ロックについては、他にも罠がたくさんあります……。
実は今年のゲームブック、「ロックトラップ」と「SQLトラップ」のどちらにしようかと悩みました。
罠の解説（Extra Stage）
ゲームブックには入れませんでしたが、Extra StageとしてDB違いによるトラップを掲載しておきます。
ISNULLという関数
こちら、同じ関数名なのに、「MySQL」と「SQLServer」で挙動が全く違います。
SELECT ISNULL(NULL); -- TRUE
SELECT ISNULL(9); -- FALSE
SELECT ISNULL(NULL, 9); -- 構文エラー
SELECT ISNULL(NULL); -- 構文エラー
SELECT ISNULL(9); -- 構文エラー
SELECT ISNULL(NULL, 9); -- 9
- MySQLの
ISNULLは「引数がNULLかどうか判定し、判定結果（boolean）を返すもの」です。 - SQLServerの
ISNULLは「１つめの引数がNULLの場合に、２つめの引数の値を返すもの」です。
なお、「SQLServerのISNULLに該当する関数」は、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によって挙動が違っていて……なかなかのトラップだと思います。